使用逻辑复制实现接近零停机的大版本升级

命令

# 0) On the old version wal_level = logical (restart); every table needs a PRIMARY KEY or REPLICA IDENTITY
# 1) Move roles and schema to the new version (a 'role postgres already exists' error is expected):
pg_dumpall -h <old_host> --globals-only | psql -h <new_host> -X -d postgres
pg_dump -h <old_host> --schema-only --create -d <db> | psql -h <new_host> -X -d postgres
# 2) Publication on the old version, subscription on the new version (initial copy + continuous stream):
psql -h <old_host> -d <db> -X -c "CREATE PUBLICATION upg_pub FOR ALL TABLES;"
psql -h <new_host> -d <db> -X -c "CREATE SUBSCRIPTION upg_sub CONNECTION 'host=<old_host> dbname=<db> user=<repl_user>' PUBLICATION upg_pub;"
# 3) Wait until all tables are 'r' (ready) and lag is ~0:
psql -h <new_host> -d <db> -X -c "SELECT srsubstate, count(*) FROM pg_subscription_rel GROUP BY 1;"
psql -h <old_host> -d <db> -X -c "SELECT slot_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag FROM pg_replication_slots WHERE slot_name = 'upg_sub';"
# 4) Cutover: stop application writes, confirm lag = 0, synchronize sequences (logical-sync-sequences)
# 5) Drop the subscription (the remote slot is dropped too), point the application to the new server:
psql -h <new_host> -d <db> -X -c "DROP SUBSCRIPTION upg_sub;"

作用

在新版本上构建一个空集群，迁移结构，并通过逻辑复制持续从旧版本传输数据；只有最后的切换步骤（停止写入、同步序列、重定向）会造成短暂中断。反方向的第二条复制可以保留回退路径。

适用场景

当 pg_upgrade 所需的停机不可接受、同时迁移到新硬件/操作系统，或跨越多个大版本时。

注意

DDL、序列、大对象和物化视图内容不会被复制；迁移期间请冻结结构。在没有主键/replica identity 的表上，UPDATE/DELETE 在发布端会失败。在大型数据库上，初始复制耗时较长，并会在旧服务器上占用 WAL 和 catalog_xmin。每个数据库都需要自己的发布/订阅。切换前比对行数/校验和。