MySQL 到 PostgreSQL 数据迁移实战：从工具选型到踩坑填坑全记录-尧图网络科技

作者：睡不醒男孩 | 发布时间：2026-06-24

一、为什么要迁？

近年来，PostgreSQL 在开发者群体中的受欢迎程度持续攀升。根据某知名开发者调查，PostgreSQL（46.48%）在专业开发者群体中已超越 MySQL（45.68%）成为最受欢迎的数据库。越来越多的团队开始考虑将核心业务从 MySQL 迁移到 PostgreSQL，驱动因素包括：更强大的 JSON 支持（JSONB）、更丰富的索引类型、更严格的数据完整性保障、以及对复杂查询的优化能力。

然而，“为什么要迁”的答案往往很简单，但“怎么迁”的过程却充满挑战。MySQL 和 PostgreSQL 虽然都是关系型数据库，但在数据类型、SQL 语法、约束机制、字符集处理等方面存在大量细微差异。一次看似简单的数据迁移，可能因为一个字段类型的不兼容、一条 SQL 语法的差异，就让整个项目陷入困境。

本文基于真实项目的迁移实践，系统梳理了从 MySQL 到 PostgreSQL 的完整迁移路径，重点记录了我们踩过的坑和填坑的方法，希望能为正在或即将进行类似迁移的团队提供参考。

二、迁移方案选型：没有最好的工具，只有最合适的方案

在开始迁移之前，首先需要回答一个问题：用什么工具？

目前行业主流的三类迁移方案各有适用场景：

方案一：命令行工具链（mysqldump + psql）

mysqldump -u root -p --skip-lock-tables db_name > dump.sql sed -i 's/AUTO_INCREMENT=/SERIAL/g' dump.sql psql -U postgres -d target_db -f dump.sql

这种方案的优势在于完全可控，不依赖第三方工具。但缺点也很明显：需要手工处理大量数据类型转换和语法适配工作，对于复杂 schema 或大数据量场景，效率极低且容易出错。

方案二：专用迁移工具（pgloader）

pgloader 是一款专门为迁移到 PostgreSQL 设计的开源命令行工具，支持从 MySQL、SQLite、SQL Server 等多种数据源迁移数据。它使用 PostgreSQL 的COPY协议进行数据流式传输，迁移速度远快于常规 SQL 插入。同时，pgloader 能自动处理大部分数据类型转换，并提供灵活的配置能力。

这是本文采用的核心工具。

方案三：图形化/商业化迁移工具

对于缺乏专业技术团队的中小企业，或需要零停机迁移的场景，可以考虑 DBeaver、NineData、DBConvert 等商业化工具。它们提供可视化界面、CDC 实时同步等高级功能。

我们最终选择了pgloader，原因有三：开源免费、性能卓越、社区活跃。对于 20GB 左右的数据量，pgloader 的表现令人满意。

三、迁移前的准备工作

3.1 环境与数据概况

本次迁移的源端和目标端环境如下：

项目	源端（MySQL）	目标端（PostgreSQL）
版本	MySQL 5.7	PostgreSQL 15.3
数据量	约 20GB	—
表数量	87 张	—
最大单表	约 3800 万行	—
字符集	utf8mb4	UTF8

3.2 迁移前的三项核心评估

在动手之前，我们完成了三项核心评估：

数据规模评估：千万级数据量，决定采用分批次迁移策略，而非一次性全量迁移。
应用兼容性分析：重点检查了应用中使用的存储过程、函数、触发器等，评估 PL/pgSQL 语法兼容性。
迁移成本测算：明确了停机窗口期（4 小时）、数据一致性要求（强一致性）、以及回滚方案。

3.3 安装 pgloader

在 Linux 环境（Ubuntu 22.04）上安装 pgloader：

# Ubuntu/Debian sudo apt-get update sudo apt-get install pgloader # 或从源码编译 # 从 https://github.com/dimitri/pgloader 获取最新版本

