Postgres主从流复制+pgpool高可用方案：从WAL原理到Failover实操-北京尧图网络

简介一份针对 PostgreSQL 高可用架构的完整方案文档面向数据库运维与架构设计工程师重点解决基于 WAL 流复制搭建主从库、实时数据同步以及结合 pgpool-II 实现连接池管理、读写分离与故障自动切换的问题。文档详细介绍了同步复制与异步复制的差异、主库 postgresql.conf 关键参数、备库初始化流程、pgpool-II 的负载均衡与健康检查配置还融入了 pg_rman 备份工具、环境规划与部署前的系统准备包括用户创建、防火墙与 SELinux 关闭、hosts 配置、时钟同步、sysctl 参数调整及 limit 资源限制等整体以章节化目录呈现便于按步骤落地。资源包包含 1 个 docx 文档压缩包约 773KB内容覆盖方案综述、实践环境、客户环境准备、配置步骤与总结结构完整可作为生产环境搭建前的技术评审和部署参考。该文档已有 467 人学习浏览适合需要独立搭建 PostgreSQL 主从复制与 pgpool 高可用环境的中高级数据库工程师。1. Postgres主从流复制pgpool高可用方案这套文档解决什么问题先说结论单机 PostgreSQL 在生产环境里最缺的不是性能而是误操作后的后悔药。一次 DROP TABLE 或者磁盘损坏如果数据只落在一台机器上恢复手段基本只剩过去的备份而备份时间点和故障点之间的数据只能靠日志手工拉齐代价极大。Postgres主从流复制pgpool高可用方案解决的就是这个三角问题流复制让 WAL 日志实时或准实时地到达备库pgpool 负责接入层管理、读写分离和主节点故障转移pg_rman 再兜底离线备份。这套方案适合正在维护单机库、想把主备链路补上的 DBA 和运维工程师也适合要做高可用验收的架构师。下面按实操顺序把环境准备、复制配置、pgpool 落地和 Failover 验证逐步过完。2. 流复制原理与选型为什么是 WAL 流复制而不是文件归档PostgreSQL 从 9.0 开始提供流复制它和更早的 WAL 日志归档是两种不同的数据同步方式。归档方式要等一个 WAL 文件写满之后才把整个文件拷贝到备库延迟通常是一个日志文件的时间流复制则由主库的 walsender 进程把产生的 WAL 记录实时推给备库的 walreceiver同步颗粒度更细延迟更小。方案里选流复制而不是归档不是因为它新而是因为它在备库数据新鲜度和恢复点目标上更可控。2.1 流复制链路WAL 日志从产生到应用的全过程理解这条链路后面所有配置都不会跑偏。主库上有事务提交时数据页面的改变会以 WAL 记录的形式先写进内存中的 WAL bufferwalwriter 进程周期性地把这些 WAL page 刷新到磁盘上的 pg_wal 目录。如果配置了备库walsender 进程会把 WAL page 持续发送给备库的 walreceiver 进程walreceiver 负责把收到的 WAL 直接写进本地磁盘同时备库的 startup 辅助进程不断重放这些 xlog修改本地数据文件让备库跟上主库的状态。这条链路里有个关键点备库处于恢复模式但仍然可以接受只读请求。这就是 pgpool 做读写分离的基础——读流量可以被分到备库写流量留在主库两边各干各的活。2.2 同步与异步synchronous_commit 的取舍流复制默认是异步的主库事务提交后备库通常能在 1 秒内追上但如果主库突然崩溃异步模式可能有少量数据丢失。同步复制的做法是主库必须等备库也写完 WAL 才能提交事务数据一致性更强代价是事务响应时间变长。配置同步其实只要两个参数但是要不要开取决于业务容忍度-- 在主库的 postgresql.conf 里调整 synchronous_commit on synchronous_standby_names *参数说明synchronous_standby_names设为*表示任意一台备库确认写入即可提交synchronous_commit设为on才是真正等待备库确认。如果这两个参数不配套比如只开synchronous_commit而synchronous_standby_names为空同步不会生效。我一般建议金融类、订单类核心库开同步内部系统或者报表库用异步就够了异步模式下主备延迟很小但要在方案文档里明确写清楚「主库崩溃可能丢最后 1 秒数据」这个前提。2.3 pgpool 的职责连接池、复制、负载均衡和连接限制pgpool-II 处在客户端和 PostgreSQL 服务器之间是一个专门的中间件。它做四件事连接池、复制、负载均衡和连接限制。连接池把已经打开的数据库连接保持住客户端用相同参数连接时直接复用减少建连开销复制功能让多个节点上的数据保持一致一台节点失效时服务不断负载均衡针对 select 查询把所有后端服务器的读压力摊开连接限制则是在 PostgreSQL 的max_connections打满之前把多余的连接放进队列而不是直接返回错误。这个定位决定了它不参与 WAL 复制只管流量和健康检查。所以 pgpool 自己挂了不影响主备数据同步但会影响客户端接入生产环境里 pgpool 节点也要做双机这就是原文里 master/standby 两台机器都装 pgpool 的原因。2.4 备份选型为什么需要 pg_rman 而不是 pg_dumppg_dump 是逻辑备份恢复时要重放 SQL速度慢而且很难精确恢复到某个时间点。pg_rman 做物理备份基于 WAL 归档支持完整备份、增量备份和基于时间点恢复PITR。在线同步复制解决的是故障转移离线备份解决的是误操作恢复两类场景不能互相替代。方案里把 pg_rman 作为离线备份组件和流复制、pgpool 形成三层防线这个结构在实施前要先想清楚后面每一步配置才有依据。3. 环境准备与 PostgreSQL 安装从离线包到 initdb 的完整路径正式配置流复制之前环境里有一堆不熟的人容易忽略的细节。这一章把客户环境准备、资源调整、数据库安装一次性过完。原文适用的系统是 RHEL 和 CentOS所有节点主库 master 和备库 standby都要执行相同的系统配置。3.1 软件包下载与 postgres 用户先把三个软件包下载到固定目录postgresql、pgpool、pg_rman。下载之后创建 postgres 用户并设置密码规划安装目录和数据目录。我的习惯是建/opt/src放源码包/usr/local/pgsql放 PostgreSQL 安装目录/var/lib/pgsql/12/data放数据目录。useradd -m postgres echo your_password | passwd --stdin postgres mkdir -p /opt/src /usr/local/pgsql /var/lib/pgsql/12/data chown -R postgres:postgres /usr/local/pgsql /var/lib/pgsql/12/data目录权限必须一开始就归到 postgres 用户否则后面initdb和pg_ctl会因为权限问题报各种奇怪的错。安装目录如果放在 postgres 用户家目录下也要注意家目录权限不能是 700否则其他服务访问会碰壁。3.2 系统资源调整防火墙、SELinux 与时钟同步这三个点不处理干净后面的流复制和 pgpool 健康检查都会在奇怪的地方翻车。先关防火墙RHEL/CentOS 7 用 firewalld6 用 iptables# RHEL/CentOS 7 systemctl stop firewalld systemctl disable firewalld # RHEL/CentOS 6 service iptables stop chkconfig iptables off然后是 SELinux直接改成 disabledsed -i s/^SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config setenforce 0SELinux 在 enforcing 模式下会拦截 PostgreSQL 对 socket 文件和数据目录的访问这种问题在日志里看是权限拒绝排查起来很费时间属于血泪经验。时钟同步也很关键主备节点时间差超过 1 分钟pgpool 的健康检查超时设置就容易误判。用 chronyd 或 ntpdate 指向公司内部的 NTP 服务器同步周期设短一点。3.3 sysctl 与 limit 资源设置PostgreSQL 对共享内存和文件描述符的要求比较高/etc/sysctl.conf里至少要调这几个参数kernel.shmmax 34359738368 kernel.shmall 8388608 vm.overcommit_memory 2参数说明kernel.shmmax是单个共享内存段的最大大小kernel.shmall是共享内存页的总数两个值要大于 PostgreSQL 配置的shared_buffers和max_connections对应需求。vm.overcommit_memory 2是禁止内存超额分配避免数据库进程被系统 OOM Killer 误杀。limits.conf 里给 postgres 用户放开文件数postgres soft nofile 65536 postgres hard nofile 65536 postgres soft nproc 131072 postgres hard nproc 131072流复制的 walsender 和 walreceiver 要保持长连接备库 startup 进程要持续打开 WAL 文件文件描述符不够会直接导致复制中断。3.4 RHEL 离线环境的安装源配置很多客户机房不允许服务器直接连外网。原文专门提供了「RHEL 安装环境获取脚本」思路是在有网环境先下载需要的 rpm 包保存到 cache再拷贝到离线环境配置本地源。我一般用 yum 的 downloadonly 插件mkdir -p /opt/rpm_cache yum install --downloadonly --downloaddir/opt/rpm_cache \ postgresql12-server postgresql12-contrib postgresql12-devel把整个/opt/rpm_cache拷到客户机之后createrepo 建本地源客户机上配置一个指向本地源的 repo 文件之后yum install就能离线安装。这套方法比手动一个个装 rpm 依赖可靠得多尤其是 PostgreSQL 依赖 readline、zlib 这类基础库时。3.5 编译安装 PostgreSQL 与 initdb 初始化离线环境下最稳妥的方式是源码编译。依赖包先装好gcc、make、readline-devel、zlib-devel、openssl-devel。然后解压编译cd /opt/src/postgresql-12.2 ./configure --prefix/usr/local/pgsql make -j4 make install编译参数说明--prefix指定安装目录我这里用/usr/local/pgsql如果你用系统自带的包管理器安装路径会不一样。编译完成后设置环境变量echo export PATH/usr/local/pgsql/bin:$PATH /etc/profile.d/pgsql.sh echo export PGDATA/var/lib/pgsql/12/data /etc/profile.d/pgsql.sh source /etc/profile.d/pgsql.sh初始化数据库sudo -u postgres /usr/local/pgsql/bin/initdb -D /var/lib/pgsql/12/data -E UTF8 --localeen_US.utf8参数说明-E UTF8指定数据库编码--locale指定区域设置。如果业务对中文有要求locale 也可以设成zh_CN.utf8但要确保系统里已经生成了对应的 locale。初始化之后先不要急着启动主库流复制的参数还没配。4. 配置主从流复制从 postgresql.conf 到 standby 标志的细节数据库装完之后接下来是流复制的核心配置。主库调整实例参数并放行复制连接备库从主库拉取基础数据再以恢复模式启动。12.X 版本和旧版本在备库标志文件的处理上不一样这是整个方案里最容易踩坑的地方。4.1 主库参数调整wal_level、max_wal_senders 与访问控制在主库的postgresql.conf里先调整这几个参数listen_addresses * wal_level replica max_wal_senders 10 max_replication_slots 10 wal_keep_size 1024 synchronous_commit on synchronous_standby_names *参数说明wal_level replica是流复制的最低要求生产环境如果以后要做逻辑复制也可以直接设logicalmax_wal_senders决定最多能有多少个 WAL 发送进程备库数量加未来可能的新备库留点余量wal_keep_size是 12.X 之后替代旧版wal_keep_segments的参数单位 MB表示在 pg_wal 目录里保留多少 WAL 段供备库拉取备库长时间离线时这个值要够大否则备库重新上线会因为 WAL 已被清理而需要重新全量同步。synchronous_standby_names *配合synchronous_commit on启用同步复制。然后创建复制用户CREATE ROLE repl LOGIN REPLICATION PASSWORD repl_password;给pg_hba.conf加放行规则host replication repl 10.10.10.0/24 md5注意是replication这个数据库关键字不是具体的数据库名。很多第一次配流复制的人在这里写成了业务库名备库连接时直接报 no pg_hba.conf entry。4.2 备库初始化pg_basebackup 与 standby 标志备库不需要手动 initdb直接从主库拉一份基础备份最快。我用 pg_basebackupsudo -u postgres /usr/local/pgsql/bin/pg_basebackup -h 主库IP -U repl \ -D /var/lib/pgsql/12/data -P --wal-methodstream -R参数说明-h指定主库 IP-U指定复制用户-D是备库数据目录--wal-methodstream表示在备份过程中同步接收 WAL-R会自动生成standby.signal文件并把primary_conninfo写入postgresql.conf这一步非常省事。12.X 之后备库标志文件叫standby.signal它告诉数据库这个实例要以恢复模式启动。如果-R没有生效检查数据目录里有没有这个文件没有就手动创建touch /var/lib/pgsql/12/data/standby.signal chown postgres:postgres /var/lib/pgsql/12/data/standby.signal同时确认postgresql.conf里有 primary_conninfoprimary_conninfo host主库IP port5432 userrepl passwordrepl_password注意12.X 之前的老版本不是这个玩法。PostgreSQL 10.3 需要手动创建recovery.conf文件里面写standby_mode on和primary_conninfo。而主库上如果残留了recovery.conf要改成recovery.done否则主库启动时会试图进入恢复模式。原文里专门强调了 12.X 以前的版本靠recovery.done这个标志文件区分主备12.X 以后版本直接看postgresql.conf和standby.signal这个差异在升级老项目时一定要先确认清楚。4.3 启动备库与验证链路主库启动之后再启动备库。备库启动命令sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/12/data start启动后看进程。备库需要能看到 walreceiver 和 startup 进程ps aux | grep -E walreceiver|startup主库上要有 walsender 进程ps aux | grep walsender然后做主备数据验证。在主库建一张测试表插入数据备库马上应该能查到-- 主库执行 CREATE TABLE sync_test(id int, note text); INSERT INTO sync_test VALUES(1, ok); -- 备库执行 SELECT * FROM sync_test;如果备库查不到数据先看备库日志最常见的两个原因pg_hba.conf没放行复制用户或者primary_conninfo里的密码不对。如果启用了同步复制主库事务在备库确认前会一直等待测试时如果发现 INSERT 卡住优先看备库的 walreceiver 是否正常连上。5. pgpool 配置与常见问题排查从连接池到虚拟 IP 的完整落地主备数据链路通了之后pgpool 才能上岗。pgpool 接管客户端连接、健康检查和故障切换这一章按原文的安装、配置、启动顺序来最后把三个反复踩的坑单独列出来。5.1 pgpool-II 安装与相关函数pgpool-II 在主备两台机器上都要装编译安装和 PostgreSQL 类似cd /opt/src/pgpool-II-4.1.1 ./configure --prefix/usr/local/pgpool make -j4 make install安装完成后需要到主库执行 pgpool 自带的相关函数否则 pgpool 在做读写分离时解析表名会出问题sudo -u postgres psql -f /usr/local/pgpool/share/pgpool-II/pgpool-regclass.sql postgres这个函数的作用是让 pgpool 能正确识别表名和 schema 的关系尤其是数据库里存在同名表时不装的话会把查询路由到错误的节点。原文里单独有一章「安装相关函数」这一步别跳过。5.2 pgpool.conf后端节点、连接池与健康检查4.X 版本的pgpool.conf里最核心的是后端节点定义和连接池参数listen_addresses * port 9999 socket_dir /var/run/pgpool pcp_port 9898 backend_hostname0 主库IP backend_port0 5432 backend_weight0 1 backend_data_directory0 /var/lib/pgsql/12/data backend_hostname1 备库IP backend_port1 5432 backend_weight1 1 backend_data_directory1 /var/lib/pgsql/12/data num_init_children 32 max_pool 8参数说明backend_weight控制读流量的权重两台机器都是 1 表示 50/50 分摊num_init_children是 pgpool 预派生的子进程数max_pool是每个子进程能缓存多少个后端连接两者乘积决定了 pgpool 能同时处理的连接总数。如果这个值超过 PostgreSQL 的max_connections连接会堆积到 PostgreSQL 侧反而起不到保护作用所以要先算好。健康检查参数默认值就能用但超时时间建议按网络状况调一下内网可以设短跨机房要放宽。5.3 配置 pcp.conf、pool_passwd 与 pool_hbapcp.conf 是 pgpool 控制台的管理用户用于执行 pcp 命令和 failover 脚本。生成方式/usr/local/pgpool/bin/pg_md5 -m -u pgpool your_control_pwd echo pgpool:生成的md5值 /usr/local/pgpool/etc/pcp.confpool_passwd 用于客户端登录校验把 PostgreSQL 用户的密码加密存进去/usr/local/pgpool/bin/pg_md5 -m -u postgres postgres_pwd mv pool_passwd /usr/local/pgpool/etc/ chown postgres:postgres /usr/local/pgpool/etc/pool_passwdpool_hba.conf 的配置逻辑和 PostgreSQL 的 pg_hba.conf 一样限制哪些来源 IP 能通过 pgpool 访问数据库。注意这是 pgpool 层的校验和数据库层的 pg_hba.conf 是两层独立的访问控制。5.4 failover 脚本故障切换时执行的钩子failover 脚本在 pgpool 判定后端节点故障时触发。常见做法是写一个 shell 脚本在双节点场景下做备库提升#!/bin/bash # $1: 故障节点ID $2: 新主库ID $3: 旧主库ID echo $(date) failover: node $1 down, promote node $2 /var/log/pgpool/failover.log /usr/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/12/data promote脚本要加执行权限属主必须和 pgpool 进程一致否则 pgpool 调不起来chmod x /usr/local/pgpool/etc/failover.sh chown postgres:postgres /usr/local/pgpool/etc/failover.sh注意多备库场景下不能简单对所有备库执行 promote需要根据$2参数判断哪个节点该提升这块要根据实际的集群拓扑写逻辑。双节点场景下这个脚本可以保持简单。5.5 启动 pgpool 与状态验证主备两台机器都要启动 pgpool。后台启动方式sudo -u postgres /usr/local/pgpool/bin/pgpool -n -D \ /var/log/pgpool/pgpool.log 21 -n表示前台模式运行配合后台符号使用-D删除旧的 socket 文件和 pid 文件再启动避免残留文件干扰。验证分三步。第一步看进程ps aux | grep pgpool第二步通过 pgpool 访问数据库psql -h 虚拟IP -p 9999 -U postgres -d postgres第三步查看后端节点状态/usr/local/pgpool/bin/pcp_node_info -h 虚拟IP -p 9898 -U pgpool -w正常状态下两个节点都应该是 up 状态角色分别是 primary 和 standby。虚拟 IP 通过ip addr show确认它落在主节点上。5.6 常见问题排查三个反复踩的坑第一个坑no pg_hba.conf entry for host 10.10.10.x。现象是客户端通过 pgpool 连接时报这个错直接连 PostgreSQL 也报。原因很直接PostgreSQL 的 pg_hba.conf 没有放行 pgpool 节点的 IP 或者客户端网段。解决方法是确认主备库的 pg_hba.conf 都加上了host all all 客户端网段 md5然后 reload 配置。注意主备库都要加因为故障切换后备库会变成主库放行规则必须两边一致。第二个坑all backend nodes are down。现象是 pgpool 日志或客户端报所有后端节点不可达。原因通常是后端 PostgreSQL 进程没启动或者 pgpool.conf 里的端口、IP 写错。解决方法是先逐个看 PostgreSQL 是否在监听对应端口再确认 pgpool.conf 的 backend_hostname 和 backend_port 和实际一致最后检查健康检查用户有没有权限连接数据库。第三个坑主库故障后虚拟 IP 不漂移。现象是主库 postgres 进程已经停了但虚拟 IP 还在原主库上客户端无法接入。原因多半是 watchdog 配置没生效或者 failover 脚本没有做 VIP 切换。解决方法是检查 pgpool.conf 里 watchdog 段是否启用虚拟 IP 是否配置在正确的网卡上以及 failover 脚本是否有漂移虚拟 IP 的逻辑。这个坑最容易在演练时才发现所以一定要做 Failover 测试不能只配不验。6. Failover 验证与 pg_rman 备份的实战技巧6.1 故障切换验证两次停机测试配置完成后要强制自己做两次故障演练。第一次模拟主库数据库进程宕机在主库上停止 postgres 服务观察 pgpool 日志里的 failover 记录确认备库自动提升为新的主库虚拟 IP 漂移到备库应用通过虚拟 IP 的读写请求恢复正常。第二次模拟 pgpool 节点宕机停止主库节点上的 pgpool 进程确认虚拟 IP 漂移到备库节点客户端连接依赖的 VIP 不中断。# 测试一停主库 postgres sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/12/data stop -m fast tail -200 /var/log/pgpool/pgpool.log | grep -i failover # 测试二停主库 pgpool sudo -u postgres kill -TERM $(cat /var/run/pgpool/pgpool.pid) ip addr show | grep 虚拟IP测试二通过后再手动把 VIP 切回原主库或者按既定流程做一次主备切换回切确保整个集群没有留下脏状态。6.2 pg_rman 备份归档模式与两种备份脚本备份组件要尽早启用。先在主库打开归档模式archive_mode on archive_command test ! -f /backup/archive/%f cp %p /backup/archive/%f%p是源 WAL 文件路径%f是文件名归档目录要提前创建好属主归 postgres。然后编译安装 pg_rman初始化备份目录mkdir -p /backup/pg_rman chown postgres:postgres /backup/pg_rman pg_rman init -B /backup/pg_rman全量备份脚本和归档备份脚本分别跑#!/bin/bash # pg_rman_full.sh 每周全量 BACKUP_PATH/backup/pg_rman pg_rman backup --backup-modefull --with-serverlog -B $BACKUP_PATH pg_rman validate -B $BACKUP_PATH#!/bin/bash # pg_rman_archive.sh 每小时归档 BACKUP_PATH/backup/pg_rman pg_rman backup --backup-modearchive -B $BACKUP_PATH pg_rman validate -B $BACKUP_PATH从那以后我每次在生产库做任何变更前都会强制自己先跑一遍 pg_rman 全量备份并把主备状态和虚拟 IP 归属打到一个固定检查清单里。这套方案的故障切换本质是把「紧急动作」转成「日常演练」备库提升和 VIP 漂移都验证过了真正出事时才不会手忙脚乱。希望帮到你。本文还有配套的精品资源点击获取

