从 MySQL 迁移到 PostgreSQL

适用场景

需要从 MySQL 迁移到 PostgreSQL，获得更丰富的 SQL 能力、扩展生态或统一数据库栈。迁移难点在数据类型、自增、大小写、存储过程和 SQL 方言。

前置条件

• 完整备份和可恢复的测试环境。
• 评估 schema、索引、触发器、存储过程和字符集。
• 选择迁移工具：pgloader、AWS DMS、Debezium 或自研脚本。
• 明确停机窗口和回滚方案。

步骤

1. 导出 MySQL schema 和数据：

mysqldump --single-transaction --routines --triggers \
  --set-gtid-purged=OFF app > app.sql

2. 评估不兼容点：

grep -nE 'AUTO_INCREMENT|ENGINE=|UNSIGNED|TINYINT|DATETIME|ON UPDATE|`' app.sql | head -50

3. 使用 pgloader 迁移：

pgloader mysql://user:***@mysql:3306/app \
  postgresql://user:***@postgres:5432/app

4. 处理类型映射：

• TINYINT(1) → boolean
• DATETIME → timestamp
• AUTO_INCREMENT → generated by default as identity
• UNSIGNED 需要扩大到更大整数类型
• 反引号标识符改为双引号或小写

5. 迁移存储过程和触发器，改写 MySQL 方言。

6. 校验数据：

psql -c "SELECT count(*) FROM orders;"
mysql -e "SELECT count(*) FROM orders;"

7. 切换应用连接串和驱动，观察错误率和慢查询。

验证

• 表、索引、约束、序列和权限完整。
• 行数和关键业务对账一致。
• 应用读写、事务和分页正常。
• 自增/序列不会冲突。
• 备份和恢复流程可用。

回滚

• 保留 MySQL 原库和连接串。
• 切换前记录增量位点，必要时反向同步。
• 回滚时停止写入 PostgreSQL，确认数据补偿。
• DNS/连接池切换使用短 TTL。

常见坑

• 大小写敏感：MySQL 表名不区分大小写，PostgreSQL 默认区分。
• UNSIGNED 溢出，迁移后数据被截断。
• AUTO_INCREMENT 序列没有同步，插入冲突。
• ON UPDATE CURRENT_TIMESTAMP 语义不同，需要触发器。
• 存储过程、触发器和自定义函数没有迁移。
• 字符集和排序规则不同，中文排序结果变化。
• 分页 LIMIT offset, size 语法不同。
• 连接池驱动和事务隔离级别行为不同。

相关命令

• pgloader — MySQL 到 PostgreSQL 迁移
• mysqldump — MySQL 导出
• psql — PostgreSQL 验证
• pg_dump / pg_restore — 备份恢复