PostgreSQL+Pgpool实现HA主备切换的操作

yum install -y ntpdate && ntpdate ntp1.aliyun.com

echo -e "# sync time from ntp1.aliyun.com\n5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com > /dev/null 2>&1

" >> /var/spool/cron/root # 写入定时任务，执行一次即可

su - postgres

ssh-keygen -t rsa -f /home/postgres/.ssh/id_rsa -P ""

cd ~/.ssh/

ssh-copy-id postgres@master # 三台主机执行

scp authorized_keys postgres@slave:~/.ssh # 只在master主机执行

scp authorized_keys postgres@pool:~/.ssh # 只在master主机执行

vi /etc/profile #增加以下内容

export PGHOME=/usr/pgsql-9.6/

export PGDATA=/data1/pg_data

export PGPORT=54321

export PATH=$PATH:$PGHOME/bin

# 生效

source /etc/profile

# Include the default config:

.include /usr/lib/systemd/system/postgresql-9.6.service

[Service]

Environment=PGDATA=/data1/pg_data

systemctl daemon-reload

su - postgres -c '/usr/pgsql-9.6/bin/initdb -D /data1/pg_data'

systemctl restart postgresql-9.6

systemctl enable postgresql-9.6.service

cp /data1/pg_data/pg_hba.conf{,.bak}

cat >/data1/pg_data/pg_hba.conf<<EOF

local all     all             trust

host all     all       10.0.0.11/32   trust

host all     all       10.0.0.12/32   trust

host all     all       0.0.0.0/0    md5

host all     all       ::1/128     trust

host replication  stream_replication  0.0.0.0/0    md5

EOF

#host replication  stream_replication  0.0.0.0/0    md5 为流复制用户

cp /data1/pg_data/postgresql.conf{,.bak}

cat >/data1/pg_data/postgresql.conf<<EOF

listen_addresses = '*'

port = 54321

max_connections = 256

shared_buffers = 16GB

effective_cache_size = 48GB

work_mem = 64MB

maintenance_work_mem = 2GB

min_wal_size = 2GB

max_wal_size = 4GB

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistics_target = 100

wal_level = hot_standby

wal_log_hints = on

max_wal_senders = 1

hot_standby = on

logging_collector = on

log_directory = 'pg_log'

EOF

#操作完记得重启 pg_ctl restart

listen_addresses = '*'

port = 54321

max_connections = 256

shared_buffers = 32GB

effective_cache_size = 96GB

work_mem = 128MB

maintenance_work_mem = 2GB

min_wal_size = 2GB

max_wal_size = 4GB

checkpoint_completion_target = 0.9

wal_buffers = 16MB

default_statistics_target = 100

wal_level = hot_standby

wal_log_hints = on

max_wal_senders = 1

hot_standby = on

logging_collector = on

log_directory = 'pg_log'

# Include the default config:

.include /usr/lib/systemd/system/postgresql-9.6.service

[Service]

Environment=PGDATA=/data1/pg_data

standby_mode= 'on'

primary_conninfo = 'host=master port=54321 user=stream_replication password=your_password'

restore_command = ''

recovery_target_timeline = 'latest'

# 重启PG服务

systemctl restart postgresql-9.6

systemctl enable postgresql-9.6.service

create table test (id int4, create_time timestamp (0) without time zone);

insert into test values (1, now());

select * from test;

yum install -y http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-II-release-3.6-1.noarch.rpm

yum -y install pgpool-II-pg96 pgpool-II-pg96-debuginfo pgpool-II-pg96-devel pgpool-II-pg96-extensions

systemctl enable pgpool.service #开启自动启动

useradd postgres # 环境准备时已操作

chown -R postgres.postgres /etc/pgpool-II

chown -R postgres.postgres /var/run/pgpool/

postgres=# select rolname,rolpassword from pg_authid;

   rolname  |    rolpassword   

--------------------+-------------------------------------

  pg_signal_backend |

  srcheck   | md5662c10f61b27a9ab38ce69157186b25f

  postgres   | md5d3612d57ee8d4c147cf27b11e3a0974d

  stream_replication | md59279ef6b904bc483e4f85e6d44cfc0ed

(4 rows )

# CONNECTIONS

listen_addresses = '*'

port = 54321

socket_dir = '/var/run/pgpool'

pcp_listen_addresses = '*'

pcp_port = 9898

pcp_socket_dir = '/var/run/pgpool'

# - Backend Connection Settings -

backend_hostname0 = 'master'

backend_port0 = 54321

backend_weight0 = 1

backend_data_directory0 = '/data1/pg_data'

backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'slave'

backend_port1 = 54321

backend_weight1 = 1

backend_data_directory1 = '/data1/pg_data'

backend_flag1 = 'ALLOW_TO_FAILOVER'

# - Authentication -

enable_pool_hba = on

pool_passwd = 'pool_passwd'

# FILE LOCATIONS

