PostgreSQL逻辑复制配置与多主架构数据同步实战

PostgreSQL逻辑复制原理与物理复制对比分析

PostgreSQL的复制机制分为物理复制（Streaming Replication）和逻辑复制（Logical Replication）两种。物理复制在WAL字节层面同步数据，备库是主库的完整块级拷贝，无法选择性复制表或库。逻辑复制在逻辑变更层面工作，基于发布-订阅模型，可以精确控制复制哪些表、哪些列，甚至通过WHERE条件过滤行级数据。

逻辑复制的核心架构：发布端（Publisher）解析WAL日志，将INSERT/UPDATE/DELETE操作解码为逻辑变更消息；订阅端（Subscriber）接收变更消息并回放到本地表。逻辑复制不要求备库与主库结构完全一致，订阅端表可以有不同的索引、额外列、不同的列顺序。

逻辑复制适用场景：多主写入、跨版本升级、部分数据同步、读写分离精细化控制、跨数据库数据集成。

逻辑复制发布端配置与Publication创建

发布端需要修改postgresql.conf配置：

# 逻辑复制必须设置wal_level为logical
wal_level = logical

# max_replication_slots控制最大复制槽数量
max_replication_slots = 10

# WAL发送进程数
max_wal_senders = 10

创建Publication指定需要复制的表：

-- 复制单表
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- 复制整个数据库所有表
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- 带行级过滤的发布（PostgreSQL 15+）
CREATE PUBLICATION pub_active_orders FOR TABLE orders WHERE (status = 'active');

-- 查看发布状态
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Publication支持动态添加和移除表：

ALTER PUBLICATION pub_orders ADD TABLE payments;
ALTER PUBLICATION pub_orders DROP TABLE order_items;

订阅端配置与Subscription创建

订阅端同样需要wal_level = logical，创建Subscription连接到发布端：

CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=10.0.1.100 port=5432 dbname=order_db user=replicator password=secret'
  PUBLICATION pub_orders
  WITH (
    copy_data = true,     -- 初始数据同步
    create_slot = true,   -- 自动创建复制槽
    synchronous_commit = off
  );

-- 查看订阅状态
SELECT * FROM pg_subscription;
SELECT * FROM pg_stat_subscription;

copy_data = true会在订阅创建时触发初始数据快照同步，将发布端已有数据全量复制到订阅端。对于大表，初始同步可能耗时较长，建议在业务低峰期操作。

订阅冲突处理：当订阅端数据与发布端变更产生冲突时（如主键冲突、外键约束违反），复制会暂停。冲突原因可通过pg_stat_subscription的latest_error字段查看。解决方式包括手动修复冲突数据后重启订阅，或使用ALTER SUBSCRIPTION ... REFRESH PUBLICATION重建同步。

多主架构部署与冲突解决策略

多主（Multi-Master）架构是逻辑复制的高级应用场景，两个或多个PostgreSQL实例互相发布和订阅对方的数据变更，实现双向写入。多主架构的核心挑战是写冲突解决。

部署多主架构的关键配置：

-- 节点A发布
CREATE PUBLICATION node_a_pub FOR ALL TABLES;

-- 节点B发布
CREATE PUBLICATION node_b_pub FOR ALL TABLES;

-- 节点A订阅节点B
CREATE SUBSCRIPTION sub_from_b
  CONNECTION 'host=node_b port=5432 dbname=app user=replicator password=secret'
  PUBLICATION node_b_pub
  WITH (copy_data = false, origin = none);

-- 节点B订阅节点A
CREATE SUBSCRIPTION sub_from_a
  CONNECTION 'host=node_a port=5432 dbname=app user=replicator password=secret'
  PUBLICATION node_a_pub
  WITH (copy_data = false, origin = none);

origin = none参数确保从对端订阅来的变更不会再次被发布回去，避免循环复制。PostgreSQL 16+支持origin参数，早期版本需通过触发器或其他机制避免循环。

写冲突解决策略包括：时间戳优先（每行增加updated_at字段，冲突时取最新值）；主键分段（不同节点使用不同主键序列段，从根源避免主键冲突）；业务层避免（按地域或业务模块划分写入归属，减少同一行被多节点同时修改的概率）。

监控逻辑复制延迟可查询pg_stat_subscription，对比latest_end_lsn与发布端当前LSN。延迟过大需检查网络带宽、订阅端写入性能、是否发生冲突暂停等。