尧图网页设计 
迁移这种事做之前总觉得是“导出数据再导入改改连接串就完事”做之后才发现真正让人熬夜的从来不是搬运数据本身而是那些藏在 SQL 语义、驱动行为、运维模型里的隐性差异。我前后带团队做过两次从 MySQL 到 PostgreSQL 的完整迁移第一次从排期到稳定运行花了三个月第二次有了完整方法论文档压缩到五周。这篇就是把两次实践沉淀下来的经验拆开讲从要不要迁、怎么盘点、如何做结构转换、用什么工具搬数据到应用层怎么改、上线后怎么校验和回滚以及迁移完的运维要点。适合正在评估换库的技术负责人、DBA也适合刚接触 PostgreSQL、想提前避坑的应用开发者。先说一个总判断MySQL 和 PostgreSQL 都是非常成熟的数据库绝大多数场景下选哪个都能满足业务。所以“从 MySQL 迁到 PostgreSQL”这个命题前提一定是业务出现了 MySQL 解决起来很别扭、而 PostgreSQL 天生擅长的需求。后面的每一章都围绕这个前提展开。1. 为什么放着 MySQL 不用非要迁到 PostgreSQL一次迁移的真实动机1.1 触发迁移的典型业务信号我经手的第一条迁移案例来自一个电商后台系统。MySQL 8.0核心订单表 3000 多万行后台运营人员的组合筛选经常拖到 5 秒以上。一开始团队怀疑是索引没建好但排查后发现真正把 MySQL 逼到死角的是两类需求第一是大量 JSON 半结构化数据。商品扩展属性、活动配置、买家标签都塞在 JSON 字段里查询条件经常落在 JSON 内部的数组元素上MySQL 的 JSON 类型虽然能做路径查询但索引覆盖能力非常有限执行计划经常走不了任何索引。第二是地理信息处理。做门店配送范围分析时需要在经纬度上做距离排序和范围圈选InnoDB 的索引结构本身不支持这类空间语义只能靠应用层把数据捞出来硬算。这两件事放到 PostgreSQL 里几乎是开箱即用jsonb配 GIN 索引PostGIS 配 GiST 索引性能差距不是一个量级。类似的功能性诉求还包括复杂报表里的窗口函数、递归 CTE 越来越高频PostgreSQL 的优化器对复杂 JOIN 的选路明显更稳数据完整性要求高外键和 CHECK 约束需要被严格贯彻执行而 MySQL 在部分历史配置下会出现“约束定义还在实际校验却松散”的情况。这些信号通常不是单独出现的。如果在你的系统里同时看到两三条迁移就有了真实的价值支点如果只是“听说 PG 更强”建议先冷静。1.2 迁移前先做的三件事摸清实例、查 SQL、定窗口决定迁移前除了确认业务动机还要把三件基础工作提前做完否则排期全靠拍脑袋。第一摸清存量实例。到底有多少个 MySQL 实例每个实例里哪些库是核心业务库、哪些已经没人维护生产、测试、预发环境分别怎么管理很多时候团队只迁了主力库留下十几套边角库后续运维反而更混乱。第二收集应用 SQL 清单。这一步在整个迁移里价值最高。把源库慢查询日志收集两周整理出 Top 100 的 SQL同时让各个业务线自查代码仓库里的 SQL 写法。后面所有兼容性改造、性能回归都靠这份清单做底。第三定停服窗口。如果业务允许一个周末停服迁移后面的增量同步那一层可以简化全量搬完校验即可如果要求在线迁移不中断就要提前设计 binlog 增量同步方案。我见过不少项目在最开始没确认这点做到一半发现停服窗口不够只能临时加班赶增量方案风险一下子高了很多。1.3 什么情况下不建议迁不是所有场景都适合迁移。遇到下面四类情况我通常会明确劝退核心业务全是简单 CRUD单条 SQL 只跑主键查询没有复杂聚合和 JSON 检索。MySQL 和 PostgreSQL 在这种场景下没有体感差异迁移投入纯粹是浪费。存量存储过程和触发器数量巨大且重度依赖 MySQL 专有函数。PostgreSQL 的 PL/pgSQL 确实更强但等价改写的工作量很容易被低估。我见过一个系统 600 多个存储过程迁移组最初乐观估计两周实际用了两个月。团队没有 PostgreSQL 运维经验且预算不允许补充人力。vacuum、连接进程模型、事务快照机制都和 MySQL 差异很大出了问题现场查文档很难应对线上故障。周边工具链没有就绪。监控告警、备份恢复、中间件兼容、数据同步工具都要重新接这些隐性成本经常被忽略。一句话先确认你遇到的是“MySQL 解决不了的问题”而不是“你没把 MySQL 用好的问题”。前者迁移才有意义。2. 迁移前的地图版本选型、环境搭建与对象盘点2.1 PostgreSQL 版本选择与实例初始化参数版本选择上目前建议直接上 PostgreSQL 16 或 17。新项目直接用 17存量项目 16 也足够不建议选 15 以下版本越新JSON 能力和优化器表现越好。安装层面Windows 上用 EnterpriseDB 官方安装包最省事搜索热词里经常出现“postgresql windows 安装 服务启动失败”这种问题八成是 data 目录权限不对或者 5432 端口被占用Linux 上建议直接用官方 PGDG 源例如 RHEL 系列# RHEL 9 / Rocky Linux 9 示例其他 EL 版本对应替换 sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo dnf install -y postgresql17-server postgresql17-contrib sudo /usr/pgsql-17/bin/postgresql-17-setup initdb sudo systemctl enable --now postgresql-17装完第一件事不是急着建库而是调整几个和 MySQL 使用习惯差异很大的初始化参数。shared_buffers我通常设为物理内存的 25%但一般不超过 8GBwork_mem默认只有 4MB如果业务排序和哈希操作多建议按连接数评估后调到 16MB 甚至 64MBmaintenance_work_mem直接影响 VACUUM 和建索引速度迁移期间至少给到 256MB。MySQL 的调优思路相对集中主要围着innodb_buffer_pool_size转PG 的内存管理更分散刚上手的人很容易困惑为什么所有参数都调了性能还是上不去。关键认知是shared_buffers只负责缓存数据页大量的文件系统页缓存被操作系统接管所以不能把 MySQL 那套“buffer pool 尽量大”的思路直接搬过来。还有两个初始化时就要确认的点字符集统一 UTF8排序规则建议用C或C.UTF-8。如果业务对中文排序有特定要求可以在 initdb 时指定 ICU 规则不然后面 LIKE 查询和 ORDER BY 的默认行为会和你预期的完全不一样。2.2 迁移对象全景清单漏掉一个后面都是坑如果只把表数据导过去这个迁移一定不完整。我习惯在动任何工具之前先画一张迁移对象全景图表结构、视图、物化视图以及物化视图的刷新逻辑索引包括唯一索引、全文索引、前缀索引、函数索引主键、外键、唯一约束、非空约束、CHECK 约束、默认值存储过程、函数、触发器、事件计划任务MySQL EVENT 在 PG 没有原生资源需要评估用外部队列或 pg_cron数据库用户、角色、权限、行级安全策略依赖的字符集、排序规则、MySQL 特有 SQL 模式其中 SQL 模式是隐性炸弹。MySQL 的sql_mode影响字符串比较、日期严格校验和 GROUP BY 行为PG 没有直接对应项迁移后行为差异只能在 SQL 层消化后面第 6 章会重点讲。2.3 用数据字典做一次“结构体检”动手迁移前先用数据字典给自己做一次体检。MySQL 的information_schema能查出所有表、列、索引的基本信息PG 同样兼容这套标准视图但更深入的膨胀信息、索引使用情况要查系统表比如pg_stat_user_tables和pg_index。一个更省事的做法先用pg_dump导出目标库结构再人工 review。pg_dump --schema-only -h localhost -U pguser -d target_db schema.sql这份 SQL 比任何可视化差异报告都直观因为它把建表顺序、依赖关系、扩展加载完整呈现出来。源库侧用mysqldump --no-data导出结构两边放到同一份对比脚本里逐一核对列名和类型映射。不要指望工具全自动完成这一步结构转换的正确率直接决定后面数据迁移的顺利程度。3. 结构转换的深水区数据类型、自增列与隐式转换差异3.1 字段类型映射表照着改就对了结构转换的第一步是把 MySQL 字段类型一一映射成 PG 类型。我整理了一份在实际项目里验证过的对照表并标注了容易踩坑的点MySQL 类型PostgreSQL 类型注意点TINYINTSMALLINT如果原列是 TINYINT(1) 且当布尔用建议直接用 BOOLEANINT / INTEGERINTEGER显示宽度 INT(11) 直接去掉BIGINTBIGINT位宽一致DECIMALNUMERIC精度和小数位数保持一致FLOATREALPG 的 FLOAT 默认是 DOUBLE PRECISION 别名单精度必须显式用 REALDOUBLEDOUBLE PRECISION对应关系明确VARCHAR(N)VARCHAR(N)长度上限一致但超长写入时 PG 报错更果断CHAR(N)CHAR(N)尾部空格补齐行为两边有差异建议统一去掉尾部空格TEXTTEXTPG 的 TEXT 没有 64KB 包上限相当于 MySQL 的 LONGTEXTBLOB / LONGBLOBBYTEA类型改了应用层读写 API 也需要改DATETIMETIMESTAMP不带时区TIMESTAMPTIMESTAMPTZ如果原字段存 UTC建议直接转带时区类型DATE / TIMEDATE / TIME基本等价JSONJSONB推荐但要注意写入时键的顺序会被重排ENUM枚举类型或 VARCHAR CHECKPG 枚举后续加值靠 ALTER TYPE麻烦能用 CHECK 就用 CHECKSET关联表或数组MySQL 的 SET 找不到直接等价物推荐拆关联表这张表里最容易忽略的是 FLOAT。MySQL 的FLOAT是单精度PG 里如果直接写FLOAT得到的是双精度单精度必须用REAL否则数值精度和索引选择都会改变。3.2 自增主键的三种写法与序列同步问题MySQL 的自增主键是最典型的迁移点CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, ... ) ENGINEInnoDB;到 PG 里等价写法有三类-- 方式一SERIAL 伪类型最快但不够严谨 CREATE TABLE orders ( id SERIAL PRIMARY KEY, ... ); -- 方式二GENERATED BY DEFAULT AS IDENTITY推荐 CREATE TABLE orders ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, ... ); -- 方式三GENERATED ALWAYS AS IDENTITY禁止手动插入 CREATE TABLE orders ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ... );方式二和方式三的关键区别是BY DEFAULT 允许显式插入 idALWAYS 会强制使用序列生成值。迁移过程中如果要保留原主键值建议用 BY DEFAULT否则大批量导入历史数据时主键冲突会让人心态爆炸。导入完成后真正容易漏掉的是同步序列值。PG 的序列和表是分离对象即使你插入了 id300000 的数据序列可能还停在 1应用层下一条 INSERT 就会报唯一约束冲突。需要手动对齐SELECT setval(pg_get_serial_sequence(orders, id), (SELECT max(id) FROM orders));很多用 ORM 自动建表的项目迁移后没有这一步线上第一个新增数据就炸建议把它写进迁移脚本的收尾动作。3.3 字符集、排序规则与大小写敏感的坑MySQL 最常见的排序规则是utf8mb4_general_ci默认字符串比较不区分大小写。也就是说WHERE name ABC能匹配到abc唯一索引上ABC和abc会被当成同一个值。PG 默认排序规则区分大小写切过去之后会出现两个典型现象一是唯一索引不再拦截仅大小写不同的值业务层唯一逻辑可能冲突二是登录名、用户名校验等环节突然查不到数据因为之前依赖了不区分大小写的隐式比较。解决办法有三类把查询统一改成lower(name)并建表达式索引安装citext扩展让特定字段使用不区分大小写的类型或者在迁移时通过 COLLATE 指定不区分大小写的规则。我的经验是字段少、查询模式简单时用 citext 最省事字段多时还是统一lower()加表达式索引更可控因为 citext 的索引和排序行为会让后续优化器选路变得更难预测。3.4 索引与约束迁移PG 不会替你做的事MySQL InnoDB 有个隐藏行为建外键时如果列上还没索引InnoDB 会自动创建索引。PG 不会外键列上的索引必须手动建否则 UPDATE 或 DELETE 父表时子表会做全表扫描线上性能落差非常大。迁移时建议把外键列和常用过滤条件列提前建好索引。另一个常见差异是前缀索引。MySQL 允许INDEX idx_name (name(10))PG 原生 B-tree 不支持带长度的前缀索引但可以用表达式索引代替CREATE INDEX idx_name_left10 ON users (left(name, 10));代价是应用层查询必须写成同样的表达式才能命中索引。至于全文索引MySQL 的FULLTEXT在 PG 里对应tsvector加 GIN 索引这只是 DDL 替换查询语法也要从MATCH...AGAINST改成to_tsvector和plainto_tsquery的组合。约束方面还要注意默认值函数差异。MySQL 的CURRENT_TIMESTAMP可以直接作为 timestamp 的默认值PG 同样支持但一些历史表里写死DEFAULT 0的 timestamp 字段在 PG 里需要改成合法的字面量或保持可空否则建表直接报错。4. 数据迁移的三种实操路线pgloader、mysqldump 加工与增量工具4.1 pgloader一键迁移的上限和下限如果迁移的表结构比较常规没有特别复杂的自定义函数和视图pgloader 是最省力的起点。它天然处理 MySQL 到 PostgreSQL 的转换能自动完成类型映射、建表、搬索引和约束甚至能重置序列。一个典型配置长这样LOAD DATABASE FROM mysql://root:passwordlocalhost:3306/source_db INTO postgresql://pguser:passwordlocalhost:5432/target_db WITH include drop, create tables, create indexes, reset sequences, workers 8, concurrency 1, max rows per insert 500 CAST type datetime to timestamptz drop not null using zero-dates-to-null, type date drop not null using zero-dates-to-null; SET PostgreSQL PARAMETERS maintenance_work_mem 256MB, work_mem 16MB;这里有两个细节值得注意。CAST语句把 MySQL 无法表示的0000-00-00 00:00:00自动转成 NULL这是脏数据最常见的来源不处理的话导入会卡死。另外source_db和target_db必须在同一个数据库实例里pgloader 没有跨库推送的概念。pgloader 的局限也很明显对视图、函数、触发器、事件的支持很弱通常只负责表结构和数据超大表迁移速度虽然可以调整 workers 提升但和物理导入相比还是慢。我的使用习惯是拿它做中小型系统的整体搬迁大型系统反而更倾向下面的组合路线。4.2 mysqldump 导出 SQL 文本改写的应急路线不少网上教程会建议 mysqldump 加--compatiblepostgresql这里先纠正一个误区mysqldump 的--compatible选项并不支持 postgresql它只支持 oracle、ansi、no_table_options 等组合即便指定 ansi 也不会自动生成 PG 兼容语法。真正可行的“文本改写”路线是分段处理第一步用 mysqldump 只导出数据不导出表结构mysqldump -u root -p --no-create-info --skip-add-locks \ --skip-lock-tables --complete-insert --hex-blob source_db data.sql第二步用 sed 或 perl 清理反引号和 MySQL 专有转义sed -i s///g data.sql第三步用 psql 导入建议包在事务里失败可以整体回滚。数据量大时文本 INSERT 导入远不如 CSV 中转高效。MySQL 侧用SELECT ... INTO OUTFILE导出 CSVPG 侧用 COPY 导入速度能差一个数量级COPY target_table (col1, col2, col3) FROM /data/source_table.csv WITH (FORMAT csv, HEADER true, NULL NULL);如果表里有二进制字段CSV 中转要小心编码更推荐让 pgloader 这类专用工具处理 bytea 映射别用文本中转硬碰。4.3 在线迁移与增量同步把停服时间从 8 小时压到 10 分钟很多业务不允许停服一晚上做迁移这时必须做增量同步。整体思路是“先全量、后增量、再切换”。全量同步挑业务低峰时段用上述方式把存量数据搬过去。增量同步源库开启 binlog消费 binlog 变更在目标库重放。开源方案里 Debezium 最常用通过 MySQL binlog 把变更事件发到 Kafka下游消费后写入 PGpg_chameleon 是专门做 MySQL 到 PG 实时复制的轻量工具配置相对简单适合中小系统。切换确认增量延迟追平后停源库写入追平最后一段增量再切应用流量。整个时间线可以这样估算阶段动作预计耗时准备建库建表、权限、连接串预埋0.5 小时全量pgloader 或 COPY 导入存量2-6 小时不等增量binlog 同步持续运行直到追平校验行数、校验和、抽样比对1 小时内切换停写、追平、切读、观察10-30 分钟增量同步工具不是银弹。binlog 里的 DDL 变更、超大事务、特殊字符都需要在消费端容错。我就见过 Debezium 因为源库一条 ALTER TABLE 执行时间过长导致 binlog 积压整个 Kafka topic 重建。所以即便有增量工具也建议保留源库作为热备至少两周切完不要急着销毁。5. 应用层改造连接串、驱动、连接池与 ORM 适配5.1 各语言驱动替换清单与连接串写法数据库换了应用层第一件事就是换驱动。这一步比想象中简单但连接串参数带来的坑不少。语言MySQL 驱动PostgreSQL 驱动备注Javamysql-connector-jorg.postgresql:postgresql连接池配置几乎不变PythonPyMySQL / mysqlclientpsycopg2 / psycopg3事务行为有差异需要逐段检查Node.jsmysql2pg回调风格略有变化Gogo-sql-driver/mysqljackc/pgx/v5pgx 性能更好推荐.NETMySqlConnectorNpgsqlEF Core 提供器要换Java 里的典型替换// 旧 String url jdbc:mysql://localhost:3306/source_db?useSSLfalseserverTimezoneAsia/Shanghai; // 新 String url jdbc:postgresql://localhost:5432/target_db?sslmodeprefer;注意 PG 的 JDBC URL 不需要指定 serverTimezone驱动默认按照数据库 session 的 timezone 处理。如果代码里大量依赖 MySQL 的时区转换逻辑切到 PG 后反而要检查时间字段到底是不是带时区避免展示层时间整体偏移。Python 侧psycopg2 和 PyMySQL 的事务风格差异很容易引发线上故障。PyMySQL 进入with connection块后并不会自动开启事务psycopg2 却会自动 commit 或 rollback。这个差异会把一批“原来能跑、迁后丢数据”的案例带出来改代码时必须逐段检查事务边界。5.2 连接池与 PG 进程模型的匹配MySQL 的连接是线程模型连接池开到 200 甚至更多问题不大。PG 是进程模型每一条后端连接对应一个操作系统进程内存开销明显更高。很多团队迁移后第一反应是“怎么这么占内存”其实就是连接池开太大了。我通常把应用连接池最大连接数控制在 20 到 50PG 服务端max_connections调大到 200 左右给运维脚本、监控、手动查询留出余量。这里有个容易被忽略的联动work_mem是按连接计算的。如果work_mem64MB、连接数 200理论排序内存峰值就有 12.8GB这还没算其他内存。所以调高 work_mem 时连接数必须同步控制。如果业务里有大量短连接场景比如 Serverless 函数周期性地建连建议在 PG 前面加一层 PgBouncer把数据库后端连接数压到可控范围。迁移期间临时跑的同步任务很容易把后端连接占满直接触发max_connections报错。5.3 ORM 迁移中的隐形改动以 Java 生态最常见。Spring Boot JPA 项目换库时要在 application.yml 里改两项spring: datasource: url: jdbc:postgresql://localhost:5432/target_db driver-class-name: org.postgresql.Driver jpa: database: POSTGRESQL hibernate: ddl-auto: validateSQL 里如果有自定义方言或 MySQL 特有函数要逐个排查。Hibernate 对 PG 的 jsonb 类型默认支持一般如果实体里有 String 字段要存 JSON建议引入 hibernate-types 或直接把字段类型映射成自定义的 JsonbType。MyBatis 相对好一些因为#{}占位符两边通用但 XML 里写死的 MySQL 函数还是要逐个改。SQLAlchemy 项目换库最顺连接 URL 从mysqlpymysql://改成postgresqlpsycopg2://大部分声明式模型可以复用但 Enum 和 JSON 类型的映射要看 SQLAlchemy 版本差异。5.4 高频 SQL 写法差异对照先给一份最常见的对照表MySQL 写法PostgreSQL 写法说明IFNULL(expr, 0)COALESCE(expr, 0)等价COALESCE 支持多参数IF(cond, a, b)CASE WHEN cond THEN a ELSE b ENDPG 没有 IF 函数DATE_FORMAT(now(), %Y-%m-%d)TO_CHAR(now(), YYYY-MM-DD)格式串语法完全不同DATE_ADD(now(), INTERVAL 1 DAY)now() INTERVAL 1 day注意单引号GROUP_CONCAT(name SEPARATOR ,)STRING_AGG(name, ,)STRING_AGG 内部支持 ORDER BYSUBSTRING_INDEXsplit_part或substring position语义不同需要改写a || ba || bMySQL 下默认当逻辑或PG 是字符串连接符LIMIT 10 OFFSET 20LIMIT 10 OFFSET 20语法兼容反引号是另一处高频坑。MySQL 用反引号包裹字段名PG 不加引号的标识符会被转成小写加双引号则严格区分大小写。曾经有个字段叫OrderCountMySQL 里用反引号写没问题PG 里没加双引号所有查询都变成ordercount一夜之间全报列不存在。遇到驼峰字段名要么全局加双引号要么趁迁移改成下划线命名别留历史包袱。6. SQL 兼容性整改同样语义、不同写法的典型差异6.1 GROUP BY 宽松模式的消失最让人崩溃的一条如果评选“从 MySQL 迁 PG 最容易翻车的一条规则”我投 GROUP BY。MySQL 默认允许 SELECT 出没有参与 GROUP BY 的非聚合列SELECT user_id, user_name, order_id, COUNT(*) FROM orders GROUP BY user_id;这在 MySQL 里能跑user_name和order_id取的是分组内某一行结果不确定但不会报错。PG 会直接报错非聚合列必须出现在 GROUP BY 里或用于聚合函数。解决办法没有捷径只能逐条改写要么把所有非聚合字段放进 GROUP BY要么改成MAX(user_name)这类聚合写法。如果业务真的想要最细粒度的行更合理的做法是先按 user_id 分组后再自关联。这类 SQL 通常藏在报表系统里数量大、难发现。建议迁移前用静态扫描工具把 SQL 清单拉出来逐条过不要光靠测试环境跑用例。6.2 空值排序、分页与单行函数的行为差异排序差异最隐蔽。MySQL 里ORDER BY col ASC时NULL 默认排在前面PG 默认升序时 NULL 排在最后。比如一个列表页按最后登录时间升序排序MySQL 会把从未登录用户排在最前PG 会排到最后产品和运营第二天就会发现统计口径变了。解决方案是显式声明排序规则ORDER BY last_login_at ASC NULLS LAST;分页部分LIMIT/OFFSET 语法两边兼容但大数据量分页性能都不好。MySQL 的常用优化是走主键游标WHERE id last_id LIMIT 20PG 同样适用而且 PG 的 keyset pagination 实现很标准迁移时推荐顺手把分页接口改成游标模式反正逻辑类似改造量不大。单行函数差异里最坑的是日期和字符串。DATE_FORMAT在 PG 里不存在必须换成TO_CHAR格式串从%Y-%m-%d变成YYYY-MM-DD这个缩放经常导致报表日期出现“前一天”或“全空”的现象。SUBSTRING_INDEX也没有直接对应用split_part改写时要注意分隔符不存在时的行为差异。6.3 事务隔离级别与锁机制差异对业务的影响MySQL InnoDB 默认隔离级别是 REPEATABLE READPG 默认是 READ COMMITTED但这不是关键差别。真正影响业务的是 REPEATABLE READ 下 PG 的快照语义和 MySQL 不同。MySQL 的 REPEATABLE READ 在大多数情况下靠锁和间隙锁防止幻读更新冲突时事务会等待。PG 的 REPEATABLE READ 基于快照隔离不用间隙锁快照建立时看不到的行事务内永远看不到。经典场景是两个事务同时更新同一行MySQL 那边后到的事务会等待并最终成功PG 这边可能直接抛 serialization failure应用层如果没有重试机制用户就会看到更新失败。所以迁移后凡是涉及高并发“先读后写”的业务逻辑建议在应用层增加乐观锁重试。如果不想改太多代码可以把这些事务的隔离级别降到 READ COMMITTED配合SELECT ... FOR UPDATE保底。反过来也要提醒 DBA不要把全局隔离级别默认改成 SERIALIZABLEPG 的 SERIALIZABLE 是真正的 SSI 实现并发性能开销明显业务没充分测试前不要开。7. 数据校验、业务验收与灰度切换7.1 怎么证明数据没丢没多三层校验法数据导入完成后不要只信工具日志里那行“成功导入”更不要用一个count(*)就宣告结束。我常用三层校验法。第一层是行数校验。每张表分别统计count(*)、max(id)、min(id)两边对比。PG 的count(*)在大表上同样扫全表建议分批跑避免拖慢业务。第二层是特征值校验。选大表的业务主键做抽样分别算 ID 集合的差集和并集或者对关键数值字段做 SUM 对比。比如订单表按天抽样对比几天的金额合计、状态分布。这比简单 count 更能发现重复导入或字段错位。第三层是工具辅助比对。PG 的 FDW 生态里有 mysql_fdw可以把 MySQL 表包成外部表直接在 PG 里跑 SQL 比对差异。但 MySQL 到 PG 的 FDW 安装配置并不简单小项目不值得。多数情况下用脚本同时连两个库把摘要结果拉回来做 diff几十张表几分钟就能跑完更实用。业务验收阶段建议把源库慢查询日志 Top 100 的 SQL 在新的 PG 环境重放一遍对比执行时间和执行计划。这一步既是兼容性验证也是性能回归能提前暴露大多数隐藏 SQL 问题。7.2 灰度切流与双写设计切流最稳妥的方式不是“某天晚上一把梭”而是灰度。典型做法第一阶段双写。应用层把写操作同时发到 MySQL 和 PG读操作继续走 MySQL。这个阶段用真实业务流量验证结构差异。第二阶段读流量灰度。把 5% 或某个分片的读流量切到 PG观察接口耗时和错误率逐步放大到 50%。第三阶段切换写主库。停 MySQL 写入开关所有写流量切到 PGMySQL 保持只读热备。双写阶段最大的坑是幂等和顺序。MySQL 和 PG 两边的自增序列各自增长双写时不能依赖数据库生成主键否则两边 id 对不上后续比对没法做。通常的做法是应用层用分布式 ID 生成器生成主键双写两边都写入同一个 ID。如果做不到至少先用离线同步工具代替双写不要强行上双写方案。7.3 回滚预案切换不是一口气跑完的灰度切换的好处是回滚窗口足够长。我的习惯是 MySQL 侧至少保留两周热备期间所有变更单独记录。回滚触发条件提前写清楚比如“订单写入失败率超过 0.5% 持续 5 分钟”或“核心报表延迟超过阈值”不要让值班同学现场做判断。回滚动作也要提前演练停止 PG 写入恢复应用双写或直接切回 MySQL再按差异量倒灌最后一段增量数据。这里容易出问题的是回滚后 MySQL 里已经存在双写阶段产生的重复数据需要准备按业务主键去重的脚本。我手里三个项目都把回滚预演列入了迁移前 Checklist这个习惯至少救了一次上线危机。8. 迁移后的运维要点vacuum、统计信息与备份策略8.1 autovacuum 与 bloat维护模式完全不同MySQL InnoDB 也清理旧版本数据但 DBA 基本不用关心内部机制。PG 的 MVCC 实现会把旧版本留在数据文件里必须靠 VACUUM 清理否则表会越来越胀这就是 bloat。刚迁移完的头两周最容易出问题。批量导入产生大量死元组如果 autovacuum 没跟上查询执行计划会越来越差。启动项目前先检查大表统计信息SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;如果n_dead_tup持续走高考虑调低autovacuum_vacuum_scale_factor和autovacuum_vacuum_threshold或者对大表做一次手工 VACUUM。注意VACUUM FULL会锁表绝对不要在业务高峰期执行一般只用于 bloat 严重且能申请维护窗口的时候。8.2 统计信息收集与执行计划变化迁移完不要急着切换流量先对所有业务表做一次ANALYZE。批量导入很多时候会破坏统计信息的均匀性PG 优化器采样不准可能选出很差的 JOIN 顺序。导入完直接执行ANALYZE;超大表如果字段值分布极不均匀默认default_statistics_target100可能不够用可以针对列提高统计目标ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; ANALYZE orders;执行计划差异没法偷懒把慢查询日志拉出来在两边分别 EXPLAIN ANALYZE。刚开始会有不少 SQL 在 PG 上的计划比 MySQL 差常见原因是行数估算偏差、work_mem 不足导致排序落盘、或者函数写法导致索引失效。调整参数后计划会很快改善花一两天专门调慢查询是迁移上线前性价比最高的工作。8.3 备份恢复与监控体系调整MySQL 常用的备份工具是 mysqldump 和 xtrabackupPG 对应的是 pg_dump、pg_dumpall 和 pg_basebackup。建议备份策略在迁移前就接好别等上线后再补。日常备份基础上至少每周做一次恢复演练。数据损坏不可怕可怕的是备份从没验证过。监控项基本是替换式迁移MySQL 的连接数、慢查询、锁等待对应 PG 的pg_stat_activity、pg_stat_statements、pg_locks。慢查询日志在 PG 里最接近的替代是pg_stat_statements加auto_explain扩展可以记录每条 SQL 的执行计划和耗时便于日常巡检。磁盘监控要额外关注 WAL 目录增长PG 的 WAL 累积和 MySQL 的 binlog 有点相似但清理策略完全不同不要拿 binlog 的经验硬套pg_wal。我在两次迁移中最深的体会是数据库迁移的难点从来不在“把数据搬过去”而在“让业务代码以新数据库的方式运行”。如果团队没有预留足够的 SQL 改造和回归时间再好的迁移工具也救不了上线夜的慌乱。最后分享一个实战技巧迁移前先把源库慢查询日志完整收集两周整理出 Top 100 的 SQL然后在 PostgreSQL 上用真实数据逐条跑一遍 EXPLAIN提前把不兼容的语法清单列出来。这份清单是整场迁移工程里最值钱的资产比任何工具文档都实用。如果你想启动类似的迁移建议第一步不是装 PG 测试环境而是先做一次应用 SQL 盘点。把时间花在悬崖前面比挂在悬崖下面补救要值得多。 从MySQL到PostgreSQL：迁移全流程实战与避坑要点
迁移这种事做之前总觉得是“导出数据再导入改改连接串就完事”做之后才发现真正让人熬夜的从来不是搬运数据本身而是那些藏在 SQL 语义、驱动行为、运维模型里的隐性差异。我前后带团队做过两次从 MySQL 到 PostgreSQL 的完整迁移第一次从排期到稳定运行花了三个月第二次有了完整方法论文档压缩到五周。这篇就是把两次实践沉淀下来的经验拆开讲从要不要迁、怎么盘点、如何做结构转换、用什么工具搬数据到应用层怎么改、上线后怎么校验和回滚以及迁移完的运维要点。适合正在评估换库的技术负责人、DBA也适合刚接触 PostgreSQL、想提前避坑的应用开发者。先说一个总判断MySQL 和 PostgreSQL 都是非常成熟的数据库绝大多数场景下选哪个都能满足业务。所以“从 MySQL 迁到 PostgreSQL”这个命题前提一定是业务出现了 MySQL 解决起来很别扭、而 PostgreSQL 天生擅长的需求。后面的每一章都围绕这个前提展开。1. 为什么放着 MySQL 不用非要迁到 PostgreSQL一次迁移的真实动机1.1 触发迁移的典型业务信号我经手的第一条迁移案例来自一个电商后台系统。MySQL 8.0核心订单表 3000 多万行后台运营人员的组合筛选经常拖到 5 秒以上。一开始团队怀疑是索引没建好但排查后发现真正把 MySQL 逼到死角的是两类需求第一是大量 JSON 半结构化数据。商品扩展属性、活动配置、买家标签都塞在 JSON 字段里查询条件经常落在 JSON 内部的数组元素上MySQL 的 JSON 类型虽然能做路径查询但索引覆盖能力非常有限执行计划经常走不了任何索引。第二是地理信息处理。做门店配送范围分析时需要在经纬度上做距离排序和范围圈选InnoDB 的索引结构本身不支持这类空间语义只能靠应用层把数据捞出来硬算。这两件事放到 PostgreSQL 里几乎是开箱即用jsonb配 GIN 索引PostGIS 配 GiST 索引性能差距不是一个量级。类似的功能性诉求还包括复杂报表里的窗口函数、递归 CTE 越来越高频PostgreSQL 的优化器对复杂 JOIN 的选路明显更稳数据完整性要求高外键和 CHECK 约束需要被严格贯彻执行而 MySQL 在部分历史配置下会出现“约束定义还在实际校验却松散”的情况。这些信号通常不是单独出现的。如果在你的系统里同时看到两三条迁移就有了真实的价值支点如果只是“听说 PG 更强”建议先冷静。1.2 迁移前先做的三件事摸清实例、查 SQL、定窗口决定迁移前除了确认业务动机还要把三件基础工作提前做完否则排期全靠拍脑袋。第一摸清存量实例。到底有多少个 MySQL 实例每个实例里哪些库是核心业务库、哪些已经没人维护生产、测试、预发环境分别怎么管理很多时候团队只迁了主力库留下十几套边角库后续运维反而更混乱。第二收集应用 SQL 清单。这一步在整个迁移里价值最高。把源库慢查询日志收集两周整理出 Top 100 的 SQL同时让各个业务线自查代码仓库里的 SQL 写法。后面所有兼容性改造、性能回归都靠这份清单做底。第三定停服窗口。如果业务允许一个周末停服迁移后面的增量同步那一层可以简化全量搬完校验即可如果要求在线迁移不中断就要提前设计 binlog 增量同步方案。我见过不少项目在最开始没确认这点做到一半发现停服窗口不够只能临时加班赶增量方案风险一下子高了很多。1.3 什么情况下不建议迁不是所有场景都适合迁移。遇到下面四类情况我通常会明确劝退核心业务全是简单 CRUD单条 SQL 只跑主键查询没有复杂聚合和 JSON 检索。MySQL 和 PostgreSQL 在这种场景下没有体感差异迁移投入纯粹是浪费。存量存储过程和触发器数量巨大且重度依赖 MySQL 专有函数。PostgreSQL 的 PL/pgSQL 确实更强但等价改写的工作量很容易被低估。我见过一个系统 600 多个存储过程迁移组最初乐观估计两周实际用了两个月。团队没有 PostgreSQL 运维经验且预算不允许补充人力。vacuum、连接进程模型、事务快照机制都和 MySQL 差异很大出了问题现场查文档很难应对线上故障。周边工具链没有就绪。监控告警、备份恢复、中间件兼容、数据同步工具都要重新接这些隐性成本经常被忽略。一句话先确认你遇到的是“MySQL 解决不了的问题”而不是“你没把 MySQL 用好的问题”。前者迁移才有意义。2. 迁移前的地图版本选型、环境搭建与对象盘点2.1 PostgreSQL 版本选择与实例初始化参数版本选择上目前建议直接上 PostgreSQL 16 或 17。新项目直接用 17存量项目 16 也足够不建议选 15 以下版本越新JSON 能力和优化器表现越好。安装层面Windows 上用 EnterpriseDB 官方安装包最省事搜索热词里经常出现“postgresql windows 安装 服务启动失败”这种问题八成是 data 目录权限不对或者 5432 端口被占用Linux 上建议直接用官方 PGDG 源例如 RHEL 系列# RHEL 9 / Rocky Linux 9 示例其他 EL 版本对应替换 sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo dnf install -y postgresql17-server postgresql17-contrib sudo /usr/pgsql-17/bin/postgresql-17-setup initdb sudo systemctl enable --now postgresql-17装完第一件事不是急着建库而是调整几个和 MySQL 使用习惯差异很大的初始化参数。shared_buffers我通常设为物理内存的 25%但一般不超过 8GBwork_mem默认只有 4MB如果业务排序和哈希操作多建议按连接数评估后调到 16MB 甚至 64MBmaintenance_work_mem直接影响 VACUUM 和建索引速度迁移期间至少给到 256MB。MySQL 的调优思路相对集中主要围着innodb_buffer_pool_size转PG 的内存管理更分散刚上手的人很容易困惑为什么所有参数都调了性能还是上不去。关键认知是shared_buffers只负责缓存数据页大量的文件系统页缓存被操作系统接管所以不能把 MySQL 那套“buffer pool 尽量大”的思路直接搬过来。还有两个初始化时就要确认的点字符集统一 UTF8排序规则建议用C或C.UTF-8。如果业务对中文排序有特定要求可以在 initdb 时指定 ICU 规则不然后面 LIKE 查询和 ORDER BY 的默认行为会和你预期的完全不一样。2.2 迁移对象全景清单漏掉一个后面都是坑如果只把表数据导过去这个迁移一定不完整。我习惯在动任何工具之前先画一张迁移对象全景图表结构、视图、物化视图以及物化视图的刷新逻辑索引包括唯一索引、全文索引、前缀索引、函数索引主键、外键、唯一约束、非空约束、CHECK 约束、默认值存储过程、函数、触发器、事件计划任务MySQL EVENT 在 PG 没有原生资源需要评估用外部队列或 pg_cron数据库用户、角色、权限、行级安全策略依赖的字符集、排序规则、MySQL 特有 SQL 模式其中 SQL 模式是隐性炸弹。MySQL 的sql_mode影响字符串比较、日期严格校验和 GROUP BY 行为PG 没有直接对应项迁移后行为差异只能在 SQL 层消化后面第 6 章会重点讲。2.3 用数据字典做一次“结构体检”动手迁移前先用数据字典给自己做一次体检。MySQL 的information_schema能查出所有表、列、索引的基本信息PG 同样兼容这套标准视图但更深入的膨胀信息、索引使用情况要查系统表比如pg_stat_user_tables和pg_index。一个更省事的做法先用pg_dump导出目标库结构再人工 review。pg_dump --schema-only -h localhost -U pguser -d target_db schema.sql这份 SQL 比任何可视化差异报告都直观因为它把建表顺序、依赖关系、扩展加载完整呈现出来。源库侧用mysqldump --no-data导出结构两边放到同一份对比脚本里逐一核对列名和类型映射。不要指望工具全自动完成这一步结构转换的正确率直接决定后面数据迁移的顺利程度。3. 结构转换的深水区数据类型、自增列与隐式转换差异3.1 字段类型映射表照着改就对了结构转换的第一步是把 MySQL 字段类型一一映射成 PG 类型。我整理了一份在实际项目里验证过的对照表并标注了容易踩坑的点MySQL 类型PostgreSQL 类型注意点TINYINTSMALLINT如果原列是 TINYINT(1) 且当布尔用建议直接用 BOOLEANINT / INTEGERINTEGER显示宽度 INT(11) 直接去掉BIGINTBIGINT位宽一致DECIMALNUMERIC精度和小数位数保持一致FLOATREALPG 的 FLOAT 默认是 DOUBLE PRECISION 别名单精度必须显式用 REALDOUBLEDOUBLE PRECISION对应关系明确VARCHAR(N)VARCHAR(N)长度上限一致但超长写入时 PG 报错更果断CHAR(N)CHAR(N)尾部空格补齐行为两边有差异建议统一去掉尾部空格TEXTTEXTPG 的 TEXT 没有 64KB 包上限相当于 MySQL 的 LONGTEXTBLOB / LONGBLOBBYTEA类型改了应用层读写 API 也需要改DATETIMETIMESTAMP不带时区TIMESTAMPTIMESTAMPTZ如果原字段存 UTC建议直接转带时区类型DATE / TIMEDATE / TIME基本等价JSONJSONB推荐但要注意写入时键的顺序会被重排ENUM枚举类型或 VARCHAR CHECKPG 枚举后续加值靠 ALTER TYPE麻烦能用 CHECK 就用 CHECKSET关联表或数组MySQL 的 SET 找不到直接等价物推荐拆关联表这张表里最容易忽略的是 FLOAT。MySQL 的FLOAT是单精度PG 里如果直接写FLOAT得到的是双精度单精度必须用REAL否则数值精度和索引选择都会改变。3.2 自增主键的三种写法与序列同步问题MySQL 的自增主键是最典型的迁移点CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, ... ) ENGINEInnoDB;到 PG 里等价写法有三类-- 方式一SERIAL 伪类型最快但不够严谨 CREATE TABLE orders ( id SERIAL PRIMARY KEY, ... ); -- 方式二GENERATED BY DEFAULT AS IDENTITY推荐 CREATE TABLE orders ( id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, ... ); -- 方式三GENERATED ALWAYS AS IDENTITY禁止手动插入 CREATE TABLE orders ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ... );方式二和方式三的关键区别是BY DEFAULT 允许显式插入 idALWAYS 会强制使用序列生成值。迁移过程中如果要保留原主键值建议用 BY DEFAULT否则大批量导入历史数据时主键冲突会让人心态爆炸。导入完成后真正容易漏掉的是同步序列值。PG 的序列和表是分离对象即使你插入了 id300000 的数据序列可能还停在 1应用层下一条 INSERT 就会报唯一约束冲突。需要手动对齐SELECT setval(pg_get_serial_sequence(orders, id), (SELECT max(id) FROM orders));很多用 ORM 自动建表的项目迁移后没有这一步线上第一个新增数据就炸建议把它写进迁移脚本的收尾动作。3.3 字符集、排序规则与大小写敏感的坑MySQL 最常见的排序规则是utf8mb4_general_ci默认字符串比较不区分大小写。也就是说WHERE name ABC能匹配到abc唯一索引上ABC和abc会被当成同一个值。PG 默认排序规则区分大小写切过去之后会出现两个典型现象一是唯一索引不再拦截仅大小写不同的值业务层唯一逻辑可能冲突二是登录名、用户名校验等环节突然查不到数据因为之前依赖了不区分大小写的隐式比较。解决办法有三类把查询统一改成lower(name)并建表达式索引安装citext扩展让特定字段使用不区分大小写的类型或者在迁移时通过 COLLATE 指定不区分大小写的规则。我的经验是字段少、查询模式简单时用 citext 最省事字段多时还是统一lower()加表达式索引更可控因为 citext 的索引和排序行为会让后续优化器选路变得更难预测。3.4 索引与约束迁移PG 不会替你做的事MySQL InnoDB 有个隐藏行为建外键时如果列上还没索引InnoDB 会自动创建索引。PG 不会外键列上的索引必须手动建否则 UPDATE 或 DELETE 父表时子表会做全表扫描线上性能落差非常大。迁移时建议把外键列和常用过滤条件列提前建好索引。另一个常见差异是前缀索引。MySQL 允许INDEX idx_name (name(10))PG 原生 B-tree 不支持带长度的前缀索引但可以用表达式索引代替CREATE INDEX idx_name_left10 ON users (left(name, 10));代价是应用层查询必须写成同样的表达式才能命中索引。至于全文索引MySQL 的FULLTEXT在 PG 里对应tsvector加 GIN 索引这只是 DDL 替换查询语法也要从MATCH...AGAINST改成to_tsvector和plainto_tsquery的组合。约束方面还要注意默认值函数差异。MySQL 的CURRENT_TIMESTAMP可以直接作为 timestamp 的默认值PG 同样支持但一些历史表里写死DEFAULT 0的 timestamp 字段在 PG 里需要改成合法的字面量或保持可空否则建表直接报错。4. 数据迁移的三种实操路线pgloader、mysqldump 加工与增量工具4.1 pgloader一键迁移的上限和下限如果迁移的表结构比较常规没有特别复杂的自定义函数和视图pgloader 是最省力的起点。它天然处理 MySQL 到 PostgreSQL 的转换能自动完成类型映射、建表、搬索引和约束甚至能重置序列。一个典型配置长这样LOAD DATABASE FROM mysql://root:passwordlocalhost:3306/source_db INTO postgresql://pguser:passwordlocalhost:5432/target_db WITH include drop, create tables, create indexes, reset sequences, workers 8, concurrency 1, max rows per insert 500 CAST type datetime to timestamptz drop not null using zero-dates-to-null, type date drop not null using zero-dates-to-null; SET PostgreSQL PARAMETERS maintenance_work_mem 256MB, work_mem 16MB;这里有两个细节值得注意。CAST语句把 MySQL 无法表示的0000-00-00 00:00:00自动转成 NULL这是脏数据最常见的来源不处理的话导入会卡死。另外source_db和target_db必须在同一个数据库实例里pgloader 没有跨库推送的概念。pgloader 的局限也很明显对视图、函数、触发器、事件的支持很弱通常只负责表结构和数据超大表迁移速度虽然可以调整 workers 提升但和物理导入相比还是慢。我的使用习惯是拿它做中小型系统的整体搬迁大型系统反而更倾向下面的组合路线。4.2 mysqldump 导出 SQL 文本改写的应急路线不少网上教程会建议 mysqldump 加--compatiblepostgresql这里先纠正一个误区mysqldump 的--compatible选项并不支持 postgresql它只支持 oracle、ansi、no_table_options 等组合即便指定 ansi 也不会自动生成 PG 兼容语法。真正可行的“文本改写”路线是分段处理第一步用 mysqldump 只导出数据不导出表结构mysqldump -u root -p --no-create-info --skip-add-locks \ --skip-lock-tables --complete-insert --hex-blob source_db data.sql第二步用 sed 或 perl 清理反引号和 MySQL 专有转义sed -i s///g data.sql第三步用 psql 导入建议包在事务里失败可以整体回滚。数据量大时文本 INSERT 导入远不如 CSV 中转高效。MySQL 侧用SELECT ... INTO OUTFILE导出 CSVPG 侧用 COPY 导入速度能差一个数量级COPY target_table (col1, col2, col3) FROM /data/source_table.csv WITH (FORMAT csv, HEADER true, NULL NULL);如果表里有二进制字段CSV 中转要小心编码更推荐让 pgloader 这类专用工具处理 bytea 映射别用文本中转硬碰。4.3 在线迁移与增量同步把停服时间从 8 小时压到 10 分钟很多业务不允许停服一晚上做迁移这时必须做增量同步。整体思路是“先全量、后增量、再切换”。全量同步挑业务低峰时段用上述方式把存量数据搬过去。增量同步源库开启 binlog消费 binlog 变更在目标库重放。开源方案里 Debezium 最常用通过 MySQL binlog 把变更事件发到 Kafka下游消费后写入 PGpg_chameleon 是专门做 MySQL 到 PG 实时复制的轻量工具配置相对简单适合中小系统。切换确认增量延迟追平后停源库写入追平最后一段增量再切应用流量。整个时间线可以这样估算阶段动作预计耗时准备建库建表、权限、连接串预埋0.5 小时全量pgloader 或 COPY 导入存量2-6 小时不等增量binlog 同步持续运行直到追平校验行数、校验和、抽样比对1 小时内切换停写、追平、切读、观察10-30 分钟增量同步工具不是银弹。binlog 里的 DDL 变更、超大事务、特殊字符都需要在消费端容错。我就见过 Debezium 因为源库一条 ALTER TABLE 执行时间过长导致 binlog 积压整个 Kafka topic 重建。所以即便有增量工具也建议保留源库作为热备至少两周切完不要急着销毁。5. 应用层改造连接串、驱动、连接池与 ORM 适配5.1 各语言驱动替换清单与连接串写法数据库换了应用层第一件事就是换驱动。这一步比想象中简单但连接串参数带来的坑不少。语言MySQL 驱动PostgreSQL 驱动备注Javamysql-connector-jorg.postgresql:postgresql连接池配置几乎不变PythonPyMySQL / mysqlclientpsycopg2 / psycopg3事务行为有差异需要逐段检查Node.jsmysql2pg回调风格略有变化Gogo-sql-driver/mysqljackc/pgx/v5pgx 性能更好推荐.NETMySqlConnectorNpgsqlEF Core 提供器要换Java 里的典型替换// 旧 String url jdbc:mysql://localhost:3306/source_db?useSSLfalseserverTimezoneAsia/Shanghai; // 新 String url jdbc:postgresql://localhost:5432/target_db?sslmodeprefer;注意 PG 的 JDBC URL 不需要指定 serverTimezone驱动默认按照数据库 session 的 timezone 处理。如果代码里大量依赖 MySQL 的时区转换逻辑切到 PG 后反而要检查时间字段到底是不是带时区避免展示层时间整体偏移。Python 侧psycopg2 和 PyMySQL 的事务风格差异很容易引发线上故障。PyMySQL 进入with connection块后并不会自动开启事务psycopg2 却会自动 commit 或 rollback。这个差异会把一批“原来能跑、迁后丢数据”的案例带出来改代码时必须逐段检查事务边界。5.2 连接池与 PG 进程模型的匹配MySQL 的连接是线程模型连接池开到 200 甚至更多问题不大。PG 是进程模型每一条后端连接对应一个操作系统进程内存开销明显更高。很多团队迁移后第一反应是“怎么这么占内存”其实就是连接池开太大了。我通常把应用连接池最大连接数控制在 20 到 50PG 服务端max_connections调大到 200 左右给运维脚本、监控、手动查询留出余量。这里有个容易被忽略的联动work_mem是按连接计算的。如果work_mem64MB、连接数 200理论排序内存峰值就有 12.8GB这还没算其他内存。所以调高 work_mem 时连接数必须同步控制。如果业务里有大量短连接场景比如 Serverless 函数周期性地建连建议在 PG 前面加一层 PgBouncer把数据库后端连接数压到可控范围。迁移期间临时跑的同步任务很容易把后端连接占满直接触发max_connections报错。5.3 ORM 迁移中的隐形改动以 Java 生态最常见。Spring Boot JPA 项目换库时要在 application.yml 里改两项spring: datasource: url: jdbc:postgresql://localhost:5432/target_db driver-class-name: org.postgresql.Driver jpa: database: POSTGRESQL hibernate: ddl-auto: validateSQL 里如果有自定义方言或 MySQL 特有函数要逐个排查。Hibernate 对 PG 的 jsonb 类型默认支持一般如果实体里有 String 字段要存 JSON建议引入 hibernate-types 或直接把字段类型映射成自定义的 JsonbType。MyBatis 相对好一些因为#{}占位符两边通用但 XML 里写死的 MySQL 函数还是要逐个改。SQLAlchemy 项目换库最顺连接 URL 从mysqlpymysql://改成postgresqlpsycopg2://大部分声明式模型可以复用但 Enum 和 JSON 类型的映射要看 SQLAlchemy 版本差异。5.4 高频 SQL 写法差异对照先给一份最常见的对照表MySQL 写法PostgreSQL 写法说明IFNULL(expr, 0)COALESCE(expr, 0)等价COALESCE 支持多参数IF(cond, a, b)CASE WHEN cond THEN a ELSE b ENDPG 没有 IF 函数DATE_FORMAT(now(), %Y-%m-%d)TO_CHAR(now(), YYYY-MM-DD)格式串语法完全不同DATE_ADD(now(), INTERVAL 1 DAY)now() INTERVAL 1 day注意单引号GROUP_CONCAT(name SEPARATOR ,)STRING_AGG(name, ,)STRING_AGG 内部支持 ORDER BYSUBSTRING_INDEXsplit_part或substring position语义不同需要改写a || ba || bMySQL 下默认当逻辑或PG 是字符串连接符LIMIT 10 OFFSET 20LIMIT 10 OFFSET 20语法兼容反引号是另一处高频坑。MySQL 用反引号包裹字段名PG 不加引号的标识符会被转成小写加双引号则严格区分大小写。曾经有个字段叫OrderCountMySQL 里用反引号写没问题PG 里没加双引号所有查询都变成ordercount一夜之间全报列不存在。遇到驼峰字段名要么全局加双引号要么趁迁移改成下划线命名别留历史包袱。6. SQL 兼容性整改同样语义、不同写法的典型差异6.1 GROUP BY 宽松模式的消失最让人崩溃的一条如果评选“从 MySQL 迁 PG 最容易翻车的一条规则”我投 GROUP BY。MySQL 默认允许 SELECT 出没有参与 GROUP BY 的非聚合列SELECT user_id, user_name, order_id, COUNT(*) FROM orders GROUP BY user_id;这在 MySQL 里能跑user_name和order_id取的是分组内某一行结果不确定但不会报错。PG 会直接报错非聚合列必须出现在 GROUP BY 里或用于聚合函数。解决办法没有捷径只能逐条改写要么把所有非聚合字段放进 GROUP BY要么改成MAX(user_name)这类聚合写法。如果业务真的想要最细粒度的行更合理的做法是先按 user_id 分组后再自关联。这类 SQL 通常藏在报表系统里数量大、难发现。建议迁移前用静态扫描工具把 SQL 清单拉出来逐条过不要光靠测试环境跑用例。6.2 空值排序、分页与单行函数的行为差异排序差异最隐蔽。MySQL 里ORDER BY col ASC时NULL 默认排在前面PG 默认升序时 NULL 排在最后。比如一个列表页按最后登录时间升序排序MySQL 会把从未登录用户排在最前PG 会排到最后产品和运营第二天就会发现统计口径变了。解决方案是显式声明排序规则ORDER BY last_login_at ASC NULLS LAST;分页部分LIMIT/OFFSET 语法两边兼容但大数据量分页性能都不好。MySQL 的常用优化是走主键游标WHERE id last_id LIMIT 20PG 同样适用而且 PG 的 keyset pagination 实现很标准迁移时推荐顺手把分页接口改成游标模式反正逻辑类似改造量不大。单行函数差异里最坑的是日期和字符串。DATE_FORMAT在 PG 里不存在必须换成TO_CHAR格式串从%Y-%m-%d变成YYYY-MM-DD这个缩放经常导致报表日期出现“前一天”或“全空”的现象。SUBSTRING_INDEX也没有直接对应用split_part改写时要注意分隔符不存在时的行为差异。6.3 事务隔离级别与锁机制差异对业务的影响MySQL InnoDB 默认隔离级别是 REPEATABLE READPG 默认是 READ COMMITTED但这不是关键差别。真正影响业务的是 REPEATABLE READ 下 PG 的快照语义和 MySQL 不同。MySQL 的 REPEATABLE READ 在大多数情况下靠锁和间隙锁防止幻读更新冲突时事务会等待。PG 的 REPEATABLE READ 基于快照隔离不用间隙锁快照建立时看不到的行事务内永远看不到。经典场景是两个事务同时更新同一行MySQL 那边后到的事务会等待并最终成功PG 这边可能直接抛 serialization failure应用层如果没有重试机制用户就会看到更新失败。所以迁移后凡是涉及高并发“先读后写”的业务逻辑建议在应用层增加乐观锁重试。如果不想改太多代码可以把这些事务的隔离级别降到 READ COMMITTED配合SELECT ... FOR UPDATE保底。反过来也要提醒 DBA不要把全局隔离级别默认改成 SERIALIZABLEPG 的 SERIALIZABLE 是真正的 SSI 实现并发性能开销明显业务没充分测试前不要开。7. 数据校验、业务验收与灰度切换7.1 怎么证明数据没丢没多三层校验法数据导入完成后不要只信工具日志里那行“成功导入”更不要用一个count(*)就宣告结束。我常用三层校验法。第一层是行数校验。每张表分别统计count(*)、max(id)、min(id)两边对比。PG 的count(*)在大表上同样扫全表建议分批跑避免拖慢业务。第二层是特征值校验。选大表的业务主键做抽样分别算 ID 集合的差集和并集或者对关键数值字段做 SUM 对比。比如订单表按天抽样对比几天的金额合计、状态分布。这比简单 count 更能发现重复导入或字段错位。第三层是工具辅助比对。PG 的 FDW 生态里有 mysql_fdw可以把 MySQL 表包成外部表直接在 PG 里跑 SQL 比对差异。但 MySQL 到 PG 的 FDW 安装配置并不简单小项目不值得。多数情况下用脚本同时连两个库把摘要结果拉回来做 diff几十张表几分钟就能跑完更实用。业务验收阶段建议把源库慢查询日志 Top 100 的 SQL 在新的 PG 环境重放一遍对比执行时间和执行计划。这一步既是兼容性验证也是性能回归能提前暴露大多数隐藏 SQL 问题。7.2 灰度切流与双写设计切流最稳妥的方式不是“某天晚上一把梭”而是灰度。典型做法第一阶段双写。应用层把写操作同时发到 MySQL 和 PG读操作继续走 MySQL。这个阶段用真实业务流量验证结构差异。第二阶段读流量灰度。把 5% 或某个分片的读流量切到 PG观察接口耗时和错误率逐步放大到 50%。第三阶段切换写主库。停 MySQL 写入开关所有写流量切到 PGMySQL 保持只读热备。双写阶段最大的坑是幂等和顺序。MySQL 和 PG 两边的自增序列各自增长双写时不能依赖数据库生成主键否则两边 id 对不上后续比对没法做。通常的做法是应用层用分布式 ID 生成器生成主键双写两边都写入同一个 ID。如果做不到至少先用离线同步工具代替双写不要强行上双写方案。7.3 回滚预案切换不是一口气跑完的灰度切换的好处是回滚窗口足够长。我的习惯是 MySQL 侧至少保留两周热备期间所有变更单独记录。回滚触发条件提前写清楚比如“订单写入失败率超过 0.5% 持续 5 分钟”或“核心报表延迟超过阈值”不要让值班同学现场做判断。回滚动作也要提前演练停止 PG 写入恢复应用双写或直接切回 MySQL再按差异量倒灌最后一段增量数据。这里容易出问题的是回滚后 MySQL 里已经存在双写阶段产生的重复数据需要准备按业务主键去重的脚本。我手里三个项目都把回滚预演列入了迁移前 Checklist这个习惯至少救了一次上线危机。8. 迁移后的运维要点vacuum、统计信息与备份策略8.1 autovacuum 与 bloat维护模式完全不同MySQL InnoDB 也清理旧版本数据但 DBA 基本不用关心内部机制。PG 的 MVCC 实现会把旧版本留在数据文件里必须靠 VACUUM 清理否则表会越来越胀这就是 bloat。刚迁移完的头两周最容易出问题。批量导入产生大量死元组如果 autovacuum 没跟上查询执行计划会越来越差。启动项目前先检查大表统计信息SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;如果n_dead_tup持续走高考虑调低autovacuum_vacuum_scale_factor和autovacuum_vacuum_threshold或者对大表做一次手工 VACUUM。注意VACUUM FULL会锁表绝对不要在业务高峰期执行一般只用于 bloat 严重且能申请维护窗口的时候。8.2 统计信息收集与执行计划变化迁移完不要急着切换流量先对所有业务表做一次ANALYZE。批量导入很多时候会破坏统计信息的均匀性PG 优化器采样不准可能选出很差的 JOIN 顺序。导入完直接执行ANALYZE;超大表如果字段值分布极不均匀默认default_statistics_target100可能不够用可以针对列提高统计目标ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; ANALYZE orders;执行计划差异没法偷懒把慢查询日志拉出来在两边分别 EXPLAIN ANALYZE。刚开始会有不少 SQL 在 PG 上的计划比 MySQL 差常见原因是行数估算偏差、work_mem 不足导致排序落盘、或者函数写法导致索引失效。调整参数后计划会很快改善花一两天专门调慢查询是迁移上线前性价比最高的工作。8.3 备份恢复与监控体系调整MySQL 常用的备份工具是 mysqldump 和 xtrabackupPG 对应的是 pg_dump、pg_dumpall 和 pg_basebackup。建议备份策略在迁移前就接好别等上线后再补。日常备份基础上至少每周做一次恢复演练。数据损坏不可怕可怕的是备份从没验证过。监控项基本是替换式迁移MySQL 的连接数、慢查询、锁等待对应 PG 的pg_stat_activity、pg_stat_statements、pg_locks。慢查询日志在 PG 里最接近的替代是pg_stat_statements加auto_explain扩展可以记录每条 SQL 的执行计划和耗时便于日常巡检。磁盘监控要额外关注 WAL 目录增长PG 的 WAL 累积和 MySQL 的 binlog 有点相似但清理策略完全不同不要拿 binlog 的经验硬套pg_wal。我在两次迁移中最深的体会是数据库迁移的难点从来不在“把数据搬过去”而在“让业务代码以新数据库的方式运行”。如果团队没有预留足够的 SQL 改造和回归时间再好的迁移工具也救不了上线夜的慌乱。最后分享一个实战技巧迁移前先把源库慢查询日志完整收集两周整理出 Top 100 的 SQL然后在 PostgreSQL 上用真实数据逐条跑一遍 EXPLAIN提前把不兼容的语法清单列出来。这份清单是整场迁移工程里最值钱的资产比任何工具文档都实用。如果你想启动类似的迁移建议第一步不是装 PG 测试环境而是先做一次应用 SQL 盘点。把时间花在悬崖前面比挂在悬崖下面补救要值得多。