PostgreSQL逻辑复制实战：发布订阅配置与多数据中心双向同步方案

PostgreSQL逻辑复制基于WAL日志解析实现表级数据同步，相比物理复制具备选择性复制、跨版本兼容、双向同步等优势。在多数据中心高可用架构、读写分离、在线数据迁移等场景中应用广泛。本文从发布订阅基础配置、冲突处理机制、多活双向同步三个维度给出完整配置方案。

逻辑复制核心概念与前置配置

逻辑复制分为Publisher（发布端）和Subscriber（订阅端）。发布端创建Publication指定要复制的表，订阅端创建Subscription连接发布端并接收变更。逻辑复制基于WAL日志的logical decoding机制，需要配置wal_level=logical。

-- 发布端 postgresql.conf 配置
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10
-- max_worker_processes = 15

-- 重启PostgreSQL后生效
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
SELECT pg_reload_conf();

-- 创建复制专用用户
CREATE ROLE replicator REPLICATION LOGIN PASSWORD 'Str0ngPass!2026';
GRANT CONNECT ON DATABASE appdb TO replicator;

-- pg_hba.conf 添加复制权限
-- host    replication    replicator    10.0.0.0/8    md5
-- host    appdb          replicator    10.0.0.0/8    md5

-- 重新加载pg_hba.conf
SELECT pg_reload_conf();

max_replication_slots设置逻辑复制槽数量，每个Subscription对应一个复制槽。max_wal_senders设置WAL发送进程上限，需大于逻辑和物理复制的总连接数。复制槽会在订阅端断连后保留WAL日志，防止数据丢失，但长期断连会导致WAL堆积。

Publication发布配置

-- 发布端：创建Publication
-- 方式1：发布所有表
CREATE PUBLICATION pub_appdb FOR ALL TABLES;

-- 方式2：指定表和操作类型（推荐）
CREATE PUBLICATION pub_appdb
  FOR TABLE users, orders, order_items, products
  WITH (publish = 'insert, update, delete, truncate');

-- 查看Publication状态
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables WHERE pubname = 'pub_appdb';

-- 动态添加/移除表
ALTER PUBLICATION pub_appdb ADD TABLE categories;
ALTER PUBLICATION pub_appdb DROP TABLE products;

-- 修改发布操作类型
ALTER PUBLICATION pub_appdb SET (publish = 'insert, update');

publish参数控制哪些DML操作被复制。仅同步增量数据时设为insert, update即可；需要完整同步则包含delete和truncate。选择性发布表可以减少网络传输量，避免将日志表、临时表等不需要同步的表纳入复制。

-- 发布端：确保被复制的表有REPLICA IDENTITY
-- 默认值为default（使用主键）
-- 无主键表需设置为full（使用所有列作为标识）
ALTER TABLE products REPLICA IDENTITY FULL;
ALTER TABLE order_items REPLICA IDENTITY USING INDEX idx_order_items_unique;

-- 查看表REPLICA IDENTITY设置
SELECT relname, relreplident FROM pg_class
  WHERE relname IN ('users', 'orders', 'products', 'order_items');

REPLICA IDENTITY决定UPDATE和DELETE操作在WAL日志中记录哪些列作为旧值标识。default模式仅记录主键，订阅端通过主键定位行执行更新。无主键表设为full会记录所有列旧值，增加WAL日志量但保证复制正确性。

Subscription订阅配置

-- 订阅端：创建Subscription
-- 前提：订阅端已存在相同结构的表
CREATE SUBSCRIPTION sub_appdb
  CONNECTION 'host=10.0.1.10 port=5432 dbname=appdb user=replicator password=Str0ngPass!2026'
  PUBLICATION pub_appdb
  WITH (
    copy_data = true,          -- 初始数据同步
    create_slot = true,        -- 自动创建复制槽
    slot_name = 'sub_appdb_slot',
    enabled = true,
    synchronous_commit = off   -- 异步提交降低延迟
  );

-- 查看Subscription状态
SELECT * FROM pg_subscription;
SELECT * FROM pg_stat_subscription;

-- 查看复制进度
SELECT * FROM pg_replication_slots WHERE slot_name = 'sub_appdb_slot';

