简介一套围绕 PostgreSQL 主从流复制与 pgpool-II 高可用架构的实践方案文档面向 DBA、运维工程师和数据库架构师解决生产环境主备同步、故障切换、读写分离及负载均衡等核心问题。文档基于 WAL 日志机制对比同步复制与异步复制的优劣说明 walwriter、walsender、walreceiver 等进程如何协同完成日志传输和备库应用同时结合 pgpool-II 的连接池、自动故障转移、负载均衡与读写分离功能给出从客户环境准备、主库参数配置、备库初始化到 pgpool 配置与启动测试的完整部署路径并整理了防火墙、SELinux、时钟同步、系统内核参数等资源调整细节。压缩包内共 1 个 docx 文件约 773KB目录层级清晰包含方案综述、实践环境、配置步骤与总结可直接作为 Postgres 高可用项目落地的参考手册。已有 469 人学习下载适合希望快速搭建 PostgreSQL 主备高可用环境、需要完整实践步骤和常见注意事项的中高级数据库运维人员。1. 没有高可用方案主库宕机就是事故这套流复制与pgpool组合解决什么做数据库交付的人都知道单机PostgreSQL跑测试没问题上了生产后一旦主库宕机业务就停摆恢复要靠人肉介入运气好几分钟运气差就是小时级事故。这不是技术能力问题而是方案设计问题没有主从复制就没有数据冗余没有中间件就没有自动切换和读流量分摊。最常见的补救做法是搭一个主库一个备库靠流复制把WAL日志从主库实时推送到备库再在两层之上加pgpool-II来做连接池、读写分离和高可用切换。这套组合的价值很直接平时备库能分担SELECT查询压力主库挂掉时pgpool把写请求切到备库业务不感知数据不丢或丢极少取决于同步/异步配置。这份方案适合两类人一类是在RHEL或CentOS上做过PostgreSQL单机部署、想升级到主从高可用架构的DBA和运维工程师另一类是正在做数据库高可用课设或毕设的学生需要一份从环境准备到failover测试的完整可交付流程。方案覆盖三个核心模块PostgreSQL流复制、pgpool-II集群配置、pg_rman离线备份三者合起来才是完整的高可用闭环——在线同步保证不中断离线备份保证误操作可回滚。2. 总体架构设计双节点拓扑与流复制、pgpool、pg_rman的职责划分2.1 双节点高可用拓扑与组件关系整个方案采用双节点架构一个master节点、一个standby节点。每个节点上都安装PostgreSQL、pgpool-II和pg_rman三个组件各自承担不同角色。PostgreSQL负责数据存储和WAL日志的生成与传输pgpool-II作为数据库前面的中间层承接客户端连接并分发请求pg_rman在后台按策略做全量备份和归档备份。节点1master上PostgreSQL主实例正常对外提供读写服务walsender进程持续把WAL日志通过TCP流发送给节点2节点2standby的walreceiver进程接收WAL日志并写入本地startup进程不断应用这些日志把备库数据推进到与主库一致的状态。与此同时每个节点各自运行一个pgpool实例两个pgpool通过watchdog机制互相监控拥有一个虚拟IPVIP。客户端连接的不是具体某个节点的真实IP而是这个VIP由pgpool决定把请求转发到哪个后端PostgreSQL实例上。这种架构下组件职责边界很清楚。PostgreSQL解决数据冗余问题pgpool解决连接管理和故障转移问题pg_rman解决数据可恢复性问题。三者缺一不可只有流复制主库宕机后需要手动执行promote命令提升备库业务中断时间不可控只有pgpool没有流复制没有数据副本可以切换高可用无从谈起只有在线复制没有离线备份一旦出现误操作或者数据被恶意删除错误会被流复制同步到备库两边数据一起坏没有后悔药可吃。2.2 同步与异步复制模式的选择逻辑流复制有同步和异步两种模式这是方案设计里首先要做的选择题。异步模式下主库事务提交后就算完成WAL日志异步发送给备库主备之间存在一个极小的延迟窗口通常不到一秒。如果主库在这个窗口内崩溃最后一小段事务可能丢失。同步模式需要在提交事务时等备库确认收到WAL日志后才算提交完成数据一致性强但事务响应时间会增加因为每次提交都引入了一次网络往返。实际部署中怎么选文档里给出了明确倾向默认用异步认为同步复制会增加事务响应时间。不过更负责任的配置方式是给出选择机制而不是一刀切。通常的做法是核心业务表或者关键交易场景要求不能丢数据时把synchronous_commit设为on并配置synchronous_standby_names指定备库名称让主库必须等到备库刷盘后才确认提交对性能敏感的场景则保持异步。这个调节可以在会话级别做不需要改全局配置单个事务前执行SET synchronous_commit on这个事务就走同步确认其他事务继续走异步兼顾性能和一致性。2.3 pgpool-II的四个核心功能在方案中的具体体现pgpool-II提供连接池、复制、负载均衡和连接限制四个能力但在这套双节点流复制方案里复制功能并不用pgpool来做而是由PostgreSQL流复制完成。这里要区分概念pgpool的复制模式是它自己把写入同步到多台PostgreSQL而流复制模式下pgpool只是识别主备关系把写请求路由到主库把读请求分发到备库。连接池能力体现在pgpool与PostgreSQL之间维持一组长连接新的客户端连接进来时复用已有连接避免频繁创建和销毁数据库连接带来的开销。负载均衡和读写分离是这套方案日常收益最大的功能SELECT查询会按权重分发到主库和备库INSERT/UPDATE/DELETE强制走主库。在典型报表场景或者读多写少的业务中备库能分担一半左右的读流量主库压力显著下降。连接数限制方面pgpool把超过上限的连接放进等待队列而不是直接拒绝在线业务突刺时不会因为瞬间连接数暴涨导致PostgreSQL拒绝服务。2.4 备份策略在线复制不等于数据安全文档里把数据备份放在方案综述里单独强调这是很多高可用方案最容易忽视的一点。流复制保证的是主库硬件故障或进程异常时备库能顶上但如果发生误删除数据、业务逻辑错误导致大批量数据被篡改这些错误操作同样会通过WAL日志复制到备库。这种逻辑错误流复制无能为力唯一的手段是时间点恢复PITR。方案采用pg_rman作为备份工具策略是定期全量备份加持续归档备份的组合。全量备份保存某个时间点的完整数据快照归档备份保存从那个时间点之后产生的所有WAL日志。恢复时先恢复全量备份再按时间顺序重放归档日志可以把数据库恢复到任意指定的时间点精确到事务级别。这个机制就是文档里说的恢复到任意时刻的依据。3. PostgreSQL流复制实现WAL日志传输机制与主备参数配置3.1 环境准备与基础配置流复制部署前操作系统层面的准备直接影响后续的稳定性。文档里的操作清单很明确关闭防火墙和SELinux配置/etc/hosts确保主机名解析不依赖DNS配置NTP保证主备时钟同步流复制协议对时间偏差敏感时间差过大会导致WAL日志应用异常调整sysctl.conf内核参数和limits.conf资源限制。这些操作不涉及数据库本身但漏掉任何一个都可能让主从数据同步出现莫名其妙的玄学问题。用户创建方面需要在两台机器上都创建postgres用户并设置密码后续所有数据库操作都使用这个用户执行。如果客户的RHEL环境不能联网文档里给了一个可行的方法先在能联网的环境里下载rpm包并缓存到本地再用脚本打包到离线环境解压安装。在线环境的安装顺序是先安装PostgreSQL再安装pgpool-II和pg_rman全部用rpm包方式安装路径统一在/usr/pgsql-12下。3.2 主库postgresql.conf关键参数调整安装完成后修改主库的postgresql.conf启动流复制。文档里说12.X版本和旧版本的差异主要在备库标识方式主库参数基本一致。以下是一份最小可用的流复制配置以PostgreSQL 12版本为例# postgresql.conf 关键参数master节点 # 开启WAL日志级别为replica支持流复制和归档 wal_level replica # 允许备库连接的最大WAL发送进程数双节点设置为2足够 max_wal_senders 2 # 保留最近16MB的WAL日志防止备库断连后日志被回收 wal_keep_size 16 # 开启备库只读查询能力 hot_standby on # 监听所有地址允许pgpool和备库通过网络连接 listen_addresses * # 同步复制配置指定备库名称并开启同步提交 # 如果对性能更敏感synchronous_commit可改为off走异步 synchronous_standby_names standby1 synchronous_commit onwal_level设为replica是在告诉PostgreSQL生成足够详细的WAL日志这些日志不仅要支持崩溃恢复还要能被其他实例通过流复制协议拉取。max_wal_senders限制同时有多少个备库可以连接主库并接收WAL流双节点一个备库设为2留出余量。wal_keep_size控制主库保留多少WAL日志供备库追赶如果备库断连时间过长、超过了这个保留量备库就追不上了得重新做基础备份。3.3 pg_hba.conf访问控制配置流复制本质上是一个特殊的数据库连接备库以复制协议连接主库。这个连接必须被pg_hba.conf显式放行否则报no pg_hba.conf entry for host错误。文档里专门提到这个问题说明这是部署中最容易翻车的点。配置如下# pg_hba.conf 追加master节点 # 允许备库本机用户postgres以复制协议连接 local replication postgres trust # 允许192.168.1.0/24网段内的standby以复制协议连接主库 host replication postgres 192.168.1.0/24 md5注意replication关键字普通客户端连接用的是all流复制必须单独授权。不要用trust方式在公网环境放行这里为了示例简单选了trust实际客户环境建议用md5或scram-sha-256并配置强密码。3.4 初始化备库并建立流复制关系备库有两种初始化方式文档用的方法是直接复制主库数据目录到备库。流程分三步先在备库上停掉刚初始化好的PostgreSQL实例清空数据目录再从主库用pg_basebackup工具拉取一份基础备份最后在备库数据目录下创建standby标识文件。PostgreSQL 12.X版本的改变在于不再有recovery.conf文件而是用standby.signal和postgresql.conf里的配置来标识备库身份。以下是具体操作# 备库节点操作 # 1. 清空备库默认数据目录 rm -rf /var/lib/pgsql/12/data/* # 2. 从主库拉取基础备份会创建standby.signal文件 pg_basebackup -h master-host -U postgres -D /var/lib/pgsql/12/data -P -R # 3. 确认standby标识文件已生成 ls -l /var/lib/pgsql/12/data/standby.signal # 4. 备库postgresql.conf中追加主库连接信息 # primary_conninfo hostmaster-host port5432 userpostgres application_namestandby1pg_basebackup的-R参数会在备份目录里自动创建standby.signal并生成primary_conninfo配置一步到位。application_name必须和主库postgresql.conf里synchronous_standby_names指定的名称一致否则同步复制不生效主库会认为没有备库在同步。3.5 验证流复制状态配置完成后启动主库、再启动备库在主库执行以下SQL验证复制是否正常工作-- 在主库执行查看复制状态 SELECT pid, usename, application_name, client_addr, state, sync_state FROM pg_stat_replication;state应为streamingsync_state应为sync同步模式或async异步模式。在备库执行SELECT pg_is_in_recovery();返回t表示当前正处于恢复模式即备库身份正常。再在主库建一张测试表插入几行数据备库上立即SELECT验证能看到数据说明WAL日志的传输和应用链路已经打通。4. pgpool-II高可用配置连接池、读写分离与failover自动切换4.1 pgpool-II安装与函数部署pgpool-II在master和standby两台节点上都要安装双节点互为主备任何一个pgpool挂掉时另一个接管VIP。安装完成后在主库执行pgpool提供的SQL脚本创建集群管理函数# 在master节点执行文档中安装相关函数步骤 cd /usr/local/pgsql/share psql -f insert_lock.sql postgres psql -f pgpool-recovery.sql postgrespgpool-recovery.sql为pgpool提供了在线恢复备库时调用的函数insert_lock.sql用于复制模式下防止并发插入冲突虽然本方案用流复制模式而非复制模式但文档里保留了这两个脚本的安装步骤避免某些管理工具依赖缺失。4.2 pgpool.conf核心参数详解pgpool-II 4.X版本的配置文件和3.X差异不小此处给出4.1版本的关键配置片段这些参数直接决定读写分离和自动切换行为# pgpool.conf 关键参数master和standby节点 # 监听所有网卡接受客户端连接 listen_addresses * port 9999 # 后端PostgreSQL节点定义 backend_hostname0 master-host backend_port0 5432 backend_weight0 1 backend_hostname1 standby-host backend_port1 5432 backend_weight1 1 # 主从模式使用流复制 master_slave_mode on master_slave_sub_mode stream # 开启负载均衡SELECT按权重分发 load_balance_mode on # 健康检查每10秒检测一次后端节点存活 health_check_period 10 health_check_timeout 5 health_check_user postgres health_check_password postgres密码 # 故障切换脚本主库宕机时自动执行 failover_command /etc/pgpool/failover.sh %d %m %H # watchdog配置双节点pgpool互相监控 use_watchdog on wd_hostname 本机主机名 wd_port 9000 wd_interval 5 # 虚拟IP地址故障时自动漂移到另一节点 delegate_IP 192.168.1.100backend_weight0和backend_weight1都是1意思是一半的读查询走主库、一半走备库。生产环境如果备库硬件性能弱于主库可以把备库权重调低比如主库1、备库0.5让更少的读流量打到备库。failover_command里的%d表示故障节点ID%m表示新的主节点ID%H表示新的主节点主机名这些占位符由pgpool在执行脚本时自动替换。watchdog是双pgpool节点之间互相探活并管理VIP的关键机制漂移虚拟IP就是靠它实现的。4.3 failover脚本与切换逻辑failover脚本是自动切换的核心。当pgpool健康检查发现主库不可达时执行该脚本在备库上执行pg_ctl promote把备库提升为新的主库同时更新pgpool的后端节点状态信息。脚本内容如下#!/bin/bash # /etc/pgpool/failover.sh # 参数含义 # $1 故障节点ID # $2 新主节点ID # $3 新主节点主机名 FAILED_NODE_ID$1 NEW_MASTER_ID$2 NEW_MASTER_HOST$3 # 如果故障节点本身就是主库则在新的主节点上执行promote # 12.X版本使用pg_ctl promote旧版本使用trigger_file方式 if [ $FAILED_NODE_ID -eq 0 ]; then ssh postgres$NEW_MASTER_HOST /usr/pgsql-12/bin/pg_ctl promote -D /var/lib/pgsql/12/data exit 0 fi这里要特别注意一个容易翻车的地方故障节点ID为0代表主库挂了此时才需要在备库上执行promote如果挂掉的是备库节点ID为1不需要做任何切换操作只需要把该节点标记为down等修复后手动重新加入集群即可。文档第十章中的测试流程正是按照这个逻辑设计的。4.4 pool_passwd、pcp.conf与pool_hba配置pgpool需要知道PostgreSQL用户的密码才能发起连接但明文写在配置文件里不安全所以要用pgpool自带的工具生成加密密码文件# 生成pool_passwd文件 pg_enc -m md5 -u postgres -p # 输入postgres用户密码生成hash写入pool_passwd # 输出类似postgres:md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx # 配置pcp.conf用于pgpool管理命令认证 pg_md5 -p # 输入pgpool管理密码把生成的用户名和hash加入pcp.confpool_passwd供pgpool登录PostgreSQL时使用pcp.conf供管理员执行pcp_*系列命令时使用两者不能混淆。文档中提到配置pool_hba是为了让pgpool接入层再做一次客户端IP和用户认证管控做法与PostgreSQL的pg_hba.conf完全一致这里不多展开。4.5 启动pgpool并验证读写分离两台节点都启动pgpool后先在本机验证进程和日志# 启动pgpool pgpool -f /etc/pgpool/pgpool.conf -n # 检查进程 ps -ef | grep pgpool # 查看日志确认watchdog和health check工作正常 tail -100 /var/log/pgpool/pgpool.log应用层通过VIP连接pgpool的9999端口。验证读写分离行为有一个直观的方法在主库和备库上分别执行SELECT * FROM pg_stat_activity如果应用发来多个SELECT查询能看到这些连接被分散到两台PostgreSQL上而执行INSERT操作后只有主库出现写入事务。# 用psql连接pgpool的9999端口 psql -h 192.168.1.100 -p 9999 -U postgres -d testdb # 查看当前pgpool识别到的后端节点状态 pcp_node_info -h 127.0.0.1 -U postgres -p 9898 # 输出类似 # 0 master-host 5432 up # 1 standby-host 5432 up5. 部署避坑与故障排查流复制和pgpool最容易翻车的五个点5.1 主库总是报 no pg_hba.conf entry for host现象备库启动后日志里不断出现no pg_hba.conf entry for host 192.168.1.21, user postgres, database replication流复制一直建立不起来。原因只配置了普通连接的pg_hba规则忘了添加replication类型的授权条目。流复制连接使用的数据库名是replication关键词不同很多第一次部署的人容易忽略。解决在主库pg_hba.conf里追加一行host replication postgres 192.168.1.0/24 md5然后pg_ctl reload重载配置。注意reload即可生效不需要重启PostgreSQL。5.2 流复制一切正常但备库数据始终追不上主库现象pg_stat_replication能看到state为streaming但主库写入大量数据后备库的查询结果明显滞后甚至报WAL日志已不可用。原因max_wal_senders或者wal_keep_size设得太小。备库断连重连后主库需要保留备库断连期间产生的WAL日志供备库追赶如果这些日志被提前回收备库将无法继续同步。解决适当增大wal_keep_size的值。如果数据量增长很快更推荐配置archive_mode on并设置archive_command把WAL日志归档到独立存储这样后备库即使落后很多也可以从归档目录拉取历史WAL日志追平进度。5.3 PostgreSQL 12版本还在找recovery.conf现象按照网上旧教程配置备库创建了recovery.conf文件但备库启动时报错无法进入恢复模式。原因PostgreSQL 12版本开始取消了recovery.conf文件改为在数据目录放置standby.signal文件并配合postgresql.conf里的primary_conninfo参数实现。旧文件已经完全不被识别。解决删掉recovery.conf确认数据目录里有standby.signal文件并检查postgresql.conf中primary_conninfo配置是否正确。用pg_basebackup -R生成的备库目录会自动处理这一切手动创建时容易漏。5.4 测试failover后pgpool报 all backend nodes are down现象手动把主库进程停掉测试切换备库promote成功了但pgpool日志报all backend nodes are down应用连不上数据库。原因常见情况有两种。一是failover脚本执行失败备库没有被正确promote两个后端节点都不可用二是pool_passwd里密码与PostgreSQL实际用户密码不一致pgpool重新建立连接时认证失败。解决先手工在备库上执行pg_ctl promote确认数据库能正常启动再检查pgpool日志里具体的错误码是认证失败还是连接超时。如果认证失败用pg_enc重新生成pool_passwd。这个问题的排查思路是先证明备库本身可用再回头看pgpool与备库之间的连接配置。5.5 主备切换后应用写请求仍然失败现象VIP已经漂移到节点2pgpool状态显示节点2为master但应用执行INSERT时报只读错误。原因备库虽然被promote了但有些连接可能还保持着旧的事务上下文或者应用连接池里缓存的数据库连接仍然绑定在旧的pgpool连接上而这些连接指向的是已经被标记为down的旧主库。解决在failover脚本中加入一步操作等待pg_ctl promote完成后执行pcp_attach_node把新主节点重新挂到pgpool集群中并确认旧节点状态为down。应用侧如果使用连接池需要配置连接池的自动重连和失效连接清理策略最简单的方法是让应用在收到连接异常时强制重建连接。6. 验收与答辩从故障切换测试到备份恢复验证的完整方法6.1 故障切换测试的三个层次一套高可用方案能不能验收通过不是看搭建过程有多顺利关键看故障发生时系统行为是否符合预期。测试分三个层次递进。第一层是主库PostgreSQL进程异常退出测试。在主库执行pg_ctl stop -m fast模拟数据库崩溃观察pgpool日志确认failover脚本被触发、备库被promote、VIP漂移到节点2。整个切换时间应在秒级完成应用只需要短暂重连即可恢复。第二层是整个节点宕机测试。执行shutdown -h now关闭主库所在物理机模拟硬件故障。这种场景下网络断开和进程退出同时发生watchdog也要参与仲裁验证VIP和pgpool主备角色是否正确倒换。第三层是恢复回切测试。把原主库修复后重新加入集群验证pgpool能否把它作为备库挂回重新建立流复制关系数据是否追平。回切往往比切换更考验方案完整性。6.2 备份与恢复验证流程pg_rman备份的验证不能只看备份是否成功必须实际演练恢复流程。以下是标准的验证方法# 1. 查看已有备份列表 pg_rman show --backup-dest/backup # 2. 校验备份有效性 pg_rman validate --backup-dest/backup # 3. 在备用环境执行恢复 pg_rman restore --backup-dest/backup --pgdata/var/lib/pgsql/12/data恢复完成后启动数据库确认数据完整性重点是验证时间点恢复能力。归档日志配合全量备份才是恢复到任意时刻的基础。建议在验收时模拟一个误删数据的场景正常业务运行中删除一张核心表的部分数据然后通过备份恢复到删除前一刻用业务数据对比确认恢复结果。如果能顺利完成说明备份方案是真的能兜底而不只是能跑通备份命令。6.3 答辩准备把四个关键问题讲清楚如果是答辩场景评委最常问的问题往往集中在四个方面。第一个是为什么选流复制而不选逻辑复制或其他方案。回答要点是流复制基于WAL日志物理传输延迟低、无需修改业务SQL、能保持备库与主库完全一致双节点场景下是最成熟可靠的方案。第二个是同步和异步的区别以及你的选择依据。回答要点是结合业务容忍度允许丢秒级数据就选异步追求强一致就选同步。如果能补充说明本方案的synchronous_commit是可以在会话级别动态调整的会加分。第三个是pgpool故障切换的完整流程。回答要点是把failover脚本里的步骤讲清楚健康检查发现主库不可达、触发脚本、备库promote、更新节点状态、VIP漂移。能画出这个时序说明你理解你不是只敲了安装命令。第四个是流复制和离线备份如何配合。回答要点是流复制只防硬件故障不防逻辑错误误操作、恶意删数据这类问题必须靠备份恢复兜底。6.4 一份能用于验收的检查清单准备一张检查表更便于交付验收也方便你在答辩时展示方案的专业性。检查项至少包含五类流复制状态pg_stat_replication的state为streaming、pgpool节点状态pcp_node_info全部为up、读写分离生效主备的pg_stat_activity都能看到SELECT查询、failover切换时间记录从故障发生到VIP漂移完成的总时长、备份恢复演练结果恢复后的数据与故障前一致。每项都要有截图或日志记录作为证据。这套方案做完之后最大的价值不是搭建过程本身而是验证了故障预案是真的可执行的。我在做类似项目时养成的习惯是每次交付前强制自己走一遍完整的failover测试加恢复演练不走过一遍不敢交付因为从纸上配置到真实故障之间隔着很多配置和脚本细节。希望这个方案和验收方法能帮到你让你在交付高可用数据库时心里有底。本文还有配套的精品资源点击获取