Postgres主从流复制+pgpool高可用方案：从WAL原理到Failover实操

相关推荐

PA2指令系统实现：取指译码执行与框架代码阅读指南

金融级分布式数据库选型与落地：从市场报告到生产压测的实战指南

C#实现Excel实时导入SQL Server：NPOI+SqlBulkCopy完整方案

更多资讯

PyTorch人脸表情识别实战：从CNN训练到OpenCV实时部署

农作物病虫害识别毕设避坑指南：从数据清洗到模型训练全解析

PyTorch实战：STGCN时空图卷积网络实现与调优

Wind取数到Fama-French因子复现：Python与statsmodels实战

PyTorch CIFAR-10图像识别实战：从环境搭建到95%+准确率调优

深入理解Linux进程退出、等待与替换机制

今日更新

交通拥堵预测毕设：基于Python+LSTM的车辆流量预测系统全流程解析

MATLAB/Simulink搭建10机39节点电力系统暂态稳定仿真实战

无人机航拍三维重建全流程：从SfM到网格生成的避坑指南

本周精选

交通拥堵预测毕设：基于Python+LSTM的车辆流量预测系统全流程解析

MATLAB/Simulink搭建10机39节点电力系统暂态稳定仿真实战

无人机航拍三维重建全流程：从SfM到网格生成的避坑指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？