MySQL转PostgreSQL：详细步骤与数据迁移工具 – wiki词典

作者： /

MySQL 迁移 PostgreSQL：详细步骤与数据迁移工具

从 MySQL 迁移到 PostgreSQL 是一个常见的数据库升级和现代化改造任务。PostgreSQL 提供了更高级的数据完整性、更强大的功能集以及更严格的 SQL 标准合规性，吸引了越来越多的开发者和企业。本文将详细介绍从 MySQL 迁移到 PostgreSQL 的完整流程、不同策略以及常用的数据迁移工具。

为什么选择从 MySQL 迁移到 PostgreSQL？

  • 功能更强大：PostgreSQL 支持更复杂的数据类型（如 JSONB、数组、GIS 数据）、窗口函数、通用表表达式 (CTE) 和完整的 ACID 事务。
  • 扩展性：提供了强大的扩展框架，允许用户自定义函数、数据类型和索引方法。
  • 开源和社区驱动：拥有一个非常活跃的社区，不受单一商业公司控制。
  • SQL 标准遵从：相比 MySQL，PostgreSQL 更严格地遵循 SQL 标准，代码可移植性更好。

一、迁移前的准备工作

成功的迁移始于周密的计划。在开始迁移之前，请务必完成以下准备工作：

1. 评估现有 MySQL 数据库

  • 模式和复杂性：分析数据库的表结构、视图、索引、触发器和存储过程。
  • 数据量：确定数据库的总体大小，这将影响迁移策略和时间的预估。
  • MySQL 特定功能：识别出在 PostgreSQL 中没有直接对应项的 MySQL 特有功能（例如，ON DUPLICATE KEY UPDATE），并找到替代方案（如 PostgreSQL 的 ON CONFLICT DO UPDATE）。

2. 制定迁移策略

  • 可接受的停机时间：根据业务需求，确定可以容忍多长的停机时间。这直接决定了是采用简单的“离线迁移”还是复杂的“在线迁移”。
  • 资源规划：准备好足够的计算资源、存储空间和人力。
  • 制定时间表：为每个迁移阶段设定明确的时间节点。

3. 建立测试环境

  • 创建 PostgreSQL 实例：搭建一个与生产环境规格相似的 PostgreSQL 服务器作为临时或测试环境。
  • 性能基准测试：在迁移前，对现有 MySQL 数据库进行性能测试，以便迁移后进行对比。

4. 识别应用依赖

  • 梳理依赖关系：列出所有连接到该 MySQL 数据库的应用程序和服务。
  • 代码兼容性：检查应用程序代码，特别是 SQL 查询、ORM 配置和数据库连接驱动，为后续的修改做准备。

二、迁移策略与核心步骤

根据停机时间的要求，可以选择不同的迁移策略。

策略一：离线迁移 (DUMP 和恢复)

这是最简单直接的方法，适用于数据量不大或业务可以接受数小时停机时间的场景。

核心步骤：

  1. 停止应用服务：暂停所有连接到 MySQL 数据库的写入操作。
  2. 导出 MySQL 数据：使用 mysqldump 工具将整个数据库或特定表导出为 SQL 文件。
    bash
    mysqldump -u <user> -p --compatible=postgresql --default-character-set=utf8 -r mysql_dump.sql <database_name>
    --compatible=postgresql 选项可以帮助生成与 PostgreSQL 更兼容的 SQL，但效果有限。
  3. 转换 SQL 文件：导出的 SQL 文件通常无法直接在 PostgreSQL 中运行，因为存在语法差异。你可以：
    • 手动修改：对于简单的数据库，可以手动查找和替换不兼容的语法。
    • 使用转换脚本/工具：使用 sed、awk 或专门的脚本来自动完成转换。
  4. 导入 PostgreSQL：使用 psql 工具将转换后的 SQL 文件导入到 PostgreSQL 数据库。
    bash
    psql -U <user> -d <database_name> -f converted_dump.sql
  5. 数据验证和测试：详见“迁移后工作”。
  6. 切换应用连接：将应用程序的数据库连接配置指向新的 PostgreSQL 数据库，然后重启服务。

策略二：使用专业迁移工具

对于复杂的数据库，手动转换工作量巨大且容易出错。使用专业的迁移工具可以大大简化流程。

核心步骤：

  1. 选择并配置工具：选择一个合适的工具，如 pgloader。
  2. 执行迁移命令：pgloader 可以一步完成从连接 MySQL、获取模式、转换数据类型到加载数据的全过程。
    bash
    pgloader mysql://<user>:<password>@<mysql_host>/<db_name> postgresql://<user>:<password>@<pg_host>/<db_name>
    pgloader 会自动处理索引、外键和数据类型映射，非常高效。
  3. 验证和切换：迁移完成后，进行数据验证，然后将应用切换到 PostgreSQL。

策略三：在线迁移 (最小化停机时间)

对于不能长时间停机的关键业务，需要采用更复杂的在线迁移策略。

核心思想：

通过某种形式的实时数据同步，在旧数据库仍在服务时，将数据持续复制到新数据库。

