如何在Postgres主节点同时实现同步流式复制与同步逻辑复制？

核心背景说明 #

PostgreSQL的synchronous_standby_names仅管控物理流式复制的同步节点，无法直接作用于逻辑复制，但通过拆分两种复制的同步逻辑，可实现双节点的同步要求，具体配置如下：

一、配置流式物理同步从节点 #

1. 主节点postgresql.conf设置：
   指定物理从节点的应用名称（需与从节点配置一致），开启同步提交：

   synchronous_standby_names = 'streaming_slave'
   synchronous_commit = 'on'
   

2. 物理从节点配置：
   在primary_conninfo中明确application_name，确保主节点能识别该同步节点：

   primary_conninfo = 'host=主节点IP port=5432 user=repl_user password=xxx application_name=streaming_slave'
   

二、配置逻辑同步复制的同步机制 #

逻辑复制的同步不依赖主节点的synchronous_standby_names，需通过订阅端与事务级设置实现：

1. 主节点创建发布：

   CREATE PUBLICATION logical_pub FOR TABLE 需同步的表名;
   

2. 订阅节点配置：
   • 创建订阅时指定全量同步初始数据：

     CREATE SUBSCRIPTION logical_sub CONNECTION 'host=主节点IP port=5432 user=sub_user password=xxx' PUBLICATION logical_pub COPY_DATA = true;
     

   • 修改订阅节点postgresql.conf，确保事务提交等待远程应用完成：

     synchronous_commit = 'remote_apply'
     

3. 主节点事务级增强（可选）：
   若要求主节点事务必须等待逻辑订阅节点应用完成，可在目标事务中添加强制等待：

   BEGIN;
   -- 执行业务操作
   INSERT INTO 需同步的表名 VALUES (...);
   -- 等待逻辑订阅节点应用当前事务变更
   SELECT pg_logical_remote_apply('logical_sub');
   COMMIT;
   

三、实现双节点同步的协同方案 #

如果要求主节点事务必须同时等待物理流式节点和逻辑订阅节点的确认，可采用以下两种方式：

方案A：级联同步架构 #

将逻辑订阅节点作为物理同步从节点的级联从节点：

• 物理从节点postgresql.conf设置：

  synchronous_standby_names = 'logical_slave'
  synchronous_commit = 'on'
  

• 逻辑订阅节点的primary_conninfo指向物理从节点，并指定application_name=logical_slave，同时开启逻辑复制。
  这种架构下，主节点只需等待物理从节点的同步确认，物理从节点再等待逻辑订阅节点的确认，实现端到端的全链路同步。

方案B：事务级双重同步控制 #

对需要双同步的事务，同时设置物理复制和逻辑复制的同步要求：

BEGIN;
-- 确保物理同步节点接收WAL
SET LOCAL synchronous_commit = 'remote_write';
-- 执行业务操作
UPDATE 需同步的表名 SET ... WHERE ...;
-- 等待逻辑订阅节点应用完成
SELECT pg_logical_remote_apply('logical_sub');
COMMIT;

关键注意事项 #

• synchronous_standby_names支持多物理节点（如'FIRST 1 (slave1, slave2)'），但无法包含逻辑订阅节点，因为两者的同步机制完全独立。
• 逻辑同步的性能开销高于物理复制，建议仅对核心业务事务启用双同步，避免影响主节点吞吐量。

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