PostgreSQL 基于pgpool-II的读写分离

pgpool-II概述

什么是pgpool-II

pgpool-II是一个PostgreSQL连接池和负载均衡工具，提供以下核心功能：

连接池管理
读写分离
负载均衡
自动故障转移
并行查询
流复制支持

适用场景

高并发读写场景，需要分离读写流量
需要减少PostgreSQL服务器连接数
希望实现自动故障转移
需要负载均衡多个只读节点

版本兼容性

pgpool-II版本	支持的PostgreSQL版本
4.3.x	9.6 - 15
4.2.x	9.5 - 14
4.1.x	9.4 - 13

pgpool-II安装

从源代码安装

1. 安装依赖

bash

# CentOS/RHEL
yum install -y gcc gcc-c++ make libpq-devel openssl-devel

# Ubuntu/Debian
apt-get install -y gcc g++ make libpq-dev libssl-dev

2. 下载并编译pgpool-II

bash

# 下载最新稳定版
wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.3.4.tar.gz
tar xzf pgpool-II-4.3.4.tar.gz
cd pgpool-II-4.3.4

# 配置和编译
./configure --prefix=/usr/local/pgpool --with-openssl
make && sudo make install

3. 设置环境变量

bash

echo 'export PATH=/usr/local/pgpool/bin:$PATH' >> ~/.bashrc
source ~/.bashrc

从软件包管理器安装

CentOS/RHEL

bash

# 安装pgpool-II仓库
yum install -y https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/pgpool-II-release-4.3-1.noarch.rpm

# 安装pgpool-II
yum install -y pgpool-II-pg14

Ubuntu/Debian

bash

# 添加pgpool-II仓库
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update

# 安装pgpool-II
apt-get install -y pgpool2

pgpool-II配置

核心配置文件

pgpool-II的主要配置文件包括：

/etc/pgpool-II/pgpool.conf：主配置文件
/etc/pgpool-II/pool_hba.conf：访问控制配置
/etc/pgpool-II/pcp.conf：PCP命令认证配置
/etc/pgpool-II/pool_passwd：用户密码文件

主配置文件pgpool.conf

1. 基本配置

ini

# 监听地址和端口
listen_addresses = '*'
port = 9999

# PostgreSQL连接配置
backend_hostname0 = '主库IP'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/14/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '备库1IP'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/14/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = '备库2IP'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/14/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

2. 连接池配置

ini

# 连接池设置
num_init_children = 32
max_children = 128
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
reset_query = 'DISCARD ALL'

3. 读写分离配置

ini

# 负载均衡设置
load_balance_mode = on

# 读写分离设置
write_function_list = 'nextval,setval,lastval,select pg_catalog.set_config'
master_slave_mode = on
master_slave_sub_mode = 'stream'

# 主库检测语句
sr_check_period = 3
sr_check_user = 'postgres'
sr_check_password = 'password'
sr_check_database = 'postgres'
sr_check_statement = 'SELECT 1'

4. 健康检查配置

ini

# 健康检查设置
health_check_period = 5
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'password'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 1

访问控制配置pool_hba.conf

ini

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         0.0.0.0/0            scram-sha-256
host    all         all         ::/0                 scram-sha-256

PCP命令配置

1. 配置pcp.conf

bash

# 生成PCP用户密码
echo "pcpuser:`pg_md5 pcp_password`" > /etc/pgpool-II/pcp.conf

2. 配置pool_passwd

bash

# 添加PostgreSQL用户到pool_passwd
pg_md5 -u postgres -p

读写分离实现

主从节点配置

1. 配置主库

在主库上创建复制用户和监控用户：

sql

-- 创建复制用户
CREATE USER replication REPLICATION LOGIN PASSWORD 'replication_pass';

-- 创建监控用户
CREATE USER pgpool_monitor WITH SUPERUSER LOGIN PASSWORD 'monitor_pass';

2. 配置pg_hba.conf

在所有PostgreSQL节点的pg_hba.conf中添加pgpool-II访问权限：

ini

# 允许pgpool-II访问
host    all             pgpool_monitor    pgpool-ip/32            scram-sha-256
host    replication     replication       pgpool-ip/32            scram-sha-256

# 允许备库复制
host    replication     replication       备库1-ip/32            scram-sha-256
host    replication     replication       备库2-ip/32            scram-sha-256

启动pgpool-II

1. 启动服务

bash

# 系统服务方式
systemctl start pgpool-II-pg14

# 手动启动
pgpool -n -d > /var/log/pgpool.log 2>&1 &

2. 验证启动状态

bash

# 检查pgpool-II进程
ps aux | grep pgpool

# 检查端口监听
netstat -tuln | grep 9999

验证读写分离

1. 连接测试

bash

# 使用psql连接pgpool-II
psql -h pgpool-ip -p 9999 -U postgres -d postgres

2. 验证读写分离

sql

-- 在pgpool-II连接中执行以下语句
CREATE TABLE test_rw (id serial primary key, name varchar(50), created_at timestamp default now());

