PostgreSQL逻辑复制与订阅配置实现多活数据同步

PostgreSQL逻辑复制架构原理

PostgreSQL逻辑复制是基于WAL（Write-Ahead Log）日志的行级数据同步机制。与物理复制不同，逻辑复制不复制数据块级别的变更，而是将WAL日志解析为逻辑变更（INSERT/UPDATE/DELETE行级操作），通过网络传输到订阅端重放。这种机制允许发布端和订阅端使用不同的PostgreSQL大版本、不同的操作系统平台，甚至对表结构做部分差异化。

逻辑复制的核心组件包括：发布者（Publisher）上的walsender进程负责读取WAL日志并解码为逻辑变更消息，订阅者（Subscriber）上的apply进程负责接收消息并在本地执行对应SQL操作。复制槽（Replication Slot）确保发布者在订阅者确认接收前不会清理相关WAL日志，防止数据丢失。

发布端配置与Replication Slot创建

逻辑复制需要PostgreSQL参数wal_level设置为logical。修改postgresql.conf后需要重启数据库：

# postgresql.conf 关键参数
wal_level = logical
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1024MB

# pg_hba.conf 添加复制权限
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      192.168.1.0/24          md5
host    all             replicator      192.168.1.0/24          md5

创建具有复制权限的用户并创建发布：

-- 创建复制用户
CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'SecurePass2026';

-- 在发布端数据库中创建发布
-- 发布所有表的变更
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- 或只发布特定表
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items, customers;

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

-- 查看复制槽状态
SELECT slot_name, plugin, slot_type, active, restart_lsn
FROM pg_replication_slots;

发布创建后，需要注意发布的表必须有REPLICA IDENTITY。默认情况下，主键列作为复制标识。对于没有主键的表，UPDATE和DELETE操作无法在订阅端定位到对应行，需要在表上设置REPLICA IDENTITY FULL：

-- 无主键表设置完整行作为复制标识
ALTER TABLE audit_log REPLICA IDENTITY FULL;

-- 查看表的复制标识设置
SELECT relname, relreplident
FROM pg_class
WHERE relname IN ('orders', 'audit_log');
-- relreplident: d=默认(主键), f=全部列, n=无标识

订阅端配置与数据同步

在订阅端创建与发布端结构一致的表，然后建立订阅连接：

-- 在订阅端数据库创建表结构（可使用pg_dump导出）
-- 注意：序列不会自动复制，需手动同步序列值

-- 创建订阅
CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=192.168.1.10 port=5432 dbname=shopdb user=replicator password=SecurePass2026'
    PUBLICATION pub_orders
    WITH (
        copy_data = true,          -- 初始同步时复制现有数据
        create_slot = true,        -- 自动创建复制槽
        enabled = true,
        synchronous_commit = on    -- 同步提交确保数据一致性
    );

-- 查看订阅状态
SELECT subname, subenabled, subslotname, subpublications
FROM pg_subscription;

-- 查看同步进度
SELECT * FROM pg_stat_subscription;

-- 查看订阅端apply工作进程状态
SELECT pid, relid, received_lsn, last_msg_send_time
FROM pg_stat_subscription_stats;

copy_data=true参数在创建订阅时触发一次性初始数据拷贝——发布端对每张表执行COPY操作，将现有数据全量传输到订阅端。对于大表（GB级别），初始同步可能耗时较长，可以预先通过pg_dump手动导入数据后，以copy_data=false创建订阅，仅同步增量变更。

双向复制与冲突处理策略

当两台PostgreSQL实例需要互为发布者和订阅者（Active-Active多活模式），必须在两端都创建发布和订阅。关键风险是复制循环——A的变更同步到B，B又将该变更同步回A，形成无限循环。PostgreSQL通过复制身份标识（origin）解决此问题：apply进程在重放数据时会标注origin，对带有origin标记的变更不再回传。

-- 节点A（192.168.1.10）配置
CREATE PUBLICATION pub_a FOR TABLE products, inventory;
CREATE SUBSCRIPTION sub_b
    CONNECTION 'host=192.168.1.20 port=5432 dbname=shopdb user=replicator password=SecurePass2026'
    PUBLICATION pub_a
    WITH (copy_data = false, create_slot = true, enabled = true);

-- 节点B（192.168.1.20）配置
CREATE PUBLICATION pub_b FOR TABLE products, inventory;
CREATE SUBSCRIPTION sub_a
    CONNECTION 'host=192.168.1.10 port=5432 dbname=shopdb user=replicator password=SecurePass2026'
    PUBLICATION pub_b
    WITH (copy_data = false, create_slot = true, enabled = true);

双向复制面临的主要挑战是写冲突——两个节点同时修改同一行数据。PostgreSQL逻辑复制不支持自动冲突检测。当UPDATE操作在订阅端找不到匹配行（该行已被另一节点修改或删除），apply进程会报错并停止同步。

规避冲突的常用策略：按业务维度拆分写入权限（如分库分表，每个节点只写自己负责的数据范围）；使用全局序列或UUID替代自增主键避免INSERT冲突；在应用层实现乐观锁版本号控制。对于已发生的冲突，可以通过设置订阅参数跳过错误事务：

-- 查看apply进程报错详情
SELECT * FROM pg_stat_subscription
WHERE subname = 'sub_b';

-- 跳过当前出错的事务LSN
ALTER SUBSCRIPTION sub_b SKIP (lsn = '0/1A3B4C0');

-- 或暂时禁用订阅，手动修复数据后重新启用
ALTER SUBSCRIPTION sub_b DISABLE;
-- 手动修复冲突数据...
ALTER SUBSCRIPTION sub_b ENABLE;

生产环境中建议配置监控告警，实时跟踪pg_stat_subscription中的last_msg_receive_time和last_msg_feedback_time，当apply进程停止或延迟超过阈值时及时通知。结合Prometheus的postgres_exporter指标pg_replication_lag，可以精确监控逻辑复制的延迟秒数，在延迟持续上升时介入排查。