做 PostgreSQL 读写分离绕不开 pgpool-II。这个项目名经常被打成“pgpool-ll”两个小写 L其实人家是罗马数字 II我第一次看到这个写法的时候也被带偏过。简单说pgpool-II 就是放在应用和 PostgreSQL 集群之间的一层代理对应用隐藏背后到底有几台数据库对数据库统一承接连接再按规则把读流量分发到只读节点。如果你维护的系统还停留在“一台 PostgreSQL 打天下慢查询上来就报 DBA”的状态这篇东西值得往下看。我会从运维视角把读写分离的原理、pgpool-II 的配置、验证方法以及那些文档里不写的坑一次说清楚。1. 读写分离这件事pgpool-II 到底解决什么问题1.1 单库撑不住读压力时的常见选择业务一旦起来最典型的现象就是读压力先爆。列表页、报表、统计接口全部压在同一台主库上。连接数一多CPU 先上去接着是内存、锁竞争、WAL 写入排队最后连写事务都跟着慢。这时候第一反应是加机器但加机器不是简单把连接串改一下就行应用里一堆 SQL 都在往同一个地址打只靠数据库层很难把读流量拆出去。应用层自己搞读写分离是最常见的选择比如在数据访问层里做动态数据源切换SELECT走从库INSERT/UPDATE/DELETE走主库。这套逻辑本身不难难在维护成本。每加一个从库就要改一次配置每次做灰度还要担心数据源切错。而且大多数团队的数据访问层并没有那么强的路由意识一旦有人在事务里混着写和读或者一个接口内部先写后查数据源切到只读节点上就会闹出“刚插入查不到”的问题。所以需要一个更靠近数据库的方案把“哪台能写、哪台能读、哪台挂了”这件事集中管起来。pgpool-II 干的就是这个活。它站在应用和 PostgreSQL 集群中间应用只认一个虚拟地址和端口后端节点怎么变化、权重怎么配、故障怎么切换全部在代理层完成。业务代码不需要知道主库是谁、从库是几台。1.2 连接池、负载均衡、故障切换三件事一肩挑很多人把 pgpool-II 当成一个单纯的“读写分离网关”但只用它的分流能力有点浪费。它还有三个非常实际的功能在生产环境里往往是组合着用的。第一是连接池。PostgreSQL 每个连接都是独立进程连接一多内存和切换成本都很高。pgpool-II 在代理层维护连接池客户端连接可以远多于数据库侧的真实连接复用已有的后端连接。这个特性对短连接多的业务尤其友好能直接缓解数据库端的连接风暴。第二是读写分离和负载均衡。在流复制模式下写请求固定走主库读请求按照权重在多个节点之间分发。读请求可以只走从库也可以让主库承担一部分读流量完全由配置里的权重决定。第三是自动故障切换。主库宕机时pgpool-II 能识别出健康的备库把它提升为新的主库并且把失效节点从可用列表里摘除。外面应用只要配置好重试逻辑甚至感觉不到后端换了一台机器。单看每项功能都能找到替代方案比如用 PgBouncer 做连接池用 Haproxy 做负载均衡用应用层自己实现故障切换。但把这些揉在一起、并且能和 PostgreSQL 的复制体系深度整合的pgpool-II 确实是一套完整方案。团队不大的时候维护一个 pgpool-II 比维护“连接池 负载均衡 自研切换脚本”三套独立系统要省心得多。1.3 什么时候不适合用它写多读少的场景比如核心交易系统读写分离本来收益就小再加上一层代理反而增加延迟和分析复杂度。事务内部频繁“写后读”且强一致要求很高的业务也分不太动因为哪怕路由到从库从库的复制延迟也可能导致读取到旧数据。还有一个常见误区以为上了 pgpool-II 就等于数据库变高可用了。它只是帮你把切换动作做了备库数据是否完整、WAL 是否断档、切换后应用是否会自动重连这些仍然是运维的活。pgpool-II 是工具不是保险。2. 核心机制pgpool-II 是怎么把读写分开的2.1 后端节点与权重配置的玄机pgpool-II 对每个后端 PostgreSQL 节点都有一个编号从 0 开始。配置里通过backend_hostname0、backend_port0、backend_hostname1、backend_port1这种命名方式声明集群里有哪些数据库节点。每个节点还有一个backend_weight表示它在读负载均衡里占的比重。比如主库backend_weight0 1从库backend_weight1 2那么读请求大约按 1:2 的比例分发也就是三分之一左右的读流量会落在主库。想彻底把主库从读流量里摘出来可以把主库权重设成 0让读流量只走从库。但这里有个容易踩的坑如果把主库权重设成 0一旦所有从库都不可用读流量就没了去处。pgpool-II 不会“自动把读请求降级到主库”因为权重为 0 意味着主库不参与读分发。所以我会建议生产环境给主库保留一个较低的权重比如主库 1 备库 5既不影响写性能又能在从库全挂时兜底。当然如果你接受“从库挂掉时读服务直接不可用”这种强约束主库权重设 0 也没问题关键是想清楚降级策略。2.2 SQL 级别怎么判断该走主库还是从库pgpool-II 不是看到SELECT就无脑发到从库它会结合 SQL 类型、事务状态、函数属性综合判断。这是我见过最多人误解的地方。普通只读SELECT不在事务内或者事务内还没有执行过任何写入操作可以参与负载均衡。SELECT ... FOR UPDATE这种带行锁的查询必须走主库因为从库上锁没有意义锁最终还是要落在主库的数据版本上。SELECT nextval(...)这类序列操作也要走主库序列状态是在主库上推进的从库的序列值只是复制结果。事务内的复杂情况更值得注意。如果事务里已经执行了INSERT、UPDATE、DELETE之后的SELECT就只能在主库执行了否则很容易出现“刚插入的数据查不到”的一致性问题。这是 pgpool-II 的保守策略只要一个事务中出现了写操作这个事务后续所有语句都锁定在主库。如果你一个事务里写了几条然后立刻查几条那这段逻辑在 pgpool-II 里是分不出去的别指望这个事务的读能走从库。还有一类比较隐性的是 SQL 注释。pgpool-II 默认allow_sql_comments off意味着语句前面带上了注释它解析语句类型时可能认不出这是SELECT就会保守地送到主库。这个问题在遗留系统里非常常见ORM 框架生成的 SQL 前往往带着一段注释。解决办法是显式打开allow_sql_comments on或者去代码里去掉注释前者更省事。以及调用random()、timeofday()这类非不可变函数和序列相关操作的查询pgpool-II 也会倾向于送到主库因为这类函数结果无法保证在从库上一致。now()这类稳定函数一般可以走从库但如果你业务对绝对精确的时间敏感还是得自己注意。2.3 会话级负载均衡和语句级负载均衡很多人在 pgpool-II 上做了配置然后在一个 psql 窗口里反复执行SELECT发现所有查询都落在同一个节点上就以为配置没生效。其实默认情况下 pgpool-II 做的是“会话级负载均衡”客户端连接建立时pgpool-II 就为这个会话分配一个负载均衡节点这个会话后续的所有读请求基本都固定走这个节点而不是每来一条 SELECT 换一台机器。所以测试的时候要多开几个会话才能看到读流量散布到不同节点。从某个版本开始 pgpool-II 提供了statement_level_load_balance选项可以按语句粒度做均衡效果更像我们直觉里的“负载均衡器”。但打开它之前要确认你的业务 PCR 对事务语义没有强要求否则事务中的读和写被拆分到不同节点可能会读出旧数据。连接池逻辑也要理解到位。客户端连接进入 pgpool-II 后会被分配到某个子进程管理子进程内通过max_pool控制对每个后端节点最多缓存几个连接。connection_life_time控制单个后端连接最多存活多久超时后会被销毁重建避免数据库端连接长期处于空闲状态。client_idle_limit则是控制客户端连接空闲多久会被 pgpool-II 主动断开。这三个参数看着简单调不对就是各种奇怪的连接问题。3. 部署规划与配置实操3.1 一个最小可用环境的节点规划先说清楚下面这套方案适合“一主一从”的标准拓扑。主库和从库之间用 PostgreSQL 原生流复制同步应用连接 pgpool-IIpgpool-II 再分别连接两个数据库节点。我把这个环境简称为“模拟项目 X”。数据库主库地址写成10.0.0.11端口 5432。从库地址写成10.0.0.12端口 5432。pgpool-II 单独放一台机器假设地址是10.0.0.10对外监听 9999 端口。安装好 PostgreSQL 14 和对应的 pgpool-II 包之后第一步是在主库和从库上都创建一个专用于健康检查的账号。pgpool-II 需要定期探测后端节点是否活着所以这个账号必须有权限读取复制状态。我习惯在 PG10 及以上版本直接给pg_monitor角色CREATE ROLE pgpool_health LOGIN PASSWORD health_pass; GRANT pg_monitor TO pgpool_health;这只是健康检查账号。如果你后面要用到在线恢复功能还需要单独的recovery_user权限要求更高建议只在需要的时候再开。3.2 pgpool.conf 关键参数逐个说清楚pgpool-II 的主配置在/etc/pgpool2/pgpool.conf。底下这份是我在测试环境验证过的最小可用配置注释部分是我根据常见实践补充的说明。listen_addresses * port 9999 socket_dir /var/run/pgpool # 节点 0主库 backend_hostname0 10.0.0.11 backend_port0 5432 backend_weight0 1 backend_data_directory0 /var/lib/postgresql/14/main # 节点 1从库 backend_hostname1 10.0.0.12 backend_port1 5432 backend_weight1 2 backend_data_directory1 /var/lib/postgresql/14/main # 访问控制 enable_pool_hba on pool_hba_file_name /etc/pgpool2/pool_hba.conf authentication_timeout 5 # 流复制模式 读写分离 master_slave_mode on master_slave_sub_mode stream load_balance_mode on allow_sql_comments on # 连接池与连接生命周期 max_pool 4 connection_life_time 600 client_idle_limit 7200 child_life_time 300 # 健康检查 health_check_period 10 health_check_timeout 5 health_check_user pgpool_health health_check_password health_pass health_check_max_retries 3 # 故障切换脚本 failover_command /etc/pgpool2/failover.sh %d %h %D %H %Rbackend_data_directory这一项经常被人忽略。它不只是给 pgpool-II 看路径用的在做故障切换和节点恢复时脚本需要知道故障节点的数据目录位置。如果你不配在线恢复这一项可以暂时空着但建议还是填上省得以后要用的时候缺参数。master_slave_mode on表示 pgpool-II 运行在主备复制模式master_slave_sub_mode stream明确指定是流复制。写请求会自动路由到当前主库读请求按照权重负载均衡。load_balance_mode on是读写分离的总开关这两个开关一开pgpool-II 的核心功能才生效。allow_sql_comments on是我强烈建议打开的一项。前面提到过带注释的 SQL 如果不识别会被当成无法判断的语句送主库。生产环境里 ORM 产生的 SQL 五花八门开着这个选项能省很多排查时间。健康检查这一组参数决定了 pgpool-II 多久探一次后端。health_check_period太短会增加数据库压力太长会导致故障发现延迟。10 秒是我比较常用的值。health_check_password写在配置里是明文要注意这个文件的权限别让非 root 用户能读到。failover_command是故障切换的关键。后面的%d %h %D %H %R是传给脚本的位置参数分别表示故障节点编号、故障节点主机名、故障节点数据目录、新主库节点主机名、新主库数据目录。很多人在脚本里取错变量就是因为没搞清这几个占位符的顺序。我后面会专门讲这个坑。3.3 访问控制与密码文件配置pgpool-II 自己有一层访问控制叫pool_hba.conf作用和 PostgreSQL 的pg_hba.conf类似。应用连接 pgpool-II 时先过这层校验再决定能否继续。一个基础配置长这样host all all 0.0.0.0/0 md5这里用md5认证方式配合 pgpool-II 的密码文件使用。如果 PostgreSQL 后端用的还是scram-sha-256要注意确认你用的 pgpool-II 版本是否支持对应认证方式版本较老时两端密码校验会出问题。生成密码文件的命令是pg_md5。假设要给pgpool_health用户生成条目pg_md5 --md5auth -u pgpool_health -p执行后会提示输入密码并把它写入pool_passwd文件。这个文件路径一般在/etc/pgpool2/pool_passwd建议确保它是 600 权限。配置完成后先重启 pgpool-IIsystemctl restart pgpool2然后检查日志有没有报错再用 psql 连接测试psql -h 10.0.0.10 -p 9999 -U app_user -d app_db能连上说明 pgpool-II 这层已经通了。4. 读写分离生效验证与流量观察4.1 用 SHOW pool_nodes 确认节点状态pgpool-II 提供了一套以SHOW pool_开头的管理命令在连接的会话里直接执行就能看到集群状态。最常用的是SHOW pool_nodes;SHOW pool_nodes;输出里每个节点一行重点看status和role两列。status是up表示节点健康down表示已经被 pgpool-II 摘除。role区分primary和standby。如果主库显示成 standby或者出现多个 primary说明配置或者复制关系有问题要第一时间检查。输出里还能看到select_cnt这是 pgpool-II 统计的发送到该节点的 SELECT 次数。多执行几次读查询后再看这个数字能直观看到流量分配是否符合权重预期。4.2 用 PostgreSQL 内置函数验证查询落点更直接的验证方法是使用 PostgreSQL 的一个内置函数inet_server_addr()它会返回当前连接实际对应的数据库服务器地址。因为 pgpool-II 转发连接时应用感知不到后端地址但这个函数能暴露出真正的落点。SELECT inet_server_addr() AS server_ip, pg_backend_pid() AS backend_pid;多开几个 psql 会话执行这条查询如果看到有的返回10.0.0.11有的返回10.0.0.12说明读流量确实按权重分散到不同节点了。这里要特别注意“会话级负载均衡”的影响。同一个会话里反复执行这条 SQL结果大概率一直不变因为读节点在会话建立时就已经定好了。看到这个结果不要慌不是配置失效换个新会话再看。如果你用的是支持statement_level_load_balance的新版本并打开了它会话内的落点才会动态变化。4.3 通过后端日志观察真实路由在 PostgreSQL 后端节点上临时打开语句日志也是一种很直观的验证方式。在主库和从库的postgresql.conf里都设置log_statement all log_min_duration_statement 0然后从应用侧跑几个典型查询再分别去看两台数据库的日志。如果一条SELECT出现在主库日志而不在从库日志里说明这条查询被路由到了主库。反过来也一样。这个方法的优势是能区分复杂的 SQL 类型。比如验证SELECT nextval(...)是否永远只在主库日志里出现验证事务中写后读是否被锁定在主库验证带注释的 SQL 会不会因为allow_sql_comments配置而误走了主库。日志验证完记得关掉log_statement否则生产环境日志会增长得非常快。4.4 压力视角下能看到什么变化在一套读多写少的模拟业务上做简单压测能明显看到主库的 CPU 使用率曲线开始下降慢查询数量减少。但靠压测时也要注意如果你压测的全部是“事务内先写后读”的混合事务那读写分离几乎帮不上忙压测结果会非常难看。这不一定代表 pgpool-II 配置有问题而是负载模型本身不适合读写分离。真正合理的压测应该按照业务实际比例混合只读查询和写查询。比如 80% 是只读列表查询20% 是写操作这种模型下 pgpool-II 的价值才能体现出来。我习惯用一个小技巧准备两个测试账号一个账号只做只读操作验证流量是否稳定分散一个账号模拟真实业务验证写后读的一致性是否正常。5. 常见问题与排查技巧实录5.1 查询没有走从库的三分钟定位法遇到“为什么我的查询全落在主库”这个问题先别怀疑配置按下面顺序排查。第一步执行SHOW pool_nodes;确认节点状态是不是up、role是不是正常。如果从库显示down先看健康检查配置和网络。第二步确认 SQL 类型。带FOR UPDATE、nextval、random()这类语句走主库是预期行为不是故障。第三步检查事务上下文。如果客户端用的是长连接并且这个事务里已经执行过写操作那后面的 SELECT 全部留主库。这是业务特性和 pgpool-II 的保守策略不是 bug。第四步确认allow_sql_comments配置。这个看起来不起眼的选项坑过我好几次。ORM 生成的 SQL 前带着一段注释pgpool-II 默认解析不出 SELECT 类型直接丢到主库。开了allow_sql_comments on之后流量可能立刻就均衡了。第五步多开几个会话测试。一个会话里反复 SELECT 看不到交替落点是因为会话级负载均衡不是配置失效。5.2 连接池满了或者连接越来越慢怎么办应用侧偶尔会报ERROR: pooler cannot accept a new connection这个错误看着吓人其实多半是连接池参数没调好。老版本里num_init_children控制初始子进程数量max_pool控制每个子进程对每个后端节点最多缓存多少连接。两者配合不好客户端连接再多也挤不进去。我会先看 pgpool-II 日志里的连接拒绝记录的时间点再对比同时段的数据库连接数。如果数据库侧连接数没有异常但 pgpool 仍拒绝新连接常见原因有两个一是max_pool太小导致缓存条目被占满二是child_life_time太短子进程频繁重启连接反复建立造成一种“连接池很忙但实际没什么流量”的假象。connection_life_time我已经习惯设为 600 秒左右让连接生命周期可控而不是无限保留。还有一个环境层面的坑系统对用户进程数做了限制。pgpool-II 的每个子进程对一个后端节点可以建立多条连接系统参数nproc如果设得太低连接数一多就直接失败。这个不属于 pgpool-II 配置范畴但排查时经常被忽略。5.3 主库宕机后的 failover 踩坑记录先说一个最简单的底线要求failover_command指向的脚本一定要有可执行权限脚本内容即使只写一句日志也要保证能跑起来。很多故障切换演练失败就是因为脚本没有chmod x或者脚本里的命令路径写错。生产环境的 failover 脚本不能只写日志它要负责把候选从库提升为新主库。我以一个简化脚本为例说明结构#!/bin/bash FAILED_NODE_ID$1 FAILED_HOST$2 FAILED_PGDATA$3 NEW_MASTER_HOST$4 NEW_MASTER_PGDATA$5 LOGFILE/var/log/pgpool/failover.log echo $(date %Y-%m-%d %H:%M:%S) failover: node $FAILED_NODE_ID($FAILED_HOST) failed, new master $NEW_MASTER_HOST $LOGFILE # 在这里执行对候选主库的提升操作例如 # ssh postgres$NEW_MASTER_HOST /usr/lib/postgresql/14/bin/pg_ctl promote -D $NEW_MASTER_PGDATA占位参数%D和%R分别表示故障节点和新主库的数据目录脚本里如果取错会导致后续的提升命令定位错误。还有一点脚本里的主机名要用引号包起来否则万一主机名解析异常或包含空格整个命令会被拆得四分五裂。我在实际维护了一套订单系统时第一次做故障切换演练就翻过车主库宕机后pgpool-II 确实执行了 failover 脚本但由于脚本里没做提升动作备库一直停留在 standby 状态应用端的写请求全部报错。所以演练时不仅看 pgpool-II 日志还要确认数据库侧角色真的从 standby 变成了 primary。另一个容易被忽略的点是复制槽。主库挂掉后旧主库的复制槽如果没有清理在新主库上会残留 WAL 信息后续重新做流复制时可能一直卡住。这个要在切换后的恢复流程里专门处理。5.4 延迟备库读旧数据的取舍流复制天然有延迟尽管通常只有几十毫秒但在高负载下可能被拉大。pgpool-II 提供了延迟阈值参数当某个从库的复制延迟超过阈值时它会暂时降低该节点的读权重极端情况下直接不把读请求发给这个节点避免应用读到明显过期的数据。这个机制对报表类业务很关键。报表查询数据量大、执行时间长如果恰好被路由到延迟很大的从库导出的数据可能少了好几分钟。我一般把延迟阈值配在能接受的业务范围内比如几百毫秒超过就摘除该节点等延迟追上再自动恢复。但要注意主从延迟本身不是 pgpool-II 能解决的问题。如果业务要求“写入后立即读到最新值”读写分离就不适合你。这种情况要么使用强制走主库的能力要么接受延迟没有第三条路。5.5 常见问题速查表现象可能原因快速处理办法所有读流量都落在主库allow_sql_comments off、事务内已写、会话级负载均衡打开注释选项拆短事务多开会话测试从库状态一直是 down健康检查用户权限不够、网络不通、密码错误检查health_check_user、pg_hba 配置和数据库角色权限应用连接被拒绝连接池参数过小、pool_passwd缺失、系统进程数限制调大num_init_children/max_pool重新生成密码文件failover 后写请求报错failover 脚本没有执行提升操作在脚本中对候选从库执行pg_ctl promote查询落点不稳定statement_level_load_balance开启语句级均衡生效确认业务是否能接受毫秒级延迟差异带注释 SQL 全走后端主库allow_sql_comments off配置里改为on并重启 pgpool-II6. 一些个人经验与补充建议先说连接池的调参思路。别一上来就把max_pool和num_init_children调到最大pgpool-II 的连接池是“复用”而不是“预占”。数据库侧连接数会被限制在一个相对较小的规模这才是连接池的健康状态。如果发现数据库端连接暴涨先查是不是num_init_children开得太多而不是盲目增加连接池容量。再说版本问题。网上能找到的 pgpool-II 教程很多是 2.x、3.x 时代写的配置项和 4.x 差别很大照抄容易翻车。我建议遇到配置问题时优先看随包安装的官方示例文件比如/etc/pgpool2/pgpool.conf.sample-stream版本匹配才是硬道理。然后说一个小技巧把 pgpool-II 的日志单独放到一个目录并在日志里带时间戳和节点编号。故障切换时最怕的就是“不知道哪台节点先失联”日志分得清排查效率会高很多。最后想讲一个我印象很深的经验。之前给某个跨平台系统做高可用改造pgpool-II 配置都调好了自测读写分离也正常但上线前我多做了几步演练把主库直接kill掉模拟宕机。第一轮 failover 就出了幺蛾子备库没有被提升应用虽然能连上 pgpool-II但所有写操作都返回错误。排查后发现是 failover 脚本里少了提升动作而脚本本身没有报错因为日志文件写在了错误的环境变量路径下。类似这种问题平时不演练根本发现不了。所以如果你正准备在生产环境上 pgpool-II我唯一的建议是先在测试环境把主库宕机演练三遍。第一遍看 pgpool-II 能不能发现问题第二遍确认 failover 脚本真的把备库提升了第三遍验证应用自动重连后读写恢复正常。这个坑我踩过希望你跳过。