简介《Postgres主从流复制pgpool高可用方案》是一份面向PostgreSQL运维与数据库架构学习者的docx技术文档围绕主备库实时同步、故障切换、连接池与读写分离等常见高可用需求展开适合已具备基础SQL与Linux操作能力、希望系统梳理部署思路的读者参考。资源包共1个docx文件约773KB以方案说明和配置要点为主便于按章节查阅与归档。文档内容覆盖流复制的同步与异步模式、pgpool-II连接池、复制、负载均衡、读写分离及连接数限制并包含软件环境、客户环境准备、主备配置、备份和故障模拟测试等模块。当前已有470人学习可帮助读者理解Postgres高可用集群的组成、配置流程与验证方法也能为实际部署中的参数选择和排错提供参考。1. 从一个版本坑说起PostgreSQL 12.2 与 pgpool-II 4.1.1 的主从环境为什么容易装歪在 Linux 上落地 PostgreSQL 主从流复制加 pgpool 高可用方案最容易被忽略的不是 SQL而是版本分界线。PostgreSQL 10.3 的备库还认 recovery.conf到了 12.2 已经改成 standby.signalpgpool-II 3.6.14 和 4.1.1 的 pgpool.conf 参数名也有出入照着一篇旧笔记混装备库起不来、pgpool 报 all backend nodes are down排查半天才发现是配置位置写错。这套环境适合需要读写分离、连接池和故障切换的数据库场景装之前先把版本、目录和权限钉死后面才不至于反复翻车。2. 装前把环境钉死PostgreSQL 12.2、pgpool-II 4.1.1 与 pg_rman 的版本选择2.1 软件版本矩阵与两台节点的角色划分这套方案的核心组件不复杂PostgreSQL 负责数据pgpool-II 挡在数据库前面做连接池、读写分离和故障切换pg_rman 负责离线备份。真正容易乱的是版本组合。项目资料里给了两套环境旧版本是 PostgreSQL 10.3 配 pgpool-II 3.6.14新版本是 PostgreSQL 12.2 配 pgpool-II 4.1.1操作系统覆盖 CentOS 6.5 和 CentOS 7.5。新环境我一般直接选 12.2 4.1.1旧环境如果已经跑着 10.3就不要为了追新单独换 pgpool 大版本主从节点的 PostgreSQL 版本必须一致pgpool 版本也要和 PostgreSQL 主版本匹配。组件旧环境新环境角色PostgreSQL10.312.2主库、备库pgpool-II3.6.144.1.1连接池、读写分离、故障切换pg_rman与 PostgreSQL 主版本匹配与 PostgreSQL 主版本匹配离线备份与恢复操作系统CentOS 6.5CentOS 7.5运行平台示例环境用两台机器主库 10.0.0.11备库 10.0.0.12pgpool 分别装在这两台机器上虚拟 IP 用 10.0.0.100。这样不需要额外一台中间件服务器但要求两台机器的 pgpool 配置、watchdog 通信和 VIP 漂移都配通。软件包统一放在/opt/soft安装路径用/usr/local/pgsql-12.2和/usr/local/pgpool数据目录用/data/pgdata归档目录用/data/archive。目录先建出来后面所有命令都围绕这几个路径走。# 两台节点都执行建立统一的安装包和目录规划 mkdir -p /opt/soft /usr/local/pgsql-12.2 /usr/local/pgpool mkdir -p /data/pgdata /data/archive /var/log/pgpool chown -R postgres:postgres /data /var/log/pgpool chmod 700 /data/pgdata这段命令的重点不是创建目录而是把权限先归到 postgres 用户。数据目录权限给 700归档目录和日志目录也要让 postgres 可写。很多人把安装包放在/root下再用 postgres 去解压结果权限不够这不是玄学是/root默认 700 导致的。安装包统一放/opt/soft后面切换用户执行解压和编译都省事。2.2 系统资源与内核参数关闭防火墙、SELinux、配置 hosts 和 sysctl目标环境如果是 RHEL 或 CentOS安装前先处理防火墙、SELinux、hosts、时钟同步和内核参数。生产环境可以不关防火墙但必须放行 PostgreSQL 的 5432、pgpool 的 9999、pcp 的 9898 和 watchdog 的 9000/9694 等端口实验环境为了减少变量常见做法是先关掉 firewalld 和 SELinux配通后再按最小端口策略放开。不要只关服务不关开机自启重启后防火墙又回来pgpool 的 VIP 漂移会直接失效。# 实验环境关闭防火墙和 SELinux生产环境改为放行端口 systemctl stop firewalld systemctl disable firewalld setenforce 0 sed -i s/^SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config # 两台节点写入 hosts主机名和 IP 要能互相解析 cat /etc/hosts EOF 10.0.0.11 pg-master 10.0.0.12 pg-standby EOF # 时钟同步CentOS 7 用 chronydCentOS 6 用 ntpd systemctl enable chronyd systemctl start chronyd chronyc sourceshosts 里写的是主机名和内网 IP后面primary_conninfo、backend_hostname、wd_hostname都可以用主机名减少换环境时改配置的工作量。时钟同步必须做流复制本身不依赖时钟但 pgpool 的健康检查、日志排序和故障切换判断都怕两台机器时间差太大。chronyc sources能看到同步源才算完成不要只看服务状态。内核参数按 PostgreSQL 的常规做法调整。共享内存、文件句柄、进程数不够时initdb或高并发连接阶段就会报错。下面这组值适合示例环境内存更大的机器可以把kernel.shmmax和kernel.shmall继续往上调。cat /etc/sysctl.conf EOF kernel.shmmax 68719476736 kernel.shmall 16777216 vm.swappiness 10 vm.overcommit_memory 2 fs.file-max 655360 EOF sysctl -p cat /etc/security/limits.conf EOF postgres soft nproc 65536 postgres hard nproc 65536 postgres soft nofile 65536 postgres hard nofile 65536 EOFkernel.shmmax控制单个共享内存段的最大值kernel.shmall控制共享内存总页数vm.overcommit_memory2表示不允许过量分配配合 PostgreSQL 的共享内存更稳。limits.conf里的 nproc 和 nofile 是给 postgres 用户的改完要重新登录 postgres 才生效ulimit -u和ulimit -n能看到结果才算数。装库前这些参数不调后面出现“could not resize shared memory segment”再回头查成本会高很多。2.3 创建 postgres 用户、目录与权限别把安装包放在 /root 下PostgreSQL 不允许用 root 用户初始化数据库所以要先建 postgres 用户。用户 ID 不强制固定但两台节点最好保持一致后面挂载目录、备份恢复和 pgpool 调用脚本时少一层权限麻烦。密码要设置但不要用简单口令复制用户和 postgres 管理用户分开后面 pg_hba.conf 才能按权限收口。# 两台节点都执行 groupadd -g 1001 postgres useradd -u 1001 -g postgres -m -s /bin/bash postgres passwd postgres # 把安装包放到 /opt/soft并交给 postgres 可读 chown -R postgres:postgres /opt/soft ls -ld /opt/soft /data/pgdata /data/archive-u 1001 -g 1001是为了让两台机器的 postgres 用户 ID 一致后面用 rsync 或挂载备份目录时权限不会对不上。/opt/soft只需要 postgres 可读数据目录和归档目录需要可写。数据目录先给 700等initdb之后不要随手改成 777PostgreSQL 启动时会检查数据目录权限权限过宽会拒绝启动。安装包放/root再由 postgres 读取是典型的权限踩坑点提前避开。3. 安装 PostgreSQL 12.2 并搭起流复制从初始化到备库只读验证3.1 安装依赖包与二进制包解压路径和 environment 变量PostgreSQL 12.2 可以用源码编译也可以用二进制包解压。目标环境不能联网时先在可联网的 RHEL 环境里把 rpm 包缓存下来再拷到目标机建本地源源码编译则要提前准备 gcc、readline-devel、zlib-devel 等依赖。下面以源码安装为例安装路径固定为/usr/local/pgsql-12.2后面备库复制和 pgpool 编译都引用这个路径。# 安装编译依赖CentOS 7 示例 yum install -y gcc gcc-c make readline-devel zlib-devel openssl-devel pam-devel libxml2-devel # 解压并编译 PostgreSQL 12.2 cd /opt/soft tar -xzf postgresql-12.2.tar.gz cd postgresql-12.2 ./configure --prefix/usr/local/pgsql-12.2 --with-openssl --with-pam make -j4 make install--prefix决定安装目录后面PGHOME必须和它一致--with-openssl让数据库支持 SSL 连接--with-pam在需要系统认证时会用到。make -j4按 CPU 核数调整编译机核少就改小。安装完成后不要急着initdb先把 postgres 用户的环境变量写好否则psql、pg_ctl、pg_basebackup都要写全路径。# 切换到 postgres 用户写入环境变量 su - postgres cat ~/.bash_profile EOF export PGHOME/usr/local/pgsql-12.2 export PGDATA/data/pgdata export PATH$PGHOME/bin:$PATH export LD_LIBRARY_PATH$PGHOME/lib:$LD_LIBRARY_PATH export MANPATH$PGHOME/share/man:$MANPATH EOF source ~/.bash_profile which psql psql --versionPGHOME是安装目录PGDATA是数据目录PATH让命令直接可用LD_LIBRARY_PATH解决动态库找不到的问题。which psql必须指向/usr/local/pgsql-12.2/bin/psql如果还指向系统自带的旧版本说明 PATH 顺序不对。psql --version显示 12.2 才算环境变量生效。两台节点都要做不要只在主库配。3.2 初始化主库并调整 postgresql.confwal_level、max_wal_senders、wal_keep_segments主库初始化用initdb字符集和 locale 按业务要求选。示例用 UTF8 和 C避免不同机器 locale 不一致导致排序差异。初始化完成后先改postgresql.conf流复制相关的参数必须在这里打开尤其是wal_level、max_wal_senders、wal_keep_segments和hot_standby。# 主库执行初始化 initdb -D /data/pgdata -E UTF8 --localeC # 编辑 postgresql.conf加入流复制基础参数 cat /data/pgdata/postgresql.conf EOF listen_addresses * port 5432 wal_level replica max_wal_senders 10 wal_keep_segments 64 hot_standby on archive_mode on archive_command test ! -f /data/archive/%f cp %p /data/archive/%f EOFwal_levelreplica是流复制的最低要求12.2 还支持 logical但主从复制不需要开逻辑级别。max_wal_senders要大于可能连接的备库数量示例给 10 是留余量wal_keep_segments保留 64 个 WAL 段防止备库短暂落后时主库把日志删掉。hot_standbyon让备库可以接受只读查询这是读写分离的基础。archive_mode和archive_command是给 pg_rman 做归档备份用的归档目录必须存在且 postgres 可写。archive_command里的%f是 WAL 文件名%p是 WAL 文件路径。命令先判断目标文件是否存在再复制避免重复归档报错。这个命令在备库上通常不需要开备库只负责接收和应用 WAL。主库改完参数后先不要启动等pg_hba.conf配好再一起启动减少反复重启。3.3 pg_hba.conf 与复制用户replicator 的权限边界pg_hba.conf控制谁能连数据库、从哪个网段连、用什么认证方式。流复制需要单独放行replication连接不要图省事把host all all 0.0.0.0/0 trust写进去。示例放行 10.0.0.0/24 网段认证方式用 md5复制用户单独建replicator不给超级用户权限。cat /data/pgdata/pg_hba.conf EOF host replication replicator 10.0.0.0/24 md5 host all postgres 10.0.0.0/24 md5 host all all 10.0.0.0/24 md5 EOF # 启动主库 pg_ctl -D /data/pgdata -l /data/pgdata/startup.log start # 创建复制用户和健康检查用户 psql -d postgres -c CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD Repl_2024; psql -d postgres -c CREATE ROLE pgpool WITH LOGIN PASSWORD Pool_2024;第一行只允许replicator从 10.0.0.0/24 发起复制连接第二、三行放行普通连接。复制用户只给REPLICATION LOGIN不给SUPERUSER这样即使密码泄露影响面也可控。pgpool用户是后面 pgpool 健康检查用的不需要业务权限只要能连库和执行健康检查 SQL 即可。放行网段按实际环境改不要直接抄 0.0.0.0/0。3.4 用 pg_basebackup 拉备库12.x 的 standby.signal 与 primary_conninfo备库的基础备份用pg_basebackup-R参数在 PostgreSQL 12.2 里会自动创建standby.signal并把primary_conninfo写进postgresql.conf。这一步是 10.3 和 12.2 最大的分界10.3 的备库靠recovery.conf标识12.2 已经移除recovery.conf不要再手工创建否则备库启动时会忽略或报错。备库数据目录必须为空或者先移走旧数据。# 备库执行先确保 /data/pgdata 为空 rm -rf /data/pgdata/* chown -R postgres:postgres /data/pgdata chmod 700 /data/pgdata # 用 pg_basebackup 从主库拉取-R 自动写 standby.signal 和 primary_conninfo pg_basebackup -h 10.0.0.11 -p 5432 -U replicator -D /data/pgdata -Fp -Xs -P -R-h是主库地址-p是端口-U是复制用户-D是备库数据目录-Fp表示 plain 格式-Xs表示流式传输 WAL-P显示进度-R是关键参数。执行过程中会提示输入 replicator 密码。完成后检查/data/pgdata/standby.signal是否存在以及postgresql.conf里是否有primary_conninfo。如果没有手工补# 备库 /data/pgdata/postgresql.conf 中确认存在 primary_conninfo host10.0.0.11 port5432 userreplicator passwordRepl_2024primary_conninfo里的 host、port、user、password 必须和主库的pg_hba.conf对得上。密码直接写在配置里不算优雅后面可以用.pgpass或连接服务名但实验环境先保证能通。standby.signal是空文件存在即表示备库模式12.2 不要创建recovery.conf也不要照着 10.3 的笔记去改recovery.done。3.5 启动备库与检查 pg_stat_replication备库启动后用pg_is_in_recovery()判断是否处于只读恢复状态主库用pg_stat_replication看备库连接和 WAL 发送位置。这两个检查是流复制是否跑通的最小闭环。主库建表插入数据备库能查到说明 WAL 已经应用。# 备库启动 pg_ctl -D /data/pgdata -l /data/pgdata/startup.log start # 主库检查复制状态 psql -d postgres -c SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication; # 备库检查只读状态 psql -d postgres -c SELECT pg_is_in_recovery(); # 主库写入测试数据 psql -d postgres -c CREATE TABLE repl_test(id int primary key, note text); psql -d postgres -c INSERT INTO repl_test VALUES (1,from master); # 备库查询 psql -d postgres -c SELECT * FROM repl_test;pg_stat_replication里state为streaming表示流复制正常sent_lsn、replay_lsn能看出延迟。pg_is_in_recovery()返回t表示备库处于恢复状态可以接受只读查询。备库查询到主库插入的数据说明整个链路通了。如果备库查不到先看备库日志有没有could not connect to primary再检查primary_conninfo和pg_hba.conf。4. 安装 pgpool-II 4.1.1连接池、读写分离与 watchdog 的配置顺序4.1 解压安装与 pgpool 函数注册先装扩展再启动服务pgpool-II 4.1.1 编译时要用到 PostgreSQL 的头文件和 libpq所以--with-pgsql要指向 PostgreSQL 安装目录。装完 pgpool 后还要在主库注册pgpool_regclass、pgpool_recovery等函数否则故障切换和在线恢复会报函数不存在。顺序不能反先装 PostgreSQL再装 pgpool最后注册函数。# 安装 pgpool 编译依赖 yum install -y gcc make readline-devel zlib-devel openssl-devel pam-devel # 解压并编译 pgpool-II 4.1.1 cd /opt/soft tar -xzf pgpool-II-4.1.1.tar.gz cd pgpool-II-4.1.1 ./configure --prefix/usr/local/pgpool --with-pgsql/usr/local/pgsql-12.2 make -j4 make install # 在主库注册 pgpool 相关函数 psql -d postgres -f /opt/soft/pgpool-II-4.1.1/src/sql/pgpool-recovery/pgpool-recovery.sql psql -d postgres -f /opt/soft/pgpool-II-4.1.1/src/sql/pgpool-regclass/pgpool-regclass.sql--with-pgsql指向 PostgreSQL 安装目录不是 bin 目录如果写错编译会找不到 libpq 头文件。pgpool-recovery.sql提供恢复相关函数pgpool-regclass.sql提供表存在性判断函数故障切换脚本会用到。函数只需在主库注册备库通过流复制同步过去。注册完用\df pgpool*能看到函数列表。# 配置 pgpool 环境变量 cat /home/postgres/.bash_profile EOF export PGPool_HOME/usr/local/pgpool export PATH$PGPool_HOME/bin:$PATH EOF source /home/postgres/.bash_profile pgpool -Vpgpool -V显示 4.1.1 才算安装成功。两台节点都要装版本必须一致。pgpool 的配置文件默认在/usr/local/pgpool/etc包括pgpool.conf、pcp.conf、pool_hba.conf、pool_passwd。后面所有修改都围绕这个目录。4.2 pgpool.conf 核心参数backend_hostname、load_balance_mode、master_slave_modepgpool.conf 是主配置文件4.1.1 和 3.6.14 的参数名有差异照旧版本文档抄会漏项。核心是后端节点定义、主从模式、读写分离和健康检查。示例把主库放在 backend0备库放在 backend1master_slave_modeonmaster_slave_sub_modestreamload_balance_modeon。# /usr/local/pgpool/etc/pgpool.conf 关键片段 listen_addresses * port 9999 backend_hostname0 10.0.0.11 backend_port0 5432 backend_weight0 1 backend_flag0 ALLOW_TO_FAILOVER backend_hostname1 10.0.0.12 backend_port1 5432 backend_weight1 1 backend_flag1 ALLOW_TO_FAILOVER master_slave_mode on master_slave_sub_mode stream load_balance_mode on connection_cache on health_check_period 10 health_check_timeout 20 health_check_user pgpool health_check_password Pool_2024 health_check_database postgres failover_command /usr/local/pgpool/bin/failover.sh %d %H %P %D %m %M %u %pbackend_hostname0/1是后端 PostgreSQL 地址backend_port是数据库端口backend_weight影响读请求分配权重backend_flag允许故障切换。master_slave_modeon加master_slave_sub_modestream表示后端是流复制主备。load_balance_modeon把只读查询分散到备库。health_check_period是健康检查间隔太短会增加数据库压力太长会导致故障发现慢。health_check_user必须真实存在且能连库。failover_command里的%d是节点号%H是主机名%P是旧主库端口%D是数据目录%m是新主库节点号%M是新主库主机名。不同 pgpool 版本占位符可能略有差异4.1.1 按官方示例写不要直接套 3.6.14 的脚本。脚本路径必须可执行日志要能写否则故障切换时静默失败。4.3 pcp.conf、pool_passwd、pool_hba.conf 与 failover 文件pgpool 有三套认证文件pcp.conf管 pcp 管理命令pool_passwd管客户端到 pgpool 的密码认证pool_hba.conf管客户端连接的白名单。三套文件各管一段少配一个就会在连接或切换时报错。先生成 pcp 用户和 pool_passwd再配 pool_hba。# 生成 pcp.conf 中的用户密码用户名和密码按实际改 echo pgpool:$(pg_md5 Pcp_2024) /usr/local/pgpool/etc/pcp.conf # 生成 pool_passwd-m 表示 md5 认证 pg_md5 -m -u postgres Postgres_2024 /usr/local/pgpool/etc/pool_passwd pg_md5 -m -u pgpool Pool_2024 /usr/local/pgpool/etc/pool_passwd # pool_hba.conf 放行应用网段 cat /usr/local/pgpool/etc/pool_hba.conf EOF host all all 10.0.0.0/24 md5 EOFpcp.conf的格式是“用户名:md5 密文”pg_md5生成后直接追加。pool_passwd是 pgpool 保存数据库用户密码的文件-m表示 md5-u指定用户名。pool_hba.conf控制客户端到 pgpool 的连接pg_hba.conf控制 pgpool 到 PostgreSQL 的连接两段都要放行。只改一处就会出现“客户端连 pgpool 被拒”或“pgpool 连后端被拒”。failover 脚本不用写得太复杂但必须能改主备标识、重启备库、清理 VIP。实验环境可以先用 pgpool 自带的恢复命令生产环境再按实际路径补脚本。脚本里的日志一定要落到/var/log/pgpool/failover.log否则切换失败时没有黑匣子可查。4.4 启动 pgpool 与查看 nodes、虚拟 IPpgpool 启动前先确认 PostgreSQL 主备都在运行健康检查用户能连库。启动命令用pgpool -n前台跑一次看日志有没有报错再放后台。查看节点状态用show pool_nodes查看 VIP 用ip addr。这一步跑通才算 pgpool 层装完。# 前台启动看日志确认无报错后 CtrlC再后台启动 pgpool -n -D # 后台启动并写日志 pgpool -n -D /var/log/pgpool/pgpool.log 21 # 通过 pgpool 端口连接 psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c SELECT version(); # 查看后端节点状态 psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c SHOW pool_nodes; # 查看虚拟 IP 是否在预期节点 ip addr show | grep 10.0.0.100-n表示不要进入后台-D表示打印调试信息第一次启动用前台方式最容易看到配置错误。连接 9999 端口能返回版本说明 pgpool 转发正常。SHOW pool_nodes;里status为up表示后端节点健康role能看出 primary 和 standby。VIP 用ip addr查如果没漂移先看 watchdog 日志和wd_hostname配置。两台 pgpool 都要启动VIP 只会在其中一台。5. 避坑与排查pgpool 启动失败、all backend nodes are down 与主从切换的翻车记录5.1 no pg_hba.conf entry for hostpgpool 连接被拒现象客户端连 9999 端口时报no pg_hba.conf entry for host 10.0.0.11, user postgres, database postgres, SSL off。原因通常不是 PostgreSQL 的pg_hba.conf没配而是 pgpool 的pool_hba.conf没放行客户端网段另一种情况是 pgpool 到后端的pg_hba.conf没放行 pgpool 所在 IP。两段认证是分开的只配一段就会在另一段被拒。解决先确认客户端到 pgpool 这一段pool_hba.conf加host all all 10.0.0.0/24 md5再确认 pgpool 到 PostgreSQL 这一段pg_hba.conf加host all all 10.0.0.0/24 md5。改完pgpool reload和pg_ctl reload不要直接重启整个集群。# 检查两段认证文件 grep -v ^# /usr/local/pgpool/etc/pool_hba.conf | grep -v ^$ grep -v ^# /data/pgdata/pg_hba.conf | grep -v ^$ # 重载配置 pgpool reload pg_ctl -D /data/pgdata reload注意pool_hba.conf的认证方式要和pool_passwd对得上用了 md5 就要有对应的pool_passwd条目。pg_hba.conf的网段要包含 pgpool 服务器的地址不是客户端地址。很多人只盯着客户端 IP忘了 pgpool 自己也是一台客户端。5.2 all backend nodes are downbackend 标识与健康检查顺序现象SHOW pool_nodes;显示两个节点都是downpgpool 日志里反复出现健康检查失败。原因可能是backend_hostname写错、健康检查用户pgpool不存在、密码不对、pg_hba.conf没放行或者 pgpool 比 PostgreSQL 先启动导致首次检查失败后没有恢复。解决先手动用健康检查用户连后端确认账号密码和pg_hba.conf没问题再检查backend_hostname和端口最后用pcp_attach_node把节点挂回来。启动顺序上先起 PostgreSQL再起 pgpool。# 手动验证健康检查用户 psql -h 10.0.0.11 -U pgpool -d postgres -c SELECT 1; # 查看 pgpool 节点状态 psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c SHOW pool_nodes; # 节点恢复后重新挂载节点号按实际改 pcp_attach_node -h 10.0.0.11 -p 9898 -U pgpool -n 0 pcp_attach_node -h 10.0.0.11 -p 9898 -U pgpool -n 1pcp_attach_node的-n是节点号0 表示 backend01 表示 backend1。节点被 pgpool 标记为 down 后不会自动恢复需要手动挂载或等 failover 流程处理。健康检查用户不要用 postgres 超级用户单独建pgpool用户权限够用就行。5.3 备库起不来standby.signal 与 primary_conninfo 写错位置现象备库pg_ctl start后立即退出日志里报could not connect to primary或recovery.conf is not allowed。原因通常是 12.2 还创建了recovery.conf或者primary_conninfo写在了错误文件里也有可能是备库数据目录非空pg_basebackup拉取不完整。解决12.2 备库只认standby.signal恢复参数写在postgresql.conf。删除recovery.conf确认standby.signal存在primary_conninfo的主机、端口、用户、密码正确。数据目录重新用pg_basebackup -R拉一次比手工拼配置更稳。# 备库检查 ls -l /data/pgdata/standby.signal grep ^primary_conninfo /data/pgdata/postgresql.conf test -f /data/pgdata/recovery.conf echo recovery.conf 应删除 # 重新拉取备库 rm -rf /data/pgdata/* pg_basebackup -h 10.0.0.11 -p 5432 -U replicator -D /data/pgdata -Fp -Xs -P -R10.3 的备库用recovery.conf12.2 用standby.signal这是版本分界线。把 10.3 的配置直接搬到 12.2备库一定起不来。反过来12.2 的配置搬到 10.3也会因为找不到recovery.conf而无法进入恢复模式。5.4 watchdog 与 VIP 漂移pgpool 节点间通信没通现象pgpool 启动日志报 watchdog 相关错误VIP 不漂移主库故障后客户端连不上。原因可能是两台 pgpool 的wd_hostname写成127.0.0.1或者 9898、9999、9000、9694 端口被防火墙挡住也可能是pcp.conf两台不一致。解决wd_hostname写真实内网 IP不要写回环地址防火墙放行 pgpool 相关端口两台机器的pcp.conf、pool_passwd保持一致。改完先重启 pgpool再用pcp_watchdog_info查看 watchdog 状态。# 两台 pgpool 的 pgpool.conf 中确认 use_watchdog on wd_hostname 10.0.0.11 wd_port 9000 heartbeat_destination0 10.0.0.12 heartbeat_destination_port0 9694wd_hostname是本机地址heartbeat_destination0是另一台 pgpool 的地址。两台机器配置对称不要一台写主机名一台写 IP。VIP 漂移依赖 watchdog 选主watchdog 没通VIP 就不会动。实验环境先把端口全放行生产环境再按最小范围收口。5.5 pg_rman 备份失败归档模式与目录权限现象pg_rman backup报归档命令失败或permission denied。原因通常是archive_mode没开、archive_command目录 postgres 没写权限、pg_rman备份目录属主不对或者 PostgreSQL 主版本和 pg_rman 版本不匹配。解决确认archive_modeonarchive_command能手动执行成功归档目录和备份目录都归 postgres 所有pg_rman init用 postgres 用户执行。备份前先手动切一次 WAL确认归档文件能落盘。# 手动切换 WAL 并检查归档 psql -d postgres -c SELECT pg_switch_wal(); ls -l /data/archive # 初始化 pg_rman 备份目录 pg_rman init -B /data/pg_rman_backup -D /data/pgdata # 执行一次全量备份 pg_rman backup -B /data/pg_rman_backup -D /data/pgdata -b full pg_rman validate -B /data/pg_rman_backuppg_switch_wal()在 12.2 里可用10.3 用pg_switch_xlog()版本不同函数名不同。pg_rman init的-B是备份目录-D是数据目录两个目录都要 postgres 可写。pg_rman validate验证备份集完整性备份完不验证恢复时才发现文件缺失就晚了。6. 装完怎么验证跑通用 pgpool 连接、主从切换和 pg_rman 恢复做一轮验收装完不等于跑通我一般用三项验收收尾。第一项通过 9999 端口连 pgpool主库写入、备库直连查询确认读写分离和流复制都在工作第二项手动停主库看 pgpool 日志、SHOW pool_nodes;和 VIP 是否按预期切换第三项用 pg_rman 做一次全量备份再恢复到临时目录确认备份不是摆设。# 1. 通过 pgpool 写入并查询 psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c INSERT INTO repl_test VALUES (2,via pgpool); psql -h 10.0.0.12 -p 5432 -U postgres -d postgres -c SELECT * FROM repl_test; # 2. 停主库观察 pgpool 切换 pg_ctl -D /data/pgdata stop -m fast psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c SHOW pool_nodes; ip addr show | grep 10.0.0.100 # 3. pg_rman 备份并验证 pg_rman backup -B /data/pg_rman_backup -D /data/pgdata -b full pg_rman validate -B /data/pg_rman_backup第一项里通过 pgpool 插入的数据能在备库查到说明 pgpool 转发和流复制都正常。第二项停主库后SHOW pool_nodes;里原主库会变成 down备库提升为 primaryVIP 漂移到存活 pgpool 节点如果 VIP 不动回头查 watchdog。第三项备份完必须validate最好再恢复到临时目录启动一次确认数据文件可用。从那以后我每次装这套环境都会先把版本矩阵、standby.signal、primary_conninfo和 pgpool 的pool_hba.conf这四处写成检查项验收不通过不交付。这套 PostgreSQL 主从流复制加 pgpool 高可用方案的配置样本、切换脚本和备份脚本已经按章节顺序整理在一起需要复现时按目录取用、改掉示例 IP 和路径即可。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/27 11:29:20 · 阅读 1.2k PostgreSQL 12.2+pgpool-II 4.1.1主从高可用部署避坑
简介《Postgres主从流复制pgpool高可用方案》是一份面向PostgreSQL运维与数据库架构学习者的docx技术文档围绕主备库实时同步、故障切换、连接池与读写分离等常见高可用需求展开适合已具备基础SQL与Linux操作能力、希望系统梳理部署思路的读者参考。资源包共1个docx文件约773KB以方案说明和配置要点为主便于按章节查阅与归档。文档内容覆盖流复制的同步与异步模式、pgpool-II连接池、复制、负载均衡、读写分离及连接数限制并包含软件环境、客户环境准备、主备配置、备份和故障模拟测试等模块。当前已有470人学习可帮助读者理解Postgres高可用集群的组成、配置流程与验证方法也能为实际部署中的参数选择和排错提供参考。1. 从一个版本坑说起PostgreSQL 12.2 与 pgpool-II 4.1.1 的主从环境为什么容易装歪在 Linux 上落地 PostgreSQL 主从流复制加 pgpool 高可用方案最容易被忽略的不是 SQL而是版本分界线。PostgreSQL 10.3 的备库还认 recovery.conf到了 12.2 已经改成 standby.signalpgpool-II 3.6.14 和 4.1.1 的 pgpool.conf 参数名也有出入照着一篇旧笔记混装备库起不来、pgpool 报 all backend nodes are down排查半天才发现是配置位置写错。这套环境适合需要读写分离、连接池和故障切换的数据库场景装之前先把版本、目录和权限钉死后面才不至于反复翻车。2. 装前把环境钉死PostgreSQL 12.2、pgpool-II 4.1.1 与 pg_rman 的版本选择2.1 软件版本矩阵与两台节点的角色划分这套方案的核心组件不复杂PostgreSQL 负责数据pgpool-II 挡在数据库前面做连接池、读写分离和故障切换pg_rman 负责离线备份。真正容易乱的是版本组合。项目资料里给了两套环境旧版本是 PostgreSQL 10.3 配 pgpool-II 3.6.14新版本是 PostgreSQL 12.2 配 pgpool-II 4.1.1操作系统覆盖 CentOS 6.5 和 CentOS 7.5。新环境我一般直接选 12.2 4.1.1旧环境如果已经跑着 10.3就不要为了追新单独换 pgpool 大版本主从节点的 PostgreSQL 版本必须一致pgpool 版本也要和 PostgreSQL 主版本匹配。组件旧环境新环境角色PostgreSQL10.312.2主库、备库pgpool-II3.6.144.1.1连接池、读写分离、故障切换pg_rman与 PostgreSQL 主版本匹配与 PostgreSQL 主版本匹配离线备份与恢复操作系统CentOS 6.5CentOS 7.5运行平台示例环境用两台机器主库 10.0.0.11备库 10.0.0.12pgpool 分别装在这两台机器上虚拟 IP 用 10.0.0.100。这样不需要额外一台中间件服务器但要求两台机器的 pgpool 配置、watchdog 通信和 VIP 漂移都配通。软件包统一放在/opt/soft安装路径用/usr/local/pgsql-12.2和/usr/local/pgpool数据目录用/data/pgdata归档目录用/data/archive。目录先建出来后面所有命令都围绕这几个路径走。# 两台节点都执行建立统一的安装包和目录规划 mkdir -p /opt/soft /usr/local/pgsql-12.2 /usr/local/pgpool mkdir -p /data/pgdata /data/archive /var/log/pgpool chown -R postgres:postgres /data /var/log/pgpool chmod 700 /data/pgdata这段命令的重点不是创建目录而是把权限先归到 postgres 用户。数据目录权限给 700归档目录和日志目录也要让 postgres 可写。很多人把安装包放在/root下再用 postgres 去解压结果权限不够这不是玄学是/root默认 700 导致的。安装包统一放/opt/soft后面切换用户执行解压和编译都省事。2.2 系统资源与内核参数关闭防火墙、SELinux、配置 hosts 和 sysctl目标环境如果是 RHEL 或 CentOS安装前先处理防火墙、SELinux、hosts、时钟同步和内核参数。生产环境可以不关防火墙但必须放行 PostgreSQL 的 5432、pgpool 的 9999、pcp 的 9898 和 watchdog 的 9000/9694 等端口实验环境为了减少变量常见做法是先关掉 firewalld 和 SELinux配通后再按最小端口策略放开。不要只关服务不关开机自启重启后防火墙又回来pgpool 的 VIP 漂移会直接失效。# 实验环境关闭防火墙和 SELinux生产环境改为放行端口 systemctl stop firewalld systemctl disable firewalld setenforce 0 sed -i s/^SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config # 两台节点写入 hosts主机名和 IP 要能互相解析 cat /etc/hosts EOF 10.0.0.11 pg-master 10.0.0.12 pg-standby EOF # 时钟同步CentOS 7 用 chronydCentOS 6 用 ntpd systemctl enable chronyd systemctl start chronyd chronyc sourceshosts 里写的是主机名和内网 IP后面primary_conninfo、backend_hostname、wd_hostname都可以用主机名减少换环境时改配置的工作量。时钟同步必须做流复制本身不依赖时钟但 pgpool 的健康检查、日志排序和故障切换判断都怕两台机器时间差太大。chronyc sources能看到同步源才算完成不要只看服务状态。内核参数按 PostgreSQL 的常规做法调整。共享内存、文件句柄、进程数不够时initdb或高并发连接阶段就会报错。下面这组值适合示例环境内存更大的机器可以把kernel.shmmax和kernel.shmall继续往上调。cat /etc/sysctl.conf EOF kernel.shmmax 68719476736 kernel.shmall 16777216 vm.swappiness 10 vm.overcommit_memory 2 fs.file-max 655360 EOF sysctl -p cat /etc/security/limits.conf EOF postgres soft nproc 65536 postgres hard nproc 65536 postgres soft nofile 65536 postgres hard nofile 65536 EOFkernel.shmmax控制单个共享内存段的最大值kernel.shmall控制共享内存总页数vm.overcommit_memory2表示不允许过量分配配合 PostgreSQL 的共享内存更稳。limits.conf里的 nproc 和 nofile 是给 postgres 用户的改完要重新登录 postgres 才生效ulimit -u和ulimit -n能看到结果才算数。装库前这些参数不调后面出现“could not resize shared memory segment”再回头查成本会高很多。2.3 创建 postgres 用户、目录与权限别把安装包放在 /root 下PostgreSQL 不允许用 root 用户初始化数据库所以要先建 postgres 用户。用户 ID 不强制固定但两台节点最好保持一致后面挂载目录、备份恢复和 pgpool 调用脚本时少一层权限麻烦。密码要设置但不要用简单口令复制用户和 postgres 管理用户分开后面 pg_hba.conf 才能按权限收口。# 两台节点都执行 groupadd -g 1001 postgres useradd -u 1001 -g postgres -m -s /bin/bash postgres passwd postgres # 把安装包放到 /opt/soft并交给 postgres 可读 chown -R postgres:postgres /opt/soft ls -ld /opt/soft /data/pgdata /data/archive-u 1001 -g 1001是为了让两台机器的 postgres 用户 ID 一致后面用 rsync 或挂载备份目录时权限不会对不上。/opt/soft只需要 postgres 可读数据目录和归档目录需要可写。数据目录先给 700等initdb之后不要随手改成 777PostgreSQL 启动时会检查数据目录权限权限过宽会拒绝启动。安装包放/root再由 postgres 读取是典型的权限踩坑点提前避开。3. 安装 PostgreSQL 12.2 并搭起流复制从初始化到备库只读验证3.1 安装依赖包与二进制包解压路径和 environment 变量PostgreSQL 12.2 可以用源码编译也可以用二进制包解压。目标环境不能联网时先在可联网的 RHEL 环境里把 rpm 包缓存下来再拷到目标机建本地源源码编译则要提前准备 gcc、readline-devel、zlib-devel 等依赖。下面以源码安装为例安装路径固定为/usr/local/pgsql-12.2后面备库复制和 pgpool 编译都引用这个路径。# 安装编译依赖CentOS 7 示例 yum install -y gcc gcc-c make readline-devel zlib-devel openssl-devel pam-devel libxml2-devel # 解压并编译 PostgreSQL 12.2 cd /opt/soft tar -xzf postgresql-12.2.tar.gz cd postgresql-12.2 ./configure --prefix/usr/local/pgsql-12.2 --with-openssl --with-pam make -j4 make install--prefix决定安装目录后面PGHOME必须和它一致--with-openssl让数据库支持 SSL 连接--with-pam在需要系统认证时会用到。make -j4按 CPU 核数调整编译机核少就改小。安装完成后不要急着initdb先把 postgres 用户的环境变量写好否则psql、pg_ctl、pg_basebackup都要写全路径。# 切换到 postgres 用户写入环境变量 su - postgres cat ~/.bash_profile EOF export PGHOME/usr/local/pgsql-12.2 export PGDATA/data/pgdata export PATH$PGHOME/bin:$PATH export LD_LIBRARY_PATH$PGHOME/lib:$LD_LIBRARY_PATH export MANPATH$PGHOME/share/man:$MANPATH EOF source ~/.bash_profile which psql psql --versionPGHOME是安装目录PGDATA是数据目录PATH让命令直接可用LD_LIBRARY_PATH解决动态库找不到的问题。which psql必须指向/usr/local/pgsql-12.2/bin/psql如果还指向系统自带的旧版本说明 PATH 顺序不对。psql --version显示 12.2 才算环境变量生效。两台节点都要做不要只在主库配。3.2 初始化主库并调整 postgresql.confwal_level、max_wal_senders、wal_keep_segments主库初始化用initdb字符集和 locale 按业务要求选。示例用 UTF8 和 C避免不同机器 locale 不一致导致排序差异。初始化完成后先改postgresql.conf流复制相关的参数必须在这里打开尤其是wal_level、max_wal_senders、wal_keep_segments和hot_standby。# 主库执行初始化 initdb -D /data/pgdata -E UTF8 --localeC # 编辑 postgresql.conf加入流复制基础参数 cat /data/pgdata/postgresql.conf EOF listen_addresses * port 5432 wal_level replica max_wal_senders 10 wal_keep_segments 64 hot_standby on archive_mode on archive_command test ! -f /data/archive/%f cp %p /data/archive/%f EOFwal_levelreplica是流复制的最低要求12.2 还支持 logical但主从复制不需要开逻辑级别。max_wal_senders要大于可能连接的备库数量示例给 10 是留余量wal_keep_segments保留 64 个 WAL 段防止备库短暂落后时主库把日志删掉。hot_standbyon让备库可以接受只读查询这是读写分离的基础。archive_mode和archive_command是给 pg_rman 做归档备份用的归档目录必须存在且 postgres 可写。archive_command里的%f是 WAL 文件名%p是 WAL 文件路径。命令先判断目标文件是否存在再复制避免重复归档报错。这个命令在备库上通常不需要开备库只负责接收和应用 WAL。主库改完参数后先不要启动等pg_hba.conf配好再一起启动减少反复重启。3.3 pg_hba.conf 与复制用户replicator 的权限边界pg_hba.conf控制谁能连数据库、从哪个网段连、用什么认证方式。流复制需要单独放行replication连接不要图省事把host all all 0.0.0.0/0 trust写进去。示例放行 10.0.0.0/24 网段认证方式用 md5复制用户单独建replicator不给超级用户权限。cat /data/pgdata/pg_hba.conf EOF host replication replicator 10.0.0.0/24 md5 host all postgres 10.0.0.0/24 md5 host all all 10.0.0.0/24 md5 EOF # 启动主库 pg_ctl -D /data/pgdata -l /data/pgdata/startup.log start # 创建复制用户和健康检查用户 psql -d postgres -c CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD Repl_2024; psql -d postgres -c CREATE ROLE pgpool WITH LOGIN PASSWORD Pool_2024;第一行只允许replicator从 10.0.0.0/24 发起复制连接第二、三行放行普通连接。复制用户只给REPLICATION LOGIN不给SUPERUSER这样即使密码泄露影响面也可控。pgpool用户是后面 pgpool 健康检查用的不需要业务权限只要能连库和执行健康检查 SQL 即可。放行网段按实际环境改不要直接抄 0.0.0.0/0。3.4 用 pg_basebackup 拉备库12.x 的 standby.signal 与 primary_conninfo备库的基础备份用pg_basebackup-R参数在 PostgreSQL 12.2 里会自动创建standby.signal并把primary_conninfo写进postgresql.conf。这一步是 10.3 和 12.2 最大的分界10.3 的备库靠recovery.conf标识12.2 已经移除recovery.conf不要再手工创建否则备库启动时会忽略或报错。备库数据目录必须为空或者先移走旧数据。# 备库执行先确保 /data/pgdata 为空 rm -rf /data/pgdata/* chown -R postgres:postgres /data/pgdata chmod 700 /data/pgdata # 用 pg_basebackup 从主库拉取-R 自动写 standby.signal 和 primary_conninfo pg_basebackup -h 10.0.0.11 -p 5432 -U replicator -D /data/pgdata -Fp -Xs -P -R-h是主库地址-p是端口-U是复制用户-D是备库数据目录-Fp表示 plain 格式-Xs表示流式传输 WAL-P显示进度-R是关键参数。执行过程中会提示输入 replicator 密码。完成后检查/data/pgdata/standby.signal是否存在以及postgresql.conf里是否有primary_conninfo。如果没有手工补# 备库 /data/pgdata/postgresql.conf 中确认存在 primary_conninfo host10.0.0.11 port5432 userreplicator passwordRepl_2024primary_conninfo里的 host、port、user、password 必须和主库的pg_hba.conf对得上。密码直接写在配置里不算优雅后面可以用.pgpass或连接服务名但实验环境先保证能通。standby.signal是空文件存在即表示备库模式12.2 不要创建recovery.conf也不要照着 10.3 的笔记去改recovery.done。3.5 启动备库与检查 pg_stat_replication备库启动后用pg_is_in_recovery()判断是否处于只读恢复状态主库用pg_stat_replication看备库连接和 WAL 发送位置。这两个检查是流复制是否跑通的最小闭环。主库建表插入数据备库能查到说明 WAL 已经应用。# 备库启动 pg_ctl -D /data/pgdata -l /data/pgdata/startup.log start # 主库检查复制状态 psql -d postgres -c SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication; # 备库检查只读状态 psql -d postgres -c SELECT pg_is_in_recovery(); # 主库写入测试数据 psql -d postgres -c CREATE TABLE repl_test(id int primary key, note text); psql -d postgres -c INSERT INTO repl_test VALUES (1,from master); # 备库查询 psql -d postgres -c SELECT * FROM repl_test;pg_stat_replication里state为streaming表示流复制正常sent_lsn、replay_lsn能看出延迟。pg_is_in_recovery()返回t表示备库处于恢复状态可以接受只读查询。备库查询到主库插入的数据说明整个链路通了。如果备库查不到先看备库日志有没有could not connect to primary再检查primary_conninfo和pg_hba.conf。4. 安装 pgpool-II 4.1.1连接池、读写分离与 watchdog 的配置顺序4.1 解压安装与 pgpool 函数注册先装扩展再启动服务pgpool-II 4.1.1 编译时要用到 PostgreSQL 的头文件和 libpq所以--with-pgsql要指向 PostgreSQL 安装目录。装完 pgpool 后还要在主库注册pgpool_regclass、pgpool_recovery等函数否则故障切换和在线恢复会报函数不存在。顺序不能反先装 PostgreSQL再装 pgpool最后注册函数。# 安装 pgpool 编译依赖 yum install -y gcc make readline-devel zlib-devel openssl-devel pam-devel # 解压并编译 pgpool-II 4.1.1 cd /opt/soft tar -xzf pgpool-II-4.1.1.tar.gz cd pgpool-II-4.1.1 ./configure --prefix/usr/local/pgpool --with-pgsql/usr/local/pgsql-12.2 make -j4 make install # 在主库注册 pgpool 相关函数 psql -d postgres -f /opt/soft/pgpool-II-4.1.1/src/sql/pgpool-recovery/pgpool-recovery.sql psql -d postgres -f /opt/soft/pgpool-II-4.1.1/src/sql/pgpool-regclass/pgpool-regclass.sql--with-pgsql指向 PostgreSQL 安装目录不是 bin 目录如果写错编译会找不到 libpq 头文件。pgpool-recovery.sql提供恢复相关函数pgpool-regclass.sql提供表存在性判断函数故障切换脚本会用到。函数只需在主库注册备库通过流复制同步过去。注册完用\df pgpool*能看到函数列表。# 配置 pgpool 环境变量 cat /home/postgres/.bash_profile EOF export PGPool_HOME/usr/local/pgpool export PATH$PGPool_HOME/bin:$PATH EOF source /home/postgres/.bash_profile pgpool -Vpgpool -V显示 4.1.1 才算安装成功。两台节点都要装版本必须一致。pgpool 的配置文件默认在/usr/local/pgpool/etc包括pgpool.conf、pcp.conf、pool_hba.conf、pool_passwd。后面所有修改都围绕这个目录。4.2 pgpool.conf 核心参数backend_hostname、load_balance_mode、master_slave_modepgpool.conf 是主配置文件4.1.1 和 3.6.14 的参数名有差异照旧版本文档抄会漏项。核心是后端节点定义、主从模式、读写分离和健康检查。示例把主库放在 backend0备库放在 backend1master_slave_modeonmaster_slave_sub_modestreamload_balance_modeon。# /usr/local/pgpool/etc/pgpool.conf 关键片段 listen_addresses * port 9999 backend_hostname0 10.0.0.11 backend_port0 5432 backend_weight0 1 backend_flag0 ALLOW_TO_FAILOVER backend_hostname1 10.0.0.12 backend_port1 5432 backend_weight1 1 backend_flag1 ALLOW_TO_FAILOVER master_slave_mode on master_slave_sub_mode stream load_balance_mode on connection_cache on health_check_period 10 health_check_timeout 20 health_check_user pgpool health_check_password Pool_2024 health_check_database postgres failover_command /usr/local/pgpool/bin/failover.sh %d %H %P %D %m %M %u %pbackend_hostname0/1是后端 PostgreSQL 地址backend_port是数据库端口backend_weight影响读请求分配权重backend_flag允许故障切换。master_slave_modeon加master_slave_sub_modestream表示后端是流复制主备。load_balance_modeon把只读查询分散到备库。health_check_period是健康检查间隔太短会增加数据库压力太长会导致故障发现慢。health_check_user必须真实存在且能连库。failover_command里的%d是节点号%H是主机名%P是旧主库端口%D是数据目录%m是新主库节点号%M是新主库主机名。不同 pgpool 版本占位符可能略有差异4.1.1 按官方示例写不要直接套 3.6.14 的脚本。脚本路径必须可执行日志要能写否则故障切换时静默失败。4.3 pcp.conf、pool_passwd、pool_hba.conf 与 failover 文件pgpool 有三套认证文件pcp.conf管 pcp 管理命令pool_passwd管客户端到 pgpool 的密码认证pool_hba.conf管客户端连接的白名单。三套文件各管一段少配一个就会在连接或切换时报错。先生成 pcp 用户和 pool_passwd再配 pool_hba。# 生成 pcp.conf 中的用户密码用户名和密码按实际改 echo pgpool:$(pg_md5 Pcp_2024) /usr/local/pgpool/etc/pcp.conf # 生成 pool_passwd-m 表示 md5 认证 pg_md5 -m -u postgres Postgres_2024 /usr/local/pgpool/etc/pool_passwd pg_md5 -m -u pgpool Pool_2024 /usr/local/pgpool/etc/pool_passwd # pool_hba.conf 放行应用网段 cat /usr/local/pgpool/etc/pool_hba.conf EOF host all all 10.0.0.0/24 md5 EOFpcp.conf的格式是“用户名:md5 密文”pg_md5生成后直接追加。pool_passwd是 pgpool 保存数据库用户密码的文件-m表示 md5-u指定用户名。pool_hba.conf控制客户端到 pgpool 的连接pg_hba.conf控制 pgpool 到 PostgreSQL 的连接两段都要放行。只改一处就会出现“客户端连 pgpool 被拒”或“pgpool 连后端被拒”。failover 脚本不用写得太复杂但必须能改主备标识、重启备库、清理 VIP。实验环境可以先用 pgpool 自带的恢复命令生产环境再按实际路径补脚本。脚本里的日志一定要落到/var/log/pgpool/failover.log否则切换失败时没有黑匣子可查。4.4 启动 pgpool 与查看 nodes、虚拟 IPpgpool 启动前先确认 PostgreSQL 主备都在运行健康检查用户能连库。启动命令用pgpool -n前台跑一次看日志有没有报错再放后台。查看节点状态用show pool_nodes查看 VIP 用ip addr。这一步跑通才算 pgpool 层装完。# 前台启动看日志确认无报错后 CtrlC再后台启动 pgpool -n -D # 后台启动并写日志 pgpool -n -D /var/log/pgpool/pgpool.log 21 # 通过 pgpool 端口连接 psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c SELECT version(); # 查看后端节点状态 psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c SHOW pool_nodes; # 查看虚拟 IP 是否在预期节点 ip addr show | grep 10.0.0.100-n表示不要进入后台-D表示打印调试信息第一次启动用前台方式最容易看到配置错误。连接 9999 端口能返回版本说明 pgpool 转发正常。SHOW pool_nodes;里status为up表示后端节点健康role能看出 primary 和 standby。VIP 用ip addr查如果没漂移先看 watchdog 日志和wd_hostname配置。两台 pgpool 都要启动VIP 只会在其中一台。5. 避坑与排查pgpool 启动失败、all backend nodes are down 与主从切换的翻车记录5.1 no pg_hba.conf entry for hostpgpool 连接被拒现象客户端连 9999 端口时报no pg_hba.conf entry for host 10.0.0.11, user postgres, database postgres, SSL off。原因通常不是 PostgreSQL 的pg_hba.conf没配而是 pgpool 的pool_hba.conf没放行客户端网段另一种情况是 pgpool 到后端的pg_hba.conf没放行 pgpool 所在 IP。两段认证是分开的只配一段就会在另一段被拒。解决先确认客户端到 pgpool 这一段pool_hba.conf加host all all 10.0.0.0/24 md5再确认 pgpool 到 PostgreSQL 这一段pg_hba.conf加host all all 10.0.0.0/24 md5。改完pgpool reload和pg_ctl reload不要直接重启整个集群。# 检查两段认证文件 grep -v ^# /usr/local/pgpool/etc/pool_hba.conf | grep -v ^$ grep -v ^# /data/pgdata/pg_hba.conf | grep -v ^$ # 重载配置 pgpool reload pg_ctl -D /data/pgdata reload注意pool_hba.conf的认证方式要和pool_passwd对得上用了 md5 就要有对应的pool_passwd条目。pg_hba.conf的网段要包含 pgpool 服务器的地址不是客户端地址。很多人只盯着客户端 IP忘了 pgpool 自己也是一台客户端。5.2 all backend nodes are downbackend 标识与健康检查顺序现象SHOW pool_nodes;显示两个节点都是downpgpool 日志里反复出现健康检查失败。原因可能是backend_hostname写错、健康检查用户pgpool不存在、密码不对、pg_hba.conf没放行或者 pgpool 比 PostgreSQL 先启动导致首次检查失败后没有恢复。解决先手动用健康检查用户连后端确认账号密码和pg_hba.conf没问题再检查backend_hostname和端口最后用pcp_attach_node把节点挂回来。启动顺序上先起 PostgreSQL再起 pgpool。# 手动验证健康检查用户 psql -h 10.0.0.11 -U pgpool -d postgres -c SELECT 1; # 查看 pgpool 节点状态 psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c SHOW pool_nodes; # 节点恢复后重新挂载节点号按实际改 pcp_attach_node -h 10.0.0.11 -p 9898 -U pgpool -n 0 pcp_attach_node -h 10.0.0.11 -p 9898 -U pgpool -n 1pcp_attach_node的-n是节点号0 表示 backend01 表示 backend1。节点被 pgpool 标记为 down 后不会自动恢复需要手动挂载或等 failover 流程处理。健康检查用户不要用 postgres 超级用户单独建pgpool用户权限够用就行。5.3 备库起不来standby.signal 与 primary_conninfo 写错位置现象备库pg_ctl start后立即退出日志里报could not connect to primary或recovery.conf is not allowed。原因通常是 12.2 还创建了recovery.conf或者primary_conninfo写在了错误文件里也有可能是备库数据目录非空pg_basebackup拉取不完整。解决12.2 备库只认standby.signal恢复参数写在postgresql.conf。删除recovery.conf确认standby.signal存在primary_conninfo的主机、端口、用户、密码正确。数据目录重新用pg_basebackup -R拉一次比手工拼配置更稳。# 备库检查 ls -l /data/pgdata/standby.signal grep ^primary_conninfo /data/pgdata/postgresql.conf test -f /data/pgdata/recovery.conf echo recovery.conf 应删除 # 重新拉取备库 rm -rf /data/pgdata/* pg_basebackup -h 10.0.0.11 -p 5432 -U replicator -D /data/pgdata -Fp -Xs -P -R10.3 的备库用recovery.conf12.2 用standby.signal这是版本分界线。把 10.3 的配置直接搬到 12.2备库一定起不来。反过来12.2 的配置搬到 10.3也会因为找不到recovery.conf而无法进入恢复模式。5.4 watchdog 与 VIP 漂移pgpool 节点间通信没通现象pgpool 启动日志报 watchdog 相关错误VIP 不漂移主库故障后客户端连不上。原因可能是两台 pgpool 的wd_hostname写成127.0.0.1或者 9898、9999、9000、9694 端口被防火墙挡住也可能是pcp.conf两台不一致。解决wd_hostname写真实内网 IP不要写回环地址防火墙放行 pgpool 相关端口两台机器的pcp.conf、pool_passwd保持一致。改完先重启 pgpool再用pcp_watchdog_info查看 watchdog 状态。# 两台 pgpool 的 pgpool.conf 中确认 use_watchdog on wd_hostname 10.0.0.11 wd_port 9000 heartbeat_destination0 10.0.0.12 heartbeat_destination_port0 9694wd_hostname是本机地址heartbeat_destination0是另一台 pgpool 的地址。两台机器配置对称不要一台写主机名一台写 IP。VIP 漂移依赖 watchdog 选主watchdog 没通VIP 就不会动。实验环境先把端口全放行生产环境再按最小范围收口。5.5 pg_rman 备份失败归档模式与目录权限现象pg_rman backup报归档命令失败或permission denied。原因通常是archive_mode没开、archive_command目录 postgres 没写权限、pg_rman备份目录属主不对或者 PostgreSQL 主版本和 pg_rman 版本不匹配。解决确认archive_modeonarchive_command能手动执行成功归档目录和备份目录都归 postgres 所有pg_rman init用 postgres 用户执行。备份前先手动切一次 WAL确认归档文件能落盘。# 手动切换 WAL 并检查归档 psql -d postgres -c SELECT pg_switch_wal(); ls -l /data/archive # 初始化 pg_rman 备份目录 pg_rman init -B /data/pg_rman_backup -D /data/pgdata # 执行一次全量备份 pg_rman backup -B /data/pg_rman_backup -D /data/pgdata -b full pg_rman validate -B /data/pg_rman_backuppg_switch_wal()在 12.2 里可用10.3 用pg_switch_xlog()版本不同函数名不同。pg_rman init的-B是备份目录-D是数据目录两个目录都要 postgres 可写。pg_rman validate验证备份集完整性备份完不验证恢复时才发现文件缺失就晚了。6. 装完怎么验证跑通用 pgpool 连接、主从切换和 pg_rman 恢复做一轮验收装完不等于跑通我一般用三项验收收尾。第一项通过 9999 端口连 pgpool主库写入、备库直连查询确认读写分离和流复制都在工作第二项手动停主库看 pgpool 日志、SHOW pool_nodes;和 VIP 是否按预期切换第三项用 pg_rman 做一次全量备份再恢复到临时目录确认备份不是摆设。# 1. 通过 pgpool 写入并查询 psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c INSERT INTO repl_test VALUES (2,via pgpool); psql -h 10.0.0.12 -p 5432 -U postgres -d postgres -c SELECT * FROM repl_test; # 2. 停主库观察 pgpool 切换 pg_ctl -D /data/pgdata stop -m fast psql -h 10.0.0.11 -p 9999 -U postgres -d postgres -c SHOW pool_nodes; ip addr show | grep 10.0.0.100 # 3. pg_rman 备份并验证 pg_rman backup -B /data/pg_rman_backup -D /data/pgdata -b full pg_rman validate -B /data/pg_rman_backup第一项里通过 pgpool 插入的数据能在备库查到说明 pgpool 转发和流复制都正常。第二项停主库后SHOW pool_nodes;里原主库会变成 down备库提升为 primaryVIP 漂移到存活 pgpool 节点如果 VIP 不动回头查 watchdog。第三项备份完必须validate最好再恢复到临时目录启动一次确认数据文件可用。从那以后我每次装这套环境都会先把版本矩阵、standby.signal、primary_conninfo和 pgpool 的pool_hba.conf这四处写成检查项验收不通过不交付。这套 PostgreSQL 主从流复制加 pgpool 高可用方案的配置样本、切换脚本和备份脚本已经按章节顺序整理在一起需要复现时按目录取用、改掉示例 IP 和路径即可。希望帮到你。本文还有配套的精品资源点击获取