数据库

PostgreSQL 18 逻辑复制实战：零停机迁移、监控、校验与回滚

完整覆盖 Publication、Subscription、复制槽、初始同步、冲突、DDL 与序列、分片校验、切流、回滚窗口和安全清理。

TY
Tycho
技术博主
• 2026-09-28 • 44 分钟阅读 • 2 次浏览
收藏
PostgreSQL 18 逻辑复制实战：零停机迁移、监控、校验与回滚

一、场景与迁移边界

本文用 PostgreSQL 18 原生逻辑复制完成跨集群、低停机迁移：旧集群作为 Publisher，新集群作为 Subscriber；先复制全量快照，再持续应用增量，最终在短暂停写窗口内切换。逻辑复制按表和行传播，不自动复制 DDL、序列、角色、大对象和扩展，因此必须单独管理这些对象。

Application -> PostgreSQL A (publisher)
                 |-- publication
                 |-- logical replication slot
                 `-- WAL decoder
                         |
                         v
              PostgreSQL B (subscriber)
                 |-- initial table copy
                 |-- apply workers
                 `-- validation -> cutover -> rollback window

适用：跨大版本迁移、部分表同步、平台迁移、读副本数据分发。不适用：要求完整实例位级一致、未评估 DDL 变更、缺少主键且高频更新的表。

二、迁移清单与容量基线

先记录版本、数据库大小、表大小、写入速率、WAL 速率、长事务、无主键表、扩展、序列、角色和连接来源。所有命令都保留时间戳输出。

SELECT version();
SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT schemaname, relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

SELECT n.nspname, c.relname
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog','information_schema')
AND NOT EXISTS (
  SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary
);

psql "$SOURCE_DSN" -Atc "select extname, extversion from pg_extension order by 1"
psql "$SOURCE_DSN" -Atc "select rolname from pg_roles order by 1"
pg_dump --schema-only --no-owner --no-privileges "$SOURCE_DSN" > schema.sql
sha256sum schema.sql

为迁移定义 SLO：允许停写时长、最大可接受复制延迟、校验失败策略、回滚窗口和数据负责人。

三、配置 Publisher

Publisher 必须启用逻辑 WAL。max_replication_slots 至少覆盖订阅数和表同步预留；max_wal_senders 还要包含物理副本。参数值应由并发同步表数和现有复制拓扑推导。

# postgresql.conf on source
wal_level = logical
max_replication_slots = 12
max_wal_senders = 16
max_slot_wal_keep_size = '40GB'
wal_sender_timeout = '60s'
track_commit_timestamp = on

psql "$SOURCE_DSN" -c "show wal_level"
psql "$SOURCE_DSN" -c "show max_replication_slots"
psql "$SOURCE_DSN" -c "select pg_reload_conf()"

wal_level 等需要重启的参数必须走维护窗口。max_slot_wal_keep_size 防止离线订阅无限保留 WAL，但达到上限可能使槽失效，必须结合监控和恢复方案。

四、创建最小权限复制账户

不要使用应用超级用户。复制账户需要登录和 REPLICATION，并对发布表具备 SELECT；网络只允许 Subscriber 地址。

CREATE ROLE migrator WITH LOGIN REPLICATION PASSWORD 'replace-via-secret-manager';
GRANT CONNECT ON DATABASE appdb TO migrator;
GRANT USAGE ON SCHEMA app TO migrator;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO migrator;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO migrator;

# pg_hba.conf
hostssl appdb migrator 10.20.30.40/32 scram-sha-256

psql "$SOURCE_DSN" -c "select pg_reload_conf()"
psql "host=source-db dbname=appdb user=migrator sslmode=verify-full" \
  -c 'select current_user, current_database()'

连接串通过 Secret 注入，不能进入 shell history、Git 或监控标签。

五、准备 Subscriber Schema

逻辑复制不会创建表。先导出 schema-only，在目标应用并检查扩展兼容性。表需使用相同全限定名；列按名称匹配，目标可以有额外带默认值的列，但迁移期应避免结构漂移。

pg_dump --schema-only --no-owner --no-privileges \
  --exclude-table-data='app.audit_archive' "$SOURCE_DSN" > schema.sql
psql "$TARGET_DSN" -v ON_ERROR_STOP=1 -f schema.sql
pg_dump --schema-only "$SOURCE_DSN" | sha256sum
pg_dump --schema-only "$TARGET_DSN" | sha256sum

