实操 | 使用内置逻辑复制功能进行“PG迁移/大版本升级”

本期播客

实操 | 使用内置逻辑复制功能进行“PG迁移/大版本升级”

PG 已内置逻辑复制功能多年, 不管是同步性能还是使用丝滑程度都越来越棒了, 其实除了跨库的数据同步, 也可用于对PG进行数据库的迁移、大版本升级场景, 可极大的缩短业务停机窗口.

下面就翻译一篇来自crunchydata的文章, 看看如何使用内置逻辑复制功能进行“PG迁移/大版本升级”?

https://www.crunchydata.com/blog/postgres-migrations-using-logical-replication

使用内置逻辑复制功能进行“PG迁移/大版本升级”实操

迁移 Postgres 数据库并非易事。通常，对于 Postgres 用户来说，这是最大的项目之一。如果您要迁移到新的 Postgres 大版本(例如11到18)，或者迁移到一个全新的平台或主机(例如x86到arm)，您有以下几种选择：

• 使用 pg_dump 和 pg_restore：pg_dump 是一种非常可靠的方法，可以备份整个数据库并将其还原到新位置。这包括整个schema、所有表以及特殊的数据库对象。如果您要迁移一个小型数据库，例如 50、100 或 150GB，这可能是最简单的方法。在现代硬件上，使用此方法的转储和还原可以在不到一小时内完成。

• 使用 WAL：如果您拥有基于 WAL 的备份系统（例如 pgBackRest 或 WAL-G/E），您可以通过运行完整的基础备份并将 WAL 流式传输到新主机来进行 Postgres 的大规模迁移(当大版本不变, 并且CPU架构相同时可以这么操作)。当您准备好切换到新数据库时，您已经拥有一个同步的副本。对于大型数据库（TB 级）来说，这是一种以最少的停机时间进行大规模迁移的好方法。

但是，如果您的数据库太大，无法进行转储还原，并且您无法承受停机时间，但您又无法访问 WAL（例如，您在 RDS 等不共享 WAL 的主机上）。该怎么办？或者如开头说的, 您需要大版本升级、或者需要CPU异构迁移. 还有第三种选择：

• 逻辑迁移：使用 Postgres 逻辑迁移，您可以在新位置设置数据库副本。虽然 WAL 包含所有内容，但逻辑复制仅捕获数据，不会迁移schema、索引、序列和其他一些繁琐的部分。不过，借助本文中的一些技巧，您可以使用逻辑复制捕获所有内容，从而实现完整迁移。

