如何从MySQL迁移到PostgreSQL – wiki基地

作者： /

终极指南：从 MySQL 丝滑迁移到 PostgreSQL

从 MySQL 迁移到 PostgreSQL 是一个常见的数据库升级路径，许多开发者和企业选择 PostgreSQL 是因为它拥有更强大的功能集、更高的数据完整性、更强的标准符合性以及更丰富的扩展生态。然而，迁移过程并非一键完成，它需要周密的计划、细致的操作和严格的验证。

本指南将详细介绍两种主流的迁移方法，并涵盖从准备到收尾的每一个关键步骤，帮助你顺利完成迁移。

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

在开始之前，让我们简单回顾一下促使迁移的几个主要原因：

  • 数据类型更丰富：PostgreSQL 提供原生支持，如 JSONB、数组、地理空间数据 (PostGIS)、网络地址等。
  • 事务控制和数据完整性：PostgreSQL 严格遵循 ACID 标准，提供更强的事务隔离级别和数据一致性保证。
  • 可扩展性：支持自定义函数、操作符、数据类型和索引方法，使其高度可定制。
  • 高级查询功能：拥有更强大的 SQL 功能，如窗口函数（Window Functions）、公用表表达式（CTEs）和递归查询。
  • 开源和社区：拥有一个非常活跃和强大的社区，遵循自由的开源许可证。

2. 迁移前的准备与规划（Pre-Migration）

成功的迁移始于周密的规划。贸然行动是导致数据丢失或迁移失败的主要原因。

2.1. 数据类型映射

MySQL 和 PostgreSQL 的数据类型不完全兼容。你需要提前创建一个映射表，明确每种数据类型在目标数据库中的对应关系。

MySQL 数据类型 PostgreSQL 对应类型 注意事项
TINYINT SMALLINT PG 没有 1 字节的整数。
INT, INTEGER INTEGER
BIGINT BIGINT
FLOAT, DOUBLE REAL, DOUBLE PRECISION
DECIMAL(p, s) DECIMAL(p, s) or NUMERIC(p, s)
DATE DATE
TIME TIME
DATETIME TIMESTAMP
TIMESTAMP TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) PG 的 TIMESTAMP 默认不带时区信息。
CHAR(n) CHAR(n)
VARCHAR(n) VARCHAR(n) or TEXT 在 PG 中，VARCHAR(n) 和 TEXT 性能几乎无差别。
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT TEXT
BLOB, LONGBLOB BYTEA 用于存储二进制数据。
ENUM ENUM 类型或 CHECK 约束 PG 需要先 CREATE TYPE ... AS ENUM (...)。
JSON JSON 或 JSONB JSONB 是二进制格式，效率更高，强烈推荐。
BOOLEAN BOOLEAN MySQL 中常用 TINYINT(1) 模拟。

2.2. 备份！备份！备份！

在进行任何操作之前，务必完整备份你的 MySQL 数据库。这是你的安全网，无论发生什么，你都有机会回退到初始状态。

bash
mysqldump -u [用户名] -p --all-databases --master-data > full_backup.sql

2.3. 识别并评估 MySQL 特定功能

检查你的数据库和应用代码，列出所有使用了 MySQL 特定语法的部​​分。这些是迁移过程中的重点和难点。

  • 自增列：MySQL 的 AUTO_INCREMENT 需要转换为 PostgreSQL 的 SERIAL、BIGSERIAL 或更现代的 GENERATED AS IDENTITY。
  • 大小写敏感性：在 Linux 上，MySQL 默认对表名大小写敏感，而 PostgreSQL 默认对所有标识符（表名、列名等）转换为小写（除非用双引号括起来）。
  • SQL 语法差异：
    • ON DUPLICATE KEY UPDATE -> INSERT ... ON CONFLICT ... DO UPDATE
    • GROUP_CONCAT() -> STRING_AGG()
    • 日期函数（如 DATE_FORMAT()） -> TO_CHAR()
    • 非标准的引号使用（MySQL 对字符串和标识符都可用 " 和 '，PG 中 " 用于标识符，' 用于字符串）。
  • 存储过程、触发器和函数：MySQL 的语法与 PostgreSQL 的 PL/pgSQL 完全不同，需要手动重写。这是整个迁移中最耗时的工作之一。
  • 存储引擎：MyISAM 等非事务性存储引擎的特性在 PostgreSQL 中没有直接对应物，需要确保业务逻辑不再依赖这些特性。

2.4. 选择迁移工具

  • 手动迁移 (mysqldump + psql)：
    • 优点：完全控制整个过程，无需额外工具，适合中小型数据库。
    • 缺点：繁琐，易出错，对于大型或复杂的数据库工作量巨大。
  • 自动化工具 (pgloader)：
    • 优点：高度自动化，能处理数据类型转换、约束、索引甚至部分默认值，速度快，支持从 MySQL 直接迁移到 PostgreSQL。
    • 缺点：需要学习其命令和配置，对于非常特殊的转换逻辑可能仍需手动干预。

3. 方法一：手动迁移 (mysqldump + psql)

此方法分为两步：先迁移表结构（Schema），再迁移数据。

