数据库迁移是企业技术栈升级中最具风险也最具价值的工程之一。2024年Q3，我们为某头部金融科技客户完成了一次核心业务数据库从MySQL 8.0到PostgreSQL 16的全量迁移，涉及超过200张业务表、8TB数据量和日均3000万次查询。整个迁移在零停机的条件下完成。本文将详细复盘这次迁移的全过程，分享我们踩过的坑和积累的最佳实践。

为什么选择迁移到PostgreSQL

客户的核心系统最初建立在MySQL 5.7之上，后升级至8.0。随着业务规模的快速增长和金融监管要求的日益严格，MySQL在以下几个方面逐渐无法满足需求：

  • JSON处理能力不足：业务中大量使用JSON字段存储灵活的金融产品配置，MySQL的JSON函数性能和功能均落后于PostgreSQL的JSONB
  • 缺乏高级索引类型：风控系统需要GIN索引和全文检索能力，MySQL的全文索引在中文场景下表现不佳
  • 窗口函数和CTE支持有限：复杂的金融报表查询需要大量递归CTE和高级窗口函数，MySQL 8.0虽已支持但优化器表现不够成熟
  • 扩展性生态：PostgreSQL丰富的扩展（PostGIS、TimescaleDB、pg_cron等）可以覆盖更多业务场景
  • 合规与审计：PostgreSQL的行级安全策略（RLS）和审计插件（pgAudit）更贴合金融合规需求
"迁移的决策不是某一天突然做出的，而是在过去一年多的运维中逐步积累的。当我们发现团队花在绕过MySQL局限上的时间已经超过了正常业务开发时间的20%，就知道是时候做出改变了。"
—— 客户方技术总监

数据类型兼容性：最先踩到的坑

MySQL和PostgreSQL虽然都是关系型数据库，但在数据类型上存在大量细微差异。这些差异在迁移过程中如果处理不当，轻则导致数据截断，重则引发业务逻辑错误。以下是我们遇到的主要兼容性问题：

-- MySQL vs PostgreSQL 类型映射关键差异
MySQL TINYINT(1)      →  PostgreSQL BOOLEAN
MySQL DATETIME        →  PostgreSQL TIMESTAMP
MySQL INT UNSIGNED    →  PostgreSQL BIGINT (PG无UNSIGNED)
MySQL DOUBLE          →  PostgreSQL DOUBLE PRECISION
MySQL ENUM('a','b')   →  PostgreSQL 自定义TYPE 或 CHECK约束
MySQL TEXT/BLOB       →  PostgreSQL TEXT/BYTEA
MySQL AUTO_INCREMENT  →  PostgreSQL SERIAL / GENERATED ALWAYS
MySQL JSON            →  PostgreSQL JSONB (推荐)

其中最棘手的是 TINYINT(1) 到 BOOLEAN 的转换。MySQL中大量使用 TINYINT(1) 来表示布尔值，但实际存储值可能是0/1之外的数字（比如某些旧版ORM将NULL也映射为0）。我们编写了专门的数据清洗脚本来统一处理这类边界情况。

另一个隐蔽的问题是 UNSIGNED 类型。PostgreSQL不支持无符号整数，直接用 INT 替换可能导致存储超出范围的值时报错。我们的策略是统一提升一级：INT UNSIGNED 映射为 BIGINT，并添加 CHECK 约束确保值非负。

迁移工具选型：为什么我们选择pgloader

市面上常见的MySQL到PostgreSQL迁移工具包括pgloader、AWS DMS、ora2pg（也支持MySQL）和自研ETL管道。经过详细评估，我们最终选择了pgloader作为主力工具，原因如下：

  1. 内置类型映射规则。pgloader预置了MySQL到PostgreSQL的类型映射，并允许通过配置文件自定义覆盖，大幅减少了手动建表的工作量。
  2. 流式迁移，内存友好。pgloader使用流式读写模式，不会将全量数据加载到内存中，这对于我们8TB的数据量至关重要。
  3. 并行加载能力。支持多表并行迁移和单表分片并行，在测试环境中实测迁移速度达到每小时约600GB。
  4. 开源免费。作为AGPL协议的开源项目，pgloader在社区活跃度和问题排查方面都有很好的保障。
-- pgloader 配置文件示例（简化版）
LOAD DATABASE
  FROM mysql://user:pass@mysql-host:3306/finance_db
  INTO postgresql://user:pass@pg-host:5432/finance_db

WITH include drop, create tables, create indexes,
     reset sequences, downcase identifiers,
     batch rows = 10000, batch size = 200MB,
     prefetch rows = 10000

