前阵子接手了一个内部系统改造核心任务是把一套跑了快五年的MySQL 5.7存量库完整挪到PostgreSQL 14上。整个过程从评估、工具选型、全量导出、增量同步到应用改造踩了不少坑也沉淀了一套可以复用的方法。今天就把这套从MySQL迁移到PostgreSQL的完整流程写出来不绕弯子直接按实操顺序讲。这篇内容适合三类人一是业务里确实遇到了MySQL瓶颈、正在考虑换库的技术负责人二是接手了迁移任务、需要快速上手的开发或DBA三是纯粹想搞清楚这两个库到底差在哪里、以后要不要换的学习者。我会把数据模型差异、SQL语法差异、迁移工具用法、增量双写方案、应用层改造和常见坑点都讲明白争取你看完就能规划自己项目的迁移。1. 迁移前必须想清楚的三件事1.1 先回答“值不值”再动手很多人一上来就问“怎么迁”但更该先问“为什么迁”。MySQL和PostgreSQL都是优秀的开源关系型数据库不存在谁能完全取代谁。从我实际接触的场景看值得下决心迁的原因一般就这几种。第一种是业务对复杂查询、报表统计、JSON数据处理要求很高。PostgreSQL在复杂关联查询、窗口函数、CTE公共表表达式、GIN索引上的能力明显强于MySQL尤其是JSONB类型可以直接对JSON字段建索引、做复杂过滤这在电商订单属性、埋点事件这类灵活结构数据上特别好用。第二种是数据完整性要求极高。PostgreSQL的约束实现更严格比如外键、唯一约束、检查约束、排他约束连“延迟约束”都支持这在金融、财务类系统里是硬需求。MySQL的检查约束在8.0之前基本是摆设外键在分库分表场景下也常常被放弃。第三种是业务希望摆脱某个云厂商的绑定或者要把数据库从商业版替换成完全开源的方案避免授权成本。MySQL虽然是开源的但它被Oracle收购后很多团队对社区版未来演进有顾虑。PostgreSQL作为完全独立的开源项目许可证更宽松社区也很活跃。如果你只是因为“听说PG性能更好”就想迁那我要泼盆冷水。对于单纯的点查、写入、主从复制这类OLTP场景MySQL并不差甚至运维生态更成熟。迁移本身是有成本的而且应用代码要改风险不小。一定要有明确的业务指标支撑比如“某个复杂报表在MySQL跑不动”“JSON查询响应太慢”等再动手。1.2 版本选择与基础环境准备确定要迁之后第一步是选版本。MySQL这边我建议至少是5.7以上如果是8.0迁移时会少很多麻烦因为8.0的字符集默认是utf8mb4JSON类型也更成熟。PostgreSQL这边建议直接用14或更高版本15、16在性能、日志、增量备份上都有改进14.2之后实战很稳。如果你需要pgvector这种插件版本更不能太低。然后是准备目标环境。我习惯先把PostgreSQL装在一台和源库资源配置接近或者略高的机器上先用默认配置跑通流程再根据压测调优。安装时要注意几个点一是操作系统用户的权限规划PostgreSQL默认不允许root用户直接操作二是数据目录和表空间要单独挂盘避免和系统盘抢I/O三是提前想好字符集。PostgreSQL创建数据库时字符集建议直接用UTF8排序规则用默认的即可。MySQL老库如果用的是latin1或者utf8mb4导出数据之后一定要对比字符集否则中文会变成乱码。这块我建议在迁移准备阶段就写进检查清单后面数据校验时会省很多事。工具选型上最值得用的开源工具是pgloader它能自动把MySQL的表结构、数据、索引、约束转换到PostgreSQL比手动写脚本快得多。商业云平台自带的迁移服务我就不展开说了因为不同厂商的操作界面差异较大原理上其实都离不开“结构转换、全量复制、增量同步”这三板斧。2. 盘点差异SQL语法与数据类型的对照清单2.1 数据类型映射最容易被坑的一环MySQL和PostgreSQL的数据类型看起来差不多实际差异很大。最典型的就是整型和自增主键。MySQL的TINYINT对应PG的SMALLINTMySQL的INT对应PG的INTEGERBIGINT两边一致。UNSIGNED无符号整型在PostgreSQL里没有直接对应需要换成常规整型并配合CHECK约束来限制非负。实际迁移中如果表里的UNSIGNED字段值本身不会超过有符号范围直接降级成普通整型也行。日期时间类型也要注意。MySQL的DATETIME存储的是“墙上时间”不带时区TIMESTAMP会受时区影响而且范围只有1970到2038年。PostgreSQL里对应的是TIMESTAMP不带时区和TIMESTAMPTZ带时区。绝大多数业务场景我建议迁移后统一用TIMESTAMPTZ因为它在多时区部署、跨区域协作时更安全。如果原库用的是DATETIME且不关心时区那用TIMESTAMP就够了。字符串类型MySQL的VARCHAR对应PG的VARCHAR但要记得PG的VARCHAR(n)里的n是字符数而不是字节数对于中文存储更友好。MySQL的TEXT、MEDIUMTEXT、LONGTEXT在PG里统一映射为TEXTPG的TEXT没有长度限制这反而省事。MySQL的BLOB对应PG的BYTEA。自增主键是迁移中最常见的坑。MySQL用AUTO_INCREMENTPG有两种写法老版本用SERIAL包括BIGSERIAL新版本推荐用IDENTITY列也就是GENERATED ALWAYS AS IDENTITY。我强烈建议新表直接用IDENTITY更符合SQL标准后续做逻辑复制、权限管理都更规范。枚举类型也建议单独处理。MySQL的ENUM可以直接变成PG的ENUM但后续要加一个枚举值两边语法差异很大。我实际经验是把ENUM转成VARCHAR加CHECK约束更灵活业务代码改动也更少。布尔类型上MySQL用TINYINT(1)表示真假PostgreSQL原生支持BOOLEAN迁移时直接转换但要注意应用代码里查询条件的写法。MySQL类型PostgreSQL类型特别说明TINYINTSMALLINTUNSIGNED需配合CHECK约束INTINTEGER范围一致BIGINTBIGINT常见主键类型DATETIMETIMESTAMP不带时区TIMESTAMPTIMESTAMPTZ推荐带时区VARCHAR(n)VARCHAR(n)n表示字符数TEXT系列TEXT无长度限制BLOBBYTEA二进制大对象ENUMVARCHAR CHECK便于后续扩展TINYINT(1)BOOLEAN布尔语义更清晰JSONJSONB推荐使用JSONB2.2 常用SQL写法差异速查SQL语句层面的差异直接影响应用代码改造量我把高频差异整理成了一张速查表迁移前挨个过一遍就知道自己项目要改多少SQL了。首先是最容易踩的符号差异。MySQL里用反引号包裹表名和字段名PostgreSQL用双引号而且在PG里未加双引号的标识符会被自动转为小写。这意味着你原来MySQL里的UserTable如果没加反引号在PG里查就变成usertable。最稳妥的做法是统一用双引号显式指定大小写但代价是每次都要写双引号麻烦。我更推荐迁移时把所有表名、字段名统一改成小写加下划线风格一劳永逸。字符串拼接上MySQL的||默认不是拼接符除非开启PIPES_AS_CONCAT用的是CONCAT函数PG的||就是拼接符同时也支持CONCAT函数。这个对于动态SQL和报表项目影响很大排查时要特别留意。分页查询方面两边都支持LIMIT OFFSET这块几乎不用改算是比较省心的部分。但要注意MySQL在OFFSET很大时性能很差PG同样有深分页问题建议后续优化成基于游标或键集分页。INSERT语法差异也是一个重头。MySQL的INSERT ... ON DUPLICATE KEY UPDATE在PG里对应INSERT ... ON CONFLICT (唯一键) DO UPDATE SET ...。这个语法更严格必须指定冲突的约束或唯一索引好处是更安全不会随手把不相关的重复数据覆盖掉。REPLACE INTO在MySQL里是“删了再插”PG里没有需要用INSERT ... ON CONFLICT DO NOTHING或DO UPDATE替代。场景MySQL写法PostgreSQL写法标识符引用useruser 或小写user字符串拼接CONCAT(a, b)a || b 或 CONCAT(a, b)空值判断IFNULL(a, 0)COALESCE(a, 0)日期格式化DATE_FORMAT(d, %Y-%m)TO_CHAR(d, YYYY-MM)自增主键获取LAST_INSERT_ID()RETURNING id插入冲突更新ON DUPLICATE KEY UPDATEON CONFLICT (id) DO UPDATE SET ...字符串截取SUBSTRING(s, 1, 3)SUBSTRING(s FROM 1 FOR 3)更新多表UPDATE a JOIN b ON ...UPDATE a SET ... FROM b WHERE ...还有一个高频差异是GROUP BY。MySQL默认允许SELECT非聚合列比如SELECT name, age, COUNT(*) FROM t GROUP BY age这种在MySQL能跑但PG会直接报错必须把name加到GROUP BY里或者用聚合函数包裹。迁移后最容易出现的报错就是这个应用日志里看见“column must appear in the GROUP BY clause”基本就是这个原因。MySQL的UPDATE支持多表JOIN直接更新PG需要借助FROM子句来实现同等效果。DELETE语句类似MySQL的DELETE JOIN在PG里要通过USING子句来重写。3. 迁移流程实战从全量导出到增量同步3.1 全量迁移表结构、数据、索引一次搬完环境准备好、差异也盘完了接下来进入核心流程。我建议先在一个测试环境完整跑通一遍记录下每个步骤的耗时和数据量再上生产。全量迁移我首推pgloader它是专门为“从其他数据库迁到PostgreSQL”设计的工具用起来非常简单。# 安装 pgloaderUbuntu/Debian 为例 sudo apt-get install pgloader # 创建迁移配置文件 mysql_to_pg.load配置文件里核心是定义源库和目标库的连接信息以及一些转换规则。我贴一个最小可用的例子LOAD DATABASE FROM mysql://user:passwordlocalhost:3306/mydb INTO postgresql://pguser:passwordlocalhost:5432/mydb WITH include drop, create tables, create indexes, reset sequences, workers 8, concurrency 1, multiple readers per thread, rows per range 50000 SET PostgreSQL PARAMETERS maintenance_work_mem 1GB, work_mem 128MB CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null, type tinyint to smallint using tinyint-to-smallint;这个配置做了几件事先删除目标库同名表再创建然后自动建索引重置自增序列采用并发读取。CAST部分是把MySQL的DATETIME转成PG的TIMESTAMPTZ并把零日期转成NULL因为MySQL允许0000-00-00这种值PG不允许。执行迁移pgloader mysql_to_pg.loadpgloader会在终端实时打印每个表的读取速度、行数、错误数。执行完后要重点看那些有error的表大多数错误来自非法日期、重复数据、超大索引名。如果不用pgloader也可以用mysqldump导出SQL文件再手动转换但那样工作量大得多。我的建议是小项目、单机、无脑全量迁移用pgloader大项目、多实例、需要复杂转换映射的可以先用工具跑一遍再写自动化脚本弥补。pgloader不是万能的比如分区表、视图、存储过程不会为你自动转换这些要么提前处理要么迁移后手工改造。3.2 增量同步与双写策略接近不停服的关键很多系统没法接受长时间停机最理想的方案是“旧库继续写新库同步着最后在一个凌晨窗口切换”。要实现这个必须解决增量同步问题。增量方案有三种思路。第一种是最省事的业务双写。在应用层加一个开关让写操作同时写MySQL和PostgreSQL读操作仍走MySQL。这种方案适合应用代码可控、改动量能接受的团队优点是灵活、不用解析binlog缺点是双写会有一致性问题而且应用代码要动。第二种是用现成的数据同步工具。这类工具生态比前几年成熟了不少但配置复杂尤其是处理DDL变更、断点续传时比较折腾。第三种是基于业务字段自己写增量任务。如果表里有自增ID或更新时间戳可以定期跑一段脚本把增量数据从MySQL抽到PG。这个方案最朴素但胜在稳定。比如有一个updated_at字段任务每5分钟执行一次-- 在目标库PG侧执行的定时拉取示意 INSERT INTO target_table (id, name, updated_at) SELECT id, name, updated_at FROM mysql_side_view WHERE updated_at :last_sync_time AND updated_at :current_sync_time;实际操作中我建议根据数据重要性和表大小混合使用。核心交易类表用双写保证实时性普通配置表、日志表用定时增量任务就能满足。双写方案看起来简单有几个细节必须处理好。第一双写要有幂等性PostgreSQL侧用ON CONFLICT DO UPDATE保证重复执行不会产生脏数据。第二要加一个同步状态表记录每条主键的同步版本号或最后同步时间便于排查漏同步的数据。第三双写失败时不能影响主流程要以MySQL为准PG侧失败先记录日志后面用对账任务补。3.3 校验与回滚切换前必须过的最后关卡切换前最重要的一件事不是“切过去”而是“比对两边数据”。我见过太多人辛辛苦苦导完数据结果漏了一条索引上线后慢查询把库拖垮。数据校验我一般分三层做。第一层是行数校验每个表COUNT(*)对比这个只能发现明显差异。第二层是聚合校验用MD5函数对所有关键字段做拼接再求哈希对比两边是否一致。第三层是抽样明细比对随机抽几条主键逐字段比对。-- PostgreSQL 侧计算表指纹 SELECT md5(string_agg(t.row_hash, )) FROM ( SELECT md5(id::text || name || created_at::text) AS row_hash FROM target_table ORDER BY id ) t;字段里有NULL值时要小心NULL拼接会变成NULL导致整行哈希算不出来。可以用COALESCE包一层默认值。切换策略上我推荐“先灰度、再全量、留回滚”。生产环境先让5%到10%的只读流量打到PG观察错误率和响应时间。确认没问题后再在维护窗口把写流量切过来。切换窗口内先把MySQL改成只读把最后一段增量补到PG然后做一次快速校验再切应用连接。回滚方案必须提前写好。最简单的方式是应用层保留双写开关切到PG后发现重大问题一键恢复写MySQLPG侧继续同步但不承接读流量。这个方案能保证你在故障时不需要重新导入数据回滚时间控制在分钟级。4. 应用改造存储过程、函数与连接层适配4.1 存储过程和触发器的改造方案如果你的业务用了大量存储过程迁移成本会直线上升。MySQL的存储过程语法和PostgreSQL的PL/pgSQL风格差异很大几乎不存在自动转换工具基本靠人工重写好在多数互联网业务已经把逻辑从数据库层挪到了应用层存储过程占比不大。MySQL和PG在存储过程上最明显的差异是分隔符和语法结构。MySQL用DELIMITER把整个过程包起来PG直接用CREATE OR REPLACE FUNCTION$$ ... $$来包裹函数体。MySQL示例DELIMITER $$ CREATE PROCEDURE get_user_by_id(IN uid INT) BEGIN SELECT * FROM users WHERE id uid; END$$ DELIMITER ;改写为PostgreSQLCREATE OR REPLACE FUNCTION get_user_by_id(uid INT) RETURNS TABLE(id INT, name TEXT, created_at TIMESTAMPTZ) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT u.id, u.name, u.created_at FROM users u WHERE u.id uid; END; $$;变量声明方面MySQL用DECLAREPG在BEGIN ... END中间的DECLARE段里声明。异常处理上MySQL用DECLARE EXIT HANDLERPG用BEGIN ... EXCEPTION WHEN ... THEN语义和使用方式都不太一样重写时要特别注意事务行为PG里异常处理会回滚当前子事务。触发器是另一个高频改造点。MySQL触发器写在一对BEGIN END里PG需要单独创建函数再通过CREATE TRIGGER绑定到表上。PG的好处是触发器函数可以用多种语言编写灵活性更强。如果存储过程体量大且逻辑复杂我的建议是不要逐行翻译而是借这个机会把关键逻辑搬到应用层。数据库只保留基础的数据读写和约束业务规则由服务代码控制。这样后续数据库升级、拆分、横向扩展都会轻松很多。迁移存储过程不是技术问题而是架构升级的契机。4.2 连接驱动、连接池与ORM适配应用侧改造最重要的是数据库驱动和连接方式。Java系应用MySQL用com.mysql.cj.jdbc.DriverPostgreSQL用org.postgresql.Driver连接串格式差别很大。JDBC连接串MySQL形如jdbc:mysql://ip:3306/dbPG是jdbc:postgresql://ip:5432/db。连接池Druid、HikariCP基本不用改但要注意连接串里的参数比如MySQL的useSSL、serverTimezone这些参数PG不认识要去掉。PG连接串里通常要配currentSchema因为PG的逻辑结构是多schema的不像MySQL库和schema基本是一回事。Python应用MySQL常用pymysql、mysqlclientPG常用psycopg2或asyncpg。psycopg2的游标返回值和MySQL的驱动有些差别比如默认返回元组列表需要把行工厂设成dict才能得到字典类型结果。SQLAlchemy的话换方言和后端URL基本就能跑通。ORM这层是迁移的“减震器”。如果你用的MyBatisSQL写在XML里改起来确实费劲但也好在SQL都集中了搜索替换就能找出大部分问题。用JPA/Hibernate的项目框架会自动生成SQL很多差异被屏蔽掉了但遇到Query注解里的自定义SQL时还是要人工改一遍。连接池参数也需要按PG特性调整。PostgreSQL每个连接都是一个独立的进程内存占用高于MySQL的线程模型所以连接池上限不要拍脑袋设成200、300。我一般建议核心服务连接池初始10、最大50左右配合PG的max_connections一起规划。连接稳不住数据库再强也白搭。5. 常见问题排查与性能调优5.1 高频报错与解决思路速查迁移过程中遇到报错是常态我把自己踩过的、带新人也常见的几个问题整理成一个速查表方便你排查时快速定位。报错信息原因解决方案column must appear in the GROUP BY clauseGROUP BY语法严格把非聚合列加到GROUP BY或包进聚合函数relation xxx does not exist表名大小写被折叠统一使用小写表名或用双引号包裹ERROR: invalid input syntax for type timestamp日期值为0000-00-00导数据时转成NULL或改成合法时间duplicate key value violates unique constraint迁移时约束已建立数据有重复先查重复数据清理后再导there is no unique or exclusion constraint matching the ON CONFLICT specificationON CONFLICT需要指定唯一约束确认冲突列上有唯一索引或主键function now() does not exist常见于CURRENT_TIMESTAMP默认值问题统一用CURRENT_TIMESTAMP或now()could not resize shared memory segment共享内存配置不足调整docker或宿主机的shared memory限制FATAL: sorry, too many clients already连接数打满缩小连接池或调大max_connections大小写问题是我见过最多的。MySQL的表名在Linux下区分大小写Windows下不区分项目换环境后容易埋雷。PG在这方面更严格我的建议是迁移前直接写一个脚本把所有表名、列名统一转成小写下划线格式一步到位省得后面每个SQL都加双引号。还有一个很容易忽略的自增序列不同步。pgloader会自动重置序列但如果你是用自定义脚本迁移的往往会忘记重置。结果就是应用往PG插入数据时提示主键冲突。排查方法很简单查看当前序列值是否小于表里最大ID-- 修正序列值 SELECT setval(users_id_seq, (SELECT MAX(id) FROM users));时区问题也是重灾区。MySQL的DATETIME不带时区写入什么就是什么PG的TIMESTAMPTZ会按数据库时区转换。如果应用服务器、数据库、缓存用的时区不一致很容易出现“时间差了8小时”的诡异问题。我的建议是PG数据库、应用JVM时区、连接串时区全部统一成UTC展示层再转本地时区这是最不容易出错的做法。5.2 迁移后性能对比与PG关键参数调优迁移完成不代表结束性能验证和调优同样重要。我建议切流后跑一周的对比监控重点关注慢查询数量、平均响应时间、CPU/内存占用、连接数变化。PostgreSQL的调优思路和MySQL有些不同。MySQL最常用的是InnoDB缓冲池innodb_buffer_pool_sizePG对应的是shared_buffers但PG同时依赖操作系统页面缓存所以shared_buffers通常建议设置为内存的25%左右而不是像InnoDB那样直接给70%。工作内存方面work_mem控制排序、哈希操作的内存。它和MySQL的sort_buffer_size类似但PG的work_mem是按操作分配的一个复杂查询可能同时用多个所以不能调太大否则在高并发下内存瞬间被打爆。maintenance_work_mem用于维护操作比如建索引、VACUUM可以给大一些1GB到2GB都是合理范围。最需要理解的概念是MVCC和VACUUM。PG的MVCC和MySQL的InnoDB不一样PG更新一行实际上是插入一个新版本旧版本需要清理这个清理过程就是VACUUM。autovacuum在默认配置下能工作但如果你批量更新了大量数据建议手动执行一次VACUUM ANALYZE更新统计信息、清理死元组。-- 手动清理并更新统计信息 VACUUM (ANALYZE, VERBOSE) your_table;在性能调优上PG有一样MySQL羡慕的功能更精确的EXPLAIN ANALYZE执行计划。它能显示实际行数和计划行数的差异还能告诉你哪一步消耗时间最长、内存用了多少。排查慢SQL时不要瞎猜先看执行计划再看索引有没有命中。PG支持部分索引、表达式索引、GIN索引很多MySQL里只能靠“加冗余字段”解决的问题在PG里可以靠索引设计优雅地解决。最后提一下checkpointer。PG的checkpointer进程负责定期把脏数据刷到磁盘它和MySQL的checkpoint机制类似但日志叫法和触发条件不一样。如果你在日志里看到关于checkpoint的告警通常不是进程本身出问题而是磁盘I/O能力跟不上或者checkpoint_timeout、max_wal_size设置得不合理。适当调大max_wal_size可以减少checkpoint频率降低I/O峰值。迁移这个事技术方案再完美人也可能出错。我个人体会是给每一步都加一个“验证环节”导出后验证行数同步后验证指纹切换前验证灰度能解决绝大多数事故。数据库迁移不是一次性动作而是一次完整的演练所以测试环境先跑两遍把每一步的日志和耗时都记录下来等到真正动生产的那天心里才有底。
