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;

Leave a Reply