PostgreSQL逻辑复制配置与数据冲突解决机制实战

PostgreSQL逻辑复制（Logical Replication）从版本10开始原生支持，通过解码WAL（Write-Ahead Log）日志中的逻辑变更，将数据变更以行级粒度传播到订阅端。与物理复制不同，逻辑复制支持跨版本、跨平台复制，目标端可独立执行DDL操作，适用于读写分离、数据分发和零停机迁移等场景。理解Publication/Subscription模型和冲突处理机制是构建稳定逻辑复制链路的前提。

逻辑复制Publication与Subscription配置

逻辑复制采用发布-订阅模型。发布端创建Publication定义要复制的表和操作类型（insert/update/delete/truncate），订阅端创建Subscription连接到发布端并应用变更。发布端需要设置wal_level=logical，订阅端需要设置max_replication_slots和max_logical_replication_workers参数。

-- ========== 发布端配置 ==========
-- postgresql.conf关键参数
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10

-- 创建复制角色（必须具有REPLICATION属性）
CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'securepass';

-- pg_hba.conf允许复制连接
-- host    replication    replicator    0.0.0.0/0    scram-sha-256

-- 创建发布：指定表和操作类型
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items
    WITH (publish = 'insert, update, delete');

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

-- ========== 订阅端配置 ==========
-- 订阅端表结构必须与发布端一致（列名和类型兼容）
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

-- 创建订阅：连接发布端并关联发布
CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=192.168.1.10 port=5432 dbname=shopdb user=replicator password=securepass'
    PUBLICATION pub_orders
    WITH (
        copy_data = true,           -- 初始数据同步
        create_slot = true,         -- 自动创建复制槽
        enabled = true,
        slot_name = 'sub_orders_slot'
    );

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

copy_data=true在订阅创建时自动执行初始全量数据拷贝，完成后切换到增量WAL流复制。create_slot=true自动在发布端创建逻辑复制槽，复制槽记录消费位点（LSN）确保断线重连后从正确位置继续。表必须已有相同结构，逻辑复制不处理DDL变更。

数据冲突类型与解决策略

逻辑复制场景下，订阅端是可写的普通PostgreSQL实例，应用层或人工操作可能在订阅端直接修改被复制数据，导致应用WAL变更时产生冲突。常见冲突类型包括主键冲突（insert违反唯一约束）、外键约束违反和行不存在（update/delete找不到目标行）。

-- 冲突1：主键冲突（INSERT违反唯一约束）
-- 发布端INSERT id=100，订阅端已存在id=100的行
-- 解决方案1：订阅端删除冲突行后等待复制自动重试
DELETE FROM orders WHERE id = 100;

-- 解决方案2：修改conflict处理策略
-- PostgreSQL 17+支持conflict参数
-- CREATE SUBSCRIPTION ... WITH (origin = 'none');

-- 监控复制冲突
SELECT subname, pid, relid, xid, conflict_type, conflict_reason
FROM pg_stat_subscription_conflicts;

-- 冲突2：更新行不存在（UPDATE/DELETE找不到行）
-- 发布端UPDATE orderId=200，订阅端该行已被删除
-- 通常可忽略，日志记录后继续

-- 冲突3：复制延迟导致数据不一致
-- 检查复制延迟
SELECT
    now() - pg_last_xact_replay_timestamp AS replication_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- 订阅端检查应用位点
SELECT * FROM pg_stat_subscription
WHERE subname = 'sub_orders';

PostgreSQL 17引入了原生逻辑复制冲突解决机制，通过ALTER SUBSCRIPTION设置conflict_resolution参数配置冲突策略。apply_remote以发布端数据覆盖本地，keep_local保留本地数据跳过远程变更，skip跳过冲突操作并记录日志。

复制槽管理与故障恢复实践

逻辑复制槽是发布端的持久化状态记录，追踪订阅端的消费进度。如果订阅端长时间断线，发布端会保留未消费的WAL日志，可能导致WAL堆积撑满磁盘。复制槽管理是运维逻辑复制的核心任务。

-- 复制槽管理
-- 查看所有逻辑复制槽
SELECT slot_name, plugin, slot_type, database, active,
       restart_lsn, confirmed_flush_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- 临时禁用订阅（保留复制槽，不应用变更）
ALTER SUBSCRIPTION sub_orders DISABLE;

-- 重新启用订阅
ALTER SUBSCRIPTION sub_orders ENABLE;

-- 订阅端表结构变更（逻辑复制不复制DDL）
-- 1. 禁用订阅
ALTER SUBSCRIPTION sub_orders DISABLE;
-- 2. 两端同步执行DDL
ALTER TABLE orders ADD COLUMN discount NUMERIC(5,2) DEFAULT 0;
-- 3. 重新启用订阅
ALTER SUBSCRIPTION sub_orders ENABLE;

-- 清理无用复制槽（订阅端已删除但槽未清理）
SELECT pg_drop_replication_slot('sub_orders_slot');

-- 设置WAL保留警戒线
-- postgresql.conf:
-- max_slot_wal_keep_size = '10GB'  -- 单槽最大WAL保留量
-- 超出后复制槽变为lost状态，需重新创建订阅

max_slot_wal_keep_size参数限制单个复制槽的最大WAL保留量，超出后PostgreSQL自动回收旧WAL文件，复制槽变为lost状态。lost状态的复制槽需要删除并重新创建订阅，可能需要全量数据重新同步。生产环境建议设置监控告警，当复制延迟超过5分钟或WAL堆积超过2GB时触发通知。

零停机迁移是逻辑复制的高价值应用场景。迁移过程中应用层持续写入源库，逻辑复制将增量变更实时同步到目标库。切换时只需短暂进入只读模式确认数据一致，然后更新连接字符串指向目标库，整个过程停机时间可控制在秒级。迁移完成后在目标库端删除Subscription但保留Publication作为回滚保险，确认业务稳定运行后再清理源库。