常见方法：

  1. 初始全量加载：首先使用 pgloader 或 AWS DMS 等工具完成一次全量数据迁移。
  2. 增量数据同步 (CDC – Change Data Capture)：
    • 使用触发器：在 MySQL 表上创建触发器，将变更（INSERT, UPDATE, DELETE）写入一个日志表，然后同步程序定期读取日志并应用到 PostgreSQL。
    • 使用 Debezium 等 CDC 工具：Debezium 通过读取 MySQL 的 binlog 来捕获数据变更，并将其推送到 Kafka 等消息队列，然后由消费者写入 PostgreSQL。
    • 使用 pg-chameleon：这是一个专门的工具，可以模拟成一个 MySQL 从库，读取 binlog 并将数据复制到 PostgreSQL。
  3. 切换与验证：当新旧数据库数据延迟非常小时，安排一个极短的停机窗口，停止 MySQL 写入，确保所有变更都同步到 PostgreSQL，然后将应用流量切换到新数据库。

三、常用数据迁移工具对比

工具 类型 优点 缺点 适用场景
pgloader 开源命令行 强大、自动化程度高，一步完成 schema 和数据迁移，支持并行处理，速度快。 配置相对复杂，对某些复杂的存储过程支持有限。 最推荐的通用迁移工具，尤其适合中大型数据库的离线迁移。
mysqldump + psql 数据库原生工具 无需额外安装，简单直接。 手动工作量大，需要处理 SQL 语法兼容性，容易出错。 小型、结构简单的数据库。
AWS DMS 云服务 (AWS) 托管服务，支持在线迁移和 CDC，可靠性高，集成度好。 仅限于 AWS 云环境，可能会产生费用。 在 AWS 上运行的数据库迁移。
Navicat Premium / DataGrip 商业 GUI 工具 提供可视化界面，操作直观，支持跨多种数据库迁移。 商业软件，需要付费，可能不如专用工具高效。 开发者进行小规模迁移或数据同步。
SQLines 开源工具 专注于 SQL 语法和存储过程的转换。 主要处理 DDL 和业务逻辑，数据迁移能力较弱。 需要迁移大量存储过程和复杂 SQL 查询的场景。
mysql_fdw PostgreSQL 扩展 允许在 PostgreSQL 中直接查询 MySQL 表，无需数据迁移。 性能开销大，不适合作为永久解决方案。 逐步迁移，或者只需要从 PostgreSQL 访问部分 MySQL 数据的场景。

四、迁移后的工作

数据迁移完成不代表工作的结束，后续的验证和优化至关重要。

1. 数据完整性验证

  • 行数校验：对比新旧数据库中每张表的行数是否一致。
  • 数据抽样：随机抽取一些记录，对比其在新旧数据库中的内容。
  • 业务逻辑测试：运行核心业务流程，确保数据在应用层面表现正确。

2. 性能测试与优化

  • 执行 ANALYZE：在 PostgreSQL 中运行 ANALYZE; 命令，更新统计信息，帮助查询优化器制定更好的执行计划。
  • 索引检查：检查索引是否已成功迁移，并根据查询性能添加或调整索引。
  • 查询重写：对于性能不佳的查询，使用 EXPLAIN ANALYZE 分析其执行计划，并针对 PostgreSQL 进行优化。

3. 应用程序代码修改

  • 更换数据库驱动：将应用的 MySQL 驱动替换为 PostgreSQL 驱动（如 psycopg2 for Python, node-postgres for Node.js）。
  • 修改连接字符串：更新数据库的连接信息。
  • 调整 SQL 语法：修复所有与 PostgreSQL 不兼容的 SQL 查询。

4. 监控与 decommissioning

  • 持续监控：在切换后密切监控 PostgreSQL 的性能指标和错误日志。
  • 停用旧库：在确认新系统稳定运行一段时间后，可以将旧的 MySQL 数据库设置为只读模式作为备份，并最终按计划将其下线。

五、常见挑战与注意事项

  • 数据类型不匹配：MySQL 的 TINYINT(1) 通常用作布尔值，需要映射到 PostgreSQL 的 BOOLEAN。DATETIME 和 TIMESTAMP 的处理也略有不同。
  • 自增列：MySQL 的 AUTO_INCREMENT 需要转换为 PostgreSQL 的 SERIAL 或 IDENTITY 类型，并确保序列的当前值正确。
  • 大小写敏感性：默认情况下，PostgreSQL 会将未加引号的表名和列名转换为小写，而 MySQL 在某些配置下是大小写敏感的。这可能导致查询失败。
  • 存储过程和触发器：MySQL 的存储过程语法与 PostgreSQL 的 PL/pgSQL 完全不同，通常需要手动重写。

结论

从 MySQL 迁移到 PostgreSQL 是一个系统工程，涉及细致的规划、谨慎的执行和全面的测试。对于大多数场景，使用 pgloader 这样的专业工具是最高效、最可靠的选择。对于需要最小化停机时间的关键业务，则应考虑基于 CDC 的在线迁移方案。无论选择哪种方法，充分的测试和验证都是确保迁移成功的关键。

滚动至顶部