哈希不同不必然是错误，因为版本会改变输出；应使用结构化 diff 检查表、列、类型、约束、索引和默认值。

SELECT table_schema, table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'app'
ORDER BY 1,2,ordinal_position;

六、Replica Identity 与 Publication

先保证 UPDATE 与 DELETE 可定位

UPDATE/DELETE 需要 replica identity，通常是主键。没有合适唯一键时可临时 REPLICA IDENTITY FULL，但会增加 WAL 和匹配成本，应优先补业务主键。

ALTER TABLE app.orders REPLICA IDENTITY USING INDEX orders_pkey;
ALTER TABLE app.legacy_events REPLICA IDENTITY FULL;

CREATE PUBLICATION app_migration_pub
FOR TABLE app.customers, app.orders, app.order_items
WITH (publish = 'insert, update, delete, truncate', publish_generated_columns = stored);

PostgreSQL 18 可控制生成列发布。发布前检查实际范围：

SELECT pubname, schemaname, tablename, attnames, rowfilter
FROM pg_publication_tables
WHERE pubname = 'app_migration_pub'
ORDER BY schemaname, tablename;

若用行过滤或列列表，必须验证 UPDATE 后行进出过滤集合的语义，并保证 replica identity 列包含在发布列中。

七、配置 Subscriber Worker 容量

Subscriber 的 worker 预算要覆盖订阅 apply worker、初始表同步和并行 apply，同时不能耗尽全局 max_worker_processes。

# postgresql.conf on target
max_active_replication_origins = 16
max_logical_replication_workers = 12
max_sync_workers_per_subscription = 4
max_parallel_apply_workers_per_subscription = 4
max_worker_processes = 24
wal_receiver_timeout = '60s'

psql "$TARGET_DSN" -c "select pg_reload_conf()"
psql "$TARGET_DSN" -c "show max_logical_replication_workers"

提高同步并发会加快初始复制，但也增加源端读取、网络、目标写入和索引维护压力，需从 2–4 个 worker 起测。

八、创建 Subscription 并观察初始同步

在目标创建订阅。首次迁移保留 copy_data=true，大事务可使用 PostgreSQL 18 默认并行 streaming；具体设置需结合兼容性和容量测试。

CREATE SUBSCRIPTION app_migration_sub
CONNECTION 'host=source-db port=5432 dbname=appdb user=migrator password=REDACTED sslmode=verify-full'
PUBLICATION app_migration_pub
WITH (
  copy_data = true,
  create_slot = true,
  enabled = true,
  streaming = parallel,
  two_phase = false,
  disable_on_error = true
);

生产中应使用受控密码注入并限制 pg_subscription 查看权限，因为连接信息可能含明文密码。

SELECT subname, pid, relid::regclass, received_lsn, latest_end_lsn,
       latest_end_time
FROM pg_stat_subscription;

SELECT srrelid::regclass, srsubstate, srsublsn
FROM pg_subscription_rel ORDER BY 1;

状态未到 ready 前不要切流。大表同步期间监控源端磁盘、网络、WAL 保留和目标 autovacuum。

九、监控延迟、槽和冲突

Publisher 与 Subscriber 两端同时观测

Publisher 监控复制槽是否活跃及 WAL 积压；Subscriber 监控 apply worker、冲突统计和日志。

-- source
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots WHERE slot_type = 'logical';

-- target
SELECT subname, apply_error_count, sync_error_count,
       stats_reset
FROM pg_stat_subscription_stats;

psql "$TARGET_DSN" -x -c 'select * from pg_stat_subscription'
journalctl -u postgresql --since '-15 min' | rg 'logical replication|conflict|ERROR'

告警至少覆盖：订阅无 worker、延迟超阈值、槽 WAL 积压、apply error 增长、磁盘余量不足、初始同步长时间无进展。

十、DDL、序列和大对象同步策略

DDL 必须双写执行或由迁移工具在两端按兼容顺序执行。推荐 expand/contract：先两端添加可空列或兼容对象，应用双读写，确认后再移除旧结构。

-- expand on both clusters
ALTER TABLE app.orders ADD COLUMN fulfillment_state text;
-- deploy compatible application, backfill, validate
-- contract only after cutover and rollback window
ALTER TABLE app.orders ALTER COLUMN fulfillment_state SET NOT NULL;