四、分步迁移实践

4.1 第一步：Schema 迁移——建表语句的转换

Schema 迁移是整个迁移过程中最容易被忽视却最容易出问题的环节。MySQL 和 PostgreSQL 在 DDL 语法上存在诸多差异，不能简单地把 MySQL 的建表语句直接拿过来跑。

核心差异点：

特性	MySQL	PostgreSQL
自增字段	`AUTO_INCREMENT`	`SERIAL`/`BIGSERIAL`
布尔类型	`TINYINT(1)`	`BOOLEAN`
JSON 类型	`JSON`	`JSONB`（推荐）
字符串连接	`CONCAT(str1, str2)`	`str1 \|\| str2`
分页查询	`LIMIT offset, size`	`LIMIT size OFFSET offset`
日期计算	`DATE_ADD(date, INTERVAL)`	`date + INTERVAL '1 day'`

实践建议：不要试图手工转换每张表的 DDL。pgloader 自带 schema 自动转换能力，可以先让 pgloader 生成建表语句，人工审核后再执行：

# 仅生成 DDL，不迁移数据 pgloader --dry-run mysql://user:pass@host/source_db pgsql://user:pass@host/target_db

4.2 第二步：编写 pgloader 配置文件

对于复杂迁移任务，推荐创建.load配置文件而非使用命令行参数。以下是我们使用的配置文件migrate.load：

LOAD DATABASE FROM mysql://migration_user:password@mysql-host:3306/source_db INTO postgresql://postgres:password@pg-host:5432/target_db WITH include drop, create tables, create indexes, reset sequences, workers = 8, concurrency = 4, multiple readers per thread, rows per range = 50000 SET PostgreSQL PARAMETERS maintenance_work_mem = '2GB', work_mem = '256MB' CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null, type tinyint to boolean using tinyint-to-boolean, type json to jsonb;

关键参数说明：

workers = 8：启动 8 个工作线程并行迁移
rows per range = 50000：每批次处理 50000 行
CAST子句：自定义数据类型转换规则

4.3 第三步：执行数据迁移

pgloader migrate.load

执行过程中，pgloader 会实时输出进度信息，包括已迁移的行数、耗时、吞吐量等。

对于 20GB 的数据，在我们的测试环境中，全量迁移耗时约45 分钟，远快于使用mysqldump + psql的方案（预计 3-4 小时）。

4.4 第四步：迁移后处理

数据迁移完成后，还需要执行一系列收尾工作：

-- 1. 分析数据库，更新统计信息 ANALYZE; -- 2. 检查序列值是否正确（非常重要！） SELECT schemaname, sequencename, last_value FROM pg_sequences WHERE schemaname = 'public'; -- 3. 重建外键约束（如果在迁移时跳过了） -- 4. 验证数据完整性

五、那些年我们踩过的坑（及填坑方法）

坑 1：自增主键的“空值违反非空约束”

问题现象：

迁移完成后，尝试插入数据时报错：

ERROR: null value in column "id" violates not-null constraint

根本原因：

MySQL 中，AUTO_INCREMENT字段默认允许空值（插入时留空会自动填充）。而 PostgreSQL 的SERIAL类型字段不允许空值。迁移时如果源表中存在id IS NULL的记录，PostgreSQL 会直接报错。

解决方案：

在迁移之前，先检查并清理 MySQL 源数据：

-- 查找存在空值的记录 SELECT * FROM your_table WHERE auto_increment_column IS NULL;

如果确实存在空值记录，有两种处理方式：

为这些记录补上合理的值（推荐）
在迁移后手动创建序列并关联，但这种方式风险较高

迁移完成后，务必检查序列的last_value是否大于表中现有最大 ID，否则后续插入可能产生主键冲突。

坑 2：JSON 字段的“静默数据损坏”

问题现象：

迁移后第二天，业务系统突然爆发大量 JSON parsing error。排查发现，MySQL JSON 字段中存储的某些数值在 PostgreSQL 的 JSONB 中发生了精度丢失。

