PostgreSQL逻辑复制与多活架构搭建：发布订阅配置与冲突解决策略

PostgreSQL逻辑复制是数据库高可用和数据分发的重要机制，通过解析WAL日志中的逻辑变更事件，将数据变更以行级粒度复制到目标库。与物理复制不同，逻辑复制可以跨版本、跨平台、选择性复制特定表，支持双向复制实现多活架构。本文从发布订阅配置到冲突解决给出完整实践方案。

逻辑复制与物理复制的核心差异

物理复制传输的是WAL日志的原始字节流，目标库必须与源库完全一致（相同版本、相同系统架构），复制整个数据库实例。逻辑复制传输的是逻辑变更事件（INSERT/UPDATE/DELETE操作），目标库可以是不同版本、不同操作系统，可以只复制指定表，甚至允许目标库有额外表和索引。

这种差异带来的实际优势：逻辑复制支持在线跨版本升级（旧版本发布到新版本订阅）、读写分离的灵活路由（只读副本可有独立索引）、异地多活（双向复制实现两地可写）。代价是逻辑复制不支持DDL变更自动复制（需手动在两端执行），且大事务的复制延迟可能高于物理复制。

发布端配置：PUBLICATION创建

发布端需要配置wal_level=logical，并创建PUBLICATION指定要复制的表：

-- postgresql.conf 配置
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10

-- 创建复制专用用户
CREATE ROLE replicator REPLICATION LOGIN PASSWORD 'repl_passwd';

-- 授予表复制权限
GRANT SELECT ON business.orders TO replicator;
GRANT SELECT ON business.customers TO replicator;

-- 创建PUBLICATION
CREATE PUBLICATION pub_business
FOR TABLE business.orders, business.customers
WITH (publish = 'insert, update, delete');

-- 查看PUBLICATION状态
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

publish参数控制复制操作类型，默认为insert/update/delete。如果只做增量同步可排除update和delete。表必须有主键或唯一索引（REPLICA IDENTITY），否则UPDATE和DELETE操作无法在订阅端定位到对应行。

订阅端配置：SUBSCRIPTION创建与初始同步

订阅端创建对应表结构后，建立SUBSCRIPTION连接发布端：

-- 订阅端：创建与发布端相同的表结构
CREATE SCHEMA business;
CREATE TABLE business.orders (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(64) NOT NULL,
    customer_id BIGINT NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT now()
);
CREATE TABLE business.customers (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(200),
    created_at TIMESTAMP DEFAULT now()
);

-- 创建SUBSCRIPTION
CREATE SUBSCRIPTION sub_business
CONNECTION 'host=pub_host port=5432 dbname=business user=replicator password=repl_passwd'
PUBLICATION pub_business
WITH (
    copy_data = true,          -- 初始全量同步已有数据
    create_slot = true,        -- 自动创建复制槽
    slot_name = 'sub_business_slot',
    synchronous_commit = off   -- 异步提交降低延迟
);

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

copy_data=true在建立订阅时自动执行一次全量数据同步，之后进入增量复制模式。create_slot=true自动在发布端创建逻辑复制槽，记录消费进度。

双向复制与多活架构实现

单向复制只能实现读写分离，双向复制才能实现多活。双向复制的核心挑战是避免复制循环（A库的变更复制到B库，B库再复制回A库形成死循环）。PostgreSQL通过session_replication_role参数跳过复制触发的触发器，实现循环检测。

-- 节点A配置
-- 创建到节点B的PUBLICATION
CREATE PUBLICATION pub_node_a
FOR TABLE business.orders, business.customers
WITH (publish = 'insert, update, delete');

-- 创建从节点B的SUBSCRIPTION
CREATE SUBSCRIPTION sub_from_b
CONNECTION 'host=node_b port=5432 dbname=business user=replicator password=repl_passwd'
PUBLICATION pub_node_b
WITH (copy_data = false, create_slot = true);

-- 节点B配置（对称）
CREATE PUBLICATION pub_node_b
FOR TABLE business.orders, business.customers
WITH (publish = 'insert, update, delete');

