# 基于Pgpool实现读写分离
#### 功能介绍
Pgpool是一个专为PostgreSQL设计的中间件，部署在数据库服务器与客户端之间，并且在前后台之间传递消息。 因此Pgpool对于服务器和客户端来说是透明的。通过提供连接池、复制、负载均衡、限制超过限度连接，显著提升数据库集群的性能、可用性和可扩展性。
本文介绍RDS for PostgreSQL实例及只读实例如何结合Pgpool实现读写分离。
图1拓扑图   
![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002441298233.jpg "点击放大")
#### 前提条件
- 参考[自定义购买ECS](https://support.huaweicloud.com/usermanual-ecs/ecs_03_7002.html)，已购买ECS。ECS选择与RDS for PostgreSQL实例相同的区域、VPC和安全组，便于RDS for PostgreSQL和ECS网络互通。
- 参考[购买并通过PostgreSQL客户端连接RDS for PostgreSQL实例](https://support.huaweicloud.com/qs-rds-pg/rds_02_0016.html)，已购买RDS for PostgreSQL实例并安装PostgreSQL客户端。
- 参考[创建只读实例](https://support.huaweicloud.com/usermanual-rds-pg/rds_add_read_replica_pg.html)，已在RDS for PostgreSQL主实例上创建只读实例。
- 参考[通过内网连接RDS for PostgreSQL实例（Linux方式）](https://support.huaweicloud.com/usermanual-rds-pg/rds_pg_connect_05.html#section1)，已通过ECS连接到RDS for PostgreSQL实例。
 
#### 操作步骤
#### 步骤1：安装Pgpool
本文中ECS以CentOS 7为例，PostgreSQL版本以12版本为例。其他系统和数据库版本参考以下步骤替换相应命令。
登录到ECS上，执行以下命令安装PostgreSQL 12和Pgpool工具软件。
```
# 配置当前环境yum仓库
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 搜索postgresql包
sudo yum search all postgresql
# 搜索pgpool包
sudo yum search all pgpool
# 安装postgresql-12
sudo yum install -y postgresql12-server
# 安装pgpool-12
sudo yum install -y pgpool-II-12-extensions
```
#### 步骤2：配置Pgpool
使用Pgpool实现负载均衡访问，所有认证发生在客户端和Pgpool之间，同时客户端仍然需要继续通过PostgreSQL的认证过程。
1. 查询Pgpool安装路径，命令如下：
   ```
   rpm -qa | grep pgpool
   ```
   图2执行结果   
   ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002445623893.png "点击放大") 
2. 在购买的RDS for PostgreSQL主实例上执行以下SQL，创建业务用户。
   创建业务用户。
   ```
   create role digoal login encrypted password 'xxxxxxx';
   create database digoal owner digoal;
   ```
   创建Pgpool数据库健康心跳用户，检查只读实例回放延迟（wal replay）。
   只要能登录postgres数据库或指定的库即可，配合Pgpool参数使用。
   ```
   create role nobody login encrypted password 'xxxxxxx';
   ```
   xxxxxxx：新建数据库用户密码。
   
3. 修改配置文件pgpool.conf。
   ```
   cd /etc/pgpool-II-12/  
   cp pgpool.conf.sample-stream pgpool.conf  
   vi pgpool.conf
   ```
   - /etc/pgpool-II-12/：安装Pgpool的目录。
   
   - pgpool.conf.sample-stream：Pgpool针对流复制模式提供的默认配置文件模板。
   
   - pgpool.conf：Pgpool配置文件。
    
4. 按 i 键进入编辑模式，主要结合日志对进行以下项进行修改，其他配置信息可以根据pgpool.conf文件注释选择。
   ```
   listen_addresses = '*'   
   backend_hostname0 = '主实例IP地址'
   backend_port0 = 5432
   backend_flag0 = 'ALWAYS_MASTER'
   backend_hostname1 = '只读实例IP地址'
   backend_port1 = 5432
   backend_flag1 = 'ALLOW_TO_FAILOVER'
   enable_pool_hba = on
   pid_file_name = '/var/run/pgpool-II-12/pgpool.pid'
   ```
   - listen_addresses：Pgpool服务监听的网络IP地址，这里选择的是监听所有IP。
   
   - backend_hostname：连接到后端PostgreSQL数据库节点的网络IP地址。
   
   - backend_port：连接到后端PostgreSQL数据库节点的网络端口号。
   
   - backend_flag：Pgpool控制节点行为。
    
5. 使用md5模式，配置pool_passwd密码文件。
   ```
   pg_md5 --md5auth --username=digoal "xxxxxxx"  
   pg_md5 --md5auth --username=nobody "xxxxxxx"
   ```
   生成digoal和nobody用户密码，并自动写入pool_passwd文件。
   
6. 检查自动生成的pool_passwd文件。
   ```
   cd /etc/pgpool-II-12
   cat pool_passwd
   ```
   pool_passwd：Pgpool存储PostgreSQL用户的加密密码的配置文件。
   图3执行结果   
   ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002412024678.png "点击放大") 
7. 配置pgpool_hba文件。
   ```
   cd /etc/pgpool-II-12  
   cp pool_hba.conf.sample pool_hba.conf  
   vi pool_hba.conf  
   # 按i进入编辑模式
   # 在pool_hba.conf文件中添加以下内容
   host all all 0.0.0.0/0 md5
   ```
   - pool_hba.conf.sample：客户端连接认证规则的配置文件模板。
   
   - pool_hba.conf：客户端连接Pgpool时的认证规则文件。
   
   
   图4执行结果   
   ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002441303381.png "点击放大") 
8. 配置PCP管理密码文件。 该操作是用来管理Pgpool的密码和用户，不是数据库的用户和密码。
   ```
   cd /etc/pgpool-II-12 
   pg_md5 abc  # 例如密码是abc 
   900150983cd24fb0d6963f7d28e17f72
   cp pcp.conf.sample pcp.conf  
   vi pcp.conf  
   # 按i进入编辑模式
   # 在pcp.conf文件中添加以下内容
   USERID:MD5PASSWD  
   manage:900150983cd24fb0d6963f7d28e17f72  #表示使用manage用户来管理PCP密码文件
   ```
   - pcp.conf.sample：配置PCP管理接口认证的模板文件。
   
   - pcp.conf：PCP管理接口认证的文件，定义管理员远程管理Pgpool的用户名和加密密码。
    
 
#### 步骤3：启动Pgpool并验证读写分离
1. 启动Pgpool。
   ```
   cd /etc/pgpool-II-12
   pgpool -f ./pgpool.conf -a ./pool_hba.conf -F ./pcp.conf
   ```
   
2. 通过Pgpool连接数据库。
   ```
   psql -h 127.0.0.1 -p 9999 -U digoal -d postgres
   ```
   - 127.0.0.1：使用pgpool.conf配置文件中listen_addresses配置的IP地址。
   
   - 9999：pgpool.conf中配置默认监听端口号。
   
   
   图5执行结果   
   ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002445623777.png "点击放大") 
