pgpool-II 这个中间件我从 PostgreSQL 10 的时代就在用。中间换过几个大版本也踩过不少坑但总体下来它在“连接池 读写分离 自动故障转移”这个组合里依然是配套 PostgreSQL 最完整的一套方案。不过完整归完整生产环境里的问题往往都藏在细节里。最近我在帮一个项目做数据库高可用改造连续撞上了两个很典型的坑故障转移之后应用连接被占满以及负载均衡模式下写后读取到旧数据。两个问题都不算罕见但排查起来都很费时间所以我专门把它们写下来给正在用或者准备用 pgpool-II 做读写分离的朋友做个参考。1. 先说背景我的 pgpool-II 部署形态与核心需求1.1 架构里它到底干了什么我这里线上是一个一主两从的 PostgreSQL 14 集群三台数据库机器各 16 核 64G数据盘是 SSD。主库负责写入两个从库通过异步流复制拉取 WAL提供只读服务。流量结构大概是 70% 读、30% 写。pgpool-II 4.4.3 部署在一台单独的机器上编译安装配置了 pcp、watchdog对外开放 9999 端口作为唯一数据库入口。应用层全部使用 Java连接池是 HikariCP后端连接串指向 pgpool 的 9999 端口。在这个架构里pgpool-II 承担的角色说到底就是三件事。第一是连接池。应用和 pgpool 之间的连接以及 pgpool 和真实数据库之间的连接是两个层面的连接池。pgpool 会把到后端数据库的连接缓存下来复用这样应用每秒钟创建几千次新连接也没问题后端数据库并不会被打挂。第二是读写分离。pgpool 在中间解析 SQL对 INSERT、UPDATE、DELETE 这类写操作自动路由到主库对 SELECT则按权重分发到主库或从库。第三是健康检查与自动故障转移。它通过 health check 定期探测后端节点一旦发现主库不可用就触发 failover_command将某个从库提升为新主库同时更新内部路由表。这三件事单独拿出任何一件都有更专业的组件可以做。但如果想用一个中间件同时覆盖全部三项pgpool-II 几乎是唯一能撑起这套组合的方案。当然代价也很直接配置项非常多很多默认值偏向“可用”而不是“生产可用”如果直接照着默认配置跑出问题的概率并不低。1.2 为什么选它而不是其他方案选型的时候我其实花了不少时间对比。pgbouncer 的连接池效率很高事务级别的连接复用也很稳定但读写分离和自动故障转移基本要自己另外搭等于还是需要一个额外的代理层。HAProxy 可以做四层 TCP 代理做数据库 IP 漂移和高可用入口很稳但它不解析 PostgreSQL 协议根本做不到 SQL 级别的分发读写分离也没法搞。应用程序自己实现读写分离的选项也考虑过优点是可以结合业务精确控制比如实时性要求高的查询只走主库缺点是需要改造所有代码且每个服务都维护一套路由逻辑后期成本太高。pgpool-II 的好处在于对应用透明。应用只需要知道一个数据库地址至于哪个请求走主库、哪个请求走从库、主库挂掉之后怎么办全部交给它。这个特点对于存量系统迁移特别重要。我接手的时候项目已经有了不少历史代码连接数据库的方式五花八门有 JDBC 直连的有用 ORM 框架的也有少量脚本。如果采用应用层改造的方案工程量不可控。而引入 pgpool-II 之后我只要把数据库地址换成 pgpool 的入口其他都不用动。但透明也有透明的代价。正因为应用感知不到中间层的存在一旦 pgpool 的某个行为不在预期内应用侧报错往往让你摸不着头脑。下面这两个坑基本都属于这一类型。2. 第一则踩坑failover 成功后应用连接全被打满报错刷屏2.1 问题现场故障演练把一个“成功”的切换搞成了事故那天下午我们按计划做故障演练。演练动作很简单在主库机器上执行 systemctl stop postgresql-14模拟数据库进程突然退出。pgpool-II 的健康检查配置是 health_check_period 5health_check_timeout 3也就是说节点状态异常理论上最迟 8 秒内就会被发现。日志显示pgpool 在几分钟内就完成了 failover 动作识别主库 DOWN执行 failover_command其中一个从库被 promote 成新主库。我当时的第一个反应是切换成功了。因为 show pool_nodes 的结果很清楚node 0 的状态是 DOWNnode 1 的状态是 PRIMARY。但大概半分钟后告警群就开始热闹了。应用侧大量异常集中在两种错误一种来自 HikariCP报 Connection is closed另一种来自 pgpool报 sorry, too many clients already。还有一些查询直接卡死导致接口超时。原本以为演练成功就能下班结果变成了一次真实的事故排查。现在复盘问题并不出现在“切换”本身而是出现在“切换完成之后的连接清理”上。故障转移激活了但 pgpool 内部缓存的到旧主库的物理连接并没有被及时销毁这些连接被应用侧的 HikariCP 连接池当成“健康连接”稳稳占着。两边都不释放最终把中间件和数据库之间的连接额度打满了新连接进不来老连接又不能用整个入口就像堵死了一样。2.2 排查过程先把锅里外翻了个遍遇到这种问题第一步肯定是看日志。我在 pgpool 机器上看 /var/log/pgpool/pgpool.log 的后几百行确认 failover 脚本确实执行了。脚本是我自己写的当时只是简单记录时间戳、节点 ID、主机名和角色信息。执行成功没有报错。第二步是确认 pgpool 自身是否还能处理请求。我在本机执行psql -h 127.0.0.1 -p 9999 -U postgres -d postgres -c show pool_nodes。返回正常说明 pgpool 进程还活着也能接受新查询。这就基本排除了中间件本身挂掉的可能。第三步是查连接池状态。这里用到 pcp_pool_status命令类似 pcp_pool_status -h localhost -p 9898 -U pcpuser -w。输出里能看到每个 child process 维护的后端连接池的具体情况。我注意到一个扎眼的现象很多连接还挂在本应已经 DOWN 的 node 0 上状态是 idle。也就是说这些连接是 failover 之前建立、并且一直缓存着的旧连接切换发生后并没有被清理。再结合应用连接池监控HikariCP 的 active-connections 很小但 idle-connections 一直维持在高位。两边一对照原因就清楚了应用侧觉得连接是健康的一直持有不释放pgpool 侧这些连接又关联着已失效的旧主库新请求无法复用也不敢复用但旧 child 又占着资源不退出。于是一大堆连接被“无效占用”真正可用的连接反而没了。2.3 根因分析问题出在默认参数上pgpool-II 的会话模型是这样的每一个前端连接由一个独立的 child process 处理每个 child process 内部对每个后端数据库节点会按 max_pool 参数缓存连接。比如 max_pool4表示一个 child process 最多为一个前端会话缓存 4 条到各个数据库节点的连接。这些缓存连接默认是长期复用的而控制连接生命周期的参数叫 connection_life_time默认值是 0表示永不过期。还有一个参数 child_life_time默认 300 秒控制空闲 child process 的存活时间。正常情况下这套机制没有太大问题。连接空闲久了child 进程会退出缓存连接自然释放。但 failover 发生时pgpool 要做的是把路由规则换成新主库它并不会主动向已建立的客户端会话推送“后端已更换”的通知更不会立刻杀掉所有现存进程。于是在故障发生后的短时间内应用侧还握着旧连接不放pgpool 侧也守着这些旧连接不丢两边形成了一种“死锁”式的平衡。只有当这些连接的 age 超过 connection_life_time或者 child process 超过了 child_life_time才会被逐步清理。如果这两个参数都没调过抱歉短时间内你就是看不到任何好转。另外一个容易被忽略的点是应用侧连接池。HikariCP 默认认为从连接池取出的 JDBC 连接在 idleTimeout 内都是可用的它不会主动验证物理连接是否真实有效。应用拿到了连向旧主库的连接也不管后面是否还能用直接把它占住。这种“双端占死”的现象最终就会把 pgpool 的可用子进程数量耗尽报出 sorry, too many clients already。2.4 解决方案让两端都学会“及时放下”这个问题的核心其实是两个词连接生命周期和连接有效性检查。两端都设置合理的过期机制并且在取用连接时做一次轻量校验就能避免长期占用的局面。我先调整了 pgpool 侧的配置。connection_life_time 从默认的 0 改成 600让缓存的后端连接最长存活 10 分钟child_life_time 设置为 300让空闲的子进程 5 分钟回收同时在 failover_command 脚本里增加了更完整的状态记录把触发时间、切出节点、切入节点、参数占位符都记下来方便事后排查。关键配置如下connection_cache on max_pool 2 connection_life_time 600 child_life_time 300 child_max_connections 1000max_pool 我降到了 2。原因是这个场景下并发规模并不大降低每个 child 可缓存的后端连接数可以更快地释放旧连接。如果业务并发非常高max_pool 可以适当调大但 connection_life_time 一定要设置否则内存和连接都会成为瓶颈。应用侧也要配合。HikariCP 中我把 idleTimeout 设置为 3000005 分钟maxLifetime 设置为 60000010 分钟并且加上了 connectionTestQuerySELECT 1。加了 connectionTestQuery 之后HikariCP 在每次取用连接的时候会执行一次 SELECT 1用来确认连接确实可用一旦发现底层连接失效就直接丢弃并重建不再傻傻持有。还有一个参数 validationTimeout 也要注意建议设成 3000 左右避免验证连接的时候长时间阻塞。配置改完我们又做了一次同样的故障演练。这次 failover 完成之后应用侧虽然还是有少量报错但 10 秒内就会自动恢复几分钟内连接池完全回到干净的状态。把实测结果记录到项目的运维手册后这个问题就算闭环了。3. 第二则踩坑写后立刻读居然在从库上读到了旧数据3.1 问题现场新保存的配置在列表里“消失了”第二个坑和故障转移无关发生在正常的读写分离过程中。业务是一个后台管理平台编辑同学在表单里新增一条配置点击保存后页面立刻跳回列表页并刷新。正常情况下应该马上看到这条新数据但客户反馈偶尔刷新出来的列表里没有刚保存的记录再手动刷新一次才行。这个问题的频率一天大概出现一两次不算高但对客户感知很不好。尤其后台管理系统不像 C 端高并发用户不会反复刷遇到一次就会觉得系统有问题。我先在应用日志里查找对应请求的执行时间发现写操作和读操作之间的间隔非常短基本在几十毫秒以内。这个时间窗口非常尴尬恰好落在异步流复制延迟的可变区间内。于是我把怀疑对象锁定在 pgpool 的负载均衡上。3.2 排查过程把 SQL 的执行节点看个明明白白排查这个问题的关键是让 pgpool 把每条 SQL 实际发到哪个节点打出来。pgpool 提供两个参数log_statement 和 log_per_node_statement。log_statement 记录每条 SQLlog_per_node_statement 会在日志中追加一行 DETAIL 信息标明这条 SQL 被分发到哪个 DB node。配置如下log_statement on log_per_node_statement on改完配置后重载 pgpool。我在日志里很快就看到了类似下面的内容LOG: statement: SELECT ... FROM config WHERE ... DETAIL: DB node id: 1 (standby)那条出问题的 SELECT确实被分发到了 node 1也就是从库。而在这之前的 INSERT分发到的是 node 0主库。问题就很清楚了写和读被路由到了不同的节点。接着我查流复制延迟。主库上执行select application_name, state, write_lag, flush_lag, replay_lag from pg_stat_replication;结果让我有点意外平时看着都很小真到业务高峰期replay_lag 可以到几百毫秒极端情况下超过 1 秒。这看起来不大但足够让“刚保存的配置立刻要在列表里出现”这种需求出问题了。对于“刚保存的配置立刻要在列表里出现”这种需求来说几百毫秒的延迟已经足以导致读不到新数据。3.3 根因分析负载均衡按规则分不按业务语义分pgpool-II 的负载均衡核心是根据 SQL 类型做路由。源头上它对 INSERT、UPDATE、DELETE 这类写语句只会发给主库对简单的 SELECT则按照 backend_weight 权重分发给主库和各个从库。这个判断是在协议层做的它并不知道你的业务逻辑里“先写后读”是什么意思。换句话说pgpool 并不理解“这个 SELECT 依赖刚写入的数据”。它只看到一条单纯的 SELECT就按照负载均衡策略把它分发了。而此时从库因为还处于延迟窗口内没有拿到最新的 WAL所以返回的结果里少了那条新记录。还有一个因素让问题变得更隐蔽应用层若使用了 ORM很多框架会自动开启事务。如果整个查询发生在事务内并且事务里已经执行过写操作pgpool 会将该事务的所有后续 SQL 锁定在主库执行这不会出问题。但很多代码里保存和查询是两次独立的数据库操作并不在同一事务中。比如保存走 service A查询走 service B或者虽然在同一 service 但方法没有加事务中间隔着一次 RPC 或消息队列这样就成了两个独立的会话查询自然会被负载均衡器分走。当然流复制延迟本身是个动态量。主库空闲时延迟几乎为 0查询发给从库也能读到最新数据主库繁忙、从库负载高或者网络抖动时延迟就会被放大。所以这个问题经常是“偶发”非常考验现场还原能力。3.4 解决方案写后读敏感的查询必须能控住路由第一个方案也最推荐是从业务层解决。把保存和读取放在同一个数据库事务里并且保证事务中包含写操作。由于 pgpool 对于事务内已经发生写操作的会话后续所有 SQL 都会被固定在主库执行所以查询一定不会走到从库。具体到 Spring 工程就是在 Service 方法上加 Transactional让 insert 和 select 共享一个事务。要注意的是如果查询发生在另一个独立的数据源或服务里这个方案就不适用了。第二个方案是用 pgpool 的 SQL 注释标记。pgpool 支持一个特别的注释/NO LOAD BALANCE/。只要你把这条注释写在 SQL 的最前面pgpool 就会跳过负载均衡规则强制将这条 SQL 发送到主库。比如/*NO LOAD BALANCE*/ SELECT * FROM config WHERE id 123;这个方案适合“只有少数关键查询需要保证实时性”的场景。但实践中有两点要提醒。第一注释必须位于 SQL 语句最前面中间不能有空格或者换行出错第二如果你的框架会对 SQL 做规范化处理比如去掉注释那这个标记可能失效。我在 MyBatis 里使用是没问题的因为它会把 SQL 原样送到 JDBC。但如果你用了某些中间件二次封装最好先打一条日志确认实际发出的 SQL 是什么样的。第三个方案是从 pgpool 配置层面做兜底。pgpool 提供了一个参数 delay_threshold用来做从库延迟的负载均衡保护。单位是字节不是毫秒。设置成非 0 值后pgpool 会定期通过 sr_check_user 指定的账号检查各从库的 WAL 延迟情况。如果某个从库的延迟 WAL 字节数超过阈值它就会被标记为延迟节点不再参与负载均衡查询也就不会发过去。延迟恢复后节点又重新参与负载均衡。我这边因为数据量不算大设置成 2MB实际测试下来能挡住大部分高峰延迟场景。sr_check_period 5 sr_check_user postgres sr_check_password your_password delay_threshold 2097152第四个要补充的是关于备库查询冲突的坑。在排查过程中我还看到过另一种备库报错canceling statement due to conflict with recovery in standby server。这种情况是主库在清理老版本数据而备库上正好有长时间运行的查询持有旧快照两边产生冲突standby 主动取消了查询。解决办法是设置 hot_standby_feedback on让主库在清理行版本时考虑备库的快照同时调大 max_standby_streaming_delay。虽然不是这次的问题但如果你也在做读写分离大概率早晚会遇到可以一起配了。# postgresql.conf (主库和备库都加) hot_standby_feedback on max_standby_streaming_delay 60s这些方案叠加之后写后读的问题基本就消失了。需要融会贯通的一点是无论你选哪种方案核心都是“把实时性敏感的流量控制在主库可控范围内”。pgpool 本身没有业务判断能力这个判断只能由你提前设计好。4. 两个坑之后常用排查速查表与配置建议4.1 排查命令速查这一节是我在日常运维中反复用到的一些命令。建议把这张表打印出来贴在工位旁边或者存到团队知识库用的时候不用再翻文档。目的命令示例说明查看节点角色状态psql -h 127.0.0.1 -p 9999 -U postgres -c show pool_nodes;最常用的状态检查能看每个节点的角色、状态、负载权重查看运行时参数psql -h 127.0.0.1 -p 9999 -U postgres -c show pool_status;查看当前生效的 pgpool 配置排查参数覆盖查看连接池详情pcp_pool_status -h localhost -p 9898 -U pcpuser看每个 child process 的后端连接池使用情况查看节点进程数pcp_proc_count -h localhost -p 9898 -U pcpuser查看当前活动的子进程数量判断是否接近上限查看节点信息pcp_node_info -h localhost -p 9898 -U pcpuser -n 0查看指定后端节点的状态信息查看流复制延迟psql -h 主库地址 -U postgres -c select * from pg_stat_replication;查看 write_lag、flush_lag、replay_lag判断备库落后情况验证 SQL 分发节点psql -h 127.0.0.1 -p 9999 -U postgres -c /NO LOAD BALANCE/ select 1;配合 log_per_node_statement 观察 SQL 实际走主还是走备除了这些命令日志是排查问题最重要的入口。pgpool 的日志默认在 /var/log/pgpool/pgpool.log如果设置过 log_destination 和 logging_collector也可能在独立目录下。遇到问题先看日志能少走很多弯路。4.2 我目前在用的核心配置片段经过这两次踩坑我整理了一套当前项目里在用的 pgpool 核心参数覆盖连接池、负载均衡、健康检查和日志。这个片段不代表适合所有业务但可以作为起始模板。listen_addresses * port 9999 socket_dir /var/run/pgpool pcp_port 9898 backend_hostname0 pg-primary backend_port0 5432 backend_weight0 0.5 backend_hostname1 pg-standby-1 backend_port1 5432 backend_weight1 0.5 connection_cache on max_pool 2 connection_life_time 600 child_life_time 300 child_max_connections 1000 load_balance_mode on ignore_leading_white_space on white_function_list black_function_list nextval,setval,pg_reload_conf health_check_period 3 health_check_timeout 2 health_check_max_retries 2 sr_check_period 5 sr_check_user postgres sr_check_password your_password delay_threshold 2097152 failover_command /etc/pgpool/failover.sh %d %h %P关于 backend_weight我暂时设为主备各 0.5。如果你的主库还要承担写压力从库只读能力足够强可以把主库权重调低一些比如主库 0.2两个从库各 0.4这样 SELECT 更多落到从库。但要注意主库权重越低写后读数查询落到主库的概率也越低如果业务里这种模式多反而容易放大一致性问题。所以流量比例要结合业务实时性要求来权衡。4.3 部署前务必做三件事第一件事做故障演练时不要只看“切换成功”还要观察切换之后的连接恢复时间。建议在演练脚本里加入对应用接口的持续探测记录 failover 完成到接口恢复正常中间隔了多久。如果这个时间超过你的 RTO就需要继续调连接池参数。第二件事监控项里必须有流复制延迟和 pgpool 的节点状态。不要只监控 PostgreSQL 本身。pgpool 节点状态和主从延迟是判断读写分离是否健康的重要指标。可以用 pgpool 自带的 watchdog 或外部监控系统把 show pool_nodes 的结果和 pg_stat_replication 里的 replay_lag 做成曲线低于阈值才告警避免灰度噪声。第三件事跟研发团队对齐写后读的约定。不要假设中间件能帮你解决所有读取一致性的问题。把“哪些查询允许走从库、哪些必须走主库”这个决策显式化为代码规范这样才能避免每个业务都踩一遍同样的坑。5. 最后说点实在的我在实际运维里最大的体会是pgpool-II 这类中间件配置项越是丰富越容易给人一种“我什么都能搞定”的错觉。但真正的生产环境从来没有银弹。连接池被占死本质是生命周期和验证机制没有设计好写后读取到旧数据本质是异步复制的延迟在中间件的默认规则里被放大了。这些问题最终都需要你根据业务的实际情况把参数和路由策略一点点校准。还有一个小技巧可以分享每次改动完 pgpool 的多节点配置不要直接 reload 了事最好在低峰期用 show pool_nodes 和 pgpool 日志双重确认新配置确实生效。我吃过一次亏改了 failover_command 脚本但忘了给脚本加执行权限结果真正切换时脚本静默失败排查了很久才发现。类似的权限、路径、脚本执行环境问题在数据库中间件这个领域尤其隐蔽多留个心眼能省下不少深夜时间。如果你也正在被 pgpool-II 的某些行为整得头疼希望这两则踩坑记录能给你提供一些排查方向。至少下次再碰到类似报错你知道要先去查哪一个参数也就知道该往哪个方向使劲了。