Hotdry.
归档
general

postgresql instance table migration guide

PostgreSQL 实例间表迁移的零停机实践：逻辑复制、双写切换与一致性校验的工程方案

引言：为什么跨实例表迁移在生产环境中如此棘手

在生产环境里，把一张或一组表从源实例迁移到目标实例，从来不是 “导出 - 导入” 这么简单。真正的难点在于：如何在保证业务连续性的前提下，做到数据不丢失、读写不中断、模式 (DDL) 变更安全回滚，以及迁移后的性能与可靠性不打折。本文聚焦 PostgreSQL 跨实例的表级 / 数据库级迁移，从架构方法、零停机流程、逻辑复制配置、数据一致性校验、监控告警到回滚策略，给出一套可落地的工程实践路径。

我们采用 “最小依赖信息源” 的原则组织论证：一是以官方能力为基石，明确逻辑复制 (Logical Replication) 的能力边界与前提；二是以实践案例为抓手，总结零停机 (Zero Downtime) 迁移的分阶段策略、权衡点与风险控制。核心思想是 “先扩后缩”(Expand/Contract): 通过双写与双读过渡，逐步完成语义上的原子切换，再清理旧路径，从而把一次性重构的风险分散到可控的阶段中。12

场景建模与约束：从需求到可行路径的映射

表级与数据库级迁移的差异主要在粒度与依赖处理。表级迁移意味着需要单独处理主键、外键、索引、触发器、视图、权限与序列等依赖对象；数据库级迁移则相对 “打包”, 但对实例参数、网络、鉴权与发布 / 订阅 (Publication/Subscription) 提出更整体的要求。

常见触发场景包括：版本升级、操作系统与硬件变更、云厂商或账号迁移、水平 / 垂直拆分以及架构升级。每一类场景背后都有关键前提，比如 PostgreSQL 的逻辑复制要求每个被复制的表都有主键或唯一约束，wal_level 需设置为 logical, 以及 WAL 保留窗口必须覆盖全量迁移周期；否则可能出现增量变更无法解析或数据丢失。13

为便于决策，以下表 1 梳理典型迁移场景与关键前提:

表 1 迁移场景与关键前提一览

迁移场景 主要驱动 适用粒度 关键前提与限制 建议方法
主版本升级 兼容新特性 / 性能优化 数据库 / Schema 评估 pg_dump/pg_restore 窗口；业务能否容忍停机 离线 dump/restore 或 DMS 在线升级，视窗口而定4
跨云迁移 成本 / 合规 / 多云策略 数据库 / 表 网络连通、WAL 保留、参数对齐 逻辑复制优先，必要时混合应用层双写15
水平拆分 扩展性 / 热点分散 表 (按分片键) 分片键选择、重分布期间的读写语义 应用层双写 + 最终一致性，分片切换5
垂直拆分 降耦合 / 隔离热点 数据库 / Schema 跨库依赖清理、事务边界调整 物理复制或逻辑复制结合业务迁移5
硬件 / 操作系统升级 生命周期 / 性能 数据库 / Schema wal_level=logical (若增量)、参数一致 在线迁移优先，离线作兜底14
云厂商内迁移 账号 / 地域 / 规格调整 数据库 / 表 DMS 前提、连接地址稳定 DMS 逻辑复制或自定义逻辑复制43

这些前提直接影响迁移方案：是否需要逻辑复制、是否需要应用层双写、切换窗口如何设计、回滚路径是否清晰。

方案谱系与架构权衡：物理复制、逻辑复制与应用层复制

在 PostgreSQL 中，跨实例迁移主要有三条路线：物理复制、逻辑复制与应用层复制。它们在粒度、能力与工程复杂度上的权衡截然不同。

表 2 三种迁移方案对比

方案 迁移粒度 零停机支持 DDL 变更支持 实施复杂度 适用场景 关键风险
物理复制 实例 / 库级别 (集群级) 仅短暂不可用 弱，面向 WAL 的块级复制 中 垂直拆分、整体迁移 切换窗口内写入丢失风险，粒度粗5
逻辑复制 表 / 数据库级别 强 (可在线) 强 (可订阅 DDL) 中 跨实例 / 跨云，异构升级 需主键、wal_level=logical、WAL 保留1
应用层复制 任意 (按业务) 强 (完全可控) 强 (业务控制) 高 复杂语义迁移、分片重分布 双写一致性、幂等、回滚复杂65

物理复制适合 “整体搬迁”, 但粒度粗，切换窗口的写入处理尤为关键；逻辑复制则以表为单位，天然支持 DDL 与数据变更的在线同步，是云上迁移与跨实例同步的首选；应用层复制最灵活，但需要业务投入更多基础设施来确保幂等与一致性。15