3. 查看当前数据库Pgpool集群状态信息。
   ```
   show pool_nodes;
   ```
   图6执行结果   
   ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002407748524.png "点击放大") 
4. 在数据库上执行以下命令，然后断开重连，再次查询pg_is_in_recovery()，如果交替返回t和f，说明是交替将请求发送了给主库和只读库，即读写分离成功。
   ```
   SELECT pg_is_in_recovery();
   ```
   pg_is_in_recovery()：检测数据库实例当前是否处于恢复模式。返回t，表示当前是备库；返回f，表示当前是主库。
   图7执行结果   
   ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002445703605.png "点击放大") 
 
#### 常见问题
#### 如何停止、重新加载Pgpool配置？
您可以使用pgpool --help查询帮助命令，例如：
```
cd /etc/pgpool-II-12
pgpool -f ./pgpool.conf -m fast stop
```
#### 如果有多个只读实例，应该如何配置pgpool.conf文件？
根据pgpool.conf文件配置字段样式，持续增加新的配置信息，例如：
```
backend_hostname1 = 'xx.xx.xxx.xx'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'
backend_hostname2 = 'xx.xx.xx.xx'
backend_port2 = 5432
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'
```
#### 查询pg_is_in_recovery()时，一直返回t或f怎么办？
如果一直返回t，表示Pgpool只连接到了从库；如果一直返回f，表示Pgpool只连接到了主库。同时，执行**show pool_nodes;**始终存在异常节点。
图8执行结果   
![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002441309157.png "点击放大")
可以通过清理pgpool_status配置文件并重启尝试，例如：
```
cd /etc/pgpool-II-12
# 暂停服务
pgpool -f ./pgpool.conf -m fast stop
# 删除配置文件
rm /tmp/pgpool_status
# 重启服务
pgpool -f ./pgpool.conf -a ./pool_hba.conf -F ./pcp.conf
```
如果还是存在异常，可以尝试pcp_attach_node重新注册主库或从库。pcp_attach_node工具是Pgpool自带的集群管理工具，pcp_attach_node工具可以将集群中的节点重新注册到集群中。
- 查看PCP工具的连接用户。
  ```
  cd /etc/pgpool-II-12
  cat pcp.conf
  ```
  图9执行结果   
  ![](https://support.huaweicloud.com/bestpractice-rds-pg/zh-cn_image_0000002412183882.png "点击放大") 
- 使用pcp_attach_node重新注册。
  ```
  pcp_attach_node -h 本地IP -U pcp_user node_id
  ```
  例如：
  ```
  pcp_attach_node –h 127.0.0.1 –U manage 0
  ```