-- 写入数据
INSERT INTO test_rw (name) VALUES ('test1');

-- 读取数据，多次执行查看执行计划
EXPLAIN ANALYZE SELECT * FROM test_rw;

3. 使用pgpool-II监控工具

bash

# 查看后端节点状态
pcp_node_info -h localhost -p 9898 -U pcpuser -n 0
pcp_node_info -h localhost -p 9898 -U pcpuser -n 1
pcp_node_info -h localhost -p 9898 -U pcpuser -n 2

# 查看连接池状态
pcp_pool_status -h localhost -p 9898 -U pcpuser

pgpool-II监控与管理

内置监控视图

pgpool-II提供了内置的监控视图，可以直接从PostgreSQL连接中查询：

sql

-- 查看后端节点状态
SELECT * FROM pgpool_nodes;

-- 查看连接池状态
SELECT * FROM pgpool_pools;

-- 查看版本信息
SELECT * FROM pgpool_version();

PCP命令行工具

常用PCP命令

bash

# 查看节点信息
pcp_node_info -h localhost -p 9898 -U pcpuser -n 0

# 查看所有节点状态
pcp_nodes_info -h localhost -p 9898 -U pcpuser

# 关闭节点
pcp_detach_node -h localhost -p 9898 -U pcpuser -n 1

# 激活节点
pcp_attach_node -h localhost -p 9898 -U pcpuser -n 1

# 重启pgpool-II
pcp_reload_config -h localhost -p 9898 -U pcpuser

Web监控界面

1. 启用pgpoolAdmin

pgpoolAdmin是pgpool-II的Web管理界面，安装步骤如下：

bash

# 下载pgpoolAdmin
wget https://www.pgpool.net/mediawiki/images/pgpoolAdmin-4.3.2.tar.gz
tar xzf pgpoolAdmin-4.3.2.tar.gz
mv pgpoolAdmin-4.3.2 /var/www/html/pgpooladmin

# 配置权限
chown -R apache:apache /var/www/html/pgpooladmin

2. 配置pgpoolAdmin

编辑/var/www/html/pgpooladmin/conf/config.inc.php文件，配置数据库连接和PCP信息：

php

$conf['servers'][0]['host'] = 'localhost';
$conf['servers'][0]['port'] = 9999;
$conf['servers'][0]['pcp_port'] = 9898;
$conf['servers'][0]['pcp_user'] = 'pcpuser';
$conf['servers'][0]['pcp_password'] = 'pcp_password';

故障排除

常见故障类型

1. 无法连接到pgpool-II

故障现象：

psql: error: connection to server at "pgpool-ip" (pgpool-ip), port 9999 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

解决方案：

检查pgpool-II服务是否正在运行
检查防火墙是否开放9999端口
检查pgpool.conf中listen_addresses配置

2. 读写分离不生效

故障现象：

所有查询都发送到主库
pgpool_nodes视图显示备库状态为down

解决方案：

检查sr_check_period和health_check_period配置
验证pgpool_monitor用户权限
检查备库是否处于只读状态
查看pgpool日志，分析具体错误信息

3. 连接池已满

故障现象：

psql: error: connection to server at "pgpool-ip" (pgpool-ip), port 9999 failed: FATAL:  sorry, too many clients already

解决方案：

增加max_children参数值
优化应用连接管理，减少空闲连接
调整child_life_time和connection_life_time参数

日志分析

pgpool-II的日志位置：

系统服务方式：/var/log/pgpool-II/pgpool.log
手动启动方式：指定的日志文件

关键日志级别：

DEBUG：详细调试信息
INFO：普通信息
WARNING：警告信息
ERROR：错误信息
FATAL：致命错误

最佳实践

性能优化

连接池优化
- 根据系统资源和并发需求调整max_children
- 合理设置max_pool，建议为2-4
- 启用reset_query，确保连接状态干净
负载均衡优化
- 根据备库性能调整backend_weight
- 对于写入密集型应用，考虑增加备库数量
- 启用connection_life_time，定期回收旧连接
健康检查优化
- 合理设置health_check_period，建议5-10秒
- 避免使用复杂的健康检查语句
- 调整health_check_timeout，避免误判

高可用性配置

启用自动故障转移

ini

# 启用自动故障转移
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R'

# 启用自动恢复
recovery_user = 'postgres'
recovery_password = 'password'
recovery_1st_stage_command = '/etc/pgpool-II/recovery_1st_stage.sh'
recovery_2nd_stage_command = '/etc/pgpool-II/recovery_2nd_stage.sh'

配置故障转移脚本

创建/etc/pgpool-II/failover.sh脚本：

bash

#!/bin/bash
# 故障转移脚本示例
failed_node_id=$1
failed_host=$2
failed_port=$3
failed_dbcluster=$4
new_master_host=$5
new_master_port=$6
old_master_host=$7
old_master_port=$8

# 执行故障转移操作
# ...

echo "Failover completed: $failed_node_id -> $new_master_host" > /var/log/pgpool_failover.log

配置多pgpool-II实例

