作者睡不醒男孩 | 发布时间2026-06-24一、为什么要迁近年来PostgreSQL 在开发者群体中的受欢迎程度持续攀升。根据某知名开发者调查PostgreSQL46.48%在专业开发者群体中已超越 MySQL45.68%成为最受欢迎的数据库。越来越多的团队开始考虑将核心业务从 MySQL 迁移到 PostgreSQL驱动因素包括更强大的 JSON 支持JSONB、更丰富的索引类型、更严格的数据完整性保障、以及对复杂查询的优化能力。然而“为什么要迁”的答案往往很简单但“怎么迁”的过程却充满挑战。MySQL 和 PostgreSQL 虽然都是关系型数据库但在数据类型、SQL 语法、约束机制、字符集处理等方面存在大量细微差异。一次看似简单的数据迁移可能因为一个字段类型的不兼容、一条 SQL 语法的差异就让整个项目陷入困境。本文基于真实项目的迁移实践系统梳理了从 MySQL 到 PostgreSQL 的完整迁移路径重点记录了我们踩过的坑和填坑的方法希望能为正在或即将进行类似迁移的团队提供参考。二、迁移方案选型没有最好的工具只有最合适的方案在开始迁移之前首先需要回答一个问题用什么工具目前行业主流的三类迁移方案各有适用场景方案一命令行工具链mysqldump psqlmysqldump -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 或大数据量场景效率极低且容易出错。方案二专用迁移工具pgloaderpgloader 是一款专门为迁移到 PostgreSQL 设计的开源命令行工具支持从 MySQL、SQLite、SQL Server 等多种数据源迁移数据。它使用 PostgreSQL 的COPY协议进行数据流式传输迁移速度远快于常规 SQL 插入。同时pgloader 能自动处理大部分数据类型转换并提供灵活的配置能力。这是本文采用的核心工具。方案三图形化/商业化迁移工具对于缺乏专业技术团队的中小企业或需要零停机迁移的场景可以考虑 DBeaver、NineData、DBConvert 等商业化工具。它们提供可视化界面、CDC 实时同步等高级功能。我们最终选择了pgloader原因有三开源免费、性能卓越、社区活跃。对于 20GB 左右的数据量pgloader 的表现令人满意。三、迁移前的准备工作3.1 环境与数据概况本次迁移的源端和目标端环境如下项目源端MySQL目标端PostgreSQL版本MySQL 5.7PostgreSQL 15.3数据量约 20GB—表数量87 张—最大单表约 3800 万行—字符集utf8mb4UTF83.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 的建表语句直接拿过来跑。核心差异点特性MySQLPostgreSQL自增字段AUTO_INCREMENTSERIAL/BIGSERIAL布尔类型TINYINT(1)BOOLEANJSON 类型JSONJSONB推荐字符串连接CONCAT(str1, str2)str1 || str2分页查询LIMIT offset, sizeLIMIT size OFFSET offset日期计算DATE_ADD(date, INTERVAL)date INTERVAL 1 day实践建议不要试图手工转换每张表的 DDL。pgloader 自带 schema 自动转换能力可以先让 pgloader 生成建表语句人工审核后再执行# 仅生成 DDL不迁移数据 pgloader --dry-run mysql://user:passhost/source_db pgsql://user:passhost/target_db4.2 第二步编写 pgloader 配置文件对于复杂迁移任务推荐创建.load配置文件而非使用命令行参数。以下是我们使用的配置文件migrate.loadLOAD DATABASE FROM mysql://migration_user:passwordmysql-host:3306/source_db INTO postgresql://postgres:passwordpg-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否则后续插入可能产生主键冲突。坑 2JSON 字段的“静默数据损坏”问题现象迁移后第二天业务系统突然爆发大量 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( hostmysql-host, usermigration_user, password***, databasesource_db ) cursor conn.cursor(dictionaryTrue) cursor.execute(fSELECT {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时指定精度处理方式。坑 3MySQL 的“宽松类型” 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();迁移完成后恢复默认值。坑 7datetime 时区问题问题现象迁移后某些时间字段的值与 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