MySQL 到 PostgreSQL 数据迁移实战：从工具选型到踩坑填坑全记录

2026/6/25 21:35:34

作者：睡不醒男孩 | 发布时间：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 验证三道关卡。

7.3 写在最后

从 MySQL 迁移到 PostgreSQL，本质上是从一个“宽容”的数据库世界进入一个“严谨”的数据库世界。这个过程会有阵痛，会有意想不到的坑，但 PostgreSQL 在数据完整性、扩展性、复杂查询等方面的优势，值得付出这些努力。

正如一位同行所说：“MySQL 让你快速跑起来，PostgreSQL 让你跑得更远。”希望这篇文章能帮助你在迁移的路上少踩一些坑，多快好省地到达目的地。

CLup6.x产品手册：CLup简介CLup软件是专为PostgreSQL、PolarDB等数据库实现了高可用(包括读写分离)集群功能和基础监控管理以及备份恢复平台软件，本章介绍：CLup简介https://www.csudata.com/clup/manual

MySQL 到 PostgreSQL 数据迁移实战：从工具选型到踩坑填坑全记录

一、为什么要迁？

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

三、迁移前的准备工作

3.1 环境与数据概况

3.2 迁移前的三项核心评估

3.3 安装 pgloader

四、分步迁移实践

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

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

4.3 第三步：执行数据迁移

4.4 第四步：迁移后处理

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

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

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

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

坑 4：字符集与排序规则

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

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

坑 7：datetime 时区问题

六、迁移后的数据校验

6.1 行数校验

6.2 抽样校验

6.3 业务 SQL 验证

七、总结与建议

7.1 迁移 checklist

7.2 核心经验

7.3 写在最后

最新新闻

日新闻

周新闻

月新闻

一、为什么要迁？

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

三、迁移前的准备工作

3.1 环境与数据概况

3.2 迁移前的三项核心评估

3.3 安装 pgloader

四、分步迁移实践

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

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

4.3 第三步：执行数据迁移

4.4 第四步：迁移后处理

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

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

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

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

坑 4：字符集与排序规则

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

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

坑 7：datetime 时区问题

六、迁移后的数据校验

6.1 行数校验

6.2 抽样校验

6.3 业务 SQL 验证

七、总结与建议

7.1 迁移 checklist

7.2 核心经验

7.3 写在最后

相关新闻

轻松搭建个人游戏串流服务器：Sunshine实用指南

WindowResizer：免费开源窗口调整工具完全指南

靠谱的江西单招机构

最新新闻

日新闻

周新闻

月新闻