PostgreSQL12使用pgpool-II配置读写分离集群

pgpool-II是一个集连接池、主备切换与负载均衡于一身的数据库连接件，本文以配置读写分离集群为例，介绍pgpool-II的配置方式。

本文使用的版本为PostgreSQL12.1，由于PG12版本不再支持通过recovery.conf的方式进行主备切换，如果数据目录中存在recovery.conf，则数据库无法启动。

 

（一） 准备工作

1.本例中，两台数据节点IP与端口号为

Harvest1 192.168.100.1:5432

Harvest2 192.168.100.2:5432

保证服务器都安装了PostgreSQL12.1。

 

提示：

在典型配置中，可以将pgpool配置在一台独立的节点上。本文为简便起见，将pgpool部署在master节点。

 

 

2.使用yum下载rpm包安装pgpool-II，注意下载的安装包须与PG的版本一致。安装包目录详见https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-8-x86_64/。

[root@Harvest1 ~] yum install https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-8-x86_64/pgpool-II-pg12-4.1.0-1pgdg.rhel8.x86_64.rpm

 

提示：

使用rpm安装的pgpool-II的数据目录在/etc/pgpool-II路径下。

 

（二） 配置双机环境-主节点

在Harvest1的postgresql.conf中添加以下配置，将wal_level配置为hot_standy表示两台数据库进行热备份，archive_mode表示开启归档模式。

wal_level = hot_standby

checkpoint_timeout = 5min

archive_mode = on

max_wal_senders = 3

wal_keep_segments = 16 

在数据库中创建用于同步数据的用户rep：

postgres#= CREATE USER rep REPLICATION LOGIN ENCRYPTED PASSWORD '123456';

 

为rep用户修改pg_hba.conf：

host replication rep 0.0.0.0/0 md5

 

修改完成后重启数据库

pg_ctl restart

（三） 配置双机环境-备份节点

停止备份节点

pg_ctl stop

清空备份节点$PGDATA目录下的所有文件，执行命令行工具pg_basebackup：

pg_basebackup -R -D /home/postgres/data/ -Fp -Xs -v -P -h 192.168.100.1 -p 5432 -U rep

 

备份完成后启动备份数据库，查看流复制同步效果

postgres#= SELECT pg_is_in_recovery(); 

 

 

（四）配置pgpool-II

修改/etc/pgpool-II目录下的pgpool.conf：

# 以下为后端连接参数

backend_hostname0 = '192.168.26.128' 

backend_port0 = 5432

backend_weight0 = 1 

backend_flag0 = 'ALLOW_TO_FAILOVER' 

backend_hostname1 = '192.168.26.129' 

backend_port1 = 5432

backend_weight1 = 100

backend_flag1 = 'ALLOW_TO_FAILOVER'

#pgpool的hba参数

enable_pool_hba = on 

pool_passwd = 'pool_passwd'

#开启语句日志

log_statement = on 

log_per_node_statement = on 

 

log_destination = 'syslog'

print_timestamp = on

log_connections = on

log_hostname = off

log_standby_delay = 'always'

syslog_facility = 'LOCAL0'

syslog_ident = 'pgpool'

#开启附LB模式、流复制模式，设置拒绝的函数与SQL

load_balance_mode = on 

ignore_leading_white_space = on 

white_function_list = '' 

black_function_list = 'currval,lastval,nextval,setval'

master_slave_mode = on 

master_slave_sub_mode = 'stream'

 

（五）开启系统日志

pgpool的日志默认写到stderr，可以如上文中设置为syslog中。但由于默认情况下写入LOCAL0级别的日志会被丢弃，故要在/etc/rsyslog.conf中修改该syslog指定的输出路径：

local0.* /var/pgpool/pgpool.log

 

（六）pgpool秘钥

1.生成pgpool-II管理秘钥

pg_md5 -u sup_pool 123456

生成结果如：

e10adc3949ba59abbe56e057f20f883e

 

2.在pcp.conf中添加秘钥

sup_pool: e10adc3949ba59abbe56e057f20f883e

 

配置pool_hba.conf，注意认证方式必须为MD5

host all all 192.168.0.0/16 md5

 

修改pool_passwd

默认情况下，使用pg_md5即可向pass_wd写入用户与密码，例如：

pg_md5 --md5auth --username=postgres 123456

 

至此pgool-II配置完毕。

 

（七）pgool-II的启动与停止

启动pgpool：

pgpool -n -d > /var/pgpool/pgpool.log 2>&1 &

停止pgpool：

pgpool stop

若要强制定制pgpool：

pgpool stop -m fast

 

启动pgpool后，若连接正常，则会提示：

pgpool-II successfully started. version x.x.x

find_primary_node: primary node id is 0

 

例如：

 

（八）查看pgpool节点id及使用情况：

在pgpool中，默认的端口为9999，可以通过修改pgpool.conf来更改。

 

本例中，由于本地有psql，故可以直接使用psql连接pgpool：

psql -p 9999 -U postgres

 

通过以下SQL查询节点id：

show pool_nodes;