copy_data=true在首次创建订阅时复制现有数据快照，后续仅同步增量变更。synchronous_commit=off设置异步提交，减少WAL落盘延迟提升复制吞吐量，代价是崩溃时可能丢失极少量事务。create_slot=true自动在发布端创建逻辑复制槽。

数据冲突检测与处理

逻辑复制在双向同步或数据不一致时可能产生冲突。PostgreSQL不内置自动冲突解决机制，冲突发生时订阅端会停止复制并记录错误日志。

-- 查看订阅错误日志
SELECT * FROM pg_stat_subscription
  WHERE subname = 'sub_appdb';

-- 常见冲突类型：
-- 1. 主键冲突：订阅端已存在相同主键的行
-- 2. 外键冲突：引用的外键行不存在
-- 3. 唯一约束冲突：唯一索引列值重复

-- 处理主键冲突：跳过冲突事务
-- 方式1：修改订阅跳过冲突（PG 16+）
ALTER SUBSCRIPTION sub_appdb SET (skip_lsn = '0/1A2B3C4D');

-- 方式2：暂停订阅，手动修复后恢复
ALTER SUBSCRIPTION sub_appdb DISABLE;
-- 手动修复冲突数据...
ALTER SUBSCRIPTION sub_appdb ENABLE;

-- 方式3：刷新数据重新同步单表
ALTER SUBSCRIPTION sub_appdb REFRESH PUBLICATION
  WITH (copy_data = true);

skip_lsn参数跳过指定LSN位置的事务，适用于偶发冲突。DISABLE/ENABLE方式暂停后手动修复冲突行再恢复，适合需要数据干预的场景。REFRESH PUBLICATION重新同步表结构和初始数据，适合表结构变更后的全量刷新。

多数据中心双向同步配置

双向同步需要两个节点互为发布者和订阅者，关键配置是避免数据循环复制。PostgreSQL通过origin机制标记数据来源，相同origin的变更不会被反向复制。

-- 节点A (10.0.1.10) 配置
-- 创建Publication
CREATE PUBLICATION pub_node_a
  FOR TABLE users, orders, order_items
  WITH (publish = 'insert, update, delete, truncate');

-- 创建到节点B的Subscription
CREATE SUBSCRIPTION sub_from_node_b
  CONNECTION 'host=10.0.2.10 port=5432 dbname=appdb user=replicator password=Str0ngPass!2026'
  PUBLICATION pub_node_b
  WITH (
    copy_data = false,         -- 双向同步不做初始拷贝
    create_slot = true,
    enabled = true,
    origin = 'node_a'           -- 标记origin防止循环
  );

-- 节点B (10.0.2.10) 配置
CREATE PUBLICATION pub_node_b
  FOR TABLE users, orders, order_items
  WITH (publish = 'insert, update, delete, truncate');

CREATE SUBSCRIPTION sub_from_node_a
  CONNECTION 'host=10.0.1.10 port=5432 dbname=appdb user=replicator password=Str0ngPass!2026'
  PUBLICATION pub_node_a
  WITH (
    copy_data = false,
    create_slot = true,
    enabled = true,
    origin = 'node_b'
  );

origin参数是双向同步防循环的核心机制。节点A产生的数据origin标记为node_a，节点B收到后不会反向复制回节点A，因为sub_from_node_a的origin为node_a会被自动跳过。copy_data=false避免双向同步时重复复制初始数据。多活场景中建议每条记录增加节点标识列，避免不同节点同时写入导致主键冲突。

-- 多活冲突预防：使用序列分配不同ID范围
-- 节点A：序列从1开始步长2（奇数）
CREATE SEQUENCE users_id_seq START 1 INCREMENT 2;
-- 节点B：序列从2开始步长2（偶数）
CREATE SEQUENCE users_id_seq START 2 INCREMENT 2;

-- 或使用UUID主键避免序列冲突
ALTER TABLE users ADD COLUMN id UUID DEFAULT gen_random_uuid() PRIMARY KEY;

双向同步场景下，自增主键必然冲突。序列奇偶分配方案简单但节点扩展受限，UUID主键方案天然避免冲突但牺牲顺序性。业务层写入时通过负载均衡分发到不同节点，减少同一记录同时被修改的概率。