物理复制 (流复制) 的迁移模式与限制

基于 WAL (Write-Ahead Log) 的流式复制可以快速拉起一个从库，随后通过 “改读 - 改写 - 提升” 完成切换：先把读流量切到从库，再将从库提升为主库，同时拦截源端写入，最终清理不需要的对象。粒度粗是它的核心限制，更适合垂直拆分等 “整库或整实例” 迁移。对于大表迁移，物理复制的 “整体切换” 窗口短，但在 “改写” 阶段一旦失败回滚成本较高。5

逻辑复制 (Publication/Subscription) 能力与前提

PostgreSQL 10 之后的逻辑复制支持表级与数据库级订阅 / 发布。要启用它，通常需要：设置 wal_level=logical、为迁移表建立主键或唯一约束、创建具备复制权限的用户、开放 pg_hba.conf 并建立发布 (Publication) 与订阅 (Subscription)。此外，需规划 WAL 保留窗口，确保增量变更在迁移周期内可被读取；迁移过程中会创建临时对象 (如复制槽、心跳表), 需要明确清理策略。13

为便于落地，表 3 整理逻辑复制配置与运维要点:

表 3 逻辑复制配置清单

项目 源端要求 目标端要求 运维要点
版本与权限 PostgreSQL 10+; 复制用户 (REPLICATION) 版本尽量对齐 (低→高更稳妥) 验证连接、权限与白名单
wal_level 必须为 logical N/A 需重启生效，提前规划窗口13
主键 / 唯一约束 每个被复制表必须具备 N/A 缺失则先去重或建主键1
Publication CREATE PUBLICATION ... FOR TABLE/SCHEMA N/A DDL 同步需启用 DDL 订阅1
Subscription N/A CREATE SUBSCRIPTION ... CONNECTION ... PUBLICATION ... 监控延迟、断点续传1
WAL 保留 ≥24h (增量),≥7 天 (全量 + 增量) N/A 监控磁盘占用与清理3
临时对象 dts_* 心跳表、复制槽等 N/A 任务释放后自动 / 手动清理3

应用层复制的七步零停机迁移法

在复杂语义迁移 (例如分片重分布、模式变更) 中，应用层双写与最终一致性是最稳妥的通用模式。其标准流程如下：创建新表→双写→分批复制历史数据→一致性校验→切换读取→停止写入旧表→清理。迁移过程中避免长锁，历史数据复制采用小批次 (例如 LIMIT 1000), 对每批次计算校验和，确保增量同步与回滚路径清晰。65

表 4 七步迁移法步骤清单与关键检查点

步骤 核心动作 关键检查点 回滚触发
1. 创建新表 目标结构就绪 (索引 / 约束) 与旧表结构映射关系明确 不涉及
2. 双写 应用同时写入新旧表 幂等处理、失败重试、冲突策略 停双写、回滚至单写旧表6
3. 分批复制 历史数据按块迁移 (如 LIMIT 1000) 批次校验和、进度监控 迁移中断可续传65
4. 一致性校验 FULL OUTER JOIN 差异检查 缺失记录与内容不一致检测 校验失败停止切换
5. 切换读 应用逐步切换到新表读 监控延迟与错误率 切回旧读路径
6. 停写旧表 停止双写，仅写新表 短暂窗口内拦截旧写 恢复双写，延迟切换
7. 清理旧表 删除旧表与相关依赖 依赖对象 (视图 / 触发器) 更新完成 保留旧表一段时间兜底

零停机迁移实施手册：从准备到上线

零停机的关键在于 “准备充分、切换可控、监控到位”。这不仅是把步骤走完，更是让每一步都有可验证的里程碑与回退路径。

迁移前准备：架构、参数与一致性基线

首先必须建立可回滚的架构：应用支持读路径切换、双写逻辑、幂等写入与失败重试；其次对齐源端与目标端的 PostgreSQL 参数 (pg_settings), 避免因参数差异导致行为不一致；最后，为每个迁移表确认主键 / 唯一约束，这是逻辑复制与一致性校验的基础。对于大表，设计分片键与分批策略，提前演练校验算法 (如自定义 md5_agg 聚合)。13

逻辑复制的具体落地步骤 (数据库级 / 表级)

步骤通常为：在源端设置 wal_level=logical 并重启；创建复制用户与配置 pg_hba.conf; 通过 pg_dump -Fc -s 导出 Schema 并在目标端 pg_restore -C 恢复；创建发布 (CREATE PUBLICATION), 并创建订阅 (CONNECTION ... PUBLICATION ...) 以建立数据与 DDL 的同步链路。订阅建立后，增量变更开始流动，延迟监控与断点续传机制随即生效。1