序列不通过逻辑复制自动推进。切换前停写后，把目标序列设置为源端最大值与表最大主键的较大者。

SELECT setval(
  'app.orders_id_seq',
  GREATEST(
    (SELECT last_value FROM app.orders_id_seq),
    (SELECT COALESCE(max(id), 1) FROM app.orders)
  ),
  true
);

角色、授权、扩展和大对象也要独立清单验证。

十一、数据校验：行数不够

先按表比较行数和关键聚合，再分区间计算确定性摘要。复制仍在进行时结果会变化，因此最终校验必须在停写且延迟归零后执行。

SELECT count(*) AS rows, min(id), max(id),
       sum(total_cents) AS amount_sum,
       max(updated_at) AS newest
FROM app.orders;

for start in 1 100001 200001; do
  end=$((start + 99999))
  psql "$SOURCE_DSN" -Atc "select md5(string_agg(md5(row(id,status,total_cents,updated_at)::text),'') order by id) from app.orders where id between $start and $end"
  psql "$TARGET_DSN" -Atc "select md5(string_agg(md5(row(id,status,total_cents,updated_at)::text),'') order by id) from app.orders where id between $start and $end"
done

大型表避免一次 string_agg 占用过多内存，应按主键范围或时间分片，并保存每片结果。

十二、切换步骤与回滚窗口

停写窗口的执行顺序

  1. 冻结 DDL，暂停后台批任务。
  2. 应用进入停写模式，保留读流量。
  3. 等待源端活跃写事务归零。
  4. 等待订阅 apply 到最新 LSN，校验关键表和序列。
  5. 将应用连接切到目标，先小流量验证再全量。
  6. 保留源库只读和复制证据直到回滚窗口结束。
-- source: observe active transactions
SELECT pid, usename, xact_start, query
FROM pg_stat_activity
WHERE datname = current_database() AND xact_start IS NOT NULL
ORDER BY xact_start;

./maintenance enable --reason database-cutover
./wait-replication-lag --subscription app_migration_sub --max-bytes 0 --timeout 600
./validate-critical-tables --source "$SOURCE_DSN" --target "$TARGET_DSN"
./switch-secret database-primary target
./smoke-test --base-url https://app.example.com

回滚只在目标尚未产生不可逆独立写入时简单。若目标已接受写入，直接切回旧库会丢数据；需要反向复制或业务补偿方案。切换前必须明确回滚截止点。

十三、冲突处理与清理

唯一键冲突会停止复制。先保存日志中的远端事务 LSN、冲突行和来源，再决定保留本地还是远端数据。ALTER SUBSCRIPTION ... SKIP 会跳过整个事务，可能遗漏同事务内其他正常变更，只能作为审计后的最后手段。

ALTER SUBSCRIPTION app_migration_sub DISABLE;
-- repair target data or permissions
ALTER SUBSCRIPTION app_migration_sub ENABLE;

-- only after approval and impact analysis
ALTER SUBSCRIPTION app_migration_sub SKIP (lsn = '0/14C0378');

迁移确认完成后，先停订阅并确认不再需要回滚，再删除订阅和 publication；删除前检查槽，避免遗留 WAL。

ALTER SUBSCRIPTION app_migration_sub DISABLE;
DROP SUBSCRIPTION app_migration_sub;
DROP PUBLICATION app_migration_pub;
SELECT slot_name, active FROM pg_replication_slots;

十四、官方资料与验收结果

  • PostgreSQL 18 Logical Replication：Publication、Subscription、架构、监控与安全。
  • PostgreSQL 18 Configuration Settings：publisher/subscriber worker 参数。
  • PostgreSQL 18 Conflicts：冲突类别、日志、统计和 SKIP 风险。
  • PostgreSQL 18 CREATE SUBSCRIPTION：copy、slot、streaming、origin 与权限。

合格结果应包括：全量表同步完成；增量延迟有告警；Schema、序列、角色和扩展有清单；停写后关键数据摘要一致；新集群性能满足 SLO；回滚截止点和负责人明确；清理复制槽前已过回滚窗口。

标签：
PostgreSQL 逻辑复制 数据库迁移 高可用
TY

Tycho

热爱分享技术知识，帮助开发者成长。

评论 (0)

评论功能当前已关闭
暂无评论，快来抢沙发吧！