搞这套pgpool 4.2.2配合PostgreSQL 11.6的读写分离高可用架构前前后后折腾了一周多中间踩了不少坑也把各种配置参数翻来覆去调过好几轮。这篇就把整个搭建过程、测试方法、以及我在实际环境中遇到的问题全部梳理出来给后面要搞类似架构的朋友做个参考。先交代一下背景手里的业务库从单机PostgreSQL一路涨到了日均几十万次查询单库扛写入问题不大但读压力一上来慢查询和锁等待就开始冒头。临时方案是业务侧自己做读写分离但应用代码里到处埋数据源切换逻辑维护成本高且容易出错。于是决定在数据库和业务之间加一层中间件统一管连接、分读流量、做节点故障切换最后选定了pgpool II 4.2.2加PostgreSQL 11.6这个组合。这篇文章适合谁看打算引入pgpool做读写分离的、已经在用PostgreSQL流复制但缺一个统一入口的、以及想了解pgpool高可用故障切换到底怎么生效的都可以照着这套思路来。文章不会只贴配置会把每个关键参数为什么这么设说清楚。1. 为什么选pgpool 4.2.2搭配PostgreSQL 11.6选型和取舍1.1 需求拆解这个架构到底要解决什么问题先别急着装包得先想清楚自己需要什么。我这边列了四个必须满足的条件业务层只连一个数据库地址不感知后端有几台机器。读请求能被分发到多个只读节点降低主库压力。主库故障时自动切换并且切换时间控制在可接受范围内。切换后新主库能自动被集群识别业务不断连或者至少快速重连。这四个需求对应到pgpool身上分别是虚拟IP/统一入口、负载均衡、自动故障切换、以及节点自动恢复。PostgreSQL 11.6自带的流复制解决了数据冗余问题但它本身不做连接路由也需要手动执行pg_ctl promote所以中间层必须有一个能感知节点状态并执行切换动作的角色。pgpool恰好是同时具备连接池、读写负载均衡、自动故障切换这三个核心能力的方案。相比在应用层自己做pgpool对业务透明也不需要改代码。1.2 版本选择的理由为什么不是其它组合选pgpool 4.2.2之前我其实试过两个方向一是用PostgreSQL 11自带的repmgr做高可用但它只管流复制和主备切换不解决读写分离。如果业务想走读写分离还得再套一层负载均衡组件架构链路就长了。二是用应用层的多数据源框架比如读写分离中间件。但应用里多个服务都要接数据库每个服务都得配一套规则后续加节点、换节点都得改多处配置运维成本不低。pgpool则把连接管理集中到了数据库前端理论上应用只需要一个JDBC或ODBC连接串。4.2.x这个系列在配置结构上比3.x更清晰比如backend_*参数改用数字下标配置易读性更好。PostgreSQL 11.6本身是11系列比较稳定的版本支持流复制、pg_rewind等pgpool故障恢复依赖的基础能力组合在一起兼容性风险最小。1.3 版本兼容性的几个注意点pgpool 4.2.2官方支持的PostgreSQL版本覆盖9.6到12对11.6完全兼容。但要注意pgpool的小版本和PostgreSQL小版本之间有些细节差异比如pgpool_recovery扩展在PostgreSQL 11下需要依赖pg_rewind和postgresql-contrib如果只装了核心包后续做在线恢复时可能会因为缺少pg_rewind而失败。另外pgpool 4.2.2里的raw、replication、slave三种模式中读写分离高可用必须要用master/slave模式配合流复制。如果你看到网上教程让人把replication_mode打开那是pgpool内置的同步复制模式和PostgreSQL流复制是两码事千万别混淆。2. 环境规划与部署前的关键准备2.1 拓扑设计与节点规划我这边用了三台机器角色如下节点角色用途节点APostgreSQL主库 pgpool主节点承担写入同时作为pgpool仲裁主节点节点BPostgreSQL备库1 pgpool备节点承担读流量提供watchdog投票节点CPostgreSQL备库2 pgpool备节点承担读流量提供watchdog投票注意这里三台都装了pgpool三台都跑watchdog形成一个奇数节点集群。为什么必须是奇数因为watchdog做脑裂仲裁时要投票选leader奇数节点能保证多数派一定存在如果只有两台pgpool节点间网络抖动时两边都可能认为对方挂了谁也说服不了谁。PostgreSQL流复制拓扑是主库在AB和C从A拉取WAL。pgpool的前端虚拟IP可以绑定在节点A或者单独一台机器上我这边直接用的某内网负载均衡器绑定到三个pgpool节点由负载均衡做健康检查这样免去了自己维护keepalived的麻烦。如果你没有负载均衡器也可以让pgpool的watchdog直接托管虚拟IP原理一样。2.2 PostgreSQL 11.6流复制环境的搭建数据库层面先要把主备关系做好。主库的postgresql.conf里几个关键项listen_addresses * wal_level replica max_wal_senders 10 wal_keep_segments 64 hot_standby on备库在初始化完成后用pg_basebackup从主库拉基础备份pg_basebackup -h 主库IP -p 5432 -U replicator -D /var/lib/postgresql/11/main -R -X stream -P-R参数会自动生成standby.signal文件并在postgresql.auto.conf里写入primary_conninfo这是PostgreSQL 12之前开启只读备库的推荐做法。流复制账号必须单独建不要拿超级用户直接到处用。主库上创建复制账号CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD your_password;确认流复制是否在跑在主库执行SELECT * FROM pg_stat_replication;如果看到两个备库的WAL发送进程说明复制链路已经通了。这一步必须先行因为pgpool的读写分离和故障切换都建立在流复制正常的基础上流复制不稳后面一切测试都没有意义。2.3 编译安装pgpool 4.2.2时的依赖坑pgpool 4.2.2我采用的是源码编译安装。官方提供了RPM包但不同系统的仓库版本比较旧源码安装能确保拿到指定版本。编译前需要的依赖yum install -y gcc make readline-devel zlib-devel postgresql11-devel重点提醒postgresql11-devel必须装否则pgpool源码里的configure会找不到pg_config导致一些数据库相关的功能模块无法开启。装完要确认pg_config --version输出的确实是11.6的路径如果系统里同时装了多个PostgreSQL版本PATH环境变量可能会指向错的版本。编译过程本身没什么特别./configure --prefix/usr/local/pgpool --with-pgsql/usr/lib/pgsql/11 make make install安装完在/usr/local/pgpool/etc下生成配置。pgpool 4.2的配置入口是pgpool.conf.sample需要复制一份为pgpool.conf来改。还有个pcp.conf是给管理命令用的需要单独生成密码文件pg_md5 your_pcp_password echo pcp_user:加密后的密码 /usr/local/pgpool/etc/pcp.conf这个pcp_*系列命令在故障切换测试中会频繁用到如果配置文件没配对后面pcp_node_count等命令会一直报认证失败。3. pgpool核心配置逐项拆解从读写分离到故障切换3.1 master/slave模式与负载均衡参数pgpool的pgpool.conf配置量很大但读写分离高可用真正关心的可以分成三块后端节点定义、负载均衡、故障切换。后端节点定义backend_cluster_mode on backend_hostname0 节点A的IP backend_port0 5432 backend_weight0 0.3 backend_data_directory0 /var/lib/postgresql/11/main backend_flag0 ALLOW_TO_FAILOVER backend_hostname1 节点B的IP backend_port1 5432 backend_weight1 0.35 backend_data_directory1 /var/lib/postgresql/11/main backend_flag1 ALLOW_TO_FAILOVER backend_hostname2 节点C的IP backend_port2 5432 backend_weight2 0.35 backend_data_directory2 /var/lib/postgresql/11/main backend_flag2 ALLOW_TO_FAILOVERbackend_weight是负载均衡的权重0.3/0.35/0.35表示三台机器读流量分配比例约为30%、35%、35%。如果你想让主库完全不承担读流量可以设成0但这样主库连接数会下降吞吐余量也变小我一般建议主库保留少量读权重比如0.1或0.2既能让主库连接池保持活跃也可以在备库异常时兜底。读写分离的核心开关load_balance_mode on master_slave_mode on master_slave_sub_mode streammaster_slave_sub_mode stream表示后端是流复制架构pgpool会通过判断节点的pg_is_in_recovery()状态来决定谁是主库从而自动路由写请求。不要开replication_mode那个是pgpool自己做的同步复制跟流复制完全不是一回事。3.2 连接池参数为什么不能盲目调大pgpool本身自带会话级连接池不是事务级连接池。也就是说一个前端客户端连接会一直复用一个后端数据库连接直到客户端断开或超过connection_life_time。我这边配置connection_cache on max_pool 4 connection_life_time 300 reset_query_list ABORT; DISCARD ALLmax_pool 4表示每个pgpool子进程能缓存到同一个后端节点的数据库连接最多4条。如果业务并发很高max_pool不是越大越好因为每个pgpool子进程都会持有这4条连接子进程数量乘以max_pool再乘以节点数就是后端数据库要承受的最大连接数。例如num_init_children 32、max_pool 4、后端3个节点最坏情况下一个pgpool节点就要占掉后端节点128个连接槽位。如果PostgreSQL的max_connections只开到100数据库会直接拒绝连接。这是我认为pgpool配置里最容易被忽略的数学题。建议根据业务的真实并发量反推先确定PostgreSQL能承受多少连接再除以节点数再除以num_init_children得出max_pool上限。我最后用的是num_init_children 16、max_pool 4后端节点最大连接压力为16乘以4等于64留了比较充裕的余量。3.3 健康检查与故障切换的完整逻辑健康检查参数health_check_period 5 health_check_timeout 3 health_check_user pgpool_check health_check_password your_check_password health_check_max_retries 3 health_check_retry_delay 1health_check_user会自动在对应的数据库里做简单查询建议单独建一个最小权限的账号不要用超级用户。健康检查周期5秒意味着一个节点故障最多5秒内被发现加上3秒超时和重试整体感知时间在8到10秒左右这是可以接受的切换延迟。故障切换脚本failover_command /usr/local/pgpool/etc/failover.sh %d %h %H %p %P%d是被切换节点的编号%h是主机名%H是新的主库主机名%P是旧主库编号。脚本内容要做的事可以简化成#!/bin/bash old_node_id$1 old_host$2 new_primary$3 old_primary_id$4 if [ $new_primary ! ]; then ssh postgres$new_primary touch /tmp/pgpool_promote_flag fi实际上PostgreSQL 11下pgpool并不是靠failover脚本执行pg_ctl promote而是pgpool检测到主库不可用后会调用pgpromote机制自动对备库执行promote。failover脚本更多是给外部监控或收尾动作用的比如删除旧主库的pgpool节点注册信息。这点要看清pgpool 4.2对PostgreSQL流复制主备切换有自己的内置处理逻辑。不过为了稳妥我还是在failover脚本里加了通过ssh触发备库提升的逻辑确保即使pgpool内置promote链路出问题备库也能独立完成提升。脚本必须保证有执行权限且运行pgpool的用户对脚本里的路径有访问权。3.4 watchdog高可用pgpool自身不能成为单点pgpool如果只部署一台数据库主备切换了但pgpool挂了业务照样连不上数据库所以pgpool节点也得组成高可用。watchdog配置需要启用use_watchdog on wd_hostname 本机IP wd_port 9000 wd_heartbeat_port 9694 wd_lifecheck_method heartbeat wd_heartbeat_keepalive 2 wd_heartbeat_deadtime 10 wd_escalation_command /usr/local/pgpool/etc/escalation.sh wd_de_escalation_command /usr/local/pgpool/etc/de_escalation.shwd_lifecheck_method heartbeat表示通过专用心跳端口互相探活。还有另一个方法是查询模式就是每台pgpool去查数据库状态来判断存活但这个模式要求所有pgpool都能访问数据库如果数据库整体不可用watchdog判断也会受干扰。用独立心跳更干净。每台pgpool的watchdog都会参与leader选举只有leader节点对外提供VIP或负载均衡注册。wd_escalation_command在节点晋升为leader时执行我这边的内容是调用负载均衡器的API把当前节点加入后端池。如果你用keepalived托管VIP这里就是执行ip addr add绑定虚拟IP。4. 读写分离与高可用实测从压测到故障注入4.1 读写分离是否生效的判断方法架构搭完先不要急着压测先确认读写分离真的在工作。我用pgpool自带的psql连上去执行一个只读查询然后去三台数据库节点上看日志或会话最直接的方法是在每台PostgreSQL上查询pg_stat_activity看client_addr是否来自pgpool节点以及查询SQL落在哪台机器。也可以直接用pgpool提供的show pool_nodes;命令SHOW POOL_NODES;输出类似node_id | hostname | port | status | lb_weight | role ----------------------------------------------------- 0 | 节点A | 5432 | up | 0.3000 | primary 1 | 节点B | 5432 | up | 0.3500 | standby 2 | 节点C | 5432 | up | 0.3500 | standbyrole列自动识别出了主备说明pgpool和PostgreSQL的流复制状态已经打通。如果role全部显示standby或者主节点显示不对大概率是后端节点的database_redirect_preference_list或load_balance_mode配置有问题。验证负载均衡更细的办法是按连接数统计。我写了一个比较简单的压测用100个并发连接循环执行SELECT count(*) FROM t_user WHERE create_time now() - interval 1 hour;持续跑5分钟然后分别查三台数据库上该条查询的执行次数SELECT count(*) FROM pg_stat_statements WHERE query LIKE %t_user%;虽然PostgreSQL默认不开启pg_stat_statements建议你装一下contrib并把它加到shared_preload_libraries这样读写分离的流量统计会非常直观。实测结果大致是主库约29%备库1约35%备库2约36%和backend_weight基本吻合说明负载均衡是按照权重正常分发的。4.2 写请求和读请求的路由边界读写分离最容易踩的坑是以为只有SELECT才算读实际上SELECT开头的语句如果在事务里可能被路由到主库。pgpool默认对事务中的语句处理是有讲究的。在master/slave模式下以下情况会强制路由到主库BEGIN之后的第一条语句如果开启的是读写事务。写事务中执行任何SELECT。使用了临时表的查询。带FOR UPDATE或FOR SHARE的查询。调用了易变函数比如nextval()、now()的语句。所以如果你的业务代码喜欢先BEGIN然后一直做查询直到提交这些查询全部会走主库备库流量会很低。这不是pgpool配置问题而是事务模式决定的。要解决这个业务侧的只读查询尽量用只读事务BEGIN READ ONLY;pgpool识别到READ ONLY后会把后续查询路由到备库。另一个方案是让应用彻底拆分把只读接口和写接口放到不同数据源但既然用了pgpool事务标记是成本最低的做法。4.3 故障切换实测主库宕机后发生了什么故障切换是最刺激的部分。我这边模拟的场景是直接对主库执行硬下线不优雅关库模拟断电# 在节点A上模拟宕机 echo c /proc/sysrq-trigger或者更温和一点直接停PostgreSQL服务systemctl stop postgresql-11建议第一次测试用停止服务安全可控后续再测试用强制断电。两种情况下的切换流程略有差异但核心结论是差不多的。观察到的时序时间事件T0秒主库PostgreSQL停止服务T3秒pgpool健康检查第一次超时T6秒pgpool判定节点0异常进入failover流程T7秒pgpool对某个备库执行promoteT10秒新主库完成提升进入只读备库变读写状态T11秒pgpool更新节点状态节点0标记为down节点1变成primary整个过程大概10到12秒。业务侧如果配置了合理的JDBC连接超时重试基本感知就是短暂报错后自动恢复。我用的应用连接池设置connectionTimeout3000、validationTimeout1000、maxLifetime600000实测在切换期间应用大约出现10到20个失败请求随后全部恢复正常。这个过程中有个细节pgpool的failover动作不需要外部脚本去执行pg_ctl promote但建议大家还是留着failover脚本做通知。我在脚本里加了curl回调到告警平台切换发生后第一时间能收到通知比自己去查pool_nodes状态靠谱得多。4.4 故障节点恢复与重新加入集群主库宕机后旧主库本身的数据已经落后于新主库不能直接重新启动让它自动加回集群否则会出现数据分叉。要把旧主库恢复成新主库的备库必须用pg_rewind或者重新pg_basebackup。我在测试中采用的是重新拉取基础备份# 在旧主库节点A上 # 清空数据目录 rm -rf /var/lib/postgresql/11/main/* # 从当前主库重新拉取 pg_basebackup -h 新主库IP -p 5432 -U replicator -D /var/lib/postgresql/11/main -R -X stream -P重新拉完后等它追上WAL再用pcp_attach_node把节点加回pgpoolpcp_attach_node -h 127.0.0.1 -U pcp_user -p 9898 -n 0注意pcp_attach_node不是让PostgreSQL恢复流复制它只是把pgpool对节点的状态从down改为up节点真正要能服务还得靠pg_basebackup把数据追平。顺序一定要先恢复数据、再让pgpool重新认识它。如果数据量比较大拉基础备份的时间长这段时间内节点的status会一直是down但不影响业务因为读写分离只会往状态up的节点发流量。这也是pgpool的一个优点节点维护可以在线做。5. 测试过程中的问题排查认证失败、复制延迟、prepared statement5.1 客户端认证失败的完整排查链路第一次连接pgpool时应用报错信息大概是psql: FATAL: no pg_hba.conf entry for host ...非常容易让人误以为是后端数据库拒绝连接。但我排查后发现pgpool的认证不只是透传它会先和后端数据库做认证然后自己再接受前端认证前面那层如果失败错误会在后端数据库日志里体现。排查链路是这样的先在pgpool所在机器上直接连PostgreSQL确认pg_hba.conf允许对应网段访问。再确认pgpool配置文件里的enable_pool_hba off如果开启了pgpool会使用自己的pool_hba.conf做访问控制这个文件默认内容不一定包含你的应用网段。确认health_check_user对应账号能在所有节点上登录。我最后的问题是pool_hba.conf里漏了应用网段把enable_pool_hba改成off后直连pgpool就通了。如果你不想关闭这个能力就在pool_hba.conf里把网段和认证方法补全。5.2 复制延迟导致的读写不一致读写分离后另一个常见问题是备库查询读到旧数据。PostgreSQL流复制默认是异步的主库提交事务到备库应用完WAL之间存在延迟。如果应用先写了一条数据然后立刻从备库读可能查不到。我在测试中故意插入一条记录并立即查询确实存在大约几百毫秒到1秒不等的延迟网络环境好、负载低的时候延迟很小但高峰期会放大。针对这个问题有几个处理思路关键业务强制走主库pgpool支持配置database_redirect_preference_list可以把特定库、特定用户的所有读写都指向主库适合账户、订单这类强一致性场景。应用侧对需要实时读的数据加短暂等待或走主库查询。使用同步流复制把延迟降到接近零但会牺牲写入性能需要业务接受。pgpool 4.2还支持delay_threshold参数配合write_threshold如果备库复制延迟超过阈值会临时把它排除出负载均衡。这个参数我建议打开比如设成delay_threshold 10单位是MB也就是备库落后主库超过10MB的WAL时pgpool就不往它分配新查询了。5.3 failover脚本执行环境导致的连环坑我最初把failover脚本写好后手动执行是正常的但切换发生时脚本根本没跑。排查后发现问题在于pgpool的failover_command是通过shell执行的但执行环境里PATH可能没有包含ssh、curl这些命令的路径。手动测试时终端环境变量是全的而pgpool作为服务启动时继承的是最小化环境。解决办法很简单脚本开头显式设置#!/bin/bash export PATH/usr/bin:/bin:/usr/local/bin:/usr/sbin:/sbin另一个坑是脚本里用了ssh postgresnew_primary但pgpool服务运行用户如果postgres而postgres的authorized_keys没有配置好会导致ssh执行失败。我建议在脚本里用su - postgres -c ...或者避免ssh操作把通知类动作放到最后不要因为通知失败影响主流程。5.4 prepared statement和连接池冲突测试Java应用时发现某些PreparedStatement在切换后报cached plan must not change result type或者直接报no such savepoint。这是因为pgpool开启了连接池后端数据库连接被复用而应用侧的prepared statement状态还留在旧连接上。pgpool 4.2.x对协议级的prepared statement做了不少改进但JDBC默认的prepareThreshold5会让驱动在第五条重复执行时换成服务端预编译这条路在连接池场景下容易出问题。解决办法两种在JDBC连接串加prepareThreshold0强制走客户端预编译减少pgpool和后端的协议状态同步负担。或者在pgpool配置里设置enable_query_cache off这类的缓存关闭同时关注ignore_leading_white_space等解析开关。实测中用prepareThreshold0后切换期间的prepared statement报错就明显减少了。代价是预编译放到客户端性能有一点损耗但对绝大多数业务无感。6. 上线前还要做的几件事监控、备份和后续优化6.1 pgpool自身状态的监控pgpool虽然提供了pool_status、pool_nodes等命令但这些命令需要登录到数据库节点上使用不适合交给监控系统直接拉取。更推荐的做法是周期性执行pcp_node_info这个命令可以输出每个节点的状态码脚本判断很方便pcp_node_info -h 127.0.0.1 -U pcp_user -p 9898 -n 0返回里的状态码含义0是初始化、1是waiting、2是up、3是down。监控告警阈值就设成状态不为2时触发。此外还可以把pgpool的日志接入统一的日志采集。pgpool日志里面健康检查失败和failover信息都会留有痕迹例如LOG: failover: restarting和LOG: worker process received signal抓这些关键字做告警会比单纯看进程存活更可靠。6.2 数据库备份策略不能因为高可用就松懈很多人误以为有了主备流复制就不需要备份了实际上流复制不等于备份。一条DROP TABLE语句一旦在主库执行备库也会同步执行数据照样丢。所以原来的pg_dump或者pg_basebackup定时任务必须保留。我这边做法是每天凌晨通过pg_dump逻辑备份全库保留7天每周做一次pg_basebackup物理备份保留4周。逻辑备份用于误操作恢复物理备份用于快速重建整个数据目录。在pgpool环境下做备份要注意备份客户端连接pgpool的时候如果走的是读写分离pg_dump默认会开启事务可能被路由到主库或备库这不影响备份结果但如果你希望备份只从备库拉需要给备份账号设置database_redirect_preference_list强制指向备库节点。6.3 后续版本升级路线的思考这次测完pgpool 4.2.2后我对后续升级的方向也有了一些判断。pgpool 4.3和4.4主要改进在watchdog日志、在线恢复流程、以及对新版本PostgreSQL的适配。如果业务已经稳定在PostgreSQL 11上不建议为了追新而频繁升级pgpool除非遇到明确的Bug或安全漏洞。真正需要提前规划的是PostgreSQL大版本升级比如从11升到14或15。pgpool 4.2.2对PostgreSQL 12以上的版本支持不全升级前必须先升pgpool。建议在测试环境用同样的配置脚本把pgpool升到4.4或4.5确认读写分离和failover行为没有变化后再动生产不要挤在同一个变更窗口里同时升两条链路。6.4 最终体会整个项目测试下来pgpool 4.2.2加PostgreSQL 11.6这套架构性能开销非常小读写分离的流量分配精度高故障切换在10秒左右完成完全满足我对中间件高可用的预期。最耗时间的反而不是安装配置而是排查那些连接池、认证、脚本执行环境相关的隐性问题。如果让我重新来一次我会先花更多时间做业务层面的读写分类把只读事务的BEGIN READ ONLY规范推到所有应用团队这样读写分离的效果会比单纯靠pgpool参数调整好得多。连接池参数和健康检查参数也会提前用公式推算而不是等压测后被动调。另外建议准备一个独立的测试库专门用于反复做故障注入和节点恢复演练这套架构的价值只有在反复演练中才能真正体现出来。
