从事PostgreSQL运维这些年我遇到过不少次类似的场景业务方跑过来说“数据库撑不住了”登录上去一看连接数打满、主库CPU飙红、从库闲着没事干。加机器容易但怎么让这些机器真正分担压力才是关键。pgpool-II就是解决这个问题的老牌中间件它能在PostgreSQL前面做连接池、读写分离负载均衡还能配合watchdog实现高可用切换。这篇文章把我最近一次生产环境部署pgpool-II的完整过程整理出来包括架构设计、安装步骤、配置解读和踩坑记录目标是给正在做PostgreSQL高可用或读写分离的同学一份可以直接参考的实操样本。1. 架构选型pgpool-II到底解决了什么问题先说清楚pgpool-II的核心价值不然很多人装完发现用不上那不是软件的问题是场景没对口。PostgreSQL本身是进程模型每来一个客户端连接就要fork一个后端进程连接数一多内存和CPU开销直线上升。我在实际项目里见过最夸张的情况应用层的连接池配置失误一次性打过来两千多个空闲连接直接把数据库服务器的内存吃了一半。而pgpool-II可以在前端做连接复用几百个客户端连接复用几十个真实数据库连接这就能把数据库的负担降下来。另外一类常见需求是读写分离。OLTP系统的特点是读多写少PostgreSQL的流复制可以搭一主多从但应用层得自己区分读写语句、自己维护连接串很麻烦。pgpool-II的负载均衡模块会自动把SELECT语句分发到从库把INSERT/UPDATE/DELETE固定发到主库应用侧完全无感。还有高可用。主库挂了之后pgpool-II可以执行failover脚本触发从库提升并且通过VIP把流量切过去。虽然Stolon、Patroni也能做这件事但pgpool-II胜在轻量、部署简单尤其适合不想引入etcd/zk这类外部依赖的团队。有同学会问HAProxy也能做负载均衡为什么不用它核心区别在于HAProxy工作在四层不懂PostgreSQL协议它只能按轮询或连接数分发做不到按语句类型路由更做不了只把写操作发到主库这种逻辑。pgpool-II是工作在七层的数据库中间件能解析语句类型这是它不可替代的地方。当然它也有不适合的场景大批量COPY导入、长时间运行的报表查询、需要跨节点分布式事务的场景都不建议往pgpool-II后面塞。它的定位很清晰——OLTP入口的统一收口。2. 部署前的地基环境规划与PostgreSQL流复制搭建pgpool-II只是中间件它本身不存储数据所有数据都在后端的PostgreSQL里。所以动手安装pgpool-II之前必须先把PostgreSQL集群搭好节点状态健康这是整个方案能不能成功的地基。2.1 我这次用到的环境规划本次部署采用一主两备的PostgreSQL 13集群外加两台pgpool-II节点组成watchdog高可用组合对外提供一个VIP作为应用统一入口。主机名IP角色配置说明pgsql-node1192.168.10.11PostgreSQL主库8C16G数据盘200Gpgsql-node2192.168.10.12PostgreSQL备库18C16G数据盘200Gpgsql-node3192.168.10.13PostgreSQL备库28C16G数据盘200Gpgpool-node1192.168.10.21pgpool主 watchdog4C8Gpgpool-node2192.168.10.22pgpool备 watchdog4C8Gpgpool-vip192.168.10.20虚拟IP应用连接统一走这里三台数据库节点全部安装CentOS 8、PostgreSQL 13通过流复制保持数据同步。pgpool节点和数据库节点分开部署这样方便单独维护中间件层而且pgpool本身不存储数据换一台机器重装配置也很容易。2.2 PostgreSQL流复制配置要点数据库节点上的基础配置就不再赘述了重点说几个关键参数。主库的postgresql.conf里必须开启wal_level replica max_wal_senders 10 wal_keep_size 1024 hot_standby onmax_wal_senders要大于备库数量一个备库至少占一个WAL发送进程留出余量给将来的扩容和pg_basebackup操作。wal_keep_size是让主库至少保留1GB的WAL日志防止备库短暂断连后因WAL被回收而需要重新全量同步。复制用户单独创建不要用超级用户CREATE ROLE rep LOGIN REPLICATION ENCRYPTED PASSWORD Rep_2024!;pg_hba.conf里放行复制连接和pgpool的健康检查连接。我习惯把pgpool节点IP单独列一行不给它开超级权限只是普通用户用于健康检查host all all 192.168.10.21/32 md5 host all all 192.168.10.22/32 md5 host replication rep 192.168.10.0/24 md5完事后在备库上执行pg_basebackup拉取基础备份再配置standby.signal文件即可。2.3 健康检查用户与连接池用户pgpool-II要定期检查后端节点的健康状况还需要作为代理去连接PostgreSQL所以两个用户是必须的-- 健康检查用户普通登录即可 CREATE USER pgpool_check WITH PASSWORD Check_2024!; -- 应用连接用户这张表后续要交给pgpool代理的连接 CREATE USER app_user WITH PASSWORD App_2024!;很多教程只创建一个用户pgpool用同一个账号既做健康检查又做连接池代理这在某些配置组合下会出现权限不够或认证错乱的问题。我的建议是拆开各管各的排查问题也更清晰。3. 安装方式怎么选RPM仓库还是源码编译pgpool-II的安装并不复杂但安装方式直接影响后续维护成本和补丁升级路径值得单独说一说。官方提供了三种方式RPM包、源码编译、Docker镜像。生产环境我强烈建议用RPM包理由后面细说。3.1 官方RPM仓库安装推荐pgpool-II的下载页提供了针对RHEL/CentOS/Fedora的官方yum仓库配置方式很简单# 以RHEL/CentOS 8为例 dnf install -y https://www.pgpool.net/yum/rpms/4.5/redhat/rhel-8-x86_64/pgpool-II-release-4.5-2.noarch.rpm # 安装pgpool-II dnf install -y pgpool-II安装完成后主配置文件在/etc/pgpool-II/pgpool.conf认证配置文件是/etc/pgpool-II/pool_hba.conf和/etc/pgpool-II/pcp.conf日志默认走syslog。RPM包自带systemd unit文件可以直接用systemctl start pgpool管理非常省心。用RPM还有一个隐藏优势它会自动安装与当前系统匹配的libpq依赖避免源码编译时因为libpq版本不匹配而出现莫名其妙的认证或协议错误。我见过不少源码编译出问题的案例最后都是依赖库版本导致的。3.2 源码编译安装如果你用的操作系统不在官方支持的列表里或者需要打自己的补丁就必须源码编译。编译本身不难依赖装齐就行# 安装编译依赖 yum install -y gcc make postgresql13-devel libpq-devel openssl-devel pam-devel # 下载源码 wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.5.3.tar.gz tar zxvf pgpool-II-4.5.3.tar.gz cd pgpool-II-4.5.3 # configure时指定PostgreSQL安装路径 ./configure --prefix/usr/local/pgpool --with-pgsql/usr/pgsql-13 make make install--with-pgsql必须指向PostgreSQL的实际安装目录因为pgpool-II编译过程中需要引用PostgreSQL的头文件和库文件。如果这个路径不对后面的pg_md5、pcp_*这些工具都能编出来但运行时连接后端会报版本或协议不匹配的错误。编译完成后记得配置环境变量echo export PATH/usr/local/pgpool/bin:$PATH /etc/profile.d/pgpool.sh echo /usr/local/pgpool/lib /etc/ld.so.conf.d/pgpool.conf ldconfig3.3 版本匹配pgpool-II 4.5支持PostgreSQL 11到16安装前一定确认版本兼容。官方文档有一张矩阵表我实际踩过教训把pgpool-II 4.0搭在PostgreSQL 14上结果对端要求scram-sha-256认证而4.0对scram的支持不完善折腾了很久。现在生产环境我都保持pgpool-II小版本在4.4以上PostgreSQL用13或14这个组合非常成熟。4. pgpool.conf核心参数逐段解读安装完成后真正的重头戏是配置。pgpool.conf有上百个参数但真正决定行为的就是几组。我把我生产环境用的配置拆开来讲每一段都说清楚为什么这么配。4.1 连接与监听配置listen_addresses * port 9999 socket_dir /var/run/pgpool pcp_listen_addresses * pcp_port 9898pgpool默认监听9999端口这是客户端连数据库的入口pcp端口9898是管理端口pcp_node_info、pcp_attach_node这些管理命令走这里。两个端口是独立的pcp端口千万不能暴露到公网否则任何人都能调管理命令。我见过有公司把pcp端口映射到公网等于把数据库管理权限送人了非常危险。4.2 后端节点定义backend_hostname0 192.168.10.11 backend_port0 5432 backend_weight0 1 backend_data_directory0 /var/lib/pgsql/13/data backend_flag0 ALLOW_TO_FAILOVER backend_hostname1 192.168.10.12 backend_port1 5432 backend_weight1 2 backend_data_directory1 /var/lib/pgsql/13/data backend_flag1 ALLOW_TO_FAILOVER backend_hostname2 192.168.10.13 backend_port2 5432 backend_weight2 2 backend_data_directory2 /var/lib/pgsql/13/data backend_flag2 ALLOW_TO_FAILOVERbackend_weight是负载均衡权重我用1:2:2意味着一主二备中的两个备库承担更多读流量。如果你的备库硬件比主库好权重可以调得更高pgpool会按权重比例轮询分发SELECT。需要特别注意的是backend_data_directory这个路径必须和实际数据目录完全一致pgpool通过它来判断节点角色变化。很多问题都出在这里后面踩坑部分会细说。4.3 流复制延迟检测sr_check_period 10 sr_check_user pgpool_check sr_check_password Check_2024! delay_threshold 10000delay_threshold是以字节为单位的WAL延迟阈值备库落后超过10MB就会被从负载均衡列表里摘掉直到追上才重新加入。这个机制非常重要备库延迟太大时读到的数据是旧的如果业务对一致性敏感宁可让它不承担流量也不能误导应用。健康检查用户刚才我们创建的那个pgpool_check就在这里用上。4.4 连接池参数connection_cache on max_pool 4max_pool表示每个后端节点上每个数据库最多保持多少个连接。这里有个数学关系pgpool最多会为每个节点建立max_pool * 数据库数量个连接。比如4个连接池大小、3个数据库那么单个后端节点最多被pgpool建立12个连接。所以PostgreSQL的max_connections配置不能太小我一般留出30%的余量给真实连接之外的元数据操作。连接池最难调的地方是child_life_time和connection_life_time这俩控制连接和进程的存活时间。千万不要为了省资源把它设得过短否则连接频繁重建性能反而更差。我习惯设connection_life_time 3005分钟存活既保证连接新鲜又不至于频繁重建。4.5 健康检查参数health_check_period 10 health_check_timeout 5 health_check_max_retries 2 health_check_user pgpool_check health_check_password Check_2024!健康检查是pgpool判断后端节点存活的机制每10秒探测一次5秒超时连续2次失败才认为节点挂了。health_check_max_retries这个参数容易被忽略设大了会导致故障切换非常迟钝。我曾经见过配置了10次重试的线上故障主库已经宕机5分钟了pgpool还在重试应用早就超时报错了。故障切换宁可误判也要快生产环境2-3次重试足够了。4.6 故障切换failover_command /etc/pgpool-II/failover.sh %d %h %p %D %m %M %H %P %r %R failover_on_backend_error onfailover_command指定故障切换时执行的脚本脚本接收pgpool传入的节点信息参数。实际生产环境中failover脚本的核心逻辑是#!/bin/bash # failover.sh 简化示例 failed_node_id$1 old_primary_ip$3 new_primary_ip$6 # 如果挂掉的是主库选一个数据最新的备库提升为主库 if [ $old_primary_ip 192.168.10.11 ]; then ssh postgres192.168.10.12 /usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data # 将VIP漂移到新的主库节点 ssh root192.168.10.12 ip addr add 192.168.10.20/24 dev eth0 ssh root192.168.10.11 ip addr del 192.168.10.20/24 dev eth0 2/dev/null fi我用的脚本比这个复杂得多包括对多个备库的延迟判断、VIP漂移、告警通知。但核心逻辑就是主库挂了先挑一个备库提升再把流量入口切过去。脚本写好之后一定要用pcp_detach_node手动模拟节点故障测试脚本是否正常执行。没有测试过的failover脚本等于没有。4.7 watchdog高可用pgpool本身是无状态的但它要对外提供一个稳定入口单台pgpool就成了单点。watchdog的作用就是让两台pgpool互相监控一台挂了另一台接管VIP。use_watchdog on wd_hostname 192.168.10.21 wd_port 9000 wd_lifecheck_method heartbeat wd_heartbeat_port 9694 # 定义另一台pgpool other_pgpool_hostname0 192.168.10.22 other_pgpool_port0 9999 other_wd_port0 9000 # VIP设置 delegate_IP 192.168.10.20 if_cmd_path /sbin if_up_cmd ip addr add $_IP_$/24 dev eth0 label eth0:0 if_down_cmd ip addr del $_IP_$/24 dev eth0watchdog有两种心跳检测方式原生方式通过TCP连接互检和heartbeat方式通过专用端口发UDP报文。我选heartbeat方式因为它不依赖PostgreSQL后端的状态更纯粹。VIP漂移由watchdog控制主pgpool挂了备pgpool自动接管VIP应用连接不受影响。还有一点必须注意两台pgpool的配置文件和pgpool_status文件要保持一致否则切换过去后行为可能和原来不同。我每次修改完配置都会用ansible同步到两台机器。5. 鉴权配置pool_hba.conf和pcp.conf5.1 pool_hba.conf的认证机制pgpool-II在客户端连接时会先用自己的pool_hba.conf做一层鉴权通过后再用对应的账号连接后端PostgreSQL。它的语法和PostgreSQL的pg_hba.conf一致。host all all 192.168.10.0/24 scram-sha-256 local all all trustPostgreSQL 14之后默认使用scram-sha-256认证pgpool-II里也要对应配置。如果后端PostgreSQL的pg_hba.conf是md5认证pool_hba.conf也配md5两边必须一致。这个一致性是很多认证失败的老坑后面集中说。5.2 生成pool_passwdpgpool-II要代理连接后端数据库就必须知道每个用户的密码。它会把用户密码加密存在pool_passwd文件里用pg_md5命令生成# 进入pgpool配置目录 cd /etc/pgpool-II # 如果是scram认证 pg_md5 --prompt --usernameapp_user # 输入密码后会生成对应记录注意pg_md5有两种模式不带--prompt时生成md5散列带--prompt并配合--username会把记录写进pool_passwd文件。我刚才说了PostgreSQL 14之后默认scram算法pgpool-II 4.4以上版本已经完整支持scram所以优先用scram。5.3 pcp.conf管理认证pcp命令pcp_node_info、pcp_detach_node等需要一个独立的管理员认证文件pcp.conf格式是用户名:密码散列# 生成管理员密码 pg_md5 admin_pgpool_123 # 把输出追加到pcp.conf echo admin:$(pg_md5 admin_pgpool_123) /etc/pgpool-II/pcp.confpcp.conf的权限一定要设为600因为它相当于数据库的管理后门泄露了就等于有人可以直接detach节点、调整配置。6. 启动验证从show pool_nodes到故障切换演练配置全部完成后启动pgpool然后按下面的顺序做验证。我每次部署都会走完完整链路才交付。6.1 启动与节点状态检查systemctl start pgpool systemctl status pgpool # 查看节点状态 psql -h 192.168.10.21 -p 9999 -U app_user -d postgres -c show pool_nodesshow pool_nodes输出内容包含节点ID、主机名、端口、状态、角色和负载均衡权重。正常状态下一个节点是primary两个节点是standby所有节点的status都是up。注意pgpool刚启动时如果节点状态显示unknown或down先不要急着确认等一个健康检查周期通常10秒后再看。6.2 验证读写分离是否生效读写分离的验证方法很简单在主库建一张测试表插入数据然后用不同方式查询。-- 通过pgpool执行写操作 psql -h 192.168.10.21 -p 9999 -U app_user -d testdb -c INSERT INTO t1 VALUES (1, write); -- 通过pgpool执行读操作 psql -h 192.168.10.21 -p 9999 -U app_user -d testdb -c SELECT * FROM t1;关键是怎么确认SELECT真的发到了备库。我常用的办法是在备库上开启log_min_duration_statement临时参数把超过0ms的查询都记下来然后去备库日志里看有没有那条SELECT。如果能看到说明负载均衡生效了。另一招是用show pool_processes看pgpool为每个后端节点建立的连接数。6.3 故障转移演练故障演练是部署pgpool-II之后必须做的事。不要觉得配置完了就万事大吉没有演练过的故障切换方案等于纸上谈兵。# 模拟主库宕机 systemctl stop postgresql-13 # 等一个健康检查周期然后检查节点状态 psql -h 192.168.10.21 -p 9999 -U app_user -d postgres -c show pool_nodes正常结果应该是原来的备库被提升为primary且VIP已经漂移到新的主库。然后验证业务读写是否正常再验证故障节点恢复后能否重新挂回集群# 重新启动故障节点 systemctl start postgresql-13 # 等待它重新追平主库然后挂回到pgpool pcp_attach_node -h 192.168.10.21 -U admin -p 9898 -w -n 0pcp_attach_node的-n参数是指定节点ID0对应backend_hostname0。attach之前要确保该节点已经追平主库的数据否则挂回去后查不到最新数据会引发数据一致性问题。7. 我踩过的那些坑7.1 pgpool_status文件引发的血案pgpool在正常关闭时会记录各节点的状态到pgpool_status文件启动时读取它恢复节点状态。听起来很智能但实际是个坑如果后端发生了主从切换而pgpool没来得及正常更新这个文件比如被kill -9重启后会发现pgpool按旧状态判断——你以为主库是192.168.10.11实际主库已经变成192.168.10.12了pgpool还在往旧节点发写请求直接报错。解决办法要么在故障切换脚本里把pgpool_status文件删除让它启动时完全按健康检查重新探测要么每次主从切换后手动检查并修正文件内容。我现在的做法是failover脚本里加一行rm -f /var/run/pgpool/pgpool_status7.2 认证算法不一致有一次部署到PostgreSQL 14后端用scram-sha-256认证但我当时用的pgpool-II 4.0默认生成md5散列写入pool_passwd结果应用连上来一直报password authentication failed。排查了很久才发现是认证算法不匹配。pgpool-II从4.1版本开始才完整支持scram4.0版本及以前建议统一用md5认证。解决思路并不复杂要么把PostgreSQL的pg_hba.conf改成md5要么升级pgpool并重新用scram方式生成pool_passwd。关键是别让两边算法各说各话。7.3 backend_data_directory路径错误这个坑非常隐蔽。pgpool通过backend_data_directory参数判断节点是主库还是备库。如果路径填错pgpool会认为该节点数据目录不存在不断尝试重新探测日志刷屏不说节点状态时up时down极不稳定。注意这里的路径是数据库节点的本地路径不是pgpool机器的路径。两台机器的数据目录可能同名但不同位置一定要到数据库节点上执行SHOW data_directory;确认后再填。7.4 health_check_timeout设置过短导致的误切换网络抖动情况下健康检查超时可能误判节点宕机进而触发自动故障转移。我不止一次见到过一次几十毫秒的网络抖动被health_check_timeout1秒的配置判定为后端故障自动切换了主库引发全链路影响。生产环境的健康检查参数我建议health_check_timeout 5health_check_max_retries 3。既要保证故障发现的及时性也要容忍正常的网络波动。数据库节点和pgpool节点在同一内网且网络稳定时可以适当收紧跨机房部署时一定要放宽。7.5 日志刷屏与syslog配置pgpool默认把日志写到syslog如果不在配置里关掉debug级别健康检查的每次成功/失败都会刷日志一天下来几十GB日志文件。我的做法是明确指定日志输出log_destination stderr logging_collector on log_directory /var/log/pgpool log_filename pgpool-%Y-%m-%d.log log_min_messages warning这样日志干净可查出问题时能快速定位。官方默认的syslog配置在排查问题时反而不好抓取。7.6 watchdog心跳冲突两台pgpool节点的watchdog都配了9000端口和9694端口如果防火墙没有放行这两个端口的心跳包watchdog会认为对方挂了两台机器同时抢占VIP造成IP冲突。这个问题排查起来很痛苦因为从表面看节点都是正常的就是VIP时有时无。部署完watchdog后第一件事就是检查# 在两个pgpool节点上分别执行 watch ss -ulnp | grep 9694确认心跳包是通的再往下做VIP切换测试。另外两台watchdog节点的时间必须同步否则仲裁判断会出错建议统一配置NTP。8. 运维层面的最后建议部署完成以后不要把pgpool-II当成一个装完就不管的东西。它接管了数据库流量入口本身就是核心链路的一部分必须纳入你的监控体系。我会额外监控四项指标pgpool进程本身是否存活、VIP是否漂移到位、每个后端节点的状态up/down、以及pool_nodes的主从角色是否符合预期。一旦发现角色的预期不符马上人工介入不要等告警自己恢复。另外一个小建议是配置文件的版本管理。pgpool.conf、pool_hba.conf、pcp.conf全部纳入git管理每次变更都走评审和发布流程。我遇到过不止一次事故有人手改配置把load_balance_mode关了、把某个节点权重改错导致大量查询打到主库。配置变更必须留痕这也是运维成熟度的体现。补充一个扩展方向如果你后续要平滑扩缩容数据库节点pgpool-II的online recovery功能支持在不停服的情况下往集群里加新的备库节点。配合failover脚本使用整个数据库层的运维可以做到相当高的自动化程度。这次先不展开等有同学真正用到了再单独聊。