通过pgpool-II实现PostgreSQL数据库服务高可用
本文建立在数据库主从复制(Master-Slave)的基础上。
Master：10.19.100.2 port：5432 数据库版本PostgreSQL-10.3
Slave：10.19.100.3 port：5432 数据库版本PostgreSQL-10.3
pgpool-II使用虚拟IP：10.19.100.4 软件版本 pgpool-II 3.7.2
一 pgpool中间件及HA简介
在没有中间件的情况下，在Master数据库宕机，需要手工提升Standby为Master，还需要应用主动把连接切换到新Master库上才能保证整个系统能继续对外提供服务。为了实现自动切换，增加系统可靠性，这里可以考虑pgpool中间件。pgpool可以检测数据库集群中的Master节点是否存活，在Master节点失效时可以自动提升Standby库，并且pgpool本身可以做高可用部署，搭建多个pgpool，使用vip在多个pgpool上漂移。
pgpool-II 3.7对原来旧版本的几个影响用户使用的问题进行了fix，增加了新的pgpool主从模式和参数设置，通过pgpool主动切换主备后，备库如果没有问题，也可以直接加入集群了(实测pg 9.6.8也可用)。而之前版本的pgpool，脱离集群的节点无法重新加入进去。
注意1：如果需要输出pgpool日志，需要设置服务器的selinux为disable。且做以下修改
# vi /etc/rsyslog.conf local0.* /PostgreSQL/pgpool/pgpool.log
# /etc/init.d/rsyslog restart
注意2：通过pgpool登录数据库目前仅支持数据库白名单pg_hba.conf中的password验证方式。
二 pgpool安装
2.1 编译安装软
# tar zxvf pgpool-II-3.7.2.tar.gz # cd pgpool-II-3.7.2 # ./configure --prefix=/PostgreSQL/pgpool # make # make install
2.2 配置pgpool管理用户和密码
Pgpool管理员用户密码位于其配置文件pcp.conf中，手工编辑即可。其中密码需要使用pg_md5加密。
# /PostgreSQL/pgpool/bin/pg_md5 123456 e10adc3949ba59abbe56e057f20f883e # vi /PostgreSQL/pgpool/etc/pcp.conf postgres:e10adc3949ba59abbe56e057f20f883e
2.3 创建Master/Standby切换脚本
# vi /PostgreSQL/pgpool/failover.sh #! /bin/sh new_master=$1 pathdir=$2 /usr/bin/ssh -T $new_master /PostgreSQL/10/bin/pg_ctl promote -D $pathdir exit 0;
切换脚本权限
# chmod 755 /PostgreSQL/pgpool/failover.sh
2.4 软件权限
# chown -r postgres:postgres /PostgreSQL/pgpool
三 配置pgpool
3.1配置master节点的pgpool
pgpool没有必要和数据库部署在同一台服务器上，我这里为了配置方便pgpool master节点和master库放在同一台服务器上。
在Master节点上安装pgpool
安装方法见第一节。
修改配置文件pgpool.conf
Pgpool的etc目录下自带多种配置文件范例，我这里使用最基础的版本pgpool.conf.sample进行修改，仅为数据库高可用考虑，两套pgpool间为主从关系。如果需要负载均衡等其他功能，请详细阅读pgpool.conf.sample内的说明后进行配置。
$ cd /PostgreSQL/pgpool/etc
$ cp pgpool.conf.sample pgpool.conf
$ vi pgpool.conf
这里单独列出要修改的内容：
listen_addresses = '*' backend_hostname0 = '10.19.100.2' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/PostgreSQL/10/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '10.19.100.3' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/PostgreSQL/10/data' backend_flag1 = 'ALLOW_TO_FAILOVER' log_destination = 'syslog' pid_file_name = '/PostgreSQL/pgpool/pgpool.pid' logdir = '/PostgreSQL/pgpool' master_slave_mode = on sr_check_user = 'postgres' sr_check_password = '123456' sr_check_database = 'postgres' health_check_user = 'postgres' health_check_password = '123456' health_check_database = 'postgres' failover_command = '/PostgreSQL/pgpool/failover.sh %H %R' use_watchdog = on wd_hostname = '10.19.100.2' delegate_IP = '10.19.100.4' heartbeat_destination0 = '10.19.100.3' wd_lifecheck_dbname = 'postgres' wd_lifecheck_user = 'postgres' wd_lifecheck_password = '123456' other_pgpool_hostname0 = '10.19.100.3' other_pgpool_port0 = 9999 other_wd_port0 = 9000
ifconfig权限
如果需要非root用户启动Pgpool时能启动虚拟IP，需要配置该权限
$ su -
Password:
# chmod u+s /sbin/ip
ssh信任
$ su - postgres $ ssh-keygen $ ssh-copy-id postgres@10.19.100.3 $ ssh-copy-id postgres@10.19.100.2
3.2配置Standby节点的pgpool
在Standby节点上安装pgpool
安装方法见第一节。
修改配置文件pgpool.conf
修改内容和Master节点基本相同，这里仅列出不同的部分
wd_hostname = '10.19.100.3' heartbeat_destination0 = '10.19.100.2' other_pgpool_hostname0 = '10.19.100.2'
ifconfig权限
如果需要非root用户启动pgpool时能启动虚拟IP，需要配置该权限
$ su -
Password:
# chmod u+s /sbin/ip
ssh信任
$ su - postgres $ ssh-keygen $ ssh-copy-id postgres@10.19.100.2 $ ssh-copy-id postgres@10.19.100.3
四 启动pgpool
启动Master节点的pgpool
$ /PostgreSQL/pgpool/bin/pgpool -f /PostgreSQL/pgpool/etc/pgpool.conf
启动后可以观察到虚拟IP生效
$ ifconfig eth0:0 eth0:0 Link encap:Ethernet HWaddr 08:00:27:56:B5:91 inet addr:10.19.100.4 Bcast:0.0.0.0 Mask:255.255.255.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
启动Standby节点的pgpool
$ /PostgreSQL/pgpool/bin/pgpool -f /PostgreSQL/pgpool/etc/pgpool.conf
五 使用pgpool连接数据库
通过pgpool的虚拟IP和端口即可连接数据库，整个过程对用户透明，不需要更改任何连接工具：
$ psql -U postgres -d postgres -h 10.19.100.2 -p 9999 Password for user postgres: psql.bin (10.3) Type "help" for help. postgres=#
可以通过命令看到数据库各个节点状态
postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replic ation_delay ---------+-------------+------+--------+-----------+---------+------------+-------------------+------- ------------ 0 | 10.19.100.2 | 5432 | up | 0.500000 | primary | 0 |false | 0 1 | 10.19.100.3 | 5432 | up | 0.500000 | standby | 0 | true | 0 (2 rows)
六 pgpool切换
这里把10.19.100.2拔电，模拟Master节点宕机，数据库Master节点和pgpool Master节点全部失去的情况。
Master节点下电后在Standby节点服务器上可以看到：
虚拟IP发生漂移
$ ifconfig eth0:0 eth0:0 Link encap:Ethernet HWaddr 08:00:27:56:B5:91 inet addr:10.19.100.4 Bcast:0.0.0.0 Mask:255.255.255.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
Standby库变为Master库
postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replic ation_delay ---------+-------------+------+--------+-----------+---------+------------+-------------------+------- ------------ 0 | 10.19.100.2 | 5432 | down | 0.500000 | standby| 0 | true | 0 1 | 10.19.100.3 | 5432 | up | 0.500000 | primary | 0 | false | 0 (2 rows)
我这里因为只是模拟故障，因此重启10.19.100.2，将主库的recovery.done文件重命名为recovery.conf后，重新启动主库即可重新恢复流复制。流复制恢复后使用下面的命令把节点重新加入pgpool集群。
$ /PostgreSQL/pgpool/bin/pcp_attach_node -h 10.19.100.4 -p 9898 -U postgres -n 1 Password: pcp_attach_node -- Command Successful
