PostgreSQL逻辑复制配置与跨版本数据迁移实战

PostgreSQL逻辑复制（Logical Replication）是数据库运维中实现跨版本数据同步和零停机迁移的关键技术。与物理复制不同，逻辑复制基于WAL日志的逻辑变更记录，支持选择性表复制和跨大版本迁移。本文从架构原理到配置实操，覆盖发布端、订阅端搭建及迁移过程中的冲突处理。

逻辑复制与物理复制的区别与选型

PostgreSQL提供两种复制机制，适用场景不同：

• 物理复制：传输WAL字节流，从节点与主节点完全一致，要求相同大版本、相同架构。适合高可用架构的 standby 节点。
• 逻辑复制：解析WAL日志提取逻辑变更（INSERT/UPDATE/DELETE），以发布-订阅模式传输。支持跨版本、跨平台、选择性表复制，但DDL变更不同步。

逻辑复制适用于以下场景：从PostgreSQL 13迁移到16；将部分业务表实时同步到分析库；多活架构中双向数据同步。需要注意的限制：不支持序列（SEQUENCE）复制，不支持DDL自动同步，TRUNCATE操作需要单独订阅。

发布端配置流程

发布端（Publisher）需要配置WAL级别和发布集合。修改postgresql.conf：

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

# 重载配置
SELECT pg_reload_conf();

确保待复制的表有主键或REPLICA IDENTITY（逻辑复制依赖主键标识行）：

-- 检查表是否有主键
SELECT tablename, hasprimarykey 
FROM pg_tables 
WHERE schemaname = 'public';

-- 如果没有主键，设置REPLICA IDENTITY FULL（性能较差，仅作兜底）
ALTER TABLE orders REPLICA IDENTITY FULL;

-- 创建发布（publication）
-- 方式1：发布所有表的变更
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- 方式2：发布指定表
CREATE PUBLICATION pub_business FOR TABLE users, orders, products;

-- 方式3：仅发布INSERT和UPDATE（不含DELETE）
CREATE PUBLICATION pub_partial FOR TABLE users, orders 
    WITH (publish = 'insert, update');

-- 查看发布详情
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

创建逻辑复制用户并授权：

-- 创建复制专用用户
CREATE ROLE replicator WITH LOGIN PASSWORD 'StrongPass123';
GRANT REPLICATION TO replicator;

-- 授予表访问权限
GRANT SELECT ON users, orders, products TO replicator;

-- 修改pg_hba.conf允许复制连接
-- echo "host    replication    replicator    0.0.0.0/0    md5" >> pg_hba.conf
SELECT pg_reload_conf();

订阅端配置流程

订阅端（Subscriber）需要预先创建相同的表结构，然后建立订阅：

-- 确保订阅端表结构与发布端一致
-- 可以使用pg_dump导出表结构（仅schema，不含数据）
-- pg_dump -h publisher_host -U postgres --schema-only --table=users --table=orders postgres > schema.sql

-- 在订阅端执行schema.sql创建表后，建立订阅
CREATE SUBSCRIPTION sub_business
    CONNECTION 'host=10.0.0.1 port=5432 dbname=postgres user=replicator password=StrongPass123'
    PUBLICATION pub_business;

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

-- 关键状态字段：
-- received_lsn: 已接收的WAL位置
-- latest_end_lsn: 最新处理的WAL位置  
-- 如果 received_lsn 持续增长且 latest_end_lsn 跟上，说明复制正常

默认情况下，创建订阅时会自动同步初始数据（即copy_data = true）。对于大表初始同步可能产生锁和IO压力，可以选择手动导入初始数据后创建订阅时跳过数据拷贝：

-- 先手动导入数据
-- pg_dump导出数据 -> psql导入

-- 然后创建订阅时跳过初始数据同步
CREATE SUBSCRIPTION sub_business
    CONNECTION 'host=10.0.0.1 port=5432 dbname=postgres user=replicator password=StrongPass123'
    PUBLICATION pub_business
    WITH (copy_data = false);

数据迁移过程中的冲突处理

逻辑复制中，订阅端允许对复制表的本地写入，但这会产生冲突。常见冲突类型及处理方式：

-- 冲突1：主键冲突（订阅端已存在相同主键的行）
-- 错误信息：duplicate key value violates unique constraint
-- 处理：删除订阅端冲突行，复制自动恢复
DELETE FROM users WHERE id = 1001;

-- 冲突2：更新了订阅端不存在的行
-- 错误信息：logical replication did not find row for update in replication target
-- 处理：手动插入缺失的行或跳过该事务

-- 查看复制错误日志
SELECT * FROM pg_stat_subscription;
-- 若 pid 为 NULL 且 last_error_message 有值，说明复制中断

-- 临时禁用订阅、处理冲突后恢复
ALTER SUBSCRIPTION sub_business DISABLE;
-- 处理冲突数据...
ALTER SUBSCRIPTION sub_business ENABLE;

预防冲突的最佳实践：订阅端对复制表设置为只读，应用层不写入这些表。如果需要双向同步，必须使用BDR或Pglogical等扩展方案处理冲突逻辑，原生逻辑复制不支持双向。

复制槽管理与监控

逻辑复制依赖复制槽（Replication Slot）管理WAL保留。订阅正常时WAL在消费后释放，若订阅断开，WAL会持续堆积导致磁盘写满。监控命令：

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

-- active为false的槽表示订阅端未连接，WAL正在堆积
-- 计算WAL堆积大小
SELECT slot_name, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;

-- 如果订阅端永久下线，删除复制槽释放WAL
-- 先删除订阅（会自动删除槽）
DROP SUBSCRIPTION sub_business;

-- 如果订阅已删除但槽残留，手动删除
SELECT pg_drop_replication_slot('sub_business');

-- 监控复制延迟
SELECT now() AS current_time,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS flush_lag
FROM pg_replication_slots
WHERE slot_name = 'sub_business';

对于跨版本迁移场景的完整流程：在目标版本创建表结构 → 建立逻辑复制订阅同步增量数据 → 验证数据一致性 → 在维护窗口切换应用连接到新库 → 删除旧库订阅。整个过程仅切换瞬间有短暂中断，实现了准零停机迁移。迁移完成后，需要同步序列值，因为逻辑复制不复制序列状态：

-- 在新库上同步序列值（取发布端和订阅端的较大值）
SELECT setval('users_id_seq', 
    GREATEST(
        (SELECT MAX(id) FROM users),
        (SELECT last_value FROM users_id_seq)
    ));

这套迁移方案适用于从PostgreSQL 12到16的大版本升级，也适用于从自建PostgreSQL迁移到云数据库（如阿里云RDS for PostgreSQL、AWS RDS）。在数据备份恢复策略中，逻辑复制可以作为物理备份的补充，实现更细粒度的数据同步和实时分析场景的ETL数据管道。