刘航：国信司南地理信息技术有限公司部门GIS主管
基于Pgpool-II4.1.0搭建PostgreSQL11集群
该篇文章主要是介绍基于pgpool4.1以及PostgreSQL的流复制实现PostgreSQL的读写分离以及高可用，配置参考Pgpool-II官方文档。使用版本为Pgpool4.1、PostgreSQL11.6。
Pgpool介绍
pgpool相关介绍不在该文章中，需要者可以参考我的另一篇文章。传送门：pgpool介绍
环境准备
本次使用三台CentOS7.3的服务器来搭建集群，首先在三台服务器上部署PostgreSQL11.6版本的数据库，部署教程大家可以网上自行查找。具体规划如下：
架构图参考官网：
搭建配置
服务器配置
故障转移、在线恢复时需要ssh到其它服务器执行命令，故需要配置服务器之间无密码ssh登录（如服务器之间已经配置ssh可以跳过该节，但是需要修改failover.sh等脚本）
[all servers]# cd ~/.ssh[all servers]# ssh-keygen -t rsa -f id_rsa_pgpool[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server1[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server2[all servers]# ssh-copy-id -i id_rsa_pgpool.pub postgres@server3[all servers]# su postgres[all servers]$ cd ~/.ssh[all servers]$ ssh-keygen -t rsa -f id_rsa_pgpool[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server1[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server2[all servers]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@server3
pgpool安装
pgpool安装不在该文章中介绍，需要者可以参考我另一篇文章。传送门：pgpool安装。
PostgreSQL数据库配置
1.WAL归档
需要WAL归档的可自行配置，该示例暂时未使用WAL归档
[all servers]# su - postgres[all servers]$ mkdir var/lib/pgsql/archivedir## postgres.conf 配置archive_mode = onarchive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
这儿有一个归档的脚本，使用该脚本可以自行修改保留多少天归档 pg_archive.sh
2.postgres.conf配置
该配置仅在主节点配置，从节点使用pgpool的在线恢复功能配置
listen_addresses = '*'port = 5432max_wal_senders = 10max_replication_slots = 10wal_level = replicahot_standby = onwal_log_hints = on
3.数据库用户以及密码配置
[server1]# psql -U postgres -p 5432postgres=# CREATE ROLE pgpool WITH LOGIN PASSWORD 'pgpool';postgres=# CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'repl';## 用于SHOW POOL_NODES 展示 "replication_state" and "replication_sync_state"postgres=# GRANT pg_monitor TO pgpool;
4.pg_hba.con配置
添加用户验证配置，将repl复制用户添加到pg_hba中。此文档中所有密码使用md5验证。
host all all 0.0.0.0/0 md5host replication repl 0.0.0.0/0 md5
5.配置.pgpass文件用于无密码操作
由于在故障转移、在线恢复时使用脚本进行操作，脚本中使用pg_basebakup、pg_rewind等命令，所以需要配置无密码操作。
在postgres用户的home目录下创建.pgpass文件，并且文件权限为600。
[all servers]# su - postgres[all servers]$ vi ~/.pgpass## 格式为：hostname:port:database:username:passwordserver1:5432:replication:repl:<repl user password>server2:5432:replication:repl:<repl user passowrd>server3:5432:replication:repl:<repl user passowrd>server1:5432:postgres:postgres:<postgres user passowrd>server2:5432:postgres:postgres:<postgres user passowrd>server3:5432:postgres:postgres:<postgres user passowrd>[all servers]$ chmod 600 ~/.pgpass
pgpool配置
1)#cp-p/usr/local/pgpool/etc/pgpool.conf.sample-stream usr/local/pgpool/etc/pgpool.conf
2)配置PostgreSQL数据库信息
# - Backend Connection Settings -# 有几台PostgreSQL数据库，配置几个后端信息，使用后缀名0、1、2……backend_hostname0 = 'server1' # Host name or IP address to connect to for backend 0backend_port0 = 5432 # Port number for backend 0backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode)backend_data_directory0 = '/data/pgsql/sport/' # Data directory for backend 0# Controls various backend behavior# ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVERbackend_flag0 = 'ALLOW_TO_FAILOVER'backend_hostname1 = 'server2'backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/var/lib/pgsql/11/data'backend_flag1 = 'ALLOW_TO_FAILOVER'backend_hostname2 = 'server3'backend_port2 = 5432backend_weight2 = 1backend_data_directory2 = '/var/lib/pgsql/11/data'backend_flag2 = 'ALLOW_TO_FAILOVER'
3）基础配置
listen_addresses = '*'pid_file_name = '/var/run/pgpool/pgpool.pid'# PID file name# Can be specified as relative to the"# location of pgpool.conf file or# as an absolute path# (change requires restart)logdir = '/var/run/pgpool'# Directory of pgPool status file# (change requires restart)
创建pgpool运行需要目录mkdir -p var/run/pgpool
4）配置复制延迟检查
sr_check_user = 'pgpool'## 自从4.0版本后，如果密码设置为的话，pgpool会首先从`pool_passwd`文件中获取密码，然后在使用空密码sr_check_password = 'pgpool'
5）配置健康检查
# Health check period# Disabled (0) by defaulthealth_check_period = 5# Health check timeout# 0 means no timeouthealth_check_timeout = 30health_check_user = 'pgpool'health_check_password = 'pgpool'health_check_max_retries = 3
6）配置故障转移
failover_command = '/usr/locla/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'follow_master_command = '/usr/locla/pgpool/etc/follow_master.sh %d %h %p %D %m %H %M %P %r %R'
关于脚本会在后面给出下载地址以及脚本使用说明
# 给脚本设置执行权限，注意：该脚本需要在三台pgpool服务器中都需要创建chmod +x usr/local/pgpoll/etc/{failover.sh,follow_master.sh}
7）配置在线恢复
为了使用Pgpool-II执行在线恢复，我们需要配置PostgreSQL用户名和在线恢复命令recovery_1st_stage。由于执行在线恢复需要PostgreSQL中的超级用户特权，因此我们在recovery_user中指定postgres用户。然后，我们在PostgreSQL主服务器（server1）的数据库目录中创建recovery_1st_stage和pgpool_remote_start，并添加执行权限。
recovery_user = 'postgres'# Online recovery userrecovery_password = 'postgres'# Online recovery passwordrecovery_1st_stage_command = 'recovery_1st_stage'
## 该脚本只需要在数据库主节点创建，后续使用在线恢复时会复制过去[server1]# su - postgres[server1]$ vi data/pgsql/sport/recovery_1st_stage[server1]$ vi data/pgsql/sport/pgpool_remote_start[server1]$chmod+x/data/pgsql/sport/{recovery_1st_stage,pgpool_remote_start}
数据库创建扩展，该扩展是为了能够执行在线恢复（如果在安装时已经创建该扩展则可以跳过该步骤）
[server1]# su - postgres[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
8）配置客户端身份验证
## pgpool.conf中enable_pool_hba = on
身份验证文件为/usr/local/pgpool/etc/pool_hba.conf，配置方式与PostgreSQL基本一样。(scram-sha-256方式可参考pgpool官网)
host all pgpool 0.0.0.0/0 md5host all postgres 0.0.0.0/0 md5
cd usr/local/pgpool/etc../bin/pg_md5 -p -m -u postgres pool_passwd../bin/pg_md5 -p -m -u pgpool pool_passwdcat etc/pgpool-II/pool_passwd# pgpool:AESheq2ZMZjynddMWk5sKP/Rw==# postgres:AESHs/pWL5rtXy2IwuzroHfqg==
9）看门狗配置
use_watchdog = ondelegate_IP = '192.168.111.6' ##vip配置## 网卡名字需要正确配置if_up_cmd = '/usr/bin/sudo sbin/ip addr add $_IP_$/24 dev enp0s8 label enp0s8:0'if_down_cmd = '/usr/bin/sudo sbin/ip addr del $_IP_$/24 dev enp0s8'arping_cmd = '/usr/bin/sudo usr/sbin/arping -U $_IP_$ -w 1 -I enp0s8'配置其它pgpool信息，注意：该配置在三台服务器不一样，只需要配置另外几台即可[server1配置如下]# - Other pgpool Connection Settings -other_pgpool_hostname0 = 'server2'other_pgpool_port0 = 9999other_wd_port0 = 9000other_pgpool_hostname1 = 'server3'other_pgpool_port1 = 9999other_wd_port1 = 9000heartbeat_destination0 = 'server2'heartbeat_destination_port0 = 9694heartbeat_device0 = ''heartbeat_destination1 = 'server3'heartbeat_destination_port1 = 9694heartbeat_device1 = ''[server2配置如下]# - Other pgpool Connection Settings -other_pgpool_hostname0 = 'server1'other_pgpool_port0 = 9999other_wd_port0 = 9000other_pgpool_hostname1 = 'server3'other_pgpool_port1 = 9999other_wd_port1 = 9000heartbeat_destination0 = 'server1'heartbeat_destination_port0 = 9694heartbeat_device0 = ''heartbeat_destination1 = 'server3'heartbeat_destination_port1 = 9694heartbeat_device1 = ''[server3配置如下]# - Other pgpool Connection Settings -other_pgpool_hostname0 = 'server1'other_pgpool_port0 = 9999other_wd_port0 = 9000other_pgpool_hostname1 = 'server2'other_pgpool_port1 = 9999other_wd_port1 = 9000heartbeat_destination0 = 'server1'heartbeat_destination_port0 = 9694heartbeat_device0 = ''heartbeat_destination1 = 'server2'heartbeat_destination_port1 = 9694heartbeat_device1 = ''
10）pcp命令配置
cd /usr/local/pgpool/binecho 'pgpool:'`pg_md5 pgpool` >> /usr/local/pgpool/etc/pcp.conf
以上pgpool相关配置都配置完成，最终会生成pgpool.conf、pool_hba.conf、pool_passwd、pcp.conf，可以配置完一台后，在其它服务器copy配置文件即可。注意：pgpool.conf中看门狗配置需要在其它服务器修改一下。
相关脚本
此处一共需要四个脚本分别为:
1.failover.sh目录为: /usr/local/pgpool/etc/下
2.follow_master.sh目录为: /usr/local/pgpool/etc/下
3.recovery_1st_stage目录为：PostgreSQL数据库数据目录下
4.pgpool_remote_start目录为：PostgreSQL数据库数据目录下
脚本地址：https://github.com/MrSmallLiu/pgpool
脚本参考于官网，但是稍有改动：
1.创建与删除复制槽时，如果使用ip地址，则会报错，因为名字不允许有.，修改：${FAILED_NODE_HOST//./_}
2.follow_master.sh脚本中一处bug
# drop replication slotssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool "${PGHOME}/bin/psql -p ${NEW_MASTER_NODE_PORT} -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST//./_}')\""
注意：
1.注意所有脚本中 PGHOME变量为PostgreSQL安装路径
2.注意所有脚本中ARCHIVEDIR，如果未使用，需要将其注释，并且修改相应使用地方
3.注意所有脚本中PGPOOL_PATH变量为pgpool的bin路径
4.注意recovery_1st_stage脚本中PRIMARY_NODE_HOST变量，该变量取hostname值，可以先在自己服务器测试一下该命令是否符合预期。不符合者使用hostnamectl set-hostname server1
启动
1.后台运行
/usr/local/pgpool/bin/pgpool
2.debug运行
/usr/local/pgpool/bin/pgpool -n -d
/usr/local/pgpool/bin/pgpool -n
3.standby 数据库启动
-n 表示在pgpool.conf配置文件中PostgreSQL配置的后面的序号/usr/local/pgpool/bin/pcp_recovery_node -h 192.168.111.6 -p 9898 -U pgpool -n 1Password:pcp_recovery_node -- Command Successful/usr/local/pgpool/bin/pcp_recovery_node -h 192.168.111.6 -p 9898 -U pgpool -n 2Password:pcp_recovery_node -- Command Successful
停止
/usr/local/pgpool/bin/pgpool -m fast stop
测试
1.使用客户端连接（navicat、pgAdmin等）
psql -h 192.168.111.6 -p 9999 -U passwd：postgres
2.执行show pool_nodes
负载均衡测试
可以配置PostgreSQL日志打印sql语句，使用vip地址连接上后测试输出即可看到查询会负载到三台服务器（默认根据连接使用负载均衡，需要几次新建查询来测试）
自动故障转移测试
可以使用PostgreSQL命令停掉主节点数据库来测试故障转移
写在最后
1.以上配置经过我们部署测试是可以正常运行的，但是是后续整理才写的该篇文章，可能存在遗漏不足的地方。小伙伴在配置过程中有疑惑、配置后未能正常启动、有文档意见都可以与我联系。随时欢迎提给我，然后我进行修改文档，以帮助更多人。
2.后面抽时间整理一个pgpool的部署流程图，方便大家理解部署流程。
3.关于配置文件的具体讲解以及优化会在后面再写相关文档，欢迎持续关注。
I Love PG
关于我们
中国开源软件推进联盟PostgreSQL分会（简称：PG分会）于2017年成立，由国内多家PG生态企业所共同发起，业务上接受工信部产业发展研究院指导。PG分会致力于构建PG产业生态，推动PG产学研用发展，是国内一家PG行业协会组织。
技术文章精彩回顾 PostgreSQL学习的九层宝塔 PostgreSQL职业发展与学习攻略 搞懂PostgreSQL数据库透明数据加密之加密算法介绍 一文读懂PostgreSQL-12分区表 PostgreSQL源码学习之：RegularLock Postgresql源码学习之词法和语法分析 PostgreSQL buffer管理 最佳实践—PG数据库系统表空间重建 PostgreSQL V12中的流复制配置 2019，年度数据库舍 PostgreSQL 其谁? PostgreSQL使用分片（sharding）实现水平可扩展性 一文搞懂PostgreSQL物化视图 PostgreSQL原理解析之：PostgreSQL备机是否做checkpoint PostgreSQL复制技术概述 Postgres是最好的开源软件 PostgreSQL是世界上最好的数据库 从Oracle迁移到PostgreSQL的十大理由 PG活动精彩回顾 见证精彩|PostgresConf.CN2019大会盛大开幕 PostgresConf.CN2019大会DAY2|三大分论坛，精彩不断 PostgresConf.CN2019培训日|爆满！Training Day现场速递！ 「PCC-Training Day」培训日Day2圆满结束，PCC2019完美收官 创建PG全球生态！PostgresConf.CN2019大会盛大召开 首站起航！2019“让PG‘象’前行”上海站成功举行 走进蓉城丨2019“让PG‘象’前行”成都站成功举行 中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行 PostgreSQL实训基地落户沈阳航空航天大学和渤海大学，高校数据库课改正当时 群英论道聚北京，共话PostgreSQL 相聚巴厘岛| PG Conf.Asia 2019 DAY0、DAY1简报 相知巴厘岛| PG Conf.Asia 2019 DAY2简报 相惜巴厘岛| PG Conf.Asia 2019 DAY3简报 独家|硅谷Postgres大会简报 全球规模最大的PostgreSQL会议等你来！ PostgreSQL线上沙龙第一期精彩回顾 PostgreSQL线上沙龙第二期精彩回顾 PostgreSQL线上沙龙第三期精彩回顾 PostgreSQL线上沙龙第四期精彩回顾 PostgreSQL线上沙龙第五期精彩回顾 PG培训认证精彩回顾 关于中国PostgreSQL培训认证，你想知道的都在这里！ 首批中国PGCA培训圆满结束，首批认证考试将于10月18日和20日举行！ 中国首批PGCA认证考试圆满结束，203位考生成功获得认证！ 中国第二批PGCA认证考试圆满结束，115位考生喜获认证！ 请查收：中国首批PGCA证书！ 重要通知：三方共建，中国PostgreSQL认证权威升级！ 一场考试迎新年 | 12月28日，首次PGCE中级认证考试开考！ 近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！ 通知：PostgreSQL技术能力电子证书上线！
