PostgreSQL17-pgpool-II_异步流复制实现高可用

17.pgpool-II+异步流复制实现高可用

1.pgpool部署

主备操作
tar xvf pgpool-II-3.6.6.tar.gz
./configure --prefix=/opt/pgpool --with-pgsql=/opt/pgsql

2.互信配置

主备操作
vim /etc/hosts
192.168.26.57 pghost4
192.168.26.57 pghost5
两者ssh免密登陆

3.配置pool_hba.conf

主备操作
cd /opt/pgpool/etc
cp pool_hba.conf.sample pool_hba.conf

加入一下内容，建议与PG的pg_hba.conf配置一致
host  replication  repuser  192.168.26.57/32  md5
host  replication  repuser  192.168.26.57/32  md5
host  replication  repuser  192.168.26.57/32  md5
host  all  all  0.0.0.0/0  md5

4.配置pool_passwd配置文件

主备操作
pg_md5 -u postgres -m postgres123
cat pool_passwd
postgres:md58sn28n8fn3k8snwk7vt2b7vb3s9

5.配置pgpool.conf配置文件

主备操作
cd /opt/pgpool/etc
cp pgpool.conf.sample-stream pgpool.conf

主库

vim pgpool.conf
端口
listen_addresses = '*'
port = 9999

后端节点
backend_hostname0 = 'pghost4'
backend_port0 = 1921
backend_data_directory0 = '/data1/pg10/pg_root'
backend_flag0 = 'ALLOW_TO_FAILOVER'

认证
enable_pool_hba = on
pool_passwd = 'pool_passwd'

日志
log_destination = 'syslog'
pid_filename = '/opt/pgpool/pgpool.pid'

LB
load_balance_mode = off

pgpool 的复制模式设置和流复制检测配置
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'repuser'
sr_check_password = '123456'
sr_check_database = 'postgres'
delay_threshold = 10000000

健康检查
health_check_period = 5
health_check_timeout = 20
health_check_user = 'repuser'
health_check_password = '123456'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 3

故障转移脚本
failover_command = '/opt/pgpool/failover_stream.sh %d %P %H %R'

watchdog配置
use_watchdog = on
wd_hostname 'pghost4'

备库