去年年底，我们接了一个电商系统的重构项目。客户要求把底层数据库从MySQL 5.7换成PostgreSQL 14，理由很实际：新功能需要复杂的JSON查询和全文检索，MySQL用着别扭。我作为技术负责人，本以为就是改改连接串、调调语法，结果整整折腾了两个月。这篇文章把整个过程复盘一下，包括字符集、自增主键、JSONB类型、SQL方言差异和并发死锁这五个坑，以及我们最后怎么用双写和灰度发布平稳落地的。
背景：为什么要迁？
这个电商项目有几十万商品、日均几万订单。MySQL里存了一些JSON字段，比如商品属性、订单扩展信息，但查询和更新都很痛苦。客户提了个需求：按商品属性的任意组合筛选，还要支持全文搜索。MySQL的JSON函数能力有限，全文索引对中文支持又差。我们评估了一圈，PostgreSQL的JSONB和GIN索引正好能解决，而且窗口函数、CTE等特性也更丰富。
迁移前，我们先列了个风险清单：数据量不小（订单表几千万行），停机时间有限（最多2小时），业务不能断。所以计划是先并行迁移，再切换。结果第一关就卡住了。
坑一：字符集不兼容，中文变乱码
我们用pgloader做全量迁移，第一次跑完，发现所有中文都变成了问号。查了一下，MySQL的utf8mb4和PostgreSQL的UTF8编码本质一样，但pgloader默认的字符集映射有问题。MySQL的utf8mb4对应PostgreSQL的UTF8，但pgloader识别不了，需要手动指定。
解决方法是修改pgloader的load文件，明确指定编码：
LOAD DATABASE FROM mysql://user:pass@host:3306/mydb INTO postgresql://user:pass@pg-host:5432/mydb WITH include drop, create tables, create indexes, reset sequences SET maintenance_work_mem to '128MB' CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null; -- 指定源和目标编码 BEFORE LOAD DO $$ SET client_encoding TO 'UTF8'; $$; AFTER LOAD DO $$ SET standard_conforming_strings TO 'on'; $$;另外，MySQL里很多字段是utf8mb4_general_ci，pgloader会自动转成PostgreSQL的collation，但排序规则不一样，可能导致索引失效。我们干脆在目标库里统一用en_US.UTF-8，毕竟是电商，排序规则影响不大。
坑二：自增主键断了
MySQL的自增字段是AUTO_INCREMENT，PostgreSQL是SERIAL或IDENTITY。pgloader迁移时，SERIAL会自动创建序列，但序列的起始值不会自动设置成当前最大值+1。结果新插入数据时，主键冲突。
我们写了个脚本，在迁移后重置所有序列：
DO $$ DECLARE r record; BEGIN FOR r IN SELECT tc.table_name, c.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.columns c ON c.table_name = tc.table_name AND c.column_name = kcu.column_name WHERE tc.constraint_type = 'PRIMARY KEY' AND c.column_default LIKE 'nextval%' LOOP EXECUTE format('SELECT setval(pg_get_serial_sequence(''%1$s'', ''%2$s''), (SELECT COALESCE(MAX(%2$s), 1) FROM %1$s))', r.table_name, r.column_name); END LOOP; END $$;这个脚本执行后，自增主键就正常了。但注意，如果表里有删除过的记录，序列不一定连续，这没问题，只要不冲突就行。
坑三：JSONB类型和查询的差异
MySQL的JSON类型本质上是一个LONGTEXT，而PostgreSQL的JSONB是二进制格式，支持索引。迁移后，我们原本的JSON查询语法全变了。MySQL的`$.属性`变成了PostgreSQL的`->>`操作符。比如查商品颜色为红色的：
-- MySQL SELECT * FROM products WHERE attributes->'$.color' = 'red'; -- PostgreSQL SELECT * FROM products WHERE attributes->>'color' = 'red';这还算简单，复杂的嵌套查询和数组操作差异更大。我们花了一周写转换脚本，把应用层所有JSON查询都改成了PostgreSQL语法。另外，PostgreSQL的JSONB支持GIN索引，我们给高频查询字段建了索引，性能提升明显。
核心洞见：迁移不只是换数据库，更是换思维。JSONB的强大在于它支持索引和复杂操作，但代价是更严格的类型约束。如果应用层之前对JSON格式很随意，迁移前必须先清理数据规范。
坑四：SQL方言差异，隐性bug
MySQL对SQL标准比较宽松，比如`GROUP BY`可以省略非聚合列，而PostgreSQL严格遵循标准，必须指定所有非聚合列。我们有一堆老查询都踩了这个坑。还有MySQL的`LIMIT 10 OFFSET 5`和PostgreSQL一样，但`REPLACE INTO`、`ON DUPLICATE KEY UPDATE`在PostgreSQL里要用`INSERT ... ON CONFLICT`。
我们先用工具扫描了所有SQL，逐个修改。但有些动态拼接的SQL在代码里，只能靠测试。我们给应用加了日志，把每次执行的SQL都打出来，在测试环境跑了一遍全链路。即便如此，上线后还是有一个查询因为`GROUP BY`报错，直接影响了一个报表接口。当时已经切到PostgreSQL，紧急修了一版，热更新。
坑五：并发死锁，差点回滚
迁移后的第三周，突然出现大量死锁告警。订单表在并发插入和更新时频繁死锁，MySQL下从未发生过。排查发现，PostgreSQL的锁粒度更细，但我们的业务模式是：一个订单更新时会锁住关联的多个行，不同事务的锁顺序不一致，导致死锁。
解决方案分两层：应用层调整事务顺序，统一按订单ID排序再操作；数据库层把隔离级别从默认的Read Committed调成Repeatable Read？不，我们反而调低了，因为死锁往往是因为间隙锁。最后我们分析死锁日志，发现是外键约束导致的额外锁。我们移除了几个不必要的索引和外键，死锁率降了90%。
双写与灰度发布
为了平滑过渡，我们设计了双写方案：应用层同时写MySQL和PostgreSQL，读走MySQL，校验两边数据一致性。用消息队列异步同步，保证最终一致。灰度发布阶段，先切5%的读流量到PostgreSQL，观察性能和错误日志，逐步扩大到100%。切换后保留MySQL只读运行一周，以防万一。
双写期间，我们发现两边自增主键不一致，导致关联数据错乱。解决办法是统一使用UUID作为主键，或者让PostgreSQL的序列从MySQL的最大值+1开始。我们选择了后者，因为业务代码依赖自增ID。
效果数据与总结
迁移完成后，我们做了性能对比：复杂查询（多条件JSONB过滤+全文检索）从原来的1.2秒降到80毫秒，提升了15倍；插入和更新性能略降（PostgreSQL的fsync策略导致），但通过调整`checkpoint_completion_target`和`wal_buffers`，最终和MySQL持平。更重要的是，新功能开发效率提高了，不用再绕开数据库能力的限制。
这次迁移让我学到几条经验：第一，不要低估字符集和自增序列这种基础问题，迁移工具不是万能的；第二，SQL方言差异必须提前用工具扫描加测试覆盖；第三，死锁问题要多看日志，理解锁机制，而不是盲目加超时；第四，双写+灰度是数据库迁移的保险绳，虽然成本高，但值得。
最后说一句，如果你们也在考虑数据库迁移，或者想优化现有系统，可以找我们聊聊。铭锦数智在数据迁移和AI应用方面有不少实战经验，能帮你少踩坑。