应用层双写的实现细节与校验

双写不是简单复制 INSERT/UPDATE/DELETE, 而是以最终一致性为目标的重构工程。创建与更新需要事务边界对齐，保证新旧表的记录一一对应；删除与更新需要幂等保障；分批复制历史数据时，用小批次与校验和控制进度与一致性；FULL OUTER JOIN 用于识别缺失记录，INNER JOIN + 转换函数用于校验内容一致性。6

表 5 一致性校验 SQL 模板与判定标准

校验项 SQL 模板 (示意) 判定标准
缺失记录 SELECT * FROM old FULL OUTER JOIN new ON old.id = new.id WHERE new.id IS NULL OR old.id IS NULL; 结果集为空
内容一致 SELECT * FROM old INNER JOIN new ON old.id = new.id WHERE CAST(old.data AS TIMESTAMP) <> new.created_date; 结果集为空
校验和 SELECT md5_agg(t::text) FROM (SELECT * FROM table ORDER BY id) AS t; 两端 md5 值相等

风险控制与运维要点：锁、延迟与可观测性

零停机不是 “零风险”, 而是 “风险可控、影响最小”。三类风险尤其常见：DDL 长锁导致写入阻塞、物理复制切换窗口的写入丢失风险、WAL 保留不足引发增量同步失败。

表 6 常见风险 — 影响 — 缓解 — 监控项矩阵

风险 影响 缓解策略 监控项
DDL 长锁 写入阻塞、超时 使用 pgroll 理念的非阻塞 DDL, 分阶段发布 DDL 耗时、锁等待队列2
复制延迟 数据不同步 小批次复制、限速、索引优化 订阅延迟 (秒 / 条)、消费速率1
WAL 保留不足 增量变更丢失 延长 wal_keep_segments 与保留窗口 WAL 磁盘占用、保留时长3
复制槽累积 磁盘耗尽、实例不可用 自动 / 手动清理历史复制槽 复制槽数量与占用3
切换窗口写入 数据丢失 短窗口拦截写、重试队列 写失败率、重试成功率5

此外，迁移期间的临时表 (如 DTS 创建的心跳表) 与复制槽需要明确清理策略，避免任务释放后长期占用资源；读路径切换要在监控下渐进推进，确保错误率与延迟不超标。3

验证与切换策略：从双读到单写的渐进路径

切换读 (改读) 阶段的目标是 “可控渐进”。先把部分流量切到新表，观察错误率与延迟，再逐步扩大范围；必要时支持按分片键或路由规则分流。切换写 (改写) 阶段建议设置短时间拦截窗口，把未能及时切换的写入重试或导向新表，确保最终写入路径单一且一致。整个过程要有清晰的回退方案：发现异常即刻切回旧读 / 旧写路径，恢复双写与复制，定位问题后再择机重试。5

何时选择离线 dump/restore：窗口评估与性能权衡

尽管在线迁移是理想选择，离线 dump/restore 依然在以下情况更具性价比：数据库规模小 (<10GB 或 10–100GB)、可容忍一定停机、无法满足 DMS 在线迁移前提、或需要简化迁移复杂度。离线方法通过 pg_dump 导出、pg_restore 导入，期间需要应用停机窗口，适合低峰期维护。4

表 7 按数据库规模与业务约束选择迁移方式

数据库规模 / 约束 停机窗口 网络条件 DDL 变更频率 建议方法
<10GB, 可容忍短停机 可接受 良好 低 离线 dump/restore4
10–100GB, 需降停机 严格 良好 中 DMS 在线或逻辑复制4
>100GB, 跨云 / 跨实例 极严格 一般 变动频繁 逻辑复制 + 应用层双写16
大表强一致 极严格 良好 有 分片切换 + 校验和65

案例化演练：以 “用户表描述字段 NOT NULL 化” 为例

在 “客户端驱动的零停机模式变更” 实践中，pgroll 提出了以扩展 / 收缩 (Expand/Contract) 为核心的模式迁移策略：先扩展新列与写入路径，再收缩旧列与应用读取路径。这与七步迁移法相辅相成。我们以一个 “用户表描述字段从可空 TEXT 升级为 NOT NULL TIMESTAMP” 为例，演示一条可落地的安全路径。26

