做了几年数据迁移最近又把一套跑了快五年的MySQL 5.7业务库迁到了PostgreSQL 15整个过程从评估、踩坑到最终切换差不多花了一周。这篇“MySQL迁移到PostgreSQL操作指南”就是基于这次实操整理出来的适合正在纠结要不要换库、或者已经决定迁移但不知道从哪下手的DBA和后端开发。我会把迁移前要盘点什么、两边数据库的差异点、三种可落地的迁移路径、以及迁完后容易踩的坑一次讲清楚尽量不绕弯子。先说明一下我的场景源库是MySQL 5.7单实例总共120多张表数据量80GB左右最大的订单表有3000多万行带分区。目标库是PostgreSQL 15部署在同一内网的新服务器上。业务是典型的Java Spring Boot应用用MyBatis-Plus这决定了SQL层不能太任性很多MySQL私有的写法要提前规避。整个迁移全程要求尽量少停服最终我们选择了“全量增量切换”的路径但也会把更简单的一次性迁移方案一并讲清楚。1. 迁移前别急着动手把“家底”盘点清楚1.1 为什么MySQL到PostgreSQL不是导出导入这么简单很多人刚开始做迁移时想的是“把数据导出再导进去不就行了”。真这么干通常会在第二步就卡住MySQL的建表语句里全是反引号、ENGINEInnoDB、AUTO_INCREMENT、TINYINT(1)这些PostgreSQL根本不认导出来的SQL直接执行报错能刷一屏幕。两个数据库设计哲学就不一样。MySQL更偏“实用主义”很多类型和语法怎么方便怎么来PostgreSQL更偏“标准主义”类型严格、约束严谨同样一条SQL在MySQL里能跑在PG里可能直接拒绝。这决定了迁移不是数据搬运而是“翻译加改造”。另外一个常被忽略的点MySQL里的隐式类型转换很宽松比如字符串和数字比较MySQL会默默转PG则会较真类型不匹配就报错。应用层写的时候没感觉迁过去之后接口突然开始飘异常这就是“隐性差异”在作怪。所以迁移前把兼容性差异搞清楚比急着导出数据重要得多。1.2 迁移前的评估清单对象、数据、应用三层我的习惯是先把家底分三层盘一遍每层一张清单盘完才动手。第一层是数据库对象第二层是数据特征第三层是应用侧依赖三者缺一不可。对象层要统计的东西包括表数量、每张表行数和大小、索引数量及类型、视图、触发器、存储过程/函数、事件调度器MySQL的Event。我这次就发现业务里有7个存储过程、3个Event这些PG不直接兼容需要改写或用外部调度替代。建议用一个SQL把所有对象清单导出来格式大概是对象类型、对象名、所属库、是否使用MySQL私有语法。数据层要关注的是大表超过100万行的单独列出来分区表要标记清楚字符集统一确认一遍自增主键的最大值记录下来后面重置序列要用。最好对每张表做一次SELECT COUNT(*)别用information_schema.tables里的估算行数那个不准后面做数据校验时会被坑。应用层是大多数人容易漏掉的部分。你要确认应用用的数据库驱动是哪个版本MySQL驱动和PG驱动不能混用配置中心里有多少个数据源连接串ORM或SQL语句里有没有用到LIMIT a,b、REPLACE INTO这类MySQL私有写法有没有依赖MySQL的GROUP BY宽松模式。建议把应用日志里执行频率最高的前100条SQL抓出来人工扫一遍把可疑的标记成“待改造”。这三层清单整理完迁移方案的选型、工作量评估、风险点也就基本清楚了。我这次是120张表结构转换加SQL改造两个人花了两天节奏还算可控。2. 读懂MySQL与PostgreSQL的“语言差异”迁移就成功了一半2.1 数据类型映射表TINYINT、DATETIME、AUTO_INCREMENT……逐个说数据类型是迁移的“地基”映射错了后面全乱。我整理了一张常用映射表可以直接对照参考MySQL 类型PostgreSQL 类型注意事项TINYINTSMALLINTMySQL的TINYINT(1)常被当布尔用PG端最好用BOOLEAN并配合驱动转换SMALLINTSMALLINT直接对应INT / INTEGERINTEGER直接对应注意显示宽度如INT(11)在PG没有意义BIGINTBIGINT直接对应DECIMAL / NUMERICNUMERIC能对应PG的NUMERIC更严格精度和标度要提前确认FLOAT / DOUBLEREAL / DOUBLE PRECISION浮点都有精度问题关键金额别用浮点CHAR / VARCHARCHAR / VARCHARVARCHAR长度含义一致但PG没有VARCHAR(0)这种写法TEXT / LONGTEXTTEXTPG的TEXT性能好和VARCHAR基本没区别BLOB / LONGBLOBBYTEA注意驱动层的二进制类型处理方式不同DATETIMETIMESTAMP对应TIMESTAMP WITHOUT TIME ZONETIMESTAMPTIMESTAMPTZMySQL TIMESTAMP有时区概念落地建议用TIMESTAMPTZDATEDATE直接对应TIMETIME直接对应YEARSMALLINTMySQL有YEAR类型PG没有用SMALLINT存ENUM自定义类型或TEXTCHECK谨慎使用PG原生的ENUM加值麻烦建议用CHECK约束SETTEXTCHECK或关联表MySQL的SET类型PG没有需要拆JSONJSONBPG的JSONB功能强大推荐使用GEOMETRYPostGIS的GEOMETRY需要安装PostGIS扩展提前装好这里面最坑的是TINYINT(1)和DATETIME。很多业务系统用TINYINT(1)存布尔值比如用户表的is_deleted字段值只有0和1。迁到PG后如果用工具自动转可能转成SMALLINT这本身没问题但Java应用用Boolean类型接收时驱动层会报错或者拿到奇怪的值。我的建议是TINYINT(1)统一在结构转换阶段就改成BOOLEAN应用层同步把字段类型从Integer改成Boolean。这一步虽然要动代码但值得。DATETIME则要看清业务里存的是“本地时间”还是“带时区的时间”。如果你的应用一直用CURRENT_TIMESTAMP存时间而且服务器时区统一那用TIMESTAMP WITHOUT TIME ZONE就够了如果业务跨时区或者有用户时区显示需求直接用TIMESTAMPTZ省得后面一遍遍改。我这次是统一用了TIMESTAMPTZ因为业务明明有海外用户之前MySQL的DATETIME其实存的是东八区时间迁到PG正好把时区语义纠正过来。2.2 SQL语法差异反引号、LIMIT、REPLACE INTO、UPDATE JOIN……逐个说类型映射只是第一步SQL语法差异才是让开发天天加班的元凶。我整理几个高频差异点都是实际迁移中一定会遇到的反引号问题。MySQL允许用反引号包裹表名和字段名PG里反引号是非法字符标识符用双引号。更关键的是大小写语义PG中不带引号的标识符会被折叠成小写带双引号的标识符严格区分大小写。这就导致一个经典场景MySQL里建的User表迁到PG后如果不加注意建出来的可能是user而应用SQL里写的是User直接报“关系不存在”。LIMIT语法差异。MySQL的LIMIT 10, 20表示跳过10条取20条PG不认识这种写法必须写成LIMIT 20 OFFSET 10。虽然很多ORM会生成标准语法但如果你有手写分页SQL的习惯迁移后第一批报错就来自这里。REPLACE INTO和INSERT ... ON DUPLICATE KEY UPDATE。这是MySQL里很常见的“存在就更新不存在就插入”写法PG没有这两个语法。PostgreSQL对应的是INSERT ... ON CONFLICT (id) DO UPDATE SET ...。举个例子MySQL写法INSERT INTO user_score (user_id, score) VALUES (1, 100) ON DUPLICATE KEY UPDATE score VALUES(score);PG里要改成INSERT INTO user_score (user_id, score) VALUES (1, 100) ON CONFLICT (user_id) DO UPDATE SET score EXCLUDED.score;注意VALUES(score)在PG里是有歧义的新语法用EXCLUDED引用被插入但发生冲突的那行数据。不只是语法ON CONFLICT要求目标列必须有唯一约束或排除约束所以建表时的唯一索引必须提前设计好。UPDATE ... JOIN。MySQL支持UPDATE t1 JOIN t2 ON ... SET t1.col t2.col WHERE ...PG不支持这种关联更新写法需要改成UPDATE ... FROMUPDATE user_info u SET city o.city FROM orders o WHERE o.user_id u.id AND o.created_at 2024-01-01;GROUP BY的宽松度。MySQL默认非ONLY_FULL_GROUP_BY模式下允许SELECT的列不出现在GROUP BY里PG的默认行为更严格必须严格按照GROUP BY字段来。这条会导致不少报表SQL在PG上直接报错需要把相关SQL全部捞出来改写。字符串拼接。MySQL里a b在某些模式会得到0PG里字符串用||连接也可以用CONCAT函数。应用代码里如果用拼字符串拼接参数迁过去可能出现莫名奇妙的结果非常隐蔽。这些差异要想不遗漏建议提前把应用里所有SQL语句做一次静态扫描。我自己是写了个简单的正则脚本把LIMIT、REPLACE INTO、ON DUPLICATE KEY UPDATE、反引号等关键字匹配出来然后逐个确认。这一步很花时间但值得越早发现问题后面切换越顺利。3. 实操三种迁移路径按场景选3.1 路径一pgloader一把梭适合快速验证如果只是小项目表结构简单应用层也没用太多MySQL私有特性pgloader是最快的迁移工具没有之一。pgloader是一个开源工具支持从MySQL、SQLite、MS SQL等数据库迁移到PostgreSQL用法很简单。安装好后一条命令就能跑起来pgloader mysql://root:密码127.0.0.1:3306/mydb postgresql://postgres:密码127.0.0.1:5432/mydbpgloader会自动读取MySQL的表结构生成PG的建表语句把数据类型做转换并把数据导过去。它还能自动创建序列、迁移索引甚至能处理ENUM类型。缺点也很明显一旦遇到类型无法自动映射、特殊索引或者超大表它会跑得很慢甚至卡住而且它生成的PG结构并不一定是最优的默认索引、约束、字段命名都是“能跑就好”的风格生产环境如果直接上线后面还是得手工调。所以我的建议是pgloader适合迁移前的快速验证也就是先拉一个影子库让开发在上面试跑应用提前发现SQL兼容性问题。真正上生产还是要用更可控的流程。我这次也用了pgloader做了一次预迁移跑了大概40分钟拿到了一个能用的影子库开发在上面测了一轮提前发现了几十个SQL不兼容点为正式迁移省了不少时间。3.2 路径二mysqldump加脚本转换适合结构复杂、需要精细控制的场景当表结构复杂、特别是涉及大量存储过程、触发器和特殊类型时我推荐用“导出-转换-导入”的精细路线。控制力最强但工作量也最大。第一步用mysqldump把结构和数据分开导出。结构用--no-data导出数据用--no-create-info导出# 只导结构 mysqldump -h 127.0.0.1 -u root -p --no-data --single-transaction --routines --triggers mydb schema_mysql.sql # 只导数据 mysqldump -h 127.0.0.1 -u root -p --no-create-info --single-transaction --hex-blob --skip-lock-tables --default-character-setutf8mb4 mydb data_mysql.sql这里有几个参数值得注意--single-transaction保证导出时读取一致性快照不锁表--routines --triggers把存储过程和触发器一起导出来--hex-blob让二进制字段导出成十六进制字符串避免乱码--skip-lock-tables避免锁表导致线上业务受影响。第二步写脚本把结构文件里的MySQL方言转为PG语法。最常见的是以下几类替换反引号去掉、ENGINEInnoDB删掉、AUTO_INCREMENT变成PG的GENERATED BY DEFAULT AS IDENTITY或SERIAL、TINYINT(1)变成BOOLEAN、DATETIME变成TIMESTAMPTZ、UNSIGNED去掉PG无此概念。用Python写转换脚本比较合适比sed/perl正则可靠得多。核心逻辑大致是import re with open(schema_mysql.sql, r, encodingutf-8) as f: sql f.read() sql sql.replace(, ) sql re.sub(rENGINE\s*\s*\w.*?(?;|$), , sql, flagsre.S) sql re.sub(rUNSIGNED, , sql, flagsre.I) sql re.sub(rINT\s*\(\d\), INTEGER, sql, flagsre.I) sql re.sub(rTINYINT\(1\), BOOLEAN, sql, flagsre.I) sql re.sub(rDATETIME, TIMESTAMPTZ, sql, flagsre.I) sql re.sub(rAUTO_INCREMENT, GENERATED BY DEFAULT AS IDENTITY, sql, flagsre.I) with open(schema_pg.sql, w, encodingutf-8) as f: f.write(sql)写转换脚本时别指望一把梭把所有规则都写完更理性的做法是先跑一遍转换把报错一条条收集起来再迭代补充规则。我这次是结构文件分了四轮才完全跑通一开始是类型不匹配然后是默认值写法再然后是分区表语法每一轮都能排掉一批问题。第三步导入PG。结构文件用psql直接执行psql -h 127.0.0.1 -U postgres -d mydb -f schema_pg.sql数据文件因为是从MySQL导出的INSERT语句PG基本能认但要注意MySQL导出的INSERT语句里有反引号的要去掉\0这类转义要处理如果数据量很大用INSERT导入会很慢。我建议数据量在1GB以上就不要用mysqldump的数据文件导入了改用CSV COPY的方式。一个更高效的组合是用mysqldump导出--tab目录格式每张表一个CSV文件再用PG的COPY命令导入# MySQL侧导出CSV mysqldump -h 127.0.0.1 -u root -p --tab/tmp/mysql_export --single-transaction --fields-terminated-by, mydb # PG侧导入 \COPY my_table FROM /tmp/mysql_export/my_table.txt WITH (FORMAT csv, DELIMITER ,);COPY比INSERT快一个数量级3000万行的订单表用COPY大概几分钟就进去了而用INSERT理论上要几小时。3.3 路径三准不停服迁移全量加增量加切换如果业务不能长时间停服就需要“准不停服”方案。我这次的生产迁移就是走的这条路整体思路是先做一次全量迁移到影子库然后通过解析MySQL的binlog做增量数据同步最后在业务低峰期做一次切换。第一步是全量迁移可以用上面提到的路径二或pgloader做一次基线。全量迁移时应用还在正常写库所以全量结束后源库必然比目标库多出一截增量数据这部分要靠增量同步工具来补。第二步是开启增量同步。MySQL侧的binlog格式建议是ROW模式这是回放增量数据的最稳方式。业界常用的工具组合是Canal或Debezium解析binlog把变更事件投递到Kafka再写一个消费程序把事件应用到PostgreSQL。这个消费程序主要做三件事把MySQL的数据类型转换为PG类型把MySQL的DELETE/INSERT/UPDATE事件翻译成PG的SQL处理主键冲突重复数据就做幂等更新。如果不想自己写消费逻辑也可以考虑商业或云厂商的同步工具比如阿里云DTS、NineData这类。它们天然支持MySQL到PostgreSQL同步配置简单稳定性有保障适合团队人力紧张的情况。社区版Canal也能用但消费端还是要自己写适合对成本敏感或者有定制需求的团队。这里要根据团队实际情况选不必盲目追求“全自研”。第三步是校验。增量同步跑起来后要持续一段时间观察源库和目标库的行数差、最大ID差。我当时是每10分钟比对一次核心表的COUNT(*)和自增主键最大值连续观察了一天后确认数据基本追平才在凌晨两点做切换。切换流程要提前写好脚本和回滚方案。我当时的具体操作是先通过配置中心把应用切到只读模式停掉写入流量等增量同步追平到当前时间点这时目标库和源库数据一致然后修改应用的数据源指向PostgreSQL恢复读写。整个过程线上实际停了8分钟左右。回滚预案也要做好如果切到PG后半小时内出现严重问题把数据源再切回MySQL增量同步仍在跑损失顶多半小时数据重放一下就能补回来。准不停服迁移的复杂度主要在前期的管道搭建和数据一致性验证真正切换那一下反而简单。但要强调的是binlog解析和增量回放不是“一锤子买卖”服务要常驻、要监控、要有告警否则切换前夜管道挂了没人发现第二天切换时目标库还在几小时前的状态那就要慌神了。4. 迁移后的“扫雷”那些文档里不写的坑4.1 序列Sequence不重置插入必炸这是MySQL转PG后最经典、最常见的问题。MySQL自增主键的“下一个值”是存在表结构里的PG的自增主键不管用SERIAL还是IDENTITY是存在独立序列对象里的。迁移过程中如果直接把数据导进去了序列对象的当前值默认是1或者在建表时用START WITH手动指定了值但没和现有数据的最大值对齐就会出现一个诡异现象插入新记录时主键从1开始撞上已有数据报“duplicate key value violates unique constraint”。解决办法是在数据导完后把每个序列重置为当前表最大主键加1。SQL写法SELECT setval(orders_id_seq, (SELECT MAX(id) FROM orders) 1);手动一张表一张表写太容易漏。建议把全库的序列和对应表关联关系查出来批量生成setval语句SELECT SELECT setval( || quote_literal(seqname) || , (SELECT COALESCE(MAX( || colname || ), 1) FROM || tablename || )); FROM ...如果之前没用工具自动处理迁移后第一时间先把这件事做了可以避免一大波线上主键冲突告警。4.2 默认值、自更新列、布尔类型的隐性差异MySQL里很常用的时间字段写法是created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP和updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。第一个写法PG兼容但第二个“行更新时自动改时间”的语义PG原生不支持需要手动建触发器。我当时的做法是把所有含ON UPDATE CURRENT_TIMESTAMP的列提取出来写一个统一触发器函数CREATE OR REPLACE FUNCTION auto_update_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; DO $$ DECLARE t text; BEGIN FOR t IN SELECT table_name FROM information_schema.columns WHERE column_name updated_at AND table_schema public LOOP EXECUTE format(CREATE TRIGGER trg_%I_updated_at BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION auto_update_timestamp(), t, t); END LOOP; END; $$;布尔类型也值得单独提一下。如果建表时按类型映射把TINYINT(1)转成了BOOLEAN那应用层传0/1就可能出问题因为JDBC驱动从PG读BOOLEAN默认返回的是Boolean对象。稳妥做法是应用层统一改成接收Boolean如果不想改代码结构上保留SMALLINT也行但这属于“拖延”后面迟早要还。4.3 迁移后的性能调优别忘了ANALYZE和VACUUM数据导入完成后PG的统计信息是空的如果直接跑业务查询执行计划会非常离谱——该走索引的不走全表扫描、嵌套循环满天飞。所以导入完第一件事是执行ANALYZE;让PG重新收集全库统计信息。对大表可以单独跑ANALYZE orders;。第二件事是理解PG的VACUUM机制。PG的MVCC机制和MySQL的InnoDB不太一样更新和删除会产生死行需要VACUUM清理。默认的autovacuum是开着的一般不用操心但迁移大批量数据后建议手动执行一次VACUUM (ANALYZE) orders;另外提醒一个很多人忽视的点MySQL的索引和PG的索引不是一回事。PG的默认索引是B-Tree功能上和MySQL的B-Tree基本等价但PG还有表达式索引、部分索引、GIN索引等高级玩法。迁移后如果发现某个查询响应很慢别急着改SQL先看看执行计划很多时候用PG特有的表达式索引或者GIN索引就能解决比改动应用划算得多。比如MySQL里常用WHERE name LIKE %关键词%这类查询只能全表扫PG里可以建pg_trgm扩展然后用GIN索引加速CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX idx_orders_remark_trgm ON orders USING gin (remark gin_trgm_ops);这类优化建议在迁移后一周内集中做业务刚开始跑慢查询日志最有参考价值。4.4 迁移问题速查表我把这次迁移中实际遇到的问题整理成了一张速查表方便后续迁移的同学先对照排查现象原因解决方法插入数据报duplicate key序列未重置用setval对齐最大ID表或字段not found大小写/引号问题统一小写命名应用层不用双引号包标识符LIMIT 10,20报语法错误MySQL分页语法改成LIMIT 20 OFFSET 10时间字段不自动更新ON UPDATE CURRENT_TIMESTAMP不支持建触发器Boolean接收异常TINYINT(1)未转BOOLEAN结构转成BOOLEAN并改应用类型全角中文乱码字符集不匹配统一UTF8MB4/UTF8连接串加characterEncoding大批量导入慢INSERT逐条执行改用COPY查询计划怪异统计信息缺失执行ANALYZE长SQL突然报错PG对GROUP BY严格改写SQL或调整group by字段类型numeric过不来精度不匹配统一decimal精度和标度这张表不是万能药但覆盖了迁移前期最常见的一波问题。遇到表上没有列出的报错也不要慌先定位是哪一层的问题——是结构导入、数据导入、还是应用运行时分层排查会快很多。最后分享一个我自己的习惯迁移完成不等于项目结束旧库别急着删。我通常会保留旧库一到两个业务周期每天用脚本把两边核心表的行数和关键指标做一次对账确认新库运行完全稳定后再彻底下线。这个习惯曾经在一次板块数据不一致时帮我快速定位到了问题省去了从备份里翻数据的麻烦。迁移这件事稳妥永远比速度重要。