Patroni PostgreSQL架构下DC-DR同步故障求助

问题背景 #

DC侧部署3节点Patroni PostgreSQL集群，DR侧为3节点standby_cluster，原本通过DR的Patroni配置指定DC主节点IP实现实时同步。但DC集群内部切换主节点后，DR主节点无法连接原DC主节点，报错：

FATAL: could not connect to the primary server: could not make a writable connection to server "13.233.76.9:5432"

参考信息 #

pg_hba.conf配置 #

TYPE DATABASE USER ADDRESS METHOD

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust

local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust

host replication replicator 127.0.0.1/32 md5
host replication replicator 172.31.4.196/32 scram-sha-256
host replication replicator 172.31.2.237/32 scram-sha-256
host replication replicator 172.31.2.83/32 scram-sha-256
host replication replicator 172.31.45.26/32 scram-sha-256
host replication replicator 172.31.43.207/32 scram-sha-256
host replication replicator 172.31.42.188/32 scram-sha-256
host replication replicator 13.230.225.219/32 trust
host replication replicator 13.200.182.158/32 trust
host replication replicator 13.112.25.208/32 trust
host replication replicator 0.0.0.0/32 trust

节点IP #

• DC原主节点：13.233.76.9
• DC新主节点：52.67.253.203
• DR主节点：13.230.225.219

故障原因 #

1. DR配置硬编码DC主节点IP：DR集群未配置动态主节点发现，DC主节点切换后仍尝试连接旧主节点，而旧主节点已转为只读备节点，无法提供可写的复制连接。
2. pg_hba权限缺失：DC新主节点52.67.253.203未在replicator用户的允许列表中，即使DR切换到新主节点，也可能因权限被拒。

解决步骤 #

1. 修复DR侧Patroni配置，实现动态主节点发现 #

放弃硬编码单个IP，改为通过DC集群的服务发现域名/负载均衡地址连接：
编辑DR节点的patroni.yml，修改standby_cluster配置：

standby_cluster:
  host: dc-patroni-service.example.com  # DC集群的负载均衡IP或服务发现域名
  port: 5432
  primary_conninfo: 'user=replicator password=your_replication_password sslmode=prefer'

若使用etcd/consul作为DCS，可直接配置DR集群从DCS获取DC主节点信息，无需手动指定地址。

2. 更新DC集群的pg_hba.conf #

在DC所有节点的pg_hba.conf中添加DC新主节点对DR节点的复制权限（或确保DR网段被允许）：

# 允许DR节点连接DC新主节点进行复制
host replication replicator 52.67.253.203/32 scram-sha-256

执行重载命令使配置生效：

patronictl reload dc-cluster-name

3. 手动切换DR集群到DC新主节点 #

• 终止DR节点上的旧复制进程：

  psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE application_name='walreceiver';"
  

• 修改DR的复制连接配置（PostgreSQL 12+在postgresql.conf，低版本在recovery.conf）：

  primary_conninfo = 'host=52.67.253.203 port=5432 user=replicator password=your_replication_password sslmode=prefer'
  

• 重载DR的Patroni配置：

  patronictl reload dr-cluster-name
  

• 验证同步状态：

  psql -U postgres -c "SELECT pg_is_in_recovery(), sync_state FROM pg_stat_replication;"
  

4. 长期优化方案 #

• 为DC集群配置VIP或负载均衡服务，DR始终连接VIP，主节点切换后VIP自动漂移。
• 配置Patroni跨集群自动故障转移，通过DCS实现DR自动感知DC主节点变化。

内容的提问来源于stack exchange，提问作者Rahi Shah