MySQL 原始数据：

INSERT INTO user_features VALUES (1, '{"embedding": [0.1234567890123456, 0.9876543210987654], "score": 12345678901234567890}' );

迁移后查询结果：

SELECT user_id, feature_json->>'score'::BIGINT FROM user_features; -- 结果：12345678901234567168 -- 注意最后几位变了！

根本原因：

MySQL 的 JSON 类型本质上是文本存储，不对数值类型做强制转换，保留原始字符串形式。而 PostgreSQL 的 JSONB 是二进制结构化存储，会解析数值为 NUMERIC 或 FLOAT8 类型。FLOAT8 是 IEEE 754 双精度浮点数，只有约 15-17 位有效数字，大整数（超过 2^53）会丢失精度。

解决方案：

在迁移前执行数据审计，识别可能受影响的字段：

#!/usr/bin/env python3 # audit_json_precision.py - 审计 JSON 字段数值精度 import mysql.connector import json def check_precision_loss(table, json_column): conn = mysql.connector.connect( host='mysql-host', user='migration_user', password='***', database='source_db' ) cursor = conn.cursor(dictionary=True) cursor.execute(f"SELECT {json_column} FROM {table} LIMIT 10000") rows = cursor.fetchall() precision_issues = [] for row in rows: data = json.loads(row[json_column]) # 检查数值精度 # 如果数值超过 2^53，标记为风险 # ... return precision_issues

长期方案：在应用层将大整数存储为字符串，或在迁移时使用自定义 CAST 规则将json转为jsonb时指定精度处理方式。

坑 3：MySQL 的“宽松类型” vs PostgreSQL 的“强类型”

问题现象：

迁移后，某些原本在 MySQL 中正常运行的 SQL 语句报错，例如：

-- MySQL 中正常运行 SELECT * FROM orders WHERE status = 1; -- status 是 VARCHAR 类型 -- PostgreSQL 中报错 SELECT * FROM orders WHERE status = 1; -- ERROR: operator does not exist: character varying = integer

根本原因：

MySQL 支持自动类型转换，在表字段类型和参数值类型不一致时会自动进行隐式转换。而 PostgreSQL 是强数据类型，字段类型和参数值类型必须严格匹配，否则抛出异常。

解决方案：

在迁移前，全面审查应用代码中的 SQL 语句，识别所有可能存在类型不匹配的地方。可以使用静态代码分析工具或 SQL 流量回放工具进行预检。

迁移后，在测试环境中运行完整的回归测试，确保所有 SQL 语句都能正常执行。

坑 4：字符集与排序规则

问题现象：

迁移后，某些中文字段的查询结果排序顺序与 MySQL 不一致，或者某些字符串比较结果不符合预期。

根本原因：

MySQL 支持在表级别和字段级别分别设置字符集和排序规则（如utf8mb4_general_ci）。而 PostgreSQL 中，字符集是数据库级别的，没有在表级别设置字符集的选项。PostgreSQL 中没有与 MySQL 的_ci（不区分大小写）排序规则直接等价的默认排序规则。

解决方案：

如果确实需要不区分大小写的比较，可以在 PostgreSQL 中创建使用 ICU 不区分大小写比较的排序规则，但只能用于列级排序规则，不能用作数据库默认排序规则：

CREATE COLLATION case_insensitive ( provider = 'icu', locale = 'und-u-ks-level2', deterministic = false ); -- 在特定列上使用 CREATE TABLE users ( name TEXT COLLATE case_insensitive );

更务实的建议：在迁移前统一源端和目标端的字符集为 UTF8，并在应用层处理大小写敏感问题，而非依赖数据库的排序规则。

坑 5：外键约束导致的迁移失败

问题现象：

pgloader 在迁移过程中因外键约束违反而中断。

根本原因：

