PostgreSQL逻辑复制与数据同步方案实战

PostgreSQL逻辑复制的核心机制

PostgreSQL从9.4版本引入逻辑复制（Logical Replication），与物理复制不同，逻辑复制基于WAL（Write-Ahead Log）的逻辑解码，以行为单位同步数据变更，而非整页的二进制拷贝。这意味着可以选择性地复制特定的表，目标端可以有不同的索引、分区策略甚至表结构，极大提升了数据同步的灵活性。

逻辑复制适用于以下场景：跨数据库的部分表同步、读写分离中的读副本、数据库版本升级的灰度切换、数据变更的实时捕获（CDC）驱动下游系统更新。

发布端与订阅端配置

逻辑复制由Publisher（发布端）和Subscriber（订阅端）组成。发布端定义要同步的数据集合，订阅端连接发布端拉取变更：

-- 发布端：主库配置
-- 修改postgresql.conf
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10

-- 创建发布（可选择特定表和操作类型）
CREATE PUBLICATION order_sync
    FOR TABLE orders, order_items, customers
    WITH (publish = 'insert, update, delete');

-- 只发布insert，忽略delete和update
CREATE PUBLICATION log_sync
    FOR TABLE access_logs
    WITH (publish = 'insert');

-- 动态添加表到已有发布
ALTER PUBLICATION order_sync ADD TABLE payments;

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

-- 订阅端：从库配置
CREATE SUBSCRIPTION order_sub
    CONNECTION 'host=primary.db.internal port=5432 dbname=appdb user=replicator password=xxx'
    PUBLICATION order_sync
    WITH (copy_data = true, create_slot = true);

-- copy_data = true 表示首次同步时先做全量COPY
-- create_slot = true 表示自动在发布端创建复制槽

-- 查看订阅状态
SELECT subname, status, received_lsn, latest_end_lsn
FROM pg_stat_subscription;

-- 暂停/恢复订阅
ALTER SUBSCRIPTION order_sub DISABLE;
ALTER SUBSCRIPTION order_sub ENABLE;

-- 删除订阅
DROP SUBSCRIPTION order_sub;

冲突处理策略

逻辑复制最常见的故障场景是冲突：订阅端修改了某行数据，发布端随后也对同一行执行了更新。默认行为是复制中断并报错。处理方式取决于业务容忍度：

策略一：订阅端只读

最简单也最可靠——订阅端仅用于读取，所有写操作路由到发布端。这是读写分离场景的标准做法。

策略二：自定义冲突解决

通过触发器实现自定义冲突解决逻辑：

CREATE OR REPLACE FUNCTION resolve_conflict()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        IF OLD.updated_at > NEW.updated_at THEN
            RETURN NULL;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER conflict_resolver
    BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION resolve_conflict();

策略三：跳过冲突事务

临时跳过出错的复制事务，手动修复数据后恢复：

-- 跳过指定LSN的事务
ALTER SUBSCRIPTION order_sub SKIP (lsn = '0/1A2B3C0');

-- 刷新订阅状态
ALTER SUBSCRIPTION order_sub REFRESH PUBLICATION;

基于CDC的实时数据管道

逻辑复制的底层机制（逻辑解码）也可用于构建CDC管道，将PostgreSQL变更实时推送到Kafka、Elasticsearch等外部系统：

-- 创建逻辑复制槽供CDC使用
SELECT pg_create_logical_replication_slot('debezium_slot', 'pgoutput');

CDC管道的优势是端到端延迟可控制在秒级，且对源库性能影响极小（仅消费WAL流）。适用于缓存自动刷新（订单变更到Kafka再到Redis更新）、搜索引擎索引同步、数据仓库实时入湖等场景。

性能调优与监控

逻辑复制的性能瓶颈通常出现在三个位置：WAL生成速度、网络传输带宽、订阅端Apply速度。

– WAL积压：当订阅端消费速度跟不上发布端WAL生成速度时，WAL文件会在发布端堆积。监控pg_replication_slots的restart_lsn与当前LSN的差距，超过1GB需要告警。

– 大事务优化：单次UPDATE影响10万行以上的大事务在逻辑复制中会拆分为逐行变更事件，导致订阅端Apply缓慢。业务层面改为分批更新，每批1000行，间隔100ms。

– 并行Apply：PostgreSQL 16+支持订阅端的并行Apply，设置max_parallel_apply_workers_per_subscription参数（建议值4-8）。

-- 复制延迟监控
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- 复制槽积压监控
SELECT slot_name, slot_type, active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS backlog_bytes
FROM pg_replication_slots;

-- 订阅端统计
SELECT subname, pid, received_lsn, latest_end_lsn,
       latest_end_time, last_msg_receipt_time
FROM pg_stat_subscription;

建议在发布端设置wal_keep_size=2GB（保留足够WAL文件避免被清理），同时配置pg_stat_replication的监控告警，延迟超过30秒触发通知。