逻辑复制的架构非常简单，如果您是新手，请参阅我们关于“数据迁移：Postgres 逻辑复制”的介绍文章( https://www.crunchydata.com/blog/data-to-go-postgres-logical-replication )。您现有的数据库将是publisher，接收数据库将是subscriber。在初始加载过程中，所有数据都会从发布服务器复制到订阅服务器。初始数据复制完成后，发布服务器端进行的任何事务都将发送给订阅服务器。

步骤 1：迁移 schema

逻辑复制仅复制数据更改（INSERT、UPDATE、DELETE），因此您必须事先确保目标数据库具有正确的schema。要获取源数据库的仅schema转储并将其应用于数据库，请运行以下命令：

pg_dump -Fc -s $SOURCE_DB_URI | pg_restore --no-acl --no-owner -d $TARGET_DB_URI

注意目前还不支持DDL变更的增量同步, 这个需要借助事件触发器, 参考:

• 《DB吐槽大会,第33期 - PG 逻辑复制不支持DDL》
• 《PostgreSQL 逻辑订阅 - DDL 订阅 实现方法》

如果在应用程序开发继续进行的同时进行迁移过程，则必须确保在对源数据库进行任何schema更改前, 更新接收数据库的schema, 否则会导致订阅端的报错。

步骤 2：发布端（当前主机）设置

逻辑复制通过 wal_level 设置启用。某些托管 Postgres 服务可能希望启用此功能。 配置 wal_level = logical, 检查复制插槽设置，确保资源充足。对于非常大型的复制项目，可能需要更改以下参数的默认值。

max_replication_slots  
max_wal_senders  
max_logical_replication_workers  
max_worker_processes  
max_sync_workers_per_subscription  

有关如何设置这些参数的详细信息，请参阅 PostgreSQL 章节中的逻辑复制配置设置。( https://www.postgresql.org/docs/current/logical-replication-config.html )

联网

确保旧数据库的网络/防火墙允许新数据库的连接。

新建数据库用户, 用于订阅数据

您可以为此目的创建一个具有REPLICATION角色属性的特定用户。同时，请确保新角色对正在复制的表具有读取权限。

CREATEROLE elizabeth WITHREPLICATION LOGIN PASSWORD'my_password';  
GRANTSELECTONALLTABLESINSCHEMApublicTO elizabeth;  

查找没有主键 或 UNIQUE 索引的表

对于逻辑复制，Postgres 需要一种唯一标识要更新/删除的行的方法。对于具有主键的表，将使用该主键，因此首先要识别缺少主键的表：

select tab.table_schema,  
       tab.table_name  
from information_schema.tables tab  
leftjoin information_schema.table_constraints tco  
on tab.table_schema = tco.table_schema  
and tab.table_name = tco.table_name  
and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
and tab.table_schema notin ('pg_catalog', 'information_schema')  
and tco.constraint_name isnull
orderby table_schema,  
         table_name;  

对于没有主键的表，可以使用任何UNIQUE索引：

ALTERTABLE tablename REPLICA IDENTITYUSINGINDEX idx_some_unique_index;  

如果没有UNIQUE索引，则可以创建一个，或者可以设置表REPLICA IDENTITY FULL - 在这种情况下，它将每一行视为自己的“键”：

ALTERTABLE tablename REPLICA IDENTITYFULL;  

# 注意, 非常不推荐这么做, 将产生大量WAL日志.  

接下来创建一个发布，它是一组要复制的表。大多数情况下，您需要为所有表创建一个发布：

CREATE PUBLICATION bridge_migration FORALLTABLES;  

检查您的表格是否已准备好发布，所有表格都应列在此处。

SELECT * FROM pg_publication_tables;  

步骤 3：订阅端(新主机)设置

在订阅端, 为每个发布创建一个订阅, 用于接收来自发布端包含的表的所有全量和增量数据.

CREATE SUBSCRIPTION bridge_migration CONNECTION'host={host} port=5432 dbname={datatbase} user={login} password={password}' PUBLICATION bridge_migration;  

以这种方式创建订阅将在发布服务器上创建一个replication slot，并开始从发布中指定的表复制数据。在初始数据同步复制期间，将为每个表创建一个单独的临时slot。

您可以通过设置max_sync_workers_per_subscription参数来限制一次同步的表的数量。 (并行的全量同步数, 在性能和带宽消耗之间取得平衡)

步骤 4：监控初始数据拷贝过程

数据全量同步开始后, 您可能想要监控此初始数据拷贝过程。pg_stat_subscription表将显示订阅端的数据：

select * from pg_stat_subscription;  

-[ RECORD 1 ]---------+------------------------------  
subid                 | 27183  
subname               | bridge_migration  
worker_type           | table synchronization  
pid                   | 1197139  
leader_pid            |  
relid                 | 26721  
received_lsn          |  
last_msg_send_time    | 2025-09-26 15:54:45.095215+00  
last_msg_receipt_time | 2025-09-26 15:54:45.095215+00  
latest_end_lsn        |  
latest_end_time       | 2025-09-26 15:54:45.095215+00  
-[ RECORD 2 ]---------+------------------------------  
subid                 | 27183  
subname               | bridge_migration  
worker_type           | apply  
pid                   | 47075  
leader_pid            |  
relid                 |  
received_lsn          | 4E32/7092F6F8  
last_msg_send_time    | 2025-09-26 15:55:11.020012+00  
last_msg_receipt_time | 2025-09-26 15:55:11.021989+00  
latest_end_lsn        | 4E32/7092F3E0  
latest_end_time       | 2025-09-26 15:55:10.843251+00  

您还可以查看 pg_subscription_rel 视图来了解每个表的同步状态select * from pg_subscription_rel;。

在这里，state_code可以告诉你有关每个对象的信息：

• d - 数据正在被复制
• f - 完成表复制
• s - 同步
• r - 就绪（正常复制）

由于表膨胀以及其他与内部表统计信息相关的因素，您将无法比较表的大小。不过，您可以使用 select count(*) 来比较记录数是否在一个量级(因为发布端可能还有DML, 记录数不一定实时一致)。

步骤 5：测试和切换

现在，您可以开始针对新数据库测试您的应用程序。确认所有数据均已存在后，即可进行迁移切换。这需要停止原始主机上的事务， 修复序列 ，并将应用程序指向新数据库。

步骤 6：修复序列

虽然逻辑复制会从源数据库复制所有数据，但它不会更新任何序列。因此，我们建议您在切换后、开始生产操作之前更新序列。修复序列的最佳方法是为源数据库中的所有序列创建 setval 命令，您可以使用以下查询执行此操作：

似乎PG 15版本开始已支持序列的发布和同步.

SELECT
'SELECT setval(' || quote_literal(quote_ident(n.nspname) || '.' || quote_ident(c.relname)) || ', ' || s.last_value || ');'
FROM
    pg_class c  
JOIN pg_namespace n ON n.oid = c.relnamespace  
JOIN pg_sequences s ON s.schemaname = n.nspname  
AND s.sequencename = c.relname  
WHERE
    c.relkind = 'S';  

生成的输出文件可以在新主机上执行，以同步所有序列。

最后的想法

逻辑复制是一种安全有效的迁移策略。只要订阅服务器的schema和发布端相同，且复制是单向的，且发布端在期间未发生DDL操作(可控, 未来版本也许能支持DDL的增量同步), 且订阅服务器上不存在写入冲突，就能确保复制表的数据一致性。