迁库这件事很多人第一反应是不就是把数据倒过去吗真上手了才会发现从MySQL到PostgreSQL最难的不是搬数据而是搬完之后业务还能不能正常跑起来。我去年把一个跑了两年的订单系统从MySQL 8.0迁到了PostgreSQL 14数据量大概1.2TB前后花了两周多。这篇文章想把整个过程中踩过的坑、验证过的方案、还有那些文档里不会明说的细节完整地梳理一遍。不管你是被PostgreSQL的JSONB、窗口函数、或者更严格的SQL标准吸引还是因为公司技术栈统一、多云部署的需要这篇都值得你花十分钟读完。目标只有一个让你在动手之前就知道自己会撞上哪些墙。1. 为什么要迁三个真实场景下的迁移动机与决策迁移这事最怕的不是技术难而是动机不清晰。我见过太多人因为听说PostgreSQL很强就拍板迁移结果迁到一半发现业务场景压根用不上这些新特性白白折腾一个月。先冷静想清楚这几个问题。1.1 场景一复杂分析查询性能到了瓶颈MySQL做OLTP很稳但一旦涉及多表关联、子查询嵌套、开窗函数的复杂报表优化器的表现就不太稳定。我之前有个统计报表单条SQL要join八张表里面还有三个子查询在MySQL 8.0上跑一次要一分半钟。因为那个报表是给运营每天凌晨跑一次的勉强还能忍。但后来需求变成实时看板这个性能就扛不住了。PostgreSQL的查询优化器做得更扎实尤其擅长处理复杂JOIN和子查询同一个报表迁过去之后没做任何SQL改写跑了大概二十秒后面加了个物化视图直接降到两秒以内。如果你的业务里有大量复杂分析场景这会是迁移最核心的收益。1.2 场景二需要高级数据类型和更强的一致性保障MySQL的JSON字段在8.0之前基本就是个存储串的存在查询要用JSON_EXTRACT函数索引效率也一般。PostgreSQL的JSONB是真正的二进制存储支持GIN索引反向匹配还支持在JSON内部字段上建索引这个差别用过的都懂。两个更典型的例子一是PostgreSQL的表继承、分区裁剪、约束排他EXCLUDE这些特性在MySQL里都没有对应物二是PostgreSQL的MVCC实现和RR隔离级别下的表现处理并发冲突时更符合读不阻塞写、写不阻塞读的直觉。如果你的业务对数据一致性、复杂查询能力有硬性要求迁移的回报率非常高。1.3 场景三标准SQL兼容性和生态中立性MySQL有一些自己的SQL方言比如REPLACE INTO、INSERT ... ON DUPLICATE KEY UPDATE这些语法换到别的数据库基本没法直接用。PostgreSQL更贴近SQL标准应用层SQL写得好未来换到Oracle、达梦这类数据库的改造成本会小很多。如果你的团队有长期的多云部署或多数据库支撑需求这是一个值得认真考虑的战略选择。我不建议的场景是业务纯CRUD、单机数据量不到几十G、也没有复杂的SQL需求。这种项目迁到PostgreSQL不会带来明显收益反而要承担迁移过程中的风险和人力成本。迁库不是炫技是解决实际问题的手段。2. 迁移前必做的一次技术盘点两类数据库的本质差异很多人以为迁移就是把数据类型一一对应改改连接串就完事。这是最大的误区。MySQL和PostgreSQL表面上看都是关系型数据库但底层架构和语义差异非常大。不理解这些差异迁移后的系统就像换了发动机的旧车跑起来总会有各种奇怪的问题。2.1 存储引擎与MVCC实现差异MySQL最常用的InnoDB是索引组织表IOT数据按主键聚簇存放二级索引的叶子节点存的是主键值。PostgreSQL用的是堆表数据按插入顺序存放在堆里索引独立存储索引项直接指向行的物理位置通过ctid定位。这个差异带来的直接后果是PostgreSQL的UPDATE会产生新行版本并触发VACUUM清理旧版本MySQL的UPDATE在InnoDB里是同页更新大多数情况下PostgreSQL的二级索引不需要回表就能找到行通过索引项里的ctidMySQL的二级索引回表是常态高频UPDATE写多读少的场景PostgreSQL要小心VACUUM压力和表膨胀问题。2.2 隔离级别与锁机制差异MySQL InnoDB的默认隔离级别是Repeatable Read但它的RR是用Next-Key Lock实现的不光锁行还锁间隙。PostgreSQL的默认隔离级别是Read Committed它的RR级别实现方式完全不同——通过快照实现不会阻塞读写。PostgreSQL在RR级别下可能出现序列化异常serialization anomaly而MySQL的RR则更倾向于锁等待和死锁。在并发更新同一行数据的压力测试里PostgreSQL的表现通常更平滑不会出现一堆事务互相锁死的情况。这点在做并发压测的时候会明显感觉到。2.3 数据字典和约束行为的差异MySQL早期版本里外键在MyISAM引擎下就是个摆设InnoDB之后的约束检查也比较宽松。PostgreSQL对外键、唯一约束、CHECK约束的检查是严格执行的。迁移时如果源库有不规范的数据比如违反唯一约束的重复记录、违反CHECK约束的非法值在PostgreSQL里会直接插入失败。这也是为什么我强烈建议迁移之前先在MySQL里做一轮数据质量检查把重复记录、空字符串和NULL混用的问题都揪出来。否则数据加载到一半崩了排查起来会很痛苦。3. 迁移方案选型为什么我把宝压在pgloader上迁移方案网上能搜到一大把但真正能用的主要就三类物理迁移冷备份恢复、逻辑迁移用工具导数据、应用层双写切换。我这次用的是逻辑迁移里的pgloader下面说说这三类的取舍逻辑。3.1 物理迁移最快但限制最多的路MySQL的物理备份比如xtrabackup的备份集和PostgreSQL的物理备份pg_basebackup字节级格式完全不兼容物理迁移只适用于从PostgreSQL到PostgreSQL的情况。如果你是从MySQL迁过来这条路直接不用考虑。3.2 应用层双写最稳但是最累的路双写方案是指业务代码同时写MySQL和PostgreSQL跑一段时间后对比数据一致性再把读流量切到PostgreSQL。好处是风险可控坏处是业务代码要改两套写入逻辑对团队开发量要求很高。适合那种不能接受任何停机时间的核心系统。我这次是内部业务系统能接受两小时停机窗口所以没选这条重量级的路。3.3 pgloader开源免费专为迁库打造pgloader是我最终的选择理由很实际能直接从MySQL读取schema和数据减少很多手工步骤内置数据校验流程加载完能对比源库和目标库的数据行数支持在线迁移模式但要小心锁表问题配置简单核心逻辑就一个.load文件。我用pgloader迁了1.2TB的数据大概花了四五个小时。如果自己写脚本导CSV再灌进去晚高峰时段的MySQL压力会把业务拖垮pgloader的流式读取和批量写入反而是最安全的。3.4 备选方案ETL工具与手工迁移的适用场景如果你所在的公司已经有成熟的ETL平台比如DataX、Kettle、Airbyte也可以走ETL通道做全量及增量同步。DataX的MySQLReader和PostgreSQLWriter组件都比较成熟断点续传也做得好。手工写脚本的方式只适合数据量在百万级以内的小表超过这个量级批处理、断点、重试这些逻辑自己在脚本里写一遍成本太高。我的建议很简单数据量小于100G、表结构不复杂的pgloader足够数据量大于100G、又有实时增量需求的上DataX或专业数据同步工具做全量加增量最后在切换窗口做一次短暂的停写追平。4. Schema迁移实战建表语句、数据类型与默认值处理迁移最繁琐的环节是schema。MySQL和PostgreSQL的类型体系虽然有交集细节上差别很大。如果让pgloader全自动生成目标schema它通常能跑通但生成的结果可能不符合你的预期。我习惯的做法是让pgloader先把schema建好我再用脚本生成DDL做一轮人工review。4.1 数据类型映射表我整理的一份实测对照MySQLPostgreSQL说明TINYINTSMALLINTMySQL的TINYINT只有1字节对应PostgreSQL的SMALLINT合理其实PostgreSQL没有专门的BOOL替代TINYINT(1)这种约定INTINTEGER直映没坑BIGINTBIGINT直映VARCHAR(n)VARCHAR(n)直映注意PostgreSQL的VARCHAR不会自动截断超出会报错MySQL在非严格模式下会静默截断DATETIMETIMESTAMP直映注意时区语义建议迁移前统一确认按哪个时区处理TIMESTAMPTIMESTAMPTZ如果MySQL的TIMESTAMP是带时区语义的建议迁到PostgreSQL的TIMESTAMPTZDECIMAL(p,s)NUMERIC(p,s)直映注意PostgreSQL需要显式指定精度ENUM视应用而定PostgreSQL有原生ENUM类型但强烈建议换成VARCHARCHECK约束原因下面说JSONJSONB如果你只做存取JSON够用如果要在查询里用一定是JSONBBLOBBYTEA直映TINYINT(1)BOOLEANMySQL里TINYINT(1)经常被当成布尔用迁移时最好显式转成BOOLEAN语义更清晰4.2 自增主键的处理最容易翻车的地方MySQL的AUTO_INCREMENT在PostgreSQL里对应两种方案SERIAL系列INT/BIGINT和IDENTITY列GENERATED AS IDENTITY。我建议用后者它是SQL标准语法语义更清晰。但这里有个坑迁移历史数据时MySQL已经产生的自增值会作为普通数据灌入PostgreSQL而PostgreSQL的序列起始值不会自动跟着变。如果你不处理下一个自增ID可能从1开始直接撞上已有数据的主键。解决办法很直接数据装载完成后手工把序列设置到当前最大值SELECT setval(orders_id_seq, (SELECT max(id) FROM orders));这条命令我每次迁移后必跑一遍否则线上第二天就会出现主键冲突。4.3 字符集、排序规则和大小写敏感性MySQL 8.0默认字符集utf8mb4排序规则是utf8mb4_0900_ai_ci末尾的ci是不区分大小写。PostgreSQL的默认UTF8排序规则通常带locale区分不同操作系统默认值不一样。最典型的影响是查询条件MySQLSELECT * FROM users WHERE name Smith默认能匹配到smithPostgreSQL Smith默认区分大小写必须用ILIKE或LOWER()处理。迁移时如果要保持和原来一样的大小写不敏感行为最稳妥的办法是在应用层统一用LOWER()函数而不是依赖数据库排序规则。否则你没法保证每台部署机器上的locale都一致。4.4 表注释、列注释和索引命名的规范化MySQL允许注释写得很随意PostgreSQL同样支持COMMENT ON。我建议迁移时把注释都带上特别是枚举含义、状态字段的取值说明不然两年后接手的人看到status 3会懵。索引命名建议统一改掉。MySQL默认的索引名是idx_xxxPostgreSQL没有这个默认习惯不同工具生成的名字千奇百怪。命名的好处很实际将来定位慢查询看索引名就知道它服务于哪个查询路径。5. 数据迁移实战用pgloader把1.2TB历史数据搬过去Schema确认没问题接下来就是大头——数据搬运。pgloader的安装很简单在Ubuntu上直接apt install pgloader就行。然后写一个.load配置文件把源库和目标库的信息都填进去。这里我截取一个精简版配置。5.1 一份可以直接改用的pgloader配置LOAD DATABASE FROM mysql://user:passwordmysql_host:3306/dbname INTO postgresql://user:passwordpg_host:5432/dbname WITH include drop, create tables, create indexes, reset sequences, disable triggers, batch rows 5000, batch concurrency 8 SET MySQL PARAMETERS net_read_timeout 120, net_write_timeout 120 , PostgreSQL PARAMETERS maintenance_work_mem 1GB, work_mem 128MB CAST type datetime to timestamptz drop typemod keep default, type tinyint to smallint ;几个关键点我要提醒batch concurrency 8并发太高会把源库搞挂建议从4开始调观察源库的CPU和IO再逐步往上加disable triggers目标库建了外键约束后数据灌入顺序如果不对会报外键冲突先禁用触发器可以避免很多麻烦灌完再启用reset sequences很大程度替代了我前面提到的setval手工步骤但配置里不显式写我仍然会用SQL再核一遍。5.2 执行与监控从哪里看进度、怎么掌握节奏pgloader运行后会在终端打一个动态进度面板显示每个表的行数、错误数、已用时间。但大批量迁移时我更推荐用--load-lisp-file加载一个带日志输出的配置把详细日志写到文件方便后续排查。执行命令pgloader --load-lisp-file my_loader.load --log-file migration.log迁移中途如果某个表报错pgloader默认会跳过并继续错误记录在日志里。我建议不要一口气跑完再去看日志而是每10分钟瞄一眼错误率。一个典型情况MySQL里某些字段值是非法UTF8序列pgloader写入PostgreSQL时会报编码错误这时候就得在CAST规则里加上with extra做清洗预处理。5.3 数据校验不能只看行数一样就以为万事大吉行数一致只是第一道关卡。我迁移完习惯跑三组校验行数校验每张表分别COUNT(*)关键字段的聚合值校验比如订单表的总金额、用户表的创建时间最大最小值用SQL分别在两端跑一遍对比抽样校验每张表随机抽100条记录对比关键字段的MD5值。在对比MD5这一步我抓到过一个很隐蔽的坑浮点数在MySQL和PostgreSQL的底层存储精度不完全一致导致0.1 0.2这类运算结果的小数位末尾会有微小差别。如果你的业务对金额、坐标这类浮点字段有严格的等值条件建议迁移时把所有浮点列改成NUMERIC类型保证计算精度。6. 业务代码适配SQL差异、驱动更换与ORM层调整数据搬完了只是完成了40%的工作。剩下的大头是让应用层的代码在新数据库上正常跑。没有哪个项目能完全不做代码调整就完成迁移我说的没有是真的没有。6.1 驱动与连接方式Java项目从mysql-connector-java换成postgresqlJDBC驱动连接串从jdbc:mysql://...改成jdbc:postgresql://...这块相对平滑。Python项目则从pymysql换成psycopg2或psycopg3。Go项目是go-sql-driver/mysql换成pgx。换驱动本身简单真正的坑在SQL语法和类型映射上。6.2 高频SQL差异清单直接从踩坑记录里抄类型MySQL写法PostgreSQL写法自增主键返回值SELECT LAST_INSERT_ID()INSERT ... RETURNING id分页LIMIT 10 OFFSET 20通用写法一致也可以用OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY字符串连接CONCAT(first_name, , last_name)first_name判空IFNULL(col, 0)COALESCE(col, 0)更新多表UPDATE t1 JOIN t2 ON ... SET t1.c ...UPDATE t1 SET c ... FROM t2 WHERE ...插入冲突处理INSERT ... ON DUPLICATE KEY UPDATE c VALUES(c)INSERT ... ON CONFLICT (id) DO UPDATE SET c EXCLUDED.c正则匹配REGEXP~或REGEXPPG 15以后支持布尔值WHERE flag 1WHERE flag如果flag是BOOLEAN类型上面列出来的语法差异都有一个共同特点它会影响SQL语义但不会报错所以特别容易漏。最危险的反而是那些MySQL能跑PostgreSQL也能跑但结果不一样的语句。6.3 ORM层MyBatis、JPA等框架的处理思路如果你用MyBatisSQL通常是自己维护的上面的表逐个替换就行。建议在Mapper XML里做一个全局搜索把IFNULL、LAST_INSERT_ID、ON DUPLICATE KEY UPDATE全部扫出来逐条确认。JPA/Hibernate这类生成SQL的框架通常内置了数据库方言切换database-platform配置后大部分关联查询会自动适配但一些自定义Query还是要手动改。对于字段类型映射我建议在ORM层做一次显式映射配置而不是依赖自动映射。比如MySQL的TINYINT(1)在PostgreSQL里如果转成BOOLEANMyBatis的resultType如果是Integer映射就会挂。这种问题在单元测试阶段很难暴露必须用真实数据联调才会发现。6.4 存储过程与触发器MySQL的这套玩法到了PostgreSQL要推倒重来MySQL的存储过程语法基于SQL/PSMPostgreSQL则是PL/pgSQL。两者差异很大几乎没有自动转换工具能靠谱。我的建议是尽量用应用层代码替代存储过程如果确实必须保留那就在PL/pgSQL里重新实现一遍。触发器同理MySQL触发器和PostgreSQL触发器的行为差异也很多PostgreSQL支持BEFORE/AFTER加FOR EACH ROW/FOR EACH STATEMENT书写方式完全不同。这套改造我做了快三天是迁移中耗时最长的单项。如果你的系统里存储过程特别多预算迁移时间时一定要把它算进去。7. 迁移后的一周从VACUUM策略到慢查询复盘迁库完成不代表项目结束。甚至可以说真正的问题排查是从切换之后才开始的。PostgreSQL和MySQL在运行机制上的差异会在高负载下以各种形式暴露出来。7.1 VACUUM和表膨胀PostgreSQL的独有功课MySQL的InnoDB有purge线程异步清理旧版本PostgreSQL的VACUUM则是显式存在的重要机制。虽然autovacuum默认是开启的但默认参数在迁移后的系统上未必合适。我遇到过一个典型问题有一张大表频繁UPDATE默认的autovacuum阈值没有及时触发表膨胀到原来的三倍查询性能雪崩。解决方法是针对热点表单独调整ALTER TABLE orders SET (autovacuum_vacuum_scale_factor 0.05); ALTER TABLE orders SET (autovacuum_vacuum_threshold 1000);如果你的业务和订单系统类似UPDATE频繁且单表数据量大建议迁移后第一周每周检查一次pg_stat_user_tables里的n_dead_tup和n_live_tup比值比值超过0.2就要考虑手动VACUUM或调低触发阈值了。7.2 ANALYZE和统计信息不给它喂数据它怎么知道怎么走索引迁移后在大量历史数据灌入的情况下自动ANALYZE收集到的统计信息可能不准确。最保险的做法是在迁移完成后对全库做一次显式ANALYZEvacuumdb --analyze-only --all -h pg_host -U user否则你会发现有些SQL走了全表扫描性能惨不忍睹。特别是那些字段取值分布很不均匀的表比如订单状态字段90%都是已完成的没有准确统计信息时查询计划基本就是抽奖。7.3 EXPLAIN的变化需要一段时间适应新体检工具MySQL的EXPLAIN输出是一张扁平的表格PostgreSQL的EXPLAIN是树状结构还要结合缓冲区命中率一起看。我适应的办法很简单把迁移前后最核心的20条慢SQL的查询计划打印出来做前后对比这个对比能帮你快速感知到PostgreSQL哪些场景强、哪些场景需要注意。一条适合PostgreSQL的分析查询示例EXPLAIN (ANALYZE, BUFFERS) SELECT c.customer_name, SUM(o.amount) FROM orders o JOIN customers c ON o.customer_id c.id WHERE o.created_at 2024-01-01 GROUP BY c.customer_name ORDER BY SUM(o.amount) DESC LIMIT 10;重点看actual time、rows和buffers如果某个节点actual rows和estimate rows差10倍以上说明统计信息还是有问题需要对相关表重新ANALYZE。7.4 备份与恢复策略也要跟着换MySQL时代你可能习惯了mysqldump定时全备。PostgreSQL的pg_dump功能类似但我要提醒一个差异pg_dump默认导出的SQL文件恢复时要手动建库建议用pg_dump -Fc生成自定义格式配合pg_restore可以做到按表恢复。逻辑备份之外物理备份pg_basebackup做全量基础备份再配合wal_archiving做时间点恢复PITR才是生产环境的标准姿势。备份策略调整好之后至少要做一次恢复演练不然等到真要恢复时才发现备份脚本有问题那时候就晚了。8. 迁移后的小结那些我希望早点知道的事文章写到这核心内容差不多了。最后补几个我在整个迁移过程中体会最深的小细节如果你马上也要做这件事它们能帮你省几个下午的时间。第一不要在业务高峰期做schema变更和数据装载。听起来像是废话但真的有人会在白天直接跑pgloader结果把源库的IO打满线上接口超时报警一片。我那次是挑了周末凌晨两点开始虽然自己辛苦点但安全。第二迁移前把MySQL那边所有utf8mb4的表过一遍字符集校验。PostgreSQL对非法字符的处理比MySQL严格如果源库有数据是历史遗留的乱码pgloader会在中途报错。第三切流量的时候别一下子全切。先让5%的读流量走PostgreSQL跑一天对比错误率再逐步提高比例。我的切流脚本是用nginx的split_clients按用户ID哈希做灰度分配的这样同一用户请求前后行为一致不会出现同一个人在MySQL和PostgreSQL之间反复横跳。迁库是个系统工程最难的不是某一道技术题而是把一堆细节串起来。你不必一回生二回熟最好第一回就按先盘点差异再选方案再做schema再搬数据再改代码再观察运行这个顺序走完。我踩过的坑你看到了应该能少走不少弯路。
