pgsql-37 数据迁移方案

2025-09-25 约 1531 字预计阅读 4 分钟

1. 概述

数据迁移是常见且高风险的工作，分两类：异构迁移（MySQL→PostgreSQL 等换数据库）和同构迁移/升级（PG 大版本升级）。本篇覆盖迁移工具、类型差异、不停机方案与校验。

迁移核心原则：先小规模验证 → 数据校验 → 可回滚 → 灰度切流。

2. MySQL → PostgreSQL 迁移

2.1 用 pgloader（推荐）

pgloader 能自动处理类型转换、批量加载，是 MySQL 迁 PG 的首选工具。

apt install pgloader

# 一行命令迁移整库
pgloader mysql://user:pass@localhost/mydb \
         postgresql://user:pass@localhost/mydb

# 配置文件方式（可定制类型映射、排除表、加索引等）
pgloader migration.load

# migration.load 示例
LOAD DATABASE
  FROM mysql://user:pass@localhost/mydb
  INTO postgresql://user:pass@localhost/mydb
WITH include drop, create tables, create indexes, reset sequences
CAST type datetime to timestamptz,
     type tinyint to boolean when (= 1 precision);

pgloader 会自动：建表、迁数据、转类型、建索引、重置序列，并输出迁移报告。

2.2 类型与语法差异对照（面试常问）

MySQL	PostgreSQL	说明
`AUTO_INCREMENT`	`SERIAL` / `IDENTITY`	自增，PG 用序列
`TINYINT(1)`	`BOOLEAN`	MySQL 用 tinyint 存布尔
`DATETIME`	`TIMESTAMP` / `TIMESTAMPTZ`	推荐带时区的 timestamptz
`ENUM('a','b')`	自定义 `ENUM` 类型或 CHECK	PG 需 CREATE TYPE
`\`反引号`` 标识符	`"双引号"`	引用标识符方式不同
`LIMIT n, m`	`LIMIT m OFFSET n`	分页语法不同
`IFNULL()`	`COALESCE()`
`CONCAT()` 容 NULL	`
`GROUP BY` 宽松	严格要求非聚合列在 GROUP BY	PG 更严格
大小写不敏感表名	默认区分（除非加引号）	注意命名
`ON UPDATE CURRENT_TIMESTAMP`	需用触发器实现	PG 无此列属性

2.3 手动迁移步骤（复杂场景）

导出 MySQL 结构，按上表转换 DDL（或用工具生成）。
在 PG 建表结构、约束。

导出 MySQL 数据为 CSV，COPY 导入 PG（最快）。

COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

建索引、重置序列、迁移视图/存储过程（存储过程需重写，语法差异大）。
校验数据。

3. 大表不停机迁移

对不能长时间停机的核心库，用"全量 + 增量追平"策略：

全量：pgloader/COPY 迁历史数据。
增量同步：用 CDC 工具（Debezium 捕获 MySQL binlog）持续把变更同步到 PG。
追平后灰度切流：先切读、验证一致后切写。
保留回滚能力：切换后一段时间内保留原库，异常可切回。

4. 数据校验（不可省略）

-- 行数比对
SELECT count(*) FROM orders;   -- 两库对比

-- 关键字段汇总比对（金额等）
SELECT sum(amount), count(*), max(created_at) FROM orders;

-- 抽样比对主键区间的记录

逐表比对行数、关键聚合值（sum/count/max）。
抽样比对具体记录。
校验外键完整性、序列当前值是否正确。

5. PostgreSQL 版本升级（同构）

方法	停机	说明
pg_dump/restore	长	简单可靠，适合小库
pg_upgrade	短	原地升级，用 `--link` 硬链接极快，适合大库
逻辑复制	极短	新版本建从库、逻辑复制追平后切换，几乎不停机

# pg_upgrade 原地升级（大库首选）
pg_upgrade -b /old/bin -B /new/bin -d /old/data -D /new/data --link

6. 迁移检查清单

类型/语法差异是否全部处理（尤其布尔、时间、自增、分页）。
存储过程/触发器/函数需重写（PL/pgSQL vs MySQL 存储过程）。
序列当前值是否正确（避免主键冲突）。
索引、约束、默认权限是否重建。
应用 SQL 是否有 MySQL 方言需改。
数据校验通过。
有回滚预案。
性能回归测试（执行计划可能不同）。

7. 面试高频问答

Q1：MySQL 迁 PostgreSQL 用什么工具？ 首选 pgloader，能自动建表、转类型、迁数据、建索引、重置序列；不停机场景用 Debezium 等 CDC 工具做增量同步。

Q2：MySQL 和 PostgreSQL 有哪些关键差异要注意？ 自增（AUTO_INCREMENT→SERIAL/序列）、布尔（tinyint→boolean）、时间（datetime→timestamptz）、分页（LIMIT n,m→LIMIT m OFFSET n）、标识符引用（反引号→双引号）、GROUP BY 更严格、字符串拼接遇 NULL 行为不同、存储过程需重写。

Q3：大表怎么不停机迁移？ 全量迁移历史数据 + CDC 增量同步追平 + 灰度切流（先切读再切写）+ 保留回滚。

Q4：迁移后怎么校验数据一致？ 逐表比对行数与关键聚合值（sum/count/max）、抽样比对记录、校验外键完整性和序列当前值，必要时做性能回归。

Q5：PostgreSQL 大版本升级怎么做到少停机？ 大库用 pg_upgrade –link（硬链接，分钟级）；要求几乎不停机则用逻辑复制搭建新版本从库、追平后切换。

8. 下一步

学习 Go语言连接PostgreSQL，进入应用开发实战。

目录