基于pgpool实现读写分离

部署环境：如果已经购买PostgreSQL 12 SSD盘及只读实例，只需在ECS安装配置pgpool即可。
一、 安装pgpool
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum search all pgpool
yum install -y pgpool-II-12-extensions

二、 配置pgpool
步骤1 查询pgpool安装位置。命令如下：
rpm -qa|grep pgpool

步骤2 修改配置文件pgpool.conf。命令如下：
cd /etc/pgpool-II-12/
cp pgpool.conf.sample-stream pgpool.conf
vi pgpool.conf --pgpool.conf全文参考见附件，请参考配置

步骤3 创建业务用户。示例如下：
说明：在购买的Postgresql数据库读写库（主库）上执行以下sql创建用户
1.创建业务用户。示例如下：
create role digoal login encrypted password ‘xxxxxxx’;
create database digoal owner digoal;
2.创建pgpool数据库健康心跳用户，检查只读节点回放延迟（wal replay），只要能登录postgres数据库或指定的库即可，配合pgpool参数使用。示例如下：
create role nobody login encrypted password ‘xxxxxxx’;

步骤4 配置pool_passwd密码文件。命令如下：
说明 通过pgpool连接数据库时需要使用密码文件，可以理解为pgpool支持了PostgreSQL的认证协议。
cd /etc/pgpool-II-12
#用法
#pg_md5 --md5auth --username=username password
#生成digoal, nobody密码, 自动写入pool_passwd。
pg_md5 --md5auth --username=digoal “xxxxxxx”
pg_md5 --md5auth --username=nobody “xxxxxxx”

步骤5 配置pool_passwd密码文件。命令如下：
自动生成pool_passwd文件。命令如下：
cd /etc/pgpool-II-12
cat pool_passwd

步骤6 配置pgpool_hba文件。命令如下：
cd /etc/pgpool-II-12
cp pool_hba.conf.sample pool_hba.conf
vi pool_hba.conf
在pool_hba.conf文件中添加以下内容
host all all 0.0.0.0/0 md5

步骤7 配置pcp管理密码文件。命令如下：
说明 这里是用来管理pgpool的密码和用户，不是数据库的用户和密码。
cd /etc/pgpool-II-12
pg_md5 abc # 例如密码是abc。
900150983cd24fb0d6963f7d28e17f72
cp pcp.conf.sample pcp.conf
vi pcp.conf

USERID:MD5PASSWD
pcp.conf文件添加以下内容，密码abc为例
manage:900150983cd24fb0d6963f7d28e17f72 #表示使用manage用户来管理pcp。

步骤8 启动pgpool。命令如下：
cd /etc/pgpool-II-12
pgpool -f ./pgpool.conf -a ./pool_hba.conf -F ./pcp.conf
步骤9 通过pgpool连接数据库。命令如下：
psql -h 127.0.0.1 -p 8001 -U digoal postgres

常见问题：

如何测试读写分离是否成功？
连接并查询pg_is_in_recovery()，然后断开重连再查询pg_is_in_recovery()，如果交替返回false和true，说明是交替将请求发送给了主库和从库，即读写分离成功。

说明：通过pgpool连接数据库，查询pg_is_in_recovery()时，若一直返回t时,则意味这pgpool只连接到了从库,同理，若一直返回f时，则意味这pgpool只连接到了主库，可以尝试pcp_attach_node重新注册主库或从库。
pcp_attach_node工具是pgpool自带的集群管理工具，pcp_attach_node工具可以将集群中的节点重新注册到集群中，对于通过show_pools显示的状态不正常的node，可以尝试通过此工具进行处理。

通过pcp_attach_node工具注册备库
步骤1 查看pcp工具的连接用户
cat pcp.conf

步骤2 使用pcp_attach_node重新注册
用法：pcp_attach_node -h 本地ip -U pcp_user node_id
示例：pcp_attach_node –h 127.0.0.1 –U manage 0

2.备库如何停止、重新加载pgpool配置？
您可以使用pgpool --help查看帮助命令，例如：
cd /etc/pgpool-II-12
停止命令：
pgpool -f ./pgpool.conf -m fast stop
3. 如果有多个只读实例, 应该如何配置？
修改pgpool.conf文件，补充多个只读实例的配置，示例如下：
backend_hostname1 = ‘xx.xx.xxx.xx’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/var/chroot/var/lib/pgsql/data’
backend_flag1 = ‘DISALLOW_TO_FAILOVER’
backend_application_name1 = ‘server1’

backend_hostname2 = ‘xx.xx.xx.xx’
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = ‘/var/chroot/var/lib/pgsql/data’
backend_flag1 = ‘DISALLOW_TO_FAILOVER’
backend_application_name1 = ‘server1’
4. 如何通过pcp查询后端状态？
示例命令如下：

pcp_node_info -U manage -h /tmp -p 9898 -n 1 -v
Password: 输入密码

5. 监听的端口有哪些？
   监听的端口如下：
   主库：5432
   备库：5432
   pgpool：8001
   pcp：9898