MySQL 和 PostgreSQL 对外键约束的处理机制不同。在数据迁移过程中，如果表之间的依赖关系复杂，先导入父表还是子表的顺序问题可能导致约束检查失败。

解决方案：

在全量导入阶段，暂时禁用所有外键约束，导入完成后再逐一启用：

-- 禁用所有触发器（包括外键约束相关的） SET session_replication_role = 'replica'; -- 执行数据导入 -- ... -- 重新启用 SET session_replication_role = 'origin';

或者在 pgloader 配置中指定create tables时不创建外键，迁移完成后再手动添加。

坑 6：性能问题——大数据量表迁移慢

问题现象：

单张 3800 万行的表，迁移耗时超过 2 小时。

根本原因：

默认配置下，pgloader 的并发度和批次大小可能不足以充分发挥硬件性能。

解决方案：

调整 pgloader 配置参数：

WITH workers = 16, -- 增加工作线程 concurrency = 8, -- 增加并发连接数 rows per range = 100000 -- 增大批次大小

同时，在目标 PostgreSQL 上调整性能参数：

-- 迁移前临时调整 ALTER SYSTEM SET maintenance_work_mem = '4GB'; ALTER SYSTEM SET work_mem = '512MB'; SELECT pg_reload_conf();

迁移完成后恢复默认值。

坑 7：datetime 时区问题

问题现象：

迁移后，某些时间字段的值与 MySQL 中不一致，相差了几个小时。

根本原因：

MySQL 的DATETIME是“无时区”的时间值，存储的是字面量时间。而 PostgreSQL 的TIMESTAMP默认使用系统时区。如果不做特殊处理，时间值在迁移过程中可能被错误转换。

解决方案：

统一使用 UTC 存储时间，在应用层进行时区转换。

在 pgloader 配置中使用 CAST 规则：

CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null;

六、迁移后的数据校验

数据迁移完成后，必须进行数据完整性校验，否则后续业务将建立在错误数据之上。

6.1 行数校验

-- 在 MySQL 和 PostgreSQL 中分别执行 SELECT COUNT(*) FROM each_table;

6.2 抽样校验

对于关键业务表，随机抽取 1000-10000 条记录，逐字段比对。

6.3 业务 SQL 验证

运行核心业务 SQL 语句，比对 MySQL 和 PostgreSQL 上的执行结果是否一致。

建议：在迁移后 24 小时内，每日运行一次校验脚本，持续监控数据一致性。

七、总结与建议

7.1 迁移 checklist

完成迁移前评估（数据规模、应用兼容性、停机窗口）
选择合适迁移工具（本文推荐 pgloader）
在测试环境完成完整迁移演练
准备回滚方案（数据库快照）
执行 Schema 迁移，人工审核 DDL
处理特殊数据类型（JSON、ENUM、datetime 等）
执行数据迁移，监控进度
迁移后处理（ANALYZE、序列重置、外键重建）
数据完整性校验
应用切换与灰度验证
正式上线

7.2 核心经验

永远先在测试环境跑一遍：生产环境直接迁移是灾难的开始。在生产迁移之前，务必在测试环境完成至少一次完整的迁移演练。
关注数据类型差异：MySQL 和 PostgreSQL 最核心的差异在于类型系统。MySQL 宽松，PostgreSQL 严格。提前识别所有类型不兼容的地方，是迁移成功的关键。
不要低估自增主键的坑：看似简单的AUTO_INCREMENT→SERIAL，实际涉及序列值的初始化、空值处理、以及迁移后的序列同步。
JSON 字段要特别小心：MySQL JSON 和 PostgreSQL JSONB 的实现差异可能导致静默数据损坏——数据看起来迁过去了，但精度已经丢了。
性能优化从配置开始：pgloader 的默认配置偏保守，调整workers、concurrency、rows per range等参数可以显著提升迁移速度。
校验不是可选项：迁移完成 ≠ 迁移成功。必须通过行数校验、抽样校验、业务 SQL 验证三道关卡。