如何查询Postgres逻辑复制下的表、数据库及主节点切换后复制变更

一、查询集群内处于逻辑复制状态的数据库与表 #

因为逻辑复制的配置信息是按节点本地独立存储的，不存在集群全局统一的元数据视图，所以需要逐台节点执行查询，不能仅依赖单节点结果判断全集群状态。

1. 定位存在逻辑复制关联的数据库 #

在任意节点的任意可连接数据库上执行以下SQL，即可拿到当前实例上所有关联逻辑复制的数据库清单：

-- 全实例视角查询所有逻辑复制槽关联的数据库
SELECT DISTINCT 
  database AS 所属数据库,
  slot_name AS 逻辑复制槽名称,
  slot_type AS 复制类型
FROM pg_replication_slots 
WHERE slot_type = 'logical';

执行后返回的database字段值，就是当前节点上承载了逻辑复制发布、订阅、逻辑槽的数据库。3节点集群需要逐台执行该查询，避免单节点结果偏差。

2. 查询纳入逻辑复制范围的具体表 #

切到上一步定位到的、存在逻辑复制的数据库内，分别执行以下查询获取表级的复制配置：

（1）查询发布端纳入复制范围的表

-- 当前库视角查询发布配置与关联表
SELECT 
  p.pubname AS 逻辑发布名称,
  p.puballtables AS 是否覆盖库内全量表,
  pt.schemaname AS 表所属模式,
  pt.tablename AS 纳入复制的表名
FROM pg_publication p
LEFT JOIN pg_publication_tables pt 
  ON p.pubname = pt.pubname;

如果puballtables为true，代表该发布覆盖数据库内所有已存在、以及未来新建的表，这类场景下无需逐表匹配清单即可判断复制范围。

（2）查询订阅端正在同步的表与运行状态

-- 当前库视角查询本地逻辑订阅的同步状态
SELECT
  s.subname AS 本地逻辑订阅名称,
  s.subconninfo AS 订阅指向的远端发布端地址,
  ss.schemaname AS 同步表的所属模式,
  ss.subrelname AS 本地同步的表名,
  ss.last_msg_receipt_time AS 最近收到变更的时间,
  ss.received_lsn AS 已同步到的远端位点
FROM pg_subscription s
LEFT JOIN pg_subscription_rel ss 
  ON s.oid = ss.srsubid;

二、切主后旧主节点重新加入集群的逻辑复制变更感知方法 #

PG11+的原生逻辑复制不具备故障场景下的自动状态同步能力，核心原因是逻辑复制槽、以及切主完成后在新主上新增的逻辑配置，不会随流复制自动同步到其他备节点，需要按以下流程对齐状态：

1. 先获取新主的逻辑复制基准清单 #

在被提升的新主节点上执行第一部分的所有查询，拿到新主上的全量逻辑复制配置：

• 所有存在逻辑复制的数据库列表
• 每个发布覆盖的表范围
• 每个订阅指向的远端地址、当前同步位点
  将这份清单作为旧主重连时的对齐基准，注意不要用旧主上缓存的老配置作为判断依据。

2. 对齐旧主与新主的配置差异 #

旧主重新上线后，对比新主的基准清单，通常存在两类配置偏差：

• 残留的无效配置：旧主上保留的、指向老主地址的逻辑订阅，这类订阅会处于持续断连状态，需要清理无效连接配置，避免无意义的重试消耗资源
• 缺失的新配置：切主完成后，运维在新主上新增的发布、新增的订阅、调整的发布表范围，这类操作完全发生在新主本地，不会同步回旧主，需要在旧主上手动补全对应配置

3. 建立正常的变更同步链路 #

根据旧主在逻辑复制拓扑中的角色，分别完成链路打通：

• 如果旧主是作为新主的逻辑订阅端（需要从新主拉取数据变更）：
  将旧主上对应订阅的连接信息，修改为新主的实际地址；由于新主上没有旧主之前关联的逻辑复制槽，需要在新主上为该订阅手动创建对应的逻辑槽。因为新主已经和旧主完成全量数据同步，创建槽时可以设置copy_data = false，直接从故障切换时间点的LSN开始拉取增量变更，无需重复全量拷贝数据，避免不必要的性能损耗。
• 如果旧主是作为新主的逻辑发布端（新主要从旧主拉取数据变更）：
  确认新主上对应订阅的连接信息指向旧主的可达地址即可，因为发布配置是随流复制同步的系统表内容，旧主启动完成后，对应的发布规则会自动生效，新主的订阅会自动连上旧主，开始持续同步旧主上属于发布范围的表变更。

关键注意事项 #

• 禁止直接将旧主上的逻辑复制槽迁移到新主使用：旧主记录的槽位点属于旧主的事务时间线，在新主的时间线上是无效的，会直接导致复制链路报错
• 每次完成切主后，必须逐节点比对逻辑复制配置，不能默认配置是全局同步的，否则会出现漏同步新发布表、断连无告警的问题

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