PostgreSQL逻辑复制（Logical Replication）从10版本开始原生支持，通过解码WAL日志生成逻辑变更事件，实现选择性表复制和跨版本数据同步。与物理复制不同，逻辑复制接收端可写、支持异构架构，适用于读写分离、数据分发和在线迁移等场景。

逻辑复制与物理复制的架构差异

物理复制在块级别同步WAL日志，备库是主库的字节级镜像，只读不可写。逻辑复制在逻辑层面解码变更（INSERT/UPDATE/DELETE），订阅端是独立可写的数据库，可选择性复制部分表。

-- 发布端配置：postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
wal_sender_timeout = 60s

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

-- pg_hba.conf 允许复制连接
-- host    replication     replicator     192.168.1.0/24    md5

-- 创建发布（Publication）
CREATE PUBLICATION app_publication
    FOR TABLE users, orders, order_items
    WITH (publish = 'insert, update, delete, truncate');

-- 复制所有表的变更
-- CREATE PUBLICATION all_tables FOR ALL TABLES;

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

-- 修改发布
ALTER PUBLICATION app_publication ADD TABLE products;
ALTER PUBLICATION app_publication SET (publish = 'insert, update');

订阅端配置与初始数据同步

订阅端创建Subscription连接发布端，自动创建复制槽并开始同步。初始数据通过COPY命令批量传输，同步完成后切换为增量WAL解码。

-- 订阅端：创建与发布端结构相同的表
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id),
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

-- 创建订阅
CREATE SUBSCRIPTION app_subscription
    CONNECTION 'host=192.168.1.10 port=5432 user=replicator password=StrongPass123! dbname=appdb'
    PUBLICATION app_publication
    WITH (
        copy_data = true,
        create_slot = true,
        slot_name = 'app_slot',
        enabled = true,
        synchronous_commit = off
    );

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

-- 监控同步延迟
SELECT
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    latest_end_time,
    now() - latest_end_time AS replication_lag
FROM pg_stat_subscription;

冲突处理与数据一致性保障

逻辑复制在订阅端执行SQL时可能遇到约束冲突（主键重复、外键违反等）。冲突发生时复制中断，需要人工介入处理。

-- 查看复制 worker 日志
-- ERROR: duplicate key value violates unique constraint "users_pkey"
-- DETAIL: Key (id)=(1001) already exists.

-- 冲突处理方案1：跳过冲突事务
ALTER SUBSCRIPTION app_subscription DISABLE;
SELECT * FROM pg_stat_subscription;
-- 等待 worker 停止

-- 手动推进复制位置跳过冲突LSN
SELECT pg_replication_origin_advance('pg_16384', '0/1A0000A0');
ALTER SUBSCRIPTION app_subscription ENABLE;

-- 冲突处理方案2：在订阅端创建触发器，冲突时跳过
CREATE OR REPLACE FUNCTION skip_duplicate_insert()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        IF EXISTS (SELECT 1 FROM users WHERE id = NEW.id) THEN
            RETURN NULL;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER skip_dup_users
    BEFORE INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION skip_duplicate_insert();
-- 注意：触发器方案会让订阅端数据偏离发布端，仅适用于容忍最终一致性的场景

多活架构与双向复制配置

双向逻辑复制实现两个数据库互为发布端和订阅端，支持Active-Active多活架构。核心难点是防止循环复制——A库的变更同步到B库后，不应再从B库同步回A库。

-- 节点A (192.168.1.10) 配置
CREATE PUBLICATION node_a_pub
    FOR TABLE users, orders
    WITH (publish = 'insert, update, delete');

CREATE SUBSCRIPTION node_a_sub
    CONNECTION 'host=192.168.1.20 port=5432 user=replicator password=StrongPass123! dbname=appdb'
    PUBLICATION node_b_pub
    WITH (copy_data = false, create_slot = true, enabled = true);

-- 节点B (192.168.1.20) 配置
CREATE PUBLICATION node_b_pub
    FOR TABLE users, orders
    WITH (publish = 'insert, update, delete');

CREATE SUBSCRIPTION node_b_sub
    CONNECTION 'host=192.168.1.10 port=5432 user=replicator password=StrongPass123! dbname=appdb'
    PUBLICATION node_a_pub
    WITH (copy_data = false, create_slot = true, enabled = true);

-- PostgreSQL 14+原生支持origin追踪，逻辑复制自动防止循环
-- 应用层通过设置 application_name 区分来源
SET application_name = 'node_a_app';

双向复制的冲突解决比单向更复杂。同一条记录在两个节点同时修改会产生Update-Update冲突。常见解决策略：

-- 基于时间戳的Last-Write-Wins策略
ALTER TABLE users ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();
ALTER TABLE users ADD COLUMN origin_node VARCHAR(10);

CREATE OR REPLACE FUNCTION set_update_metadata()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    NEW.origin_node = current_setting('application_name');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_metadata
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION set_update_metadata();
-- 收到来自另一节点的UPDATE时比较updated_at
-- 本地记录较新则拒绝应用远程变更

复制槽管理与WAL积压排查

复制槽（Replication Slot）确保发布端在订阅端接收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;

-- 输出示例：
-- slot_name   | active | lag_bytes
-- app_slot    | t      | 12345678
-- backup_slot | f      | 5432109876  <- 非活跃槽积压5GB

-- 计算WAL积压总量
SELECT pg_size_pretty(sum(pg_wal_lsn_diff(
    pg_current_wal_lsn(), restart_lsn
))) AS total_lag
FROM pg_replication_slots;

-- 清理无用的复制槽
SELECT pg_drop_replication_slot('backup_slot');

-- 设置WAL保留大小上限防止磁盘溢出
-- postgresql.conf: max_slot_wal_keep_size = 10GB

-- 监控脚本：检查积压超阈值的槽
SELECT
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824 AS over_1gb
FROM pg_replication_slots
WHERE NOT active
   OR pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824;

PostgreSQL 17增强的逻辑复制支持并行数据同步，大表初始同步吞吐量提升3-5倍。生产环境中，逻辑复制链路的监控应覆盖三个维度：复制延迟（秒）、WAL积压量（字节）和冲突次数。延迟超过30秒或积压超过1GB时应触发告警，以便及时排查订阅端性能瓶颈或网络问题。