一、简介
实现热备切换，但是是要手动建立触发文件实现，对于一些HA场景来说，需要当主机down了后，备机自动切换，经查询资料知道pgpool-II可以实现这种功能。本文基于PG流复制基础上 ，以pgpool-II实现主备切换。
基于PGPool的双机集群如上图所示：pg主节点和备节点实现流复制热备，pgpool1，pgpool2作为中间件，将主备pg节点加入集群，实现读写分离，负载均衡和HA故障自动切换。两pgpool节点可以委托一个虚拟ip节点作为应用程序访问的地址，两节点之间通过watchdog进行监控，当pgpool1宕机时，pgpool2会自动接管虚拟ip继续对外提供不间断服务。
二、前提
在配置pgpool之前需分别在两台规划机上安装好pg数据库，且配置好了流复制环境，关于流复制配置参考之前文章：PostgreSQL流复制热备
三、节点信息
四、部署PGPool集群
#给root及postgres系统账号配置ssh免密登录（master&&slave）
[root@localhost data]# ssh-keygen -t rsa
[root@localhost data]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@master
[root@localhost data]# ssh-copy-id -i ~/.ssh/id_rsa.pub root@slave
[postgres@localhost data]$ ssh-keygen -t rsa
[postgres@localhost data]$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@master
[postgres@localhost data]$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@slave
#安装PGPool（master&&slave）
[root@localhost ~]# cd /usr/local/src/
[root@localhost src]# wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.6.0.tar.gz
[root@localhost src]# tar xf pgpool-II-3.6.0.tar.gz
[root@localhost src]#cd pgpool-II-3.6.0/
[root@localhost pgpool-II-3.6.0]# ./configure --prefix=/app/pgpool --with-pgsql=/app/postgres
[root@localhost pgpool-II-3.6.0]# make && make install
[root@localhost pgpool-II-3.6.0]# chown -R postgres.postgres /app/pgpool/ /usr/local/src/pgpool-II-3.6.0/
[root@localhost sql]# su - postgres
[postgres@slave ~]$ cd /usr/local/src/pgpool-II-3.6.0/src/sql/
[postgres@slave sql]$ make && make install
#配置pgpool（master&&slave）
#配置pgpool环境变量
[postgres@master sql]$ cd /home/postgres
[postgres@master etc]$ vim .bashrc
添加如下：
PGPOOLHOME=/app/pgpool
export PGPOOLHOME
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin:$PGPOOLHOME/bin
export PATH
[postgres@master ~]$ source .bashrc
#配置pool_hba.conf， pool_hba.conf是对登录用户进行验证的，要和pg的pg_hba.conf保持一致，要么都是trust，要么都是md5验证方式，这里采用了md5验证方式如下设置：
[postgres@master ~]$ cd /app/pgpool/etc/
[postgres@master etc]$ cp pool_hba.conf.sample pool_hba.conf
[postgres@master etc]$ vim pool_hba.conf
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 0.0.0.0/0 md5
host all all 0/0 md5
#配置pcp.conf，pcp.conf配置用于pgpool自己登陆管理使用的，一些操作pgpool的工具会要求提供密码等，配置如下
[postgres@master ~]$ cd /app/pgpool/etc/
[postgres@master etc]$ cp pcp.conf.sample pcp.conf
[postgres@master etc]$ pg_md5 postgres
e8a48653851e28c69d0506508fb27fc5
[postgres@master etc]$ vim pcp.conf
添加：
postgres:e8a48653851e28c69d0506508fb27fc5
[postgres@master etc]$ cp pgpool.conf.sample pgpool.conf
[postgres@master etc]$ pg_md5 -p -m -u postgres pool_passwd
#配置系统命令权限，配置 ifconfig, arping 执行权限 ，执行failover_stream.sh需要用到，可以让其他普通用户执行。创建两个日志文件目录
[root@localhost sql]# chmod u+s /sbin/ifconfig;chmod u+s /usr/sbin
[root@localhost sql]#mkdir /var/log/pgpool;chown -R postgres.postgres /var/log/pgpool;mkdir /var/run/pgpool;chown -R postgres.postgres /var/run/pgpool
#master配置pgpool.conf
[postgres@master ~]$ cd /app/pgpool/etc/
[postgres@master etc]$ vim pgpool.conf
# CONNECTIONS
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
# - Backend Connection Settings -
backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/app/postgres/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'slave'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/postgres/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
# FILE LOCATIONS
pid_file_name = '/app/pgpool/pgpool.pid'
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'repuser'
sr_check_password = 'repuser'
sr_check_database = 'postgres'
#------------------------------------------------------------------------------
# HEALTH CHECK 健康检查
#------------------------------------------------------------------------------
health_check_period = 10 # Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = 'postgres'
# Health check user
health_check_password = 'postgres' #数据库密码
# Password for health check user
health_check_database = 'postgres'
#必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。
#只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。
#主备切换的命令行配置
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/app/pgpool/failover_stream.sh %H '
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
# - Enabling -
use_watchdog = on
# - Watchdog communication Settings -
wd_hostname = 'master'
# Host name or IP address of this watchdog
# (change requires restart)
wd_port = 9000
# port number for watchdog service
# (change requires restart)
# - Virtual IP control Setting -
delegate_IP = 'vip'
# delegate IP address
# If this is empty, virtual IP never bring up.
# (change requires restart)
if_cmd_path = '/sbin'
# path to the directory where if_up/down_cmd exists
# (change requires restart)
if_up_cmd = 'ifconfig ens33:0 inet $_IP_$ netmask 255.255.254.0'
# startup delegate IP command
# (change requires restart)
# eth1根据现场机器改掉
if_down_cmd = 'ifconfig ens33:0 down'
# shutdown delegate IP command
# (change requires restart)
# eth1根据现场机器改掉
# -- heartbeat mode --
wd_heartbeat_port = 9694
# Port number for receiving heartbeat signal
# (change requires restart)
wd_heartbeat_keepalive = 2
# Interval time of sending heartbeat signal (sec)
# (change requires restart)
wd_heartbeat_deadtime = 30
# Deadtime interval for heartbeat signal (sec)
# (change requires restart)
heartbeat_destination0 = 'slave'
# Host name or IP address of destination 0
# for sending heartbeat signal.
# (change requires restart)
heartbeat_destination_port0 = 9694
# Port number of destination 0 for sending
# heartbeat signal. Usually this is the
# same as wd_heartbeat_port.
# (change requires restart)
heartbeat_device0 = 'ens33'
# Name of NIC device (such like 'eth0')
# used for sending/receiving heartbeat
# signal to/from destination 0.
# This works only when this is not empty
# and pgpool has root privilege.
# (change requires restart)
# eth1根据现场机器改掉
# - Other pgpool Connection Settings -
other_pgpool_hostname0 = 'slave' #对端
# Host name or IP address to connect to for other pgpool 0
# (change requires restart)
other_pgpool_port0 = 9999
# Port number for othet pgpool 0
# (change requires restart)
other_wd_port0 = 9000
# Port number for othet watchdog 0
# (change requires restart)
#slave配置pgpool.conf
[postgres@master ~]$ cd /app/pgpool/etc/
[postgres@master etc]$ vim pgpool.conf
# CONNECTIONS
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
pcp_port = 9898
# - Backend Connection Settings -
backend_hostname0 = 'master'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/app/postgres/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'slave'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/app/postgres/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = on
pool_passwd = 'pool_passwd'
# FILE LOCATIONS
pid_file_name = '/app/pgpool/pgpool.pid'
replication_mode = off
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'repuser'
sr_check_password = 'repuser'
sr_check_database = 'postgres'
#------------------------------------------------------------------------------
# HEALTH CHECK 健康检查
#------------------------------------------------------------------------------
health_check_period = 10 # Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = 'postgres'
# Health check user
health_check_password = 'postgres' #数据库密码
# Password for health check user
health_check_database = 'postgres'
#必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。
#只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。
#主备切换的命令行配置
#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = '/app/pgpool/failover_stream.sh %H '
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
# - Enabling -
use_watchdog = on
# - Watchdog communication Settings -
wd_hostname = 'slave' #本端
# Host name or IP address of this watchdog
# (change requires restart)
wd_port = 9000
# port number for watchdog service
# (change requires restart)
# - Virtual IP control Setting -
delegate_IP = 'vip'
# delegate IP address
# If this is empty, virtual IP never bring up.
# (change requires restart)
if_cmd_path = '/sbin'
# path to the directory where if_up/down_cmd exists
# (change requires restart)
if_up_cmd = 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0'
# startup delegate IP command
# (change requires restart)
# eth1根据现场机器改掉
if_down_cmd = 'ifconfig eth1:0 down'
# shutdown delegate IP command
# (change requires restart)
# eth1根据现场机器改掉
# -- heartbeat mode --
wd_heartbeat_port = 9694
# Port number for receiving heartbeat signal
# (change requires restart)
wd_heartbeat_keepalive = 2
# Interval time of sending heartbeat signal (sec)
# (change requires restart)
wd_heartbeat_deadtime = 30
# Deadtime interval for heartbeat signal (sec)
# (change requires restart)
heartbeat_destination0 = 'master' #对端
# Host name or IP address of destination 0
# for sending heartbeat signal.
# (change requires restart)
heartbeat_destination_port0 = 9694
# Port number of destination 0 for sending
# heartbeat signal. Usually this is the
# same as wd_heartbeat_port.
# (change requires restart)
heartbeat_device0 = 'ens33'
# Name of NIC device (such like 'eth0')
# used for sending/receiving heartbeat
# signal to/from destination 0.
# This works only when this is not empty
# and pgpool has root privilege.
# (change requires restart)
# eth1根据现场机器改掉
# - Other pgpool Connection Settings -
other_pgpool_hostname0 = 'master' #对端
# Host name or IP address to connect to for other pgpool 0
# (change requires restart)
other_pgpool_port0 = 9999
# Port number for othet pgpool 0
# (change requires restart)
other_wd_port0 = 9000
# Port number for othet watchdog 0
# (change requires restart)
#配置文件里，故障处理配置的是failover_command = '/app/pgpool/failover_stream.sh %H '，因此，需要在/app/pgpool目录中写个failover_stream.sh脚本：
[postgres@master etc]$ cd /app/pgpool[postgres@master pgpool]$ touch failover_stream.sh
#注意这里使用了promote 而不是触发文件，触发文件来回切换有问题，编辑内容如下：
[postgres@master pgpool]$ vim failover_stream.sh
#! /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可执行权限，例如：
[postgres@master pgpool]$ chmod 777 /app/pgpool/failover_stream.sh
五、PGPool集群管理
#启动集群pgsql&pgpool（master&&slave）
[postgres@master pgpool]$ pg_ctl start -D $PGDATA
[postgres@master pgpool]$ pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 &
#查看集群节点状态
[postgres@master pgpool]$ psql -h 10.1.83.168 -p 9999
Password:
psql (9.6.0)
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 | up | 0.500000 | primary | 0 | true | 0
1 | slave | 5432 | up | 0.500000 | standby | 0 | false | 0
(2 rows)
#在slave上节点也是psql -h 10.1.83.168 -p 9999，双pgpool使用虚拟ip，做到高可用。
#Pgpool的HA
#模拟master端pgpool宕机
[postgres@master pgpool]$ pgpool -m fast stop
2020-01-08 18:07:57: pid 8535: LOG: stop request sent to pgpool. waiting for termination...
.done.
[1]+ 完成 pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1
[postgres@master pgpool]$ psql -h 10.1.83.168 -p 9999
Password:
psql (9.6.0)
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 | up | 0.500000 | primary | 0 | false | 0
1 | slave | 5432 | up | 0.500000 | standby | 0 | true | 0
(2 rows)
[postgres@master pgpool]$ pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 &
#访问成功，在master节点上的pgpool宕机后，由slave节点的pgpool接管vip和集群服务，并未中断应用访问。#在master上重新启动pgpool后，定制slave上的pgpool服务，结果一样。
#模拟master端pg primary宕机
[postgres@master pgpool]$ pg_ctl stop -D $PGDATA
waiting for server to shut down...... done
server stopped
[postgres@master pgpool]$ psql -h 10.1.83.168 -p 9999
Password:
psql (9.6.0)
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)
#slave已经被切换成primary，且master节点状态是down
#修复master节点重新加入集群
[postgres@master pgpool]$ cd $PGDATA
[postgres@master data]$ mv $PGDATA/recovery.done $PGDATA/recovery.conf#一定要把.done改成.conf
[postgres@master data]$ pg_ctl start -D $PGDATA
server starting
[postgres@master data]$ LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "/app/postgres/log".
[postgres@master data]$ pcp_attach_node -d -U postgres -h 10.1.83.168 -p 9898 -n 0
#注意master的node_id是0，所以-n 0
Password:
#提示输入密码，输入pcp管理密码。
DEBUG: recv: tos="m", len=8
DEBUG: recv: tos="r", len=21
DEBUG: send: tos="C", len=6
DEBUG: recv: tos="c", len=20
pcp_attach_node -- Command Successful
DEBUG: send: tos="X", len=4
[postgres@master data]$ psql -h 10.1.83.168 -p 9999
Password:
psql (9.6.0)
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 | up | 0.500000 | standby | 0 | false | 0
1 | slave | 5432 | up | 0.500000 | primary | 0 | true | 0
(2 rows)
#master直接down机
当前master节点是primay，我们直接将master服务器直接关机后，发现实现了主备切换，master已经down了，而slave已经被切换成了primary：
#master
[root@master ~]# reboot
#slave
[postgres@slave ~]$ psql -h 10.1.83.168 -p 9999
Password:
psql (9.6.0)
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 | 1 | true | 0
(2 rows)
#数据线同步
在主备切换时，修复节点并重启后，由于数据发生变化，或修复的节点数据发生变化再按照流复制模式加入集群，很可能报时间线不同步错误：
[postgres@master data]$ mv recovery.done recovery.conf
[postgres@master data]$ pg_ctl start -D $PGDATA
[postgres@master data]$ less /app/postgres/log/postgresql-2020-01-09_142207.log
LOG: database system was shut down at 2020-01-09 13:29:13 CST
LOG: entering standby mode
LOG: consistent recovery state reached at 0/20000098
LOG: invalid record length at 0/20000098: wanted 24, got 0
LOG: database system is ready to accept read only connections
LOG: fetching timeline history file for timeline 13 from primary server
FATAL: could not start WAL streaming: ERROR: requested starting point 0/20000000 on timeline 12 is not in this server's history
DETAIL: This server's history forked from timeline 12 at 0/1E000098.
LOG: new timeline 13 forked off current database system timeline 12 before current recovery point 0/20000098
FATAL: could not start WAL streaming: ERROR: requested starting point 0/20000000 on timeline 12 is not in this server's history
DETAIL: This server's history forked from timeline 12 at 0/1E000098.
LOG: new timeline 13 forked off current database system timeline 12 before current recovery point 0/20000098
FATAL: could not start WAL streaming: ERROR: requested starting point 0/20000000 on timeline 12 is not in this server's history
DETAIL: This server's history forked from timeline 12 at 0/1E000098.
LOG: new timeline 13 forked off current database system timeline 12 before current recovery point 0/20000098
产生这种情况，需要根据pg_rewind工具同步数据时间线，具体分六步走。
#停掉需要做同步的节点pg服务
[postgres@master data]$ pg_ctl stop -D $PGDATA
#同步new master节点上时间线
[postgres@master data]$ pg_rewind --target-pgdata=/app/postgres/data --source-server='host=slave port=5432 user=postgres dbname=postgres password=postgres'
#修改pg_hba.conf与 recovery.done文件
[postgres@master data]$ cd $PGDATA
[postgres@master data]$ mv recovery.done recovery.conf
[postgres@master data]$ vim recovery.conf
primary_conninfo = 'host=slave port=5432 user=repuser password=repuser'
[postgres@master data]$ vi pg_hba.conf
host replication repuser slave md5
#重启pg服务
[postgres@master data]$ pg_ctl start -D $PGDATA
#重新加入集群
[postgres@master data]$ pcp_attach_node -d -U postgres -h 10.1.83.168 -p 9898 -n 0
#启动pgpool
[postgres@master data]$ pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 &
#查看集群节点状态
[postgres@master data]$ psql -h 10.1.83.168 -p 9999
Password:
psql (9.6.0)
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 | up | 0.500000 | standby | 0 | true | 0
1 | slave | 5432 | up | 0.500000 | primary | 1 | false | 0
(2 rows)
