文档首页/ 云数据库 RDS_云数据库 RDS for PostgreSQL/ 最佳实践/ 基于Pgpool实现读写分离
更新时间：2025-09-04 GMT+08:00
查看PDF
分享

基于Pgpool实现读写分离

功能介绍

Pgpool是一个专为PostgreSQL设计的中间件，部署在数据库服务器与客户端之间，并且在前后台之间传递消息。 因此Pgpool对于服务器和客户端来说是透明的。通过提供连接池、复制、负载均衡、限制超过限度连接，显著提升数据库集群的性能、可用性和可扩展性。

本文介绍RDS for PostgreSQL实例及只读实例如何结合Pgpool实现读写分离。

前提条件

操作步骤

步骤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
    图1 执行结果
  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用户的加密密码的配置文件。

    图2 执行结果
  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时的认证规则文件。
    图3 执行结果
  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中配置默认监听端口号。
    图4 执行结果
  3. 查看当前数据库Pgpool集群状态信息。 
    show pool_nodes;
    图5 执行结果
  4. 在数据库上执行以下命令，然后断开重连，再次查询pg_is_in_recovery()，如果交替返回t和f，说明是交替将请求发送了给主库和只读库，即读写分离成功。 
    SELECT pg_is_in_recovery();

    pg_is_in_recovery()：检测数据库实例当前是否处于恢复模式。返回t，表示当前是备库；返回f，表示当前是主库。

    图6 执行结果

常见问题

如何停止、重新加载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;始终存在异常节点。

图7 执行结果

可以通过清理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
    图8 执行结果
  • 使用pcp_attach_node重新注册。 
    pcp_attach_node -h 本地IP -U pcp_user node_id

    例如：

    pcp_attach_node –h 127.0.0.1 –U manage 0