Postgresql + Pgpool 主从及高可用配置
软件版本
服务器：CentOS7
PostgresSql：14
PGPool-II：4.3.1
操作注意事项：
1. 注意目录归属及权限，建议相关目录均使用【chown -R postgres:postgres 目录】执行一次，防止权限问题
2. 数据库相关部分只需要配置主库，从库在安装完成后，不需要启动及初始化
3. pgpool的配置在各服务器上一致，都需要操作一遍
4. SSH免密登录配置完成请验证
5. 从库初始化及主库恢复需要配置插槽
6. psql的根目录/usr/pgsql-14/bin/psql，若搞不清楚目录，请使用完整路径运行
7. 最好联网安装，防止产生依赖，或者指令缺失
参考文档
Pgpool-II + Watchdog Setup Example
本文主体根据官方文档制作，去除大段的解释文字，优化了部分操作顺序，并补充几个官方未提及的细节点，笔者所作文档与网上其他文档差距较大，请不要同时参考，若发现了笔者没有碰到的问题，再额外去查询。
官方文档比较详细，但请注意版本，不同版本差距较大
Pg14安装
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql14-server
初始化数据库
/usr/pgsql-14/bin/postgresql-14-setup initdb
相关目录
软件目录
/usr/pgsql-14/
数据目录
/var/lib/pgsql/14/data/
存档目录
/var/lib/pgsql/archivedir
修改密码
su - postgres
psql
\password postgres
postgres在系统与数据库均有账户，两边密码可以不一致
配置远程访问
vi /var/lib/pgsql/14/data/pg_hba.conf
填入如下或类似语句，让全部或指定的服务器访问数据库
host all all 0.0.0.0/0 md5
vi /var/lib/pgsql/14/data/postgresql.conf
添加下方语句，监听所有服务器请求
listen_addresses = '*'
打开防火墙端口，默认5432
firewall-cmd --permanent --zone=public --add-service=postgresql
firewall-cmd --permanent --zone=public --add-port=5432/tcp
firewall-cmd --reload
配置自启
systemctl enable postgresql-14
开关服务
systemctl start postgresql-14
systemctl stop postgresql-14
systemctl restart postgresql-14
主从IP
主：10.70.1.118
从：10.70.1.119
虚拟：10.70.1.120
Pgpool-II安装
vi /etc/yum.repos.d/pgdg-redhat-all.repo
添加加粗内容并保存
[pgdg-common]
...
exclude=pgpool*
[pgdg14]
...
exclude=pgpool*
[pgdg13]
...
exclude=pgpool*
[pgdg12]
...
exclude=pgpool*
[pgdg11]
...
exclude=pgpool*
[pgdg10]
...
exclude=pgpool*
[pgdg96]
...
exclude=pgpool*
yum install -y https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/pgpool-II-release-4.3-1.noarch.rpm
yum install -y pgpool-II-pg14-*
目录
日志目录
/var/log/pgpool_log/
软件目录
/etc/pgpool-II/
Run目录，这两个是同一个目录，var下的是一个连接
/run/pgpool/ /var/run/pgpool/
编辑主库
vi /var/lib/pgsql/14/data/postgresql.conf
修改并添加如下内容， 可以加其他参数，官方没提，我也就没加
listen_addresses = '*'
archive_mode = on
archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on
添加用户
su - postgres
psql
SET password_encryption = 'scram-sha-256';
CREATE ROLE pgpool WITH LOGIN;
CREATE ROLE repl WITH REPLICATION LOGIN;
\password pgpool
\password repl
\password postgres
GRANT pg_monitor TO pgpool;
配置SSH，实现无密码登录效果
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
ssh-copy-id -i id_rsa_pgpool.pub postgres@server2
su - postgres
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@server1
ssh-copy-id -i id_rsa_pgpool.pub postgres@server2【
1. 使用ssh postgres@serverX -i ~/.ssh/id_rsa_pgpool指令检验是否需要密码，
2. 若需要密码，上述目录均执行restorecon -R -v ~/.ssh
3. 可能需要自己建立.ssh文件夹
4. 可能需要修改linux中postgres账户密码，非postgresql里的密码
pool_hba.conf配置访问
vi /etc/pgpool-II/pool_hba.conf
格式同pg_hba.conf,
host all all 0.0.0.0/0 trust
host replication all 0.0.0.0/0 trust
配置.pgpass
su - postgres
vi /var/lib/pgsql/.pgpass
填入下方内容，注意文件权限及归属
#【ip】:【port】:【group】:【user】:【pwd】
10.70.1.118:5432:replication:repl:repl
10.70.1.119:5432:replication:repl:repl
10.70.1.118:5432:postgres:postgres:postgres
10.70.1.119:5432:postgres:postgres:postgres
chmod 600 /var/lib/pgsql/.pgpass
防火墙
firewall-cmd --permanent --zone=public --add-service=postgresql
firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9000/tcp --add-port=9694/udp
firewall-cmd --reload
开机自启
systemctl enable pgpool.service
配置节点ID
第一台服务器配1，第二台配置2，以此类推
vi /etc/pgpool-II/pgpool_node_id
根据设备顺序，填入数字，0，1，2，3，4…
0
pgpool.conf配置
填入以下内容，根据实际进行修改，主要就是IP，网卡【本例中ens33】
# 集群模式 【流复制】
backend_clustering_mode = 'streaming_replication'
# 通讯
listen_addresses = '*'
port = 9999
# 流复制检查
sr_check_user = 'pgpool'
sr_check_password = ''
# 健康检查
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_database = 'postgres'
# 后端设置【配置IP】
backend_hostname0 = '10.70.1.118'
backend_data_directory0 = '/var/lib/pgsql/14/data/'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = "zhu" #用于显示
backend_hostname1 = '10.70.1.119'
backend_data_directory1 = '/var/lib/pgsql/14/data/'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = "cong" #用于显示
# 故障转移配置【脚本可能需要修改PGHOME】
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
# follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R' #三个及以上需要配置
# 在线恢复配置【主服务器 创建recovery_1st_stage和pgpool_remote_start，并添加执行权限】
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'
# 客户端认证配置
enable_pool_hba = on
pool_passwd = 'pool_passwd'
# 看门狗 VIP 【可能需要配置visudo】【网卡】
use_watchdog = on
delegate_IP = '10.70.1.120'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens33 label ens33:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens33'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens33'
# 看门狗停止事件【脚本内需要配置网卡】
wd_escalation_command = '/etc/pgpool-II/escalation.sh'
# 服务器【配置IP】
hostname0 = '10.70.1.118'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '10.70.1.119'
wd_port1 = 9000
pgpool_port1 = 9999
# 偶数节点需要打开
enable_consensus_with_half_votes = on
# 生命检查
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
heartbeat_hostname0 = '10.70.1.118'
heartbeat_port0 = 9694
heartbeat_device0 = 'ens33'
heartbeat_hostname1 = '10.70.1.119'
heartbeat_port1 = 9694
heartbeat_device1 = 'ens33'
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
# 日志
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
#
pid_file_name = '/run/pgpool/pgpool.pid'
注意配置文件中涉及到的目录均需要权限
需要注意配置文件中涉及到的run目录，若run目录下是pgpool-II,则都改成pgpool-II，不要自己建文件夹
pcp.conf配置
密码填入对应账户的实际密码
echo 'pgpool:'`pg_md5 密码` >> /etc/pgpool-II/pcp.conf
echo 'postgres:'`pg_md5 密码` >> /etc/pgpool-II/pcp.conf
su - postgres
echo 'localhost:9898:pgpool:密码' > ~/.pcppass
chmod 600 ~/.pcppass
故障恢复指令
cp -p /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/14/data/recovery_1st_stage
cp -p /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/14/data/pgpool_remote_start
chown postgres:postgres /var/lib/pgsql/14/data/{recovery_1st_stage,pgpool_remote_start}
仅主库需要执行
su - postgres
psql template1 -c "CREATE EXTENSION pgpool_recovery"
pool_passwd配置
su - postgres
echo 'some string' > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey
pg_enc -m -k ~/.pgpoolkey -u pgpool -p
pg_enc -m -k ~/.pgpoolkey -u postgres -p
cat /etc/pgpool-II/pool_passwd
配置escalation.sh
vi /etc/pgpool-II/escalation.sh
需配置下方内容
PGPOOLS=(zhu cong)
VIP=10.70.1.120
DEVICE=ens30
配置hosts文件
vi /etc/hosts
#Public IP
10.70.1.118 zhu
10.70.1.119 cong
#Virtual IP
10.70.1.120 vip
/etc/sysconfig/pgpool 配置
vi /etc/sysconfig/pgpool
OPTS=" -D -n"
打开服务
systemctl start pgpool.service
systemctl stop pgpool.service
systemctl restart pgpool.service
查看节点信息
使用虚拟IP
psql -h 10.70.1.120 -p 9999 -U pgpool postgres -c "show pool_nodes"
查看看门狗信息
pcp_watchdog_info -h 10.70.1.120 -p 9898 -U pgpool
从库初始化，主库恢复
1.从主库恢复数据到从库，从库不要运行，也不需要初始化
pcp_recovery_node -h 10.70.1.120 -p 9898 -U pgpool -n 1
若执行失败，多半是权限问题，可以使用脚本测试一下
./recovery_1st_stage '/var/lib/pgsql/14/data/' '10.70.1.119' '/var/lib/pgsql/14/data/' '5432' 1 '5432' '10.70.1.118'
参数说明
'/var/lib/pgsql/14/data/' 【主库】
'10.70.1.119' 【要恢复的主机IP】
'/var/lib/pgsql/14/data/' 【要恢复的库】
'5432' 【主节点端口号】
1 【要恢复的节点号】
'5432' 【要恢复的端口号】
'10.70.1.118' 【主节点IP】
执行报错后，可以去/var/log/pgpool_log找日志，有类似指令的语句可以更方便的构建该指令
2.配置插槽，若不配置，从库不能同步主库数据
su - postgres
psql
查询指令：SELECT slot_name, slot_type, active FROM pg_replication_slots;
添加指令：SELECT * FROM pg_create_physical_replication_slot('插槽名称');
插槽名称在从库的/var/lib/pgsql/14/data/ myrecovery.conf
primary_slot_name = '10_70_1_119'
3.需要杀掉pgsql进程，使用脚本内恢复数据后，从库会运行pgsql进程，若不杀掉，不能运行从库
ps -ef | grep pgsql
kill 30544
4.运行从库
systemctl start postgresql-14
高可用测试
1. 开关pgpool服务，查看虚拟IP转移情况，数据访问情况
2. 关闭从库，主库写入数据，运行从库，查看数据是否同步
3. 关闭主库，查看从库是否变成主库，是否写入数据
关闭主库后，不能立刻运行主库，会出错，原因在于从库升级为主库了，此时需要使用上面方法使主库变成从库运行
4. 关闭主库服务器，查看从库是否变主库，虚拟IP转移情况，期间应该会有短时不能访问
主从区分
ll /var/lib/pgsql/14/data/
有standby.signal文件的为从服务器
笔者出错点
1. 按本文操作，最好不要先配置好主从，再配置pgpool，自行配置的主从可能不兼容pgpool的配置
2. SSH不能免密访问，本文已提供解决方式
3. 目录归属及权限，请格外注意
4. 从库初始化及主库恢复时的插槽配置
总结
以上是生活随笔为你收集整理的Postgresql + Pgpool 主从及高可用配置的全部内容，希望文章能够帮你解决所遇到的问题。
- 上一篇： 通过DS18B20学时序
- 下一篇： qemu-img转换镜像文件格式