CREATE SUBSCRIPTION sub_from_a
CONNECTION 'host=node_a port=5432 dbname=business user=replicator password=repl_passwd'
PUBLICATION pub_node_a
WITH (copy_data = false, create_slot = true);

copy_data=false避免双向初始同步冲突。双向复制时，复制操作的UPDATE/DELETE通过origin机制被跳过，不会形成循环。但INSERT操作仍可能产生主键冲突，需要通过序列错位或UUID主键避免。

主键冲突与序列错位策略

多活架构中，两个节点同时插入数据可能产生主键冲突。解决方案是将序列起始值错开，确保两节点生成的ID不重叠：

-- 节点A：序列从1开始，步长2
ALTER SEQUENCE business.orders_id_seq RESTART WITH 1 INCREMENT BY 2;
-- 节点A生成ID: 1, 3, 5, 7, 9...

-- 节点B：序列从2开始，步长2
ALTER SEQUENCE business.orders_id_seq RESTART WITH 2 INCREMENT BY 2;
-- 节点B生成ID: 2, 4, 6, 8, 10...

对于UUID主键则天然不存在此问题。业务唯一约束（如订单号）需要在应用层保证全局唯一，例如通过节点标识前缀生成唯一编号。

复制冲突检测与自动解决

逻辑复制在订阅端遇到冲突时，复制会暂停并记录错误。常见冲突类型包括：主键冲突（INSERT已存在的行）、行不存在（UPDATE/DELETE找不到行）、外键约束冲突。

-- 查看复制冲突日志
SELECT * FROM pg_stat_subscription
WHERE worker_type = 'apply';

-- 查看最近的冲突详情
SELECT * FROM pg_logical_slot_peek_binary_changes(
    'sub_business_slot', NULL, 10
);

-- 处理冲突：跳过当前事务
ALTER SUBSCRIPTION sub_business SKIP TRANSACTION;
-- 或暂停后手动修复再恢复
ALTER SUBSCRIPTION sub_business DISABLE;
-- 修复冲突数据后...
ALTER SUBSCRIPTION sub_business ENABLE;

PostgreSQL 17+引入了自动冲突解决策略配置，支持last_update_wins（最新更新胜出）、first_update_wins（首次更新保留）、skip（跳过冲突事务）三种策略：

-- 设置冲突解决策略
ALTER SUBSCRIPTION sub_business
SET (conflict_resolution = 'last_update_wins');

-- 添加时间戳列辅助判断最新更新
ALTER TABLE business.orders ADD COLUMN updated_at TIMESTAMP DEFAULT now();

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON business.orders
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 冲突解决时比较updated_at字段，保留较新记录

last_update_wins策略需要表有时间戳字段辅助判断新旧。对于业务关键数据，建议在应用层实现幂等操作，通过唯一约束和条件更新减少冲突发生概率。

复制监控与延迟排查

监控逻辑复制延迟是运维的重要环节，通过pg_stat_subscription视图可以查看复制进度：

-- 查看订阅端接收和应用延迟
SELECT
    subname,
    received_lsn,
    latest_end_lsn,
    received_lsn - latest_end_lsn AS receive_lag,
    now() - latest_end_time AS time_lag
FROM pg_stat_subscription;

-- 计算发布端WAL积压量
SELECT
    slot_name,
    confirmed_flush_lsn,
    pg_current_wal_lsn() - confirmed_flush_lsn AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- 大事务导致的复制延迟排查
SELECT
    pid,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sent_lsn - replay_lsn AS lag_bytes
FROM pg_stat_replication;

lag_bytes持续增长说明订阅端处理速度跟不上发布端写入速度，可能原因包括：大事务积压、订阅端磁盘IO瓶颈、网络带宽不足。监控告警阈值建议设为lag_bytes超过1GB或time_lag超过60秒。

PostgreSQL逻辑复制在数据分发、跨版本迁移和多活架构场景中提供了物理复制无法替代的灵活性。合理配置复制槽、冲突解决策略和监控体系，是保障逻辑复制稳定运行的关键。多活架构需在应用层配合幂等设计和全局唯一ID生成，才能在双向复制环境下避免数据不一致。