PostgreSQL逻辑复制配置与跨库数据实时同步方案

PostgreSQL逻辑复制与物理复制的区别

PostgreSQL提供两种复制机制：物理复制（Streaming Replication）和逻辑复制（Logical Replication）。物理复制在WAL层面工作，将整个实例的数据块逐字节同步到备库，备库只读且与主库结构完全一致。逻辑复制工作在更高抽象层级，解析WAL日志提取行级变更事件，支持选择性复制表和列，目标端可读写。

逻辑复制的核心优势在于灵活性：跨大版本升级、选择性同步表、目标端可独立写入、异构数据库间的数据流转。这使得逻辑复制成为跨库数据实时同步的首选方案。

逻辑复制的基础配置

逻辑复制需要PostgreSQL 10+版本，并在postgresql.conf中设置WAL级别为logical：

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

# 创建发布端（Publisher）
-- 在源库执行
CREATE PUBLICATION user_data_pub FOR TABLE
  users,
  orders,
  products
WITH (publish = 'insert, update, delete');

发布端定义了哪些表的哪些操作需要被复制。WITH子句可控制复制的操作类型，支持insert、update、delete、truncate四种操作。

订阅端配置：

-- 在目标库执行
CREATE SUBSCRIPTION user_data_sub
CONNECTION 'host=source-db port=5432 dbname=myapp user=replicator password=secret'
PUBLICATION user_data_pub
WITH (
    copy_data = true,       -- 首次同步时复制存量数据
    synchronous_commit = off -- 异步提交提高性能
);

copy_data=true表示首次建立订阅时，自动执行全量数据快照同步，之后转为增量WAL流同步。这个初始同步过程对大表可能耗时较长，但不锁源表。

冲突处理与数据一致性

逻辑复制最常见的冲突场景：主库更新了一行数据，但该行在订阅端已被本地事务修改。PostgreSQL默认的冲突处理策略是报错停止复制，这需要运维介入解决。

-- 查看复制冲突
SELECT * FROM pg_stat_subscription;

-- 常见冲突解决：删除订阅端冲突行
DELETE FROM users WHERE id = 1001;

-- 重启复制
ALTER SUBSCRIPTION user_data_sub DISABLE;
ALTER SUBSCRIPTION user_data_sub ENABLE;

对于可预见的冲突场景，建议在订阅端使用触发器或规则进行自动化冲突消解：

-- 创建冲突解决函数
CREATE OR REPLACE FUNCTION resolve_conflict()
RETURNS TRIGGER AS $$
BEGIN
    -- 源库数据优先，直接覆盖
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 在订阅端表上创建触发器
CREATE TRIGGER trg_resolve_conflict
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION resolve_conflict();

需要注意，逻辑复制触发的触发器默认不会在订阅端执行，需要在订阅端显式启用：

ALTER TABLE users ENABLE ALWAYS TRIGGER trg_resolve_conflict;

跨版本升级实战

逻辑复制最常见的应用场景是跨大版本零停机升级。例如从PostgreSQL 14升级到16，传统pg_upgrade需要停机维护，逻辑复制可以在不停机的情况下完成：

1. 在新版本实例上创建订阅，订阅旧版本的发布

2. 等待初始数据快照同步完成

3. 持续追赶增量WAL流，直到订阅端与发布端延迟趋近于0

4. 应用只读模式，等待最后一波变更同步

5. 切换应用连接到新版本实例

6. 旧版本实例降级为只读备份

-- 监控复制延迟
SELECT
    subname,
    received_lsn,
    latest_end_lsn,
    pg_wal_lsn_diff(received_lsn, latest_end_lsn) AS lag_bytes
FROM pg_stat_subscription;

当lag_bytes稳定为0时，表示数据完全同步，可以执行切换。整个升级过程的停机窗口仅限于步骤4到5之间的秒级时间。

性能调优与监控指标

逻辑复制对源库的性能影响主要体现在WAL堆积风险。如果订阅端消费速度跟不上源库WAL生成速度，WAL文件不会被回收，磁盘空间可能耗尽。

关键调优参数：

# 源库参数
wal_sender_timeout = 60s       # 发送超时
max_wal_size = 4GB             # WAL最大保留
wal_keep_size = 1GB            # 额外保留WAL

# 订阅端参数
max_logical_replication_workers = 4  # 并行回放工作线程

监控核心指标：复制延迟（字节和秒）、WAL堆积量、复制槽活跃状态。建议设置告警阈值：延迟超过10秒触发Warning，超过60秒触发Critical。对于跨数据中心同步，网络带宽是瓶颈，需评估WAL生成速率是否超过可用带宽。