PostgreSQL逻辑复制配置与多活架构搭建实战

PostgreSQL逻辑复制（Logical Replication）通过解析WAL日志中的逻辑变更事件，将数据变更以行级粒度复制到目标库。与物理复制（Streaming Replication）不同，逻辑复制支持跨版本、跨平台、选择性表复制和双向复制。数据库运维中，逻辑复制适用于数据迁移、读写分离、多活架构和ETL数据同步等场景。

逻辑复制原理与WAL日志解析机制

PostgreSQL的WAL（Write-Ahead Log）记录所有数据变更操作。物理复制直接传输WAL字节流，目标库重放WAL恢复数据，要求主从版本一致、块结构一致。逻辑复制将WAL日志解码为逻辑变更事件（INSERT/UPDATE/DELETE），通过发布订阅模型传输，目标库执行等价SQL操作。

-- 逻辑复制前置条件：WAL级别必须为logical
SHOW wal_level;
-- 如果不是logical，需修改postgresql.conf
-- wal_level = logical
-- max_wal_senders = 10
-- max_replication_slots = 10

-- 修改后重启PostgreSQL
-- systemctl restart postgresql

-- 确认配置生效
SHOW wal_level;  -- 应输出 logical
SHOW max_replication_slots;
SHOW max_wal_senders;

-- 创建逻辑复制槽
SELECT pg_create_logical_replication_slot('my_slot', 'pgoutput');

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

-- 逻辑解码查看变更事件（调试用）
SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL);

-- 删除复制槽
SELECT pg_drop_replication_slot('my_slot');

逻辑复制依赖复制槽（Replication Slot）管理WAL保留。复制槽记录消费进度（LSN），确保主库不会在从库未接收前清理WAL日志。逻辑复制槽的输出插件将WAL解码为逻辑格式，PostgreSQL内置pgoutput插件，第三方插件如test_decoding用于调试。

发布订阅模型配置与Publication/Subscription

PostgreSQL逻辑复制采用发布订阅模型。发布端创建Publication定义要复制的表和操作类型，订阅端创建Subscription连接到Publication并开始同步数据。

-- ========== 发布端（主库） ==========

-- 创建发布：复制所有表
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- 创建发布：指定表
CREATE PUBLICATION my_publication FOR TABLE
  users,
  orders,
  products;

-- 创建发布：指定操作类型
CREATE PUBLICATION my_publication FOR TABLE users, orders
  WITH (publish = 'insert, update');  -- 不发布DELETE

-- 查看发布信息
SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

-- 发布的表必须有REPLICA IDENTITY
ALTER TABLE products REPLICA IDENTITY FULL;
-- REPLICA IDENTITY选项：
-- DEFAULT - 使用主键（默认）
-- FULL    - 使用所有列（无主键表必须设置）
-- NOTHING - 不复制UPDATE/DELETE（仅复制INSERT）
-- USING INDEX - 使用指定索引列
ALTER TABLE products REPLICA IDENTITY USING INDEX products_idx;

-- ========== 订阅端（从库） ==========

-- 创建订阅
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=192.168.1.10 port=5432 dbname=mydb user=replicator password=xxx'
  PUBLICATION my_publication;

-- 创建订阅时指定参数
CREATE SUBSCRIPTION my_subscription
  CONNECTION 'host=192.168.1.10 port=5432 dbname=mydb user=replicator password=xxx'
  PUBLICATION my_publication
  WITH (
    copy_data = true,
    create_slot = true,
    enabled = true,
    slot_name = 'my_sub_slot',
    synchronous_commit = off
  );

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

初始数据同步（copy_data = true）通过COPY命令将发布表的全量数据传输到订阅端，完成后切换到WAL增量同步模式。全量同步期间表会被锁定读取快照，大表同步可能耗时较长。

双向复制与冲突解决策略

多活架构要求双向复制：A库和B库互为发布端和订阅端，任一库写入变更同步到对方。双向复制的核心挑战是数据冲突——同一行在两端同时被修改。

-- ========== 双向复制配置 ==========

-- A库（192.168.1.10）创建发布
CREATE PUBLICATION pub_a FOR TABLE users, orders;

-- B库（192.168.1.20）创建发布
CREATE PUBLICATION pub_b FOR TABLE users, orders;

-- A库订阅B库
CREATE SUBSCRIPTION sub_b_on_a
  CONNECTION 'host=192.168.1.20 port=5432 dbname=mydb user=replicator password=xxx'
  PUBLICATION pub_b;

-- B库订阅A库
CREATE SUBSCRIPTION sub_a_on_b
  CONNECTION 'host=192.168.1.10 port=5432 dbname=mydb user=replicator password=xxx'
  PUBLICATION pub_a;

-- PostgreSQL自动识别origin，避免循环复制
-- 但UPDATE/DELETE可能产生冲突

-- 冲突解决：触发器方式
CREATE OR REPLACE FUNCTION resolve_conflict()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.updated_at >= OLD.updated_at THEN
    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER conflict_resolver
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION resolve_conflict();

双向复制的冲突类型包括：UPDATE-UPDATE冲突（两端同时修改同一行）、UPDATE-DELETE冲突（一端更新、一端删除）、INSERT冲突（两端插入相同主键）。PostgreSQL不内置自动冲突解决机制，需通过应用层或触发器处理。常见策略：基于时间戳的Last-Write-Wins（LWW）、业务字段优先级、应用层显式处理。

逻辑复制槽管理与复制延迟监控

复制槽管理是逻辑复制运维的重点。如果订阅端长时间断开，复制槽会阻止主库清理WAL日志，可能导致主库磁盘写满。需设置max_slot_wal_keep_size限制复制槽保留的WAL大小。

-- 主库配置：限制复制槽WAL保留量
-- postgresql.conf
-- max_slot_wal_keep_size = 10GB  -- 超过后复制槽变为inactive

-- 监控复制延迟
SELECT
  s.slot_name,
  s.active,
  pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn) AS lag_bytes,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn)) AS lag_size,
  s.wal_status,
  s.confirmed_flush_lsn
FROM pg_replication_slots s;

-- 订阅端监控同步状态
SELECT
  subname,
  received_lsn,
  latest_end_lsn,
  pg_wal_lsn_diff(latest_end_lsn, received_lsn) AS lag_bytes,
  last_msg_send_time,
  last_msg_receipt_time,
  now() - last_msg_receipt_time AS receive_lag
FROM pg_stat_subscription;

-- 监控脚本：每分钟检查复制延迟
SELECT
  slot_name,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag,
  wal_status,
  CASE
    WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 10737418240 THEN 'CRITICAL'
    WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824 THEN 'WARNING'
    ELSE 'OK'
  END AS status
FROM pg_replication_slots
WHERE slot_type = 'logical';

逻辑复制不支持DDL变更自动同步。表结构变更（ALTER TABLE）需在发布端和订阅端分别执行。在数据迁移实战中，通常先在目标端创建表结构，再建立订阅进行数据同步，最后切换应用连接。对于跨大版本迁移（如PG 12迁移到PG 16），逻辑复制是推荐方案——目标库版本可高于源库，兼容性由逻辑复制的SQL执行层保证。