SET PostgreSQL PARAMETERS
  maintenance_work_mem to '2GB',
  work_mem to '512MB'

CAST type tinyint to boolean using tinyint-to-boolean,
     type int with extra unsigned to bigint,
     type datetime to timestamptz

ALTER SCHEMA 'finance_db' RENAME TO 'public';

存储过程转换：最耗时的工作

客户系统中包含超过150个MySQL存储过程和触发器。MySQL使用的存储过程语法与PostgreSQL的PL/pgSQL存在较大差异，这部分工作几乎无法完全自动化，需要逐个手动转换和验证。

主要差异包括：

  • MySQL的 DELIMITER 语法在PostgreSQL中不存在，需要使用 $$ 包裹函数体
  • 变量声明从 DECLARE ... DEFAULT 改为 DECLARE ... :=
  • MySQL的 IF...ELSEIF...END IF 改为 IF...ELSIF...END IF（注意ELSIF而非ELSEIF）
  • 错误处理从 DECLARE ... HANDLER 改为 EXCEPTION WHEN ... THEN
  • MySQL的 LAST_INSERT_ID() 需改为 RETURNING id 或 currval()

我们编写了一个Python脚本来自动处理约70%的语法转换，剩下30%需要DBA手动审核和调整。整个存储过程转换工作耗时约三周，是迁移项目中最耗时的单项任务。

性能调优：迁移后的必修课

数据迁移完成后，我们发现部分查询的性能出现了退化。这是正常现象，因为MySQL和PostgreSQL的查询优化器策略差异很大。以下是我们进行的主要调优措施：

"不要指望把MySQL的索引策略原封不动搬到PostgreSQL就能获得相同甚至更好的性能。PostgreSQL的优化器更智能，但也需要你用正确的方式'喂'它信息。"
—— 项目DBA负责人

核心调优措施包括：

  1. 统计信息更新。迁移后立即执行 ANALYZE，确保优化器拥有准确的表统计信息。对于大表，调整 default_statistics_target 至500以上。
  2. 索引重建与优化。PostgreSQL支持部分索引（Partial Index）和表达式索引，充分利用这些特性可以显著提升查询效率。例如，对只查询"活跃用户"的场景，创建 WHERE status = 'active' 的部分索引。
  3. 连接池配置。PostgreSQL的进程模型（每个连接一个进程）与MySQL的线程模型不同，引入PgBouncer作为连接池中间件，将最大连接数从2000降至200，单连接性能反而提升了30%。
  4. work_mem和shared_buffers调整。根据服务器128GB内存的配置，将shared_buffers设为32GB，work_mem设为256MB，effective_cache_size设为96GB。

零停机迁移策略

作为金融系统，任何形式的停机都意味着直接的经济损失和客户信任损伤。因此，我们设计了一套基于双写+流量切换的零停机迁移方案：

App MySQL(R) PG(R/W) Debezium Phase 3: Full cutover to PostgreSQL

迁移分为四个阶段执行：

  1. Phase 1 - 全量同步（3天）：使用pgloader完成历史数据全量迁移，同时通过Debezium捕获MySQL的binlog增量变更，持续写入PostgreSQL。
  2. Phase 2 - 双写验证（1周）：应用层开启双写模式，所有写操作同时发往MySQL和PostgreSQL。读操作仍从MySQL返回，但后台异步对比两库查询结果的一致性。
  3. Phase 3 - 灰度切读（3天）：逐步将10%、30%、50%、100%的读流量切换至PostgreSQL，每个阶段观察24小时确认无异常。
  4. Phase 4 - 全量切写（1天）：关闭MySQL双写，所有读写完全切换至PostgreSQL。MySQL保留为只读备份，观察两周后下线。

迁移成果与经验总结

整个迁移项目历时6周，最终成果超出预期。迁移完成后，核心交易查询的P99延迟从45ms下降至18ms，复杂报表查询的执行时间平均缩短60%以上。同时，得益于PostgreSQL的JSONB索引能力，产品配置模块的检索效率提升了10倍。

回顾整个项目，有几个关键经验值得分享：第一，提前投入足够时间做数据类型映射的详细调研和测试，这决定了迁移的成败。第二，存储过程的转换不要追求100%自动化，手动审核环节不可省略。第三，性能调优需要在真实流量场景下进行，测试环境的基准测试仅供参考。第四，零停机迁移方案的核心在于可回滚设计，每一个阶段都必须能安全回退到上一个阶段。

如果你的团队也在考虑从MySQL迁移到PostgreSQL，欢迎与我们交流。灵犀科技拥有丰富的数据库迁移经验，可以帮助你少走弯路、降低风险。