步骤与要点:

  1. 模式扩展：在原表不变的前提下，创建新表 (或新列), 如 new (created_date TIMESTAMP NOT NULL), 通过应用双写同时维护新旧语义。
  2. 历史数据回填：分批 INSERT ... SELECT 并计算批次校验和，避免长锁。
  3. 一致性校验：用 FULL OUTER JOIN 检查缺失，用 INNER JOIN + 转换函数验证内容一致。
  4. 读取切换：逐步把读路径切到新表 / 新列，监控错误率与延迟。
  5. 写入收敛：停止双写，仅写新列；在短窗口拦截未切换的写入。
  6. 模式收缩：移除旧列 / 旧表，清理相关视图、触发器与权限。
  7. 回滚方案：任何阶段校验失败，立即切回旧路径，恢复双写，待问题消解再续迁。

表 8 关键 SQL 与参数配置清单 (示例)

类别 示例 / 说明
创建新表 CREATE TABLE new (...); 索引 / 约束预先创建6
双写 INSERT WITH new_rows AS (INSERT INTO new ...) INSERT INTO old SELECT ...; 事务内保证一致性6
历史复制 INSERT INTO new (...) SELECT ... FROM old WHERE NOT EXISTS (...) LIMIT 1000; 分批迁移6
一致性校验 FULL OUTER JOIN 与 INNER JOIN 差异检测；md5_agg 校验和16
逻辑复制 CREATE PUBLICATION ...; CREATE SUBSCRIPTION ...; wal_level=logical1
DMS 前提 版本对齐、WAL 保留、参数一致；DTS 临时对象与复制槽管理3
pgroll 策略 expand/contract, 非阻塞 DDL, 安全回滚2

这类演练的价值在于把抽象方法论落地为 “可操作脚本 + 可验证里程碑”, 让迁移工程具备可控性与可观测性。

总结与决策建议

跨实例表迁移没有银弹，但有清晰的选择框架：规模小且可容忍停机，优先离线 dump/restore; 规模大、跨云或需严格零停机，优先逻辑复制并辅以应用层双写；垂直拆分或整库迁移，考虑物理复制的 “整体切换” 路径，但要严控切换窗口的写入风险。145

工程落地的关键在于三点：第一，建立一致性与可观测性的闭环，包括幂等写入、校验和、延迟监控与异常回退；第二，控制复杂度，合理拆分迁移批次与切换窗口，避免长锁与复制拥塞；第三，明确风险边界与回滚路径，尤其在 DDL 变更与读 / 写切换阶段。

最后给出一张 “选择矩阵” 作为收尾，帮助快速对齐团队认知:

表 9 迁移方法选择矩阵

条件 物理复制 逻辑复制 应用层双写 离线 dump/restore
零停机要求高 一般 强 强 弱
DDL 变更频繁 弱 强 强 中
粒度为表 / Schema 不适合 适合 适合 适合
规模 > 100GB 适合 适合 适合 (复杂) 不建议
跨云 / 跨账号 一般 适合 适合 适合
回滚可控性 中 中 强 强 (窗口内)

在团队协作上，建议采用 “分阶段验收” 的交付方式：每步输出可验证的产物 (校验报告、监控截图、切换清单), 并配套回滚预案与演练记录，确保从方案到执行的闭环。

资料来源

Footnotes

  1. PostgreSQL 在线迁移：逻辑复制实现零停机 (RockData 文档)。https://doc.rockdata.net/zh-cn/admin/online-migration/ ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7 ↩8 ↩9 ↩10 ↩11 ↩12 ↩13 ↩14 ↩15 ↩16 ↩17 ↩18

  2. pgroll 实战：零停机 Schema 迁移的客户端评估。https://opensource-db.com/pgroll-in-action-client-side-evaluation-of-zero-downtime-schema-migrations/ ↩ ↩2 ↩3 ↩4

  3. RDS PostgreSQL 间的数据迁移 (DTS 实践)。https://antdigital.com/docs/2/321098 ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7 ↩8 ↩9 ↩10 ↩11

  4. Azure DMS / 迁移方法选择与规模建议 (Azure 官方)。https://learn.microsoft.com/zh-cn/azure/postgresql/single-server/how-to-upgrade-using-dump-and-restore ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7

  5. PostgreSQL 不停机迁移数据：逻辑复制与物理复制实践。https://m.blog.csdn.net/Auspicious_air/article/details/136059178 ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7 ↩8 ↩9 ↩10 ↩11 ↩12 ↩13

  6. 零停机迁移 PostgreSQL 数据表实践指南 (Kiran Ra)。https://kiranrao.ca/2022/05/04/zero-downtime-postgresql-migrations.html ↩ ↩2 ↩3 ↩4 ↩5 ↩6 ↩7 ↩8 ↩9 ↩10 ↩11 ↩12

查看归档