步骤 1: 迁移表结构

  1. 导出纯表结构文件：
    bash
    mysqldump -u [用户名] -p --no-data --compat=postgresql [数据库名] > schema.mysql.sql
    --compat=postgresql 选项会尝试生成更兼容的 SQL，但效果有限。

  2. 手动转换 SQL 文件：这是最关键的一步。打开 schema.mysql.sql 文件，然后：

    • 将 AUTO_INCREMENT 替换为 SERIAL 或 BIGSERIAL。
    • 根据你的映射表，修改所有数据类型。
    • 移除 MySQL 特定的 ENGINE=InnoDB、CHARSET=utf8mb4 等子句。
    • 修改索引和键的语法。例如，KEY idx_name (column) -> CREATE INDEX idx_name ON table_name (column);
    • 重写所有 CREATE TRIGGER 和 CREATE PROCEDURE 语句为 PL/pgSQL 语法。

    转换前 (MySQL DDL):
    sql
    CREATE TABLE `users` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `username` varchar(50) NOT NULL,
    `email` varchar(100) DEFAULT NULL,
    `created_at` datetime NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    转换后 (PostgreSQL DDL):
    sql
    CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    created_at TIMESTAMP NOT NULL
    );

  3. 将转换后的表结构导入 PostgreSQL：
    bash
    psql -U [用户名] -d [数据库名] -f schema.pgsql.sql
    如果出现错误，根据提示修改 SQL 文件，直到所有表和对象都成功创建。

步骤 2: 迁移数据

  1. 从 MySQL 导出数据 (CSV 格式)：使用 CSV 或制表符分隔的格式是最高效的方式。
    sql
    -- 为每个表执行
    SELECT * FROM your_table INTO OUTFILE '/path/to/data/your_table.csv'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n';
    注意 INTO OUTFILE 的路径权限问题，MySQL 进程需要有写入权限。

  2. 将数据导入 PostgreSQL：使用 COPY 命令，这是 PostgreSQL 批量加载数据的最快方法。
    sql
    -- 为每个表执行
    COPY your_table FROM '/path/to/data/your_table.csv' WITH (FORMAT csv, HEADER true);
    HEADER true 表示 CSV 文件的第一行是列名。确保 CSV 文件中的列顺序与 PG 表中的列顺序一致。

4. 方法二：使用 pgloader 自动化迁移

pgloader 是一个强大的开源工具，它能将整个迁移过程浓缩为一条命令。

步骤 1: 安装 pgloader

根据你的操作系统，可以从包管理器（如 apt, yum）或源码安装。

“`bash

Debian/Ubuntu

sudo apt-get install pgloader

RHEL/CentOS

需要先启用 EPEL repository

sudo yum install pgloader
“`

步骤 2: 执行迁移

最简单的用法是提供源和目标数据库的连接字符串。pgloader 会自动完成数据类型映射、数据迁移、索引和外键的创建。

bash
pgloader mysql://[MySQL用户名]:[密码]@[MySQL主机]/[数据库名] \
pgsql://[PG用户名]:[密码]@[PG主机]/[数据库名]

步骤 3: 使用配置文件进行高级迁移

对于更复杂的场景，你可以创建一个加载文件（.load 文件）来定制迁移行为。

例如，创建一个 migration.load 文件：
“`lisp
LOAD DATABASE
FROM mysql://user:pass@mysql_host/source_db
INTO pgsql://user:pass@pg_host/target_db

WITH
— 包含或排除某些表
including only table names matching ‘users’, ‘products’
— excluding table names matching ‘logs’

 -- 在数据加载完成后执行 SQL
 after load DO
 $$ ALTER TABLE users ADD COLUMN migrated_at TIMESTAMPTZ DEFAULT NOW(); $$,
 $$ VACUUM ANALYZE; $$

SET
— PostgreSQL 运行时参数
postgresql.maintenance_work_mem to ‘128MB’,
mysql.collation_name to ‘utf8mb4_general_ci’;
“`

然后执行：
bash
pgloader migration.load

5. 迁移后工作（Post-Migration）

迁移远未结束，后续的验证和优化至关重要。

  1. 数据验证：

    • 行数校验：检查源和目标数据库中关键表的行数是否一致。
    • 数据抽样：手动比对几条关键记录，确保所有字段都正确无误。
    • 自动化测试：如果你的应用有测试套件，将其连接到新的 PostgreSQL 数据库并运行所有测试。

  2. 应用程序代码修改：

    • 更新所有服务的数据库连接字符串。
    • 查找并重写所有之前识别出的 MySQL 特定 SQL 查询。
    • 注意处理 NULL 值、日期格式和事务逻辑的细微差异。

  3. 性能优化：

    • 更新统计信息：在 PostgreSQL 中运行 ANALYZE，让查询优化器了解数据的分布。
      sql
      ANALYZE VERBOSE;
    • 重建索引：检查索引是否成功迁移，并在必要时重建它们。
    • 调整配置：根据服务器硬件和负载，调整 postgresql.conf 文件中的参数，如 shared_buffers, work_mem 等。

  4. 最终切换（Cut-Over）

    • 规划一个停机窗口。
    • 停止应用服务，防止新的写入。
    • 进行最后一次增量数据同步（如果需要）。
    • 将应用指向新的 PostgreSQL 数据库。
    • 启动应用，并密切监控日志和性能。

6. 结论

从 MySQL 迁移到 PostgreSQL 是一项系统工程。对于小型项目，mysqldump 结合手动修改可能就足够了。但对于大多数生产环境，强烈推荐使用 pgloader 这样的自动化工具，它能极大地减少手动工作量和出错的风险。

无论选择哪种方法，充分的准备、细致的验证和耐心的调试都是通往成功迁移之路的基石。完成迁移后，你将能享受到 PostgreSQL 带来的强大功能和稳健体验。

滚动至顶部