pid_file_name = '/var/run/pgpool/pgpool.pid'

logdir = '/data1/pg_logs'

replication_mode = off

load_balance_mode = on

master_slave_mode = on

master_slave_sub_mode = 'stream'

sr_check_period = 5

sr_check_user = 'srcheck'

sr_check_password = '123456'

sr_check_database = 'postgres'

# HEALTH CHECK 健康检查

health_check_period = 10

health_check_timeout = 20

health_check_user = 'srcheck'

health_check_password = '123456'

health_check_database = 'postgres'

# FAILOVER AND FAILBACK

failover_command = '/data1/pg_bin/failover_stream.sh %H'

vim /data1/pg_bin/failover_stream.sh

chmod 777 /data1/pg_bin/failover_stream.sh

chmod u+s /sbin/ifconfig

chmod u+s /usr/sbin

pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 & ## 启动

pgpool -m fast stop ## 关闭

#! /bin/sh

# Failover command for streaming replication.

# Arguments: $1: new master hostname.

new_master=$1

trigger_command= "$PGHOME/bin/pg_ctl promote -D $PGDATA"

# Prompte standby database .

/usr/bin/ssh -T $new_master $trigger_command

exit 0;

[postgres@pool pgpool-II]$ psql -p 54321 -h 10.0.0.13 -U srcheck -d postgres

postgres=# show pool_nodes;

  node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay

---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------

  0  | master | 54321 | up  | 0.500000 | primary | 0   | false     | 0

  1  | slave | 54321 | up  | 0.500000 | standby | 0   | true     | 0

(2 rows )

[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 9898 -n 0

[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 9898 -n 1

#详情查询命令pcp_attach_node

[postgres@master ~]$ pg_ctl stop

waiting for server to shut down.... done

server stopped

[postgres@pool ~]$ psql -p 54321 -h 10.0.0.13 -U srcheck -d postgres

psql (9.6.1)

Type "help" for help.

postgres=# show pool_nodes;

  node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay

---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------

  0  | master | 5432 | down| 0.500000 | standby | 0    | false | 0

  1  | slave  | 5432 | up  | 0.500000 | primary | 0    | true | 0

(2 rows )

[postgres@master ~]$ vim recovery.conf

standby_mode= 'on'

primary_conninfo = 'host=slave port=54321 user=stream_replication password=your_password'

restore_command = ''

recovery_target_timeline = 'latest'

#如果报时间线冲突落后，先停掉pg服务，然后执行同步时间线，否知直接看状态

[postgres@master ~]$ pg_rewind --target-pgdata=/data1/pg_data --source-server='host=slave port=54321 user=postgres dbname=postgres'

servers diverged at WAL position 0/5000098 on timeline 1

rewinding from last common checkpoint at 0/5000028 on timeline 1

Done!

# 重新启动数据库

[postgres@master ~]$ pg_ctl start

[postgres@pool ~]$ psql -p 54321 -h 10.0.0.13 -U srcheck -d postgres

postgres=# show pool_nodes;

  node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay

---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------

  0  | master | 5432 | down| 0.500000 | standby | 0    | false | 0

  1  | slave  | 5432 | up  | 0.500000 | primary | 0    | true | 0

(2 rows )

#注意虽然master已经启动了，但是还是down，需要手动将master节点添加进pgpool，master的node_id是0，所以-n 0

[postgres@pool ~]$ pcp_attach_node -d -U postgres -h pool -p 54321 -n 0

#提示输入密码，输入pcp管理密码

#查看当前状态

[postgres@pool ~]$ psql -p 54321 -h 10.0.0.13 -U srcheck -d postgres

postgres=# show pool_nodes;

  node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay

---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------

  0  | master | 5432 | up | 0.500000 | standby | 0    | false | 0

  1  | slave  | 5432 | up  | 0.500000 | primary | 0    | true | 0

(2 rows )

#! /bin/bash

# Check Master host pgpool-process

while true

do

  pgcount=$(nmap 10.0.0.11|egrep '9898|9999' |wc -l)

  if [ $pgcount -eq 2 ] ; then

   echo 'Master host pgpool is GOOD！！！' > /dev/ null 2>&1

  else

   echo -e "Master host pgpool is \033[31m BAD!!! \033[0m"

   echo -e "Master host pgpool is \033[31m BAD!!! \033[0m"

   echo -e "Master host pgpool is \033[31m BAD!!! \033[0m"

   echo -e "SYSTEM WILL DO THE SHELL : \033[34m su - postgres -c 'pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 &' \033[0m"

   su - postgres -c 'pgpool -n -d -D > /data1/pg_logs/pgpool.log 2>&1 &'

   pgport=$(netstat -lntup|egrep '9898|9999' |wc -l)

   [ $pgport -gt 0 ] && echo -e "Slave host pgpool is \033[32m RUNNING!!! \033[0m"

   exit 0

  fi

done