PostgreSQL 15中repmgr自动故障转移后旧主节点重加入备节点咨询

我用PostgreSQL 15搭配repmgr搭建了1主2备的自动故障转移集群，repmgrd负责自动故障转移：主节点故障时，备节点会被选举为新主。但旧主节点恢复上线后出现脑裂，无法正常归队，想知道怎么把旧主重新加为备节点。另外我的配置未启用WAL_LOG_Hints。

故障前集群状态 #

NOTICE: using provided configuration file "/etc/repmgr/15/repmgr.conf"
INFO: connecting to database
 ID | Name   | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                                  
----+--------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------------------
 1  | Master | primary | * running |          | default  | 100      | 6        | user=repmgr password=password  host=ip1 dbname=repmgr port=5432 connect_timeout=2
 2  | slaveA | standby |   running | Master   | default  | 100      | 6        | user=repmgr password=password  host=ip2 dbname=repmgr port=5432 connect_timeout=2
 3  | slaveB | standby |   running | Master   | default  | 100      | 6        | user=repmgr password=password  host=ip3 dbname=repmgr port=5432 connect_timeout=2

故障后集群状态 #

NOTICE: using provided configuration file "/etc/repmgr/15/repmgr.conf"
INFO: connecting to database
ERROR: connection to database failed
DETAIL:
connection to server at "ip1", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

DETAIL: attempted to connect using:
  user=repmgr password=password connect_timeout=2 dbname=repmgr host=ip1 port=5432 fallback_application_name=repmgr options=-csearch_path=
WARNING: following issues were detected
  - when attempting to connect to node "Master" (ID: 1), following error encountered :
"connection to server at "ip1", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?"

 ID | Name   | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string

----+--------+---------+-----------+----------+----------+----------+----------+------------------------------------------------------------------------------------
--------
 1  | Master | primary | - failed  | ?        | default  | 100      |          | user=repmgr password=  host=ip1 dbname=repmgr port=5432 connect_ti
meout=2
 2  | slaveA | primary | * running |          | default  | 100      | 7        | user=repmgr password=host=ip2 dbname=repmgr port=5432 connect_ti
meout=2
 3  | slaveB | standby |   running | slaveA   | default  | 100      | 7        | user=repmgr password=host=ip3 dbname=repmgr port=5432 connect_ti
meout=2

后续异常状态 #

将备节点提升为主节点后，我成功将其切换为主节点；随后停止当前主节点（原备节点），旧主节点恢复为正常主节点状态，但重启原备节点后出现以下异常：

NOTICE: using provided configuration file "/etc/repmgr/15/repmgr.conf"
INFO: connecting to database
ERROR: connection to database failed
DETAIL:
connection to server at "ip3", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

DETAIL: attempted to connect using:
  user=repmgr password=password connect_timeout=2 dbname=repmgr host=ip3 port=5432 fallback_application_name=repmgr options=-csearch_path=
WARNING: following issues were detected
  - node "slaveA" (ID: 2) is registered as standby but running as primary
  - when attempting to connect to node "slaveB" (ID: 3), following error encountered :
"connection to server at "ip3", port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?"
  - node "slaveB" (ID: 3) is registered as an active standby but is unreachable

 ID | Name   | Role    | Status               | Upstream | Location | Priority | Timeline | Connection string

----+--------+---------+----------------------+----------+----------+----------+----------+-------------------------------------------------------------------------
-------------------
 1  | Master | primary | * running            |          | default  | 100      | 6        | user=repmgr password=password host=ip1 dbname=repmgr port=5432
 connect_timeout=2
 2  | slaveA | standby | ! running as primary |          | default  | 100      | 7        | user=repmgr password=password host=ip2 dbname=repmgr port=5432
 connect_timeout=2
 3  | slaveB | standby | ? unreachable        | ? Master | default  | 100      |          | user=repmgr password=password host=ip3 dbname=repmgr port=5432
 connect_timeout=2

1. 确认并锁定合法主节点 #

从异常状态可以看到，slaveA（ID2）的时间线为7，旧主（ID1）为6，slaveA是当前合法主节点。先停止旧主的PostgreSQL服务，避免脑裂持续：

sudo systemctl stop postgresql-15

2. 备份旧主节点配置文件 #

将旧主的核心配置文件备份到临时目录，防止克隆时被覆盖：

cp /var/lib/pgsql/15/data/postgresql.conf /tmp/
cp /var/lib/pgsql/15/data/pg_hba.conf /tmp/
cp /etc/repmgr/15/repmgr.conf /tmp/

3. 清空旧主数据目录（全量克隆准备） #

由于未启用WAL_LOG_HINTS，无法使用增量同步，必须全量克隆新主数据：

rm -rf /var/lib/pgsql/15/data/*

4. 从新主节点克隆数据 #

在旧主节点执行repmgr克隆命令，指定新主节点（slaveA，对应ip2）：

repmgr -h ip2 -U repmgr -d repmgr standby clone --force

--force参数用于强制覆盖目录内的残留文件。

5. 恢复配置文件 #

将之前备份的配置文件复制回原目录，注意检查repmgr.conf中的node_id和node_name是否保持原旧主的配置（ID1、Master）：

cp /tmp/postgresql.conf /var/lib/pgsql/15/data/
cp /tmp/pg_hba.conf /var/lib/pgsql/15/data/
cp /tmp/repmgr.conf /etc/repmgr/15/

6. 启动旧主并注册为备节点 #

启动旧主的PostgreSQL服务：

sudo systemctl start postgresql-15

执行注册命令覆盖原故障节点记录：

repmgr standby register --force

7. 验证集群状态 #

在任意节点执行以下命令，确认旧主已转为standby，上游指向slaveA：

repmgr cluster show

额外注意事项 #

• 脑裂期间必须确保只有一个主节点运行，禁止同时启动两个主节点，否则会导致数据永久不一致。
• 建议启用WAL_LOG_HINTS，后续故障恢复时可使用增量同步，减少数据克隆的时间和带宽消耗。
• 检查所有节点的repmgrd服务状态，确保自动故障转移功能正常运行：

sudo systemctl status repmgrd-15

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