使用HAProxy或Keepalived实现pgpool-II的高可用：

部署多个pgpool-II实例
使用HAProxy进行负载均衡
配置Keepalived实现VIP漂移

安全配置

启用SSL加密

ini

# 启用SSL
ssl = on
ssl_key = '/path/to/server.key'
ssl_cert = '/path/to/server.crt'
ssl_ca_cert = '/path/to/root.crt'

限制访问权限

在pool_hba.conf中限制允许访问的IP地址
为pgpool_monitor用户设置最小必要权限
定期更换PCP用户密码

启用日志审计

ini

# 启用详细日志
log_connections = on
log_disconnections = on
log_statement = 'all'

版本差异注意事项

pgpool-II 4.3.x

支持PostgreSQL 15
增强了逻辑复制支持
新增了多种负载均衡算法
改进了健康检查机制

pgpool-II 4.2.x

支持PostgreSQL 14
增强了自动故障转移功能
改进了Web管理界面
优化了连接池性能

pgpool-II 4.1.x

支持PostgreSQL 13
引入了动态数据库路由
改进了并行查询功能
优化了内存使用

总结

基于pgpool-II的读写分离是PostgreSQL高可用架构中的重要组成部分，它可以有效分离读写流量，提高系统吞吐量和可用性。通过合理配置和监控，可以充分发挥pgpool-II的优势，为业务提供稳定可靠的数据库服务。

在实际生产环境中，建议：

根据业务需求和系统规模选择合适的pgpool-II版本
合理配置连接池参数，避免资源浪费
建立完善的监控体系，及时发现和处理问题
定期进行故障转移演练，确保高可用机制正常工作
关注pgpool-II社区动态，及时更新到稳定版本

通过以上措施，可以确保pgpool-II在生产环境中稳定运行，为PostgreSQL架构提供可靠的读写分离和负载均衡能力。

PostgreSQL 基于pgpool-II的读写分离 ​

pgpool-II概述 ​

什么是pgpool-II ​

适用场景 ​

版本兼容性 ​

pgpool-II安装 ​

从源代码安装 ​

1. 安装依赖 ​

2. 下载并编译pgpool-II ​

3. 设置环境变量 ​

从软件包管理器安装 ​

CentOS/RHEL ​

Ubuntu/Debian ​

pgpool-II配置 ​

核心配置文件 ​

主配置文件pgpool.conf ​

1. 基本配置 ​

2. 连接池配置 ​

3. 读写分离配置 ​

4. 健康检查配置 ​

访问控制配置pool_hba.conf ​

PCP命令配置 ​

1. 配置pcp.conf ​

2. 配置pool_passwd ​

读写分离实现 ​

主从节点配置 ​

1. 配置主库 ​

2. 配置pg_hba.conf ​

启动pgpool-II ​

1. 启动服务 ​

2. 验证启动状态 ​

验证读写分离 ​

1. 连接测试 ​

2. 验证读写分离 ​

3. 使用pgpool-II监控工具 ​

pgpool-II监控与管理 ​

内置监控视图 ​

PCP命令行工具 ​

常用PCP命令 ​

Web监控界面 ​

1. 启用pgpoolAdmin ​

2. 配置pgpoolAdmin ​

故障排除 ​

常见故障类型 ​

1. 无法连接到pgpool-II ​

2. 读写分离不生效 ​

3. 连接池已满 ​

日志分析 ​

最佳实践 ​

性能优化 ​

高可用性配置 ​

安全配置 ​

版本差异注意事项 ​

pgpool-II 4.3.x ​

pgpool-II 4.2.x ​

pgpool-II 4.1.x ​

总结 ​

PostgreSQL 基于pgpool-II的读写分离

pgpool-II概述

什么是pgpool-II

适用场景

版本兼容性

pgpool-II安装

从源代码安装

1. 安装依赖

2. 下载并编译pgpool-II

3. 设置环境变量

从软件包管理器安装

CentOS/RHEL

Ubuntu/Debian

pgpool-II配置

核心配置文件

主配置文件pgpool.conf

1. 基本配置

2. 连接池配置

3. 读写分离配置

4. 健康检查配置

访问控制配置pool_hba.conf

PCP命令配置

1. 配置pcp.conf

2. 配置pool_passwd

读写分离实现

主从节点配置

1. 配置主库

2. 配置pg_hba.conf

启动pgpool-II

1. 启动服务

2. 验证启动状态

验证读写分离

1. 连接测试

2. 验证读写分离

3. 使用pgpool-II监控工具

pgpool-II监控与管理

内置监控视图

PCP命令行工具

常用PCP命令

Web监控界面

1. 启用pgpoolAdmin

2. 配置pgpoolAdmin

故障排除

常见故障类型

1. 无法连接到pgpool-II

2. 读写分离不生效

3. 连接池已满

日志分析

最佳实践

性能优化

高可用性配置

安全配置

版本差异注意事项

pgpool-II 4.3.x

pgpool-II 4.2.x

pgpool-II 4.1.x

总结