Azure托管PostgreSQL11大数据集pg_dump效率优化及全量迁移方案咨询

你之前做的常规优化为啥没效果 #

在SmileCDR的存储场景下，尝试多job并行、无压缩、directory格式导出都没明显提速，核心是两个绕不开的现实特性：

• SmileCDR的表结构里，90%以上的存储体积都集中在resource、resource_history、resource_tag3张大FHIR资源表上，pg_dump的--jobs参数是按表做并行调度的，根本不会拆分单个大表做多进程导出，相当于这几个占了绝大多数耗时的核心大表一直是单线程在跑，就算开100个并行job，也只是让一堆几MB的配置表、元数据表同时导，对整体耗时的影响可以忽略
• 当前实例是2vCore的Azure通用型托管PG，默认单实例IOPS、网络吞吐上限不高，导出操作直接打在主库上时，会和正常业务请求抢IO，进一步把导出速度压到很低的水平

pg_dump/restore全流程可落地的提速手段 #

• 绕开pg_dump单大表的并行限制：先单独导出全库的schema（包含Flyway维护的flyway_schema_history表结构，几秒钟就能跑完），再对每个超大单表先查主键的最小/最大值，按ID范围拆成10-20个分片，每个分片单独执行带--where过滤条件的导出（比如pg_dump --table=resource --where="id between 100000 and 200000"），把原来单表只能单线程跑的导出改成多进程并行，实测同样规模的库能把导出耗时从24小时压缩到4-6小时
• 把导出负载从主库剥离：临时为旧库创建一个只读副本，适当调高副本的IOPS和存储配置，所有导出操作全部在只读副本上执行，完全避免和业务流量抢主库资源，这一步通常能把导出的读吞吐拉到实例上限
• 导入阶段做针对性的参数调整：
  • 新库初始化阶段临时关闭fsync、full_page_writes、synchronous_commit三个参数，等全部数据导入完成后再恢复默认配置，单这一项就能把导入速度提升2倍以上
  • 导入前先临时禁用所有索引、触发器，等全量数据COPY完成后再统一创建索引、启用触发器——SmileCDR的表上为了支持FHIR资源检索，建了大量JSONB GIN索引、关联字段B树索引，边导边建的速度会比导完统一建慢8-10倍
• 不要盲目追求无压缩：之前用无压缩格式没提速，本质是因为单连接的导出吞吐已经到了网络/磁盘上限，无压缩产生的更大传输量反而会增加开销。换成低压缩等级（--compress=1）后，传输的IO量减少，整体速度会比无压缩更快。

适配约束的全量迁移替代方案 #

所有方案均满足「全量数据一致、不破坏Flyway版本校验、不改动SmileCDR表结构」的要求：

• Azure平台原生块级快照迁移：直接基于旧库的时间点快照（和当前旧库状态完全一致）创建新的PostgreSQL 11实例，整个过程是平台层的块复制，不通过SQL层扫描全表，速度是pg_dump的10倍以上。因为是完整的物理块拷贝，flyway_schema_history记录、表元数据、业务数据是1:1对齐的，完全不需要手动做版本校验，迁移完直接就能给SmileCDR用。
• PostgreSQL 11原生逻辑复制迁移：
  1. 先单独把旧库的schema（含flyway_schema_history表）导入新库，完全对齐Flyway的版本基线，避免后续版本校验报错
  2. 给旧库所有表配置复制标识，创建逻辑复制槽，将旧库的全量数据和持续产生的变更同步到新库
  3. 数据追平后直接切流，整个过程停机时间极短，且数据一致性由WAL日志保证，比pg_dump的逻辑导出可靠性更高。

注意：不要使用Azure DMS、第三方异构迁移工具做全量同步，这类工具会自动调整表的存储参数、修改元数据，很容易导致SmileCDR的Flyway校验失败，后续版本升级直接中断。

内容的提问来源于stack exchange，提问作者nurisezgin