做了不少年数据库相关的工作MySQL和PostgreSQL都深度用过这几年陆陆续续接手了好几个从MySQL迁移到PostgreSQL的项目。这个主题最近确实很热有人是冲着PostgreSQL更强的查询能力去的有人是为了统一技术栈也有人是因为合规或者国产化要求必须换库。不管出于什么原因迁移这件事本身都不只是“把数据倒过去”那么简单里面涉及表结构转换、SQL语法差异、数据校验、停机窗口设计、回滚预案每一步都有坑。这篇文章我打算按照我自己的实操经验把从MySQL迁到PostgreSQL的完整流程拆开讲清楚。包括迁移前怎么评估、工具怎么选、表结构怎么转换、数据怎么同步、切库怎么切、出了问题怎么回滚以及我在实际项目中踩过的那些坑。内容会比较长但都是能直接落地的东西适合正准备做迁移评估的架构师、负责执行迁移的DBA以及被临时拉来干这个活的开发同学参考。1. 为什么值得从MySQL迁到PostgreSQL1.1 两个数据库的本质差异先说结论MySQL和PostgreSQL虽然都是关系型数据库但设计哲学差别很大。MySQL的设计初衷是“轻量、快速、简单”在互联网读多写少的场景下表现优秀生态成熟运维资料一堆。PostgreSQL则更像一个“什么都能干”的瑞士军刀功能全面SQL标准兼容度高擅长复杂查询和数据分析扩展能力极强。如果你只是存一些简单业务数据几十张表查询条件单一那MySQL完全够用甚至更省心。但一旦涉及复杂报表、多层子查询、窗口函数、递归查询、JSON数据结构化查询这些场景MySQL写起来很别扭优化器也不一定配合你。PostgreSQL在这些方面是另一个水平尤其是PG 12以后优化器能力和并行查询越来越强复杂SQL的响应速度往往能给你惊喜。两者的核心差异我用一个表格归纳下对比维度MySQLPostgreSQL事务隔离默认Repeatable Read实现方式为MVCC锁默认Read CommittedMVCC实现更成熟复杂查询优化器相对保守子查询性能不稳优化器强大支持CTE、窗口函数、递归JSON支持JSON类型功能有限JSONB类型支持索引和高效查询索引类型B-Tree为主8.0支持倒排索引B-Tree、GIN、GiST、BRIN、部分索引、表达式索引分区表8.0开始支持但运维成本高原生支持声明式分区10.0后很成熟扩展能力插件生态一般扩展机制强大PostGIS、pgvector等SQL标准兼容性一般方言多兼容性高SQL标准贴合度好许可证GPL/商用双许可PostgreSQL License宽松自由这里面最关键的一个点是“优化器”。MySQL在复杂查询上的表现说实话有点看运气同样的SQL换个数据分布执行计划可能就变了。PostgreSQL基于代价的优化器做得更精细统计信息更丰富复杂查询的稳定性明显更好。这也是很多团队从MySQL迁到PG的最直接原因。1.2 什么情况下该迁什么情况下不该迁我见过不少团队是看到别人迁了自己也想迁结果搞了几个月一地鸡毛。所以在动手之前先想清楚你到底为什么要迁。适合迁移的场景有几个。第一业务里复杂查询占比高报表需求多MySQL跑不动或者SQL写起来太痛苦。第二需要JSONB、全文检索、地理空间这类高级特性MySQL要么没有要么体验不佳。第三有合规或者国产化要求需要替换掉MySQLPostgreSQL是常见选择。第四团队要统一技术栈减少多数据库维护成本。不适合迁移的也明显。如果项目就是个简单的CRUD应用几十张表查询都很简单MySQL运行得好好的那迁移纯属给自己找事。尤其是团队里没人真正用过PostgreSQL的情况下迁移后SQL写法、索引设计、调优手段都要重新学隐性成本非常可观。还有一种情况要特别提醒如果核心诉求是“解决慢查询”先别急着迁移。先看是不是SQL本身写得烂、索引没建对、数据模型设计不合理。很多时候把这些基础问题解决了MySQL的性能还远没到瓶颈。为了一个建好索引就能解决的问题去迁移数据库代价太大了。2. 迁移前的准备别急着动手2.1 盘点要迁移的存量对象迁移的第一步不是装PostgreSQL而是把现有MySQL里的家底盘清楚。别嫌这步枯燥后面所有计划都建立在这个清单之上。我一般会先执行几条SQL把全貌摸出来。查看所有数据库和大小统计每个库的表数量找出大表再列出所有视图、存储过程、触发器、定时事件。这些信息决定迁移的复杂度和工作量。比如在MySQL里可以用这样一组查询来盘点-- 查看所有数据库及大小 SELECT table_schema, ROUND(SUM(data_length index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables GROUP BY table_schema; -- 查看每个库的表数量 SELECT table_schema, COUNT(*) AS table_count FROM information_schema.tables WHERE table_type BASE TABLE GROUP BY table_schema; -- 找出超过1GB的大表 SELECT table_schema, table_name, ROUND((data_length index_length) / 1024 / 1024 / 1024, 2) AS size_gb FROM information_schema.tables WHERE table_type BASE TABLE HAVING size_gb 1 ORDER BY size_gb DESC; -- 列出所有存储过程、函数、触发器 SELECT routine_name, routine_type FROM information_schema.routines; SELECT trigger_name, event_object_table FROM information_schema.triggers;这些SQL在迁移规划阶段价值很大。我见过有人迁移到一半发现有个300GB的大表没评估进去导致停机窗口严重超时。还有的发现业务里用了大量存储过程而MySQL的存储过程和PG的PL/pgSQL语法差异不小改造工作量被严重低估。除了数据库对象本身还要梳理应用层的SQL使用情况。这一步同样关键。如果应用是ORM框架比如MyBatis、JPA、Hibernate生成的SQL还好改改方言配置就能跑。但如果项目里有大量手写SQL尤其是复杂查询、动态拼接SQL那就要逐条检查兼容性。我通常会让开发团队跑一个静态扫描把项目代码里所有手写SQL提取出来和类型转换清单做比对提前标记出有风险的语句。2.2 迁移工具选型对比工具选得好迁移成功一半。市面上可以用的方案大致分为四类我按推荐程度排个序工具/方案适用场景优点缺点pgloader中小型库全量迁移开源免费支持自动类型转换、索引转换等对超大库同步速度一般mysqldump 手工转换数据结构简单、表量少可控性强不需要额外安装工具需要大量手工调整DDLNavicat等GUI工具快速小规模迁移操作直观点点鼠标就行复杂对象支持差类型映射粗糙Debezium 自研同步脚本大库、准实时迁移支持增量同步停机短部署复杂度高需要中间件运维我个人的经验是中小型项目首选pgloader它能把MySQL的建表语句自动转换为PostgreSQL格式大部分数据类型都能正确映射索引、外键也能一并处理。真遇到转换不了的特殊类型它会报错并告诉你原因方便手工修正。如果库特别大数据量在TB级别那pgloader在全量阶段会比较吃力通常的做法是先做一次全量初始化再用Debezium监听MySQL binlog做增量同步最后在停机窗口内完成最终切换。这个方案对运维能力要求高但能实现准不停服迁移。还有一种情况是项目里MySQL用了一些PG完全没有对应的功能比如某些存储引擎特性、自定义函数这些对象需要提前在PG里用别的方案重写。这些特殊依赖都应该在盘点阶段标记出来而不是等迁移时才发现。2.3 搭建PostgreSQL目标环境迁移前先把PG环境准备好版本选择上我通常会选当前稳定版PG 16或者更新的版本。Windows环境安装直接去官网下安装包即可安装过程中记得选择安装pgAdmin和Stack Builder后续管理方便很多。Linux环境用发行版的包管理器装也行但建议直接使用PostgreSQL官方提供的APT/YUM源版本更新而且不会有系统自带版本太旧的问题。装完PG后有几个参数我强烈建议在迁移前就调好。shared_buffers设置为核心内存的25%左右work_mem根据并发和内存大小调整maintenance_work_mem在迁移建索引时可以临时调大wal_level如果是后续要做增量同步就必须设置为replica或者logical。另外一定要确认字符集选择UTF8排序规则用合理的选项这个在初始化数据库时就要确定后面改起来非常麻烦。注意数据库字符集和排序规则是迁移中最容易忽略的一项。MySQL的utf8mb4对应PG的UTF8但如果是中文排序、大小写敏感这类需求需要在初始化数据库时规划好LC_COLLATE和LC_CTYPE否则建完库再想改就麻烦了。3. 迁移实操从表结构到数据再到业务逻辑3.1 表结构与数据类型映射这是整个迁移里工作量最大、最琐碎的部分。MySQL和PostgreSQL的数据类型虽然名字看着像但实际语义和长度定义有不少出入。我整理了一张常用的映射表可以直接照着用MySQL类型PostgreSQL类型说明TINYINTSMALLINTMySQL的TINYINT是1字节PG里最接近的是SMALLINTTINYINT(1)BOOLEAN如果业务把它当布尔值用建议直接转BOOLEANSMALLINT/INTSMALLINT/INTEGER长度修饰符省略PG不关心显示宽度BIGINTBIGINT无变化FLOAT/DOUBLEREAL/DOUBLE PRECISION注意浮点精度踩坑DECIMAL/NUMERICNUMERIC基本兼容CHAR/VARCHARCHAR/VARCHAR注意VARCHAR长度定义PG没有长度上限默认值TEXTTEXT无变化DATETIMETIMESTAMP对应不带时区的时间戳TIMESTAMPTIMESTAMPTZ强烈建议用带时区类型DATEDATE无变化TIMETIME无变化ENUMVARCHAR CHECK约束PG有原生ENUM但后续加值要ALTER TYPE不灵活SETTEXT CHECK约束没有直接对应按业务拆解JSONJSONBJSONB支持索引性能更好BLOB/LONGBLOBBYTEA二进制大对象LONGTEXTTEXT无变化这里面最坑的是TINYINT(1)。MySQL里很多开发者用它表示布尔值迁移到PG时如果直接转成SMALLINT应用层用0和1判断还好但如果是通过ORM映射成boolean字段的就会出问题。我在一个项目里遇到过一次迁完后某个接口突然报错排查半天发现就是ORM框架把TINYINT(1)映射为Boolean而PG那边是SMALLINT数据能查出来但类型转换失败。所以迁移前一定要搞清每个TINYINT(1)字段在业务里的真实含义。来看一个实际的DDL转换例子。MySQL的建表语句是这样的CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL, status TINYINT(1) NOT NULL DEFAULT 0 COMMENT 0-待支付 1-已支付, total_amount DECIMAL(10,2) NOT NULL, remark TEXT, extra JSON, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;转换成PostgreSQL的DDLCREATE TABLE orders ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, status BOOLEAN NOT NULL DEFAULT FALSE, total_amount NUMERIC(10,2) NOT NULL, remark TEXT, extra JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uk_order_no UNIQUE (order_no) ); CREATE INDEX idx_user_id ON orders (user_id); COMMENT ON TABLE orders IS 订单表;注意几个细节。MySQL的AUTO_INCREMENT在PG里用GENERATED ALWAYS AS IDENTITY替代这是PG 10开始支持的标准写法比用SERIAL更规范。MySQL的默认值CURRENT_TIMESTAMP对应PG的NOW()。ON UPDATE CURRENT_TIMESTAMP这个行为PG没有原生支持通常的做法是应用层更新时同时更新updated_at字段或者用触发器实现。3.2 约束、索引、自增主键的处理索引迁移这块很多人只关心主键和普通索引其实PG的索引能力远不止这些。如果你之前用MySQL只是为了主键查询和简单过滤那PG的B-Tree索引表现不差。但如果业务里需要全文检索、JSON字段查询、数组包含这类操作PG的GIN索引能带来质的提升这是迁移后的一个红利。不过索引迁移有个容易忽略的坑MySQL里外键约束往往没建因为InnoDB下建外键会影响写入性能很多团队干脆不建。PG里如果迁移时直接把外键约束补上迁移后写入变慢会很明显。我的建议是如果业务确实依赖外键保证数据完整性那就保留如果只是为了“规范化”而建实际应用层已经保证了完整性那就不要建保持和原来一致的性能表现。还有一个需要特别留意的MySQL的唯一索引允许NULL重复而PG默认也是这样。但如果你在MySQL里用了“允许一个字段多个NULL但非NULL唯一”的业务逻辑PG的行为和MySQL是一致的这个不会出问题。可如果业务把NULL当普通值处理那就要注意了最好在迁移时用NULLS NOT DISTINCT选项创建唯一约束PG 15开始支持这个特性。3.3 存储过程与触发器的改造MySQL存储过程和PostgreSQL的PL/pgSQL虽然都是过程化语言但语法差异很大。MySQL的存储过程用BEGIN...END包裹、用DELIMITER声明结束符变量用DECLARE定义、SELECT ... INTO赋值。PL/pgSQL则用$$作为函数体界定符赋值用:返回值用RETURN结构上更接近Oracle的PL/SQL。举一个典型的例子MySQL里一个简单的存储过程DELIMITER $$ CREATE PROCEDURE get_user_orders(IN p_user_id BIGINT) BEGIN SELECT id, order_no, total_amount FROM orders WHERE user_id p_user_id; END$$ DELIMITER ;对应的PostgreSQL函数CREATE OR REPLACE FUNCTION get_user_orders(p_user_id BIGINT) RETURNS TABLE(id INTEGER, order_no VARCHAR, total_amount NUMERIC) AS $$ BEGIN RETURN QUERY SELECT o.id, o.order_no, o.total_amount FROM orders o WHERE o.user_id p_user_id; END; $$ LANGUAGE plpgsql;这还算简单的。如果遇到复杂的存储过程里面使用了游标、动态SQL、异常处理改造的工作量可能会超出想象。所以我一直强调迁移评估阶段要对存储过程按复杂度打分提前预估改造工时。触发器的差异同样值得注意。MySQL的触发器直接用CREATE TRIGGERPG则要先写一个返回TRIGGER类型的函数再用CREATE TRIGGER绑定。逻辑没变但代码结构完全不同。另外PG的触发器函数里通过NEW和OLD行变量访问数据这点和MySQL相似不算难理解。3.4 应用层SQL语句的兼容性调整数据结构和存储过程改造完成后应用层的SQL兼容性调整是另一个大工程。很多应用把MySQL特有的语法用得很顺手到了PG下面这些SQL全部报错。下面这张表是我在项目中总结的高频差异点MySQL写法PostgreSQL写法说明fieldfieldMySQL用反引号PG用双引号LIMIT 10, 20LIMIT 20 OFFSET 10MySQL的偏移分页语法PG不支持GROUP BY宽松模式GROUP BY严格模式PG要求SELECT的列必须出现在GROUP BY中IFNULL(a, b)COALESCE(a, b)功能等价CONCAT(a, b, c)CONCAT(a, b, c)或a || b || c两者都支持但||在MySQL里是逻辑或NOW()NOW()或CURRENT_TIMESTAMP基本兼容INSERT ... ON DUPLICATE KEY UPDATEINSERT ... ON CONFLICT (col) DO UPDATE语义类似语法不同REPLACE INTOINSERT ... ON CONFLICT ... DO NOTHING/UPDATEREPLACE会先删后插容易丢失数据DATE_FORMAT()TO_CHAR()格式化函数差异大%通配符在LIKE中同样适用兼容UNSIGNED整数无对应PG不支持无符号需用CHECK约束模拟这里我重点说两个容易踩坑的地方。第一个是GROUP BY的严格模式。MySQL在ONLY_FULL_GROUP_BY默认关闭时允许SELECT中列出不在GROUP BY里的列而且不报错只是返回的值不确定。很多开发者习惯了这种写法业务代码里处处都是。迁移到PG后这些SQL全部会报错必须把每个非聚合列都加进GROUP BY或者改成聚合函数包裹。这个过程非常折腾我见过一个项目在迁移阶段改了几百条类似的SQL。第二个是INSERT ... ON DUPLICATE KEY UPDATE。很多团队用它实现在并发场景下的幂等写入MySQL里一行就能搞定。PG的等价写法是ON CONFLICT但要注意必须指定冲突的约束或列名。举个例子MySQL的写法INSERT INTO orders (order_no, user_id, total_amount) VALUES (NO10001, 1, 99.00) ON DUPLICATE KEY UPDATE user_id VALUES(user_id), total_amount VALUES(total_amount);PostgreSQL的写法INSERT INTO orders (order_no, user_id, total_amount) VALUES (NO10001, 1, 99.00) ON CONFLICT (order_no) DO UPDATE SET user_id EXCLUDED.user_id, total_amount EXCLUDED.total_amount;注意PG里通过EXCLUDED关键字引用准备插入但发生冲突的那行数据而不是MySQL的VALUES()函数。如果漏掉了ON CONFLICT后面的列名PG会报错让你明确冲突目标。4. 迁移数据全量同步与校验4.1 用pgloader做全量迁移工具层面我用的最多的是pgloader。这个工具专门用于从其他数据库迁移到PostgreSQL对MySQL的支持很成熟能自动做类型转换、关键字转义、索引转换还能并行加载。安装pgloader的方式macOS直接brew install pgloaderLinux下可以从源码编译Deployment版本容易找。Windows下稍微麻烦点我建议在WSL环境里跑或者直接放在Linux服务器上执行。一条最基础的pgloader迁移命令是这样pgloader mysql://user:password127.0.0.1:3306/source_db postgresql://user:password127.0.0.1:5432/target_db它会把整个库的所有表、索引、约束一次性迁移过去并在最后输出一份报告显示每张表的行数、错误数、耗时等。如果你的库比较简单这条命令就够用了。但实际项目中通常需要更精细的控制可以使用.load文件来定义迁移规则LOAD DATABASE FROM mysql://user:password127.0.0.1:3306/source_db INTO postgresql://user:password127.0.0.1:5432/target_db WITH include drop, create tables, create indexes, reset sequences, workers 8, concurrency 4, batch rows 1000 SET PostgreSQL PARAMETERS maintenance_work_mem 256MB, work_mem 16MB CAST type datetime to timestamptz using zero-dates-to-null, type tinyint(1) to boolean when maybe;这里说几个关键参数。create tables让pgloader自动生成目标表结构reset sequences负责重建自增序列workers和concurrency控制并行度数据量大的时候适当调高能显著提升速度。CAST那段是自定义类型转换规则比如把TINYINT(1)转成BOOLEAN把无效的零日期转成NULL这些规则在实际迁移中非常实用因为很多老系统的数据质量并不理想。pgloader跑完后我会先看报告里的错误数。如果错误比较多一般是某个字段的数据格式无法转换比如MySQL的DATETIME里存了0000-00-00这种非法值PG会拒绝导入。处理方式是在CAST规则里指定zero-dates-to-null或者先在MySQL侧把这部分脏数据清理掉。注意pgloader在迁移超大的表时单表加载速度和索引重建速度可能感人。我遇到过一张5亿行的日志表全量加载加索引重建跑了将近10个小时。如果业务有这类大表一定要提前压测给停机窗口留足余量。4.2 数据校验怎么做才靠谱数据迁移完不代表万事大吉。校验是很多人会跳过或者敷衍的一步但我每次都会做得很细。原因很简单数据是公司资产哪怕丢了几行业务上线后出问题就是事故级别的故障。我常用的校验方案分三层。第一层是行数校验最简单但最容易发现问题。分别对MySQL和PG执行COUNT(*)对比每张表的行数是否一致。表多的时候可以用脚本批量比对MySQL和PG都有information_schema写个Python或Shell脚本把每张表的行数拉出来做diff几分钟就能完成。第二层是抽样数据比对。每张表按主键范围或者随机抽取一定比例的行逐字段对比内容。这个可以用工具比如从MySQL导出CSV再在PG里导入临时表对比。数据量大的表做全量比对不现实抽样加明细抽查是性价比最高的方式。第三层是业务探针。这个最灵活也最能发现“数据没丢但业务不对”的问题。挑几个核心业务场景写一些关键查询在MySQL和PG上分别执行对比返回结果。比如查某个用户的订单总数和总金额、查某天的销售汇总、查订单状态分布。这一步能发现字段类型映射导致的内容变化比如浮点精度、时区、布尔值存储差异这些靠行数校验发现不了。数据校验通过后还有一个很容易忽略的步骤对PG的表执行ANALYZE刷新统计信息。PG的查询优化器严重依赖统计信息如果刚迁移完就直接上线跑业务很多SQL会因为统计信息缺失或过旧走错执行计划性能表现会很差。迁移完第一时间对所有表跑一遍ANALYZE这个动作虽然简单但对后续线上性能有直接帮助。-- 对全库所有表执行统计信息收集 ANALYZE;5. 停机切换与回滚预案5.1 切换前检查清单数据同步完成、校验通过之后真正切库的前一刻一定要有一份详尽的检查清单。我见过太多项目在切换当天出问题不是数据库本身不行而是准备工作有遗漏。下面是我每次切换前必查的清单应用层所有连接串是否已改为指向PG的IP和端口能改配置文件的改配置写死在代码里的要提前发版数据库账号权限是否最小化配置完成原来MySQL里的账号对应的PG账号和权限是否一致自增序列是否已同步到正确位置否则插入新数据时主键冲突定时任务、消息队列的持久化表是否已经迁移这部分经常被遗漏监控系统和告警规则是否切换了数据源如果监控还指向旧库切换后就是两眼一抹黑备份策略是否已生效PG的备份机制和MySQL不一样建议切换前先做一次完整备份验证应用服务器的连接池配置是否兼容PG驱动比如连接池的初始化SQL、验证语句是否还兼容还有一个细节容易被忽略原来MySQL的连接串和PG的不一样应用的数据库驱动也要换掉。Java应用的MySQL驱动和PostgreSQL驱动是不同的JAR包切换后要确保驱动版本正确、依赖无冲突。Python应用的pymysql和psycopg2也是完全不同的库。这些前置条件如果不准备好切换时应用会直接连不上数据库。切换流程上我的建议是先做一次“试切换”。找一个业务低峰期把应用从旧库切到新库运行一小段时间观察日志、监控、慢查询确认无异常后再切回旧库。这个动作能提前暴露90%以上的配置问题。正式切换时只是把同样的事情再做一遍而已。5.2 回滚方案设计再充分的准备也必须有回滚方案。数据库迁移是一个高风险操作谁也不能保证一切顺利。我的经验是回滚方案要写在一页纸上而且要让执行切换的人能在一分钟内找到。最简单的回滚方案是原MySQL库从迁移开始就一直保留不做任何删除操作。如果切换后发现问题应用连接串改回MySQL数据层回退业务恢复。这个方案的前提是PG那边没有产生新的业务数据否则两边的数据就分叉了。如果PG侧已经跑了业务、写入了新数据回滚就复杂了。这时候要决定是丢弃PG里这段时间的新数据还是想办法同步回MySQL。丢弃新数据意味着这段时间的业务数据丢失很多业务无法接受。所以现在做迁移项目时我倾向于先让PG以只读或灰度方式运行等稳定期过了再放开写入。这样回滚时只需要切回MySQL不涉及数据分叉。还要强调一点MySQL侧在迁移期间不要停止备份。万一回滚需要用到MySQL确保它的数据副本是完整的。迁移过程中对MySQL只做读操作的话基本上不会影响它的数据安全但备份是你最后的底牌不能省。6. 常见问题与排查技巧实录迁移过程中遇到的问题千奇百怪但很多都是共性的。我把这些年遇到的典型问题整理成一张速查表方便大家按图索骥。现象可能原因解决办法迁移后中文乱码MySQL字符集不是utf8mb4或PG连接串未指定UTF8迁移前统一源库字符集PG连接参数加client_encodingUTF8时间数据少了8小时DATETIME转TIMESTAMPTZ时未处理时区明确PG的timezone配置连接串统一时区TINYINT(1)字段被ORM识别为Boolean类型映射时未处理TINYINT(1)迁移时显式CAST为BOOLEANGROUP BY SQL报错MySQL宽松模式代码未兼容PG严格模式修改SQL把非聚合列加入GROUP BY反引号报错SQL里用了MySQL专属反引号替换为PG的双引号或直接去掉分页数据错乱LIMIT offset, count语法不兼容改成LIMIT count OFFSET offsetON DUPLICATE KEY UPDATE报错PG语法不同改用ON CONFLICT (col) DO UPDATE自增主键冲突序列未重置到正确位置执行setval同步序列值连接串无法连接PG监听配置、pg_hba.conf或驱动版本问题检查listen_addresses、pg_hba.conf放行规则、更换驱动迁移后首次查询慢统计信息未更新执行ANALYZE刷新统计信息大表加载慢pgloader并行度不够或索引重建耗时调整workers参数先导数据后建索引实际项目里我踩过最惨的一个坑是在数据校验环节。当时只做了行数对比所有表行数都对得上就直接切换上线了。结果第二天运营反馈某张表的用户积分数据不对排查发现是MySQL的DECIMAL(10,2)字段里存了一个超出精度范围的值PG加载时自动做了四舍五入。行数没变但数值变了。后来我在校验脚本里加了一个字段级sum校验对关键数值字段做SUM()比对从那以后再没出过类似问题。还有一次是时区问题。MySQL的DATETIME不带时区信息原来的应用在存储时间时用代码在内存里做了时区转换表现没问题。迁移时我把DATETIME转换成了TIMESTAMPTZPG根据服务器的时区设置自动转换了时间结果应用层又做了一次时区偏移所有时间都变成了UTC16的效果。这个问题的排查过程特别痛苦因为单看PG里的数据是对的单看应用逻辑也是对的但合在一起就错了。最后通过打印SQL日志和数据库连接的timezone参数才定位到。最后说一个排查心法迁移出的问题很多时候不在数据库本身而是应用层对SQL方言的依赖。遇到诡异问题第一反应不是去翻PG文档而是先确认应用层有没有写了MySQL特定的SQL、有没有硬编码驱动配置、有没有在代码里拼了数据库特有的函数。这个思路能帮你在排查时少走很多弯路。结尾做了这么多迁移项目我最大的体会是从MySQL迁移到PostgreSQL技术上的类型转换和SQL改造只是表面工作真正决定成败的是前期的对象盘点和数据校验做得到不到位。很多项目迁完跑不起来并不是因为PG不好而是因为准备阶段偷了懒。如果你的项目也准备启动迁移我个人建议先在测试环境完整走两遍全流程。第一遍熟悉工具和流程记录所有报错和耗时第二遍处理掉之前的问题重点验证增量同步和切换步骤。两遍跑完你心里就有底了正式迁移时哪怕出点小状况也知道该往哪个方向查。别嫌这个流程费时间跟线上出事故的代价比起来这点成本真的不值一提。 从MySQL迁移到PostgreSQL：完整流程与踩坑实录
做了不少年数据库相关的工作MySQL和PostgreSQL都深度用过这几年陆陆续续接手了好几个从MySQL迁移到PostgreSQL的项目。这个主题最近确实很热有人是冲着PostgreSQL更强的查询能力去的有人是为了统一技术栈也有人是因为合规或者国产化要求必须换库。不管出于什么原因迁移这件事本身都不只是“把数据倒过去”那么简单里面涉及表结构转换、SQL语法差异、数据校验、停机窗口设计、回滚预案每一步都有坑。这篇文章我打算按照我自己的实操经验把从MySQL迁到PostgreSQL的完整流程拆开讲清楚。包括迁移前怎么评估、工具怎么选、表结构怎么转换、数据怎么同步、切库怎么切、出了问题怎么回滚以及我在实际项目中踩过的那些坑。内容会比较长但都是能直接落地的东西适合正准备做迁移评估的架构师、负责执行迁移的DBA以及被临时拉来干这个活的开发同学参考。1. 为什么值得从MySQL迁到PostgreSQL1.1 两个数据库的本质差异先说结论MySQL和PostgreSQL虽然都是关系型数据库但设计哲学差别很大。MySQL的设计初衷是“轻量、快速、简单”在互联网读多写少的场景下表现优秀生态成熟运维资料一堆。PostgreSQL则更像一个“什么都能干”的瑞士军刀功能全面SQL标准兼容度高擅长复杂查询和数据分析扩展能力极强。如果你只是存一些简单业务数据几十张表查询条件单一那MySQL完全够用甚至更省心。但一旦涉及复杂报表、多层子查询、窗口函数、递归查询、JSON数据结构化查询这些场景MySQL写起来很别扭优化器也不一定配合你。PostgreSQL在这些方面是另一个水平尤其是PG 12以后优化器能力和并行查询越来越强复杂SQL的响应速度往往能给你惊喜。两者的核心差异我用一个表格归纳下对比维度MySQLPostgreSQL事务隔离默认Repeatable Read实现方式为MVCC锁默认Read CommittedMVCC实现更成熟复杂查询优化器相对保守子查询性能不稳优化器强大支持CTE、窗口函数、递归JSON支持JSON类型功能有限JSONB类型支持索引和高效查询索引类型B-Tree为主8.0支持倒排索引B-Tree、GIN、GiST、BRIN、部分索引、表达式索引分区表8.0开始支持但运维成本高原生支持声明式分区10.0后很成熟扩展能力插件生态一般扩展机制强大PostGIS、pgvector等SQL标准兼容性一般方言多兼容性高SQL标准贴合度好许可证GPL/商用双许可PostgreSQL License宽松自由这里面最关键的一个点是“优化器”。MySQL在复杂查询上的表现说实话有点看运气同样的SQL换个数据分布执行计划可能就变了。PostgreSQL基于代价的优化器做得更精细统计信息更丰富复杂查询的稳定性明显更好。这也是很多团队从MySQL迁到PG的最直接原因。1.2 什么情况下该迁什么情况下不该迁我见过不少团队是看到别人迁了自己也想迁结果搞了几个月一地鸡毛。所以在动手之前先想清楚你到底为什么要迁。适合迁移的场景有几个。第一业务里复杂查询占比高报表需求多MySQL跑不动或者SQL写起来太痛苦。第二需要JSONB、全文检索、地理空间这类高级特性MySQL要么没有要么体验不佳。第三有合规或者国产化要求需要替换掉MySQLPostgreSQL是常见选择。第四团队要统一技术栈减少多数据库维护成本。不适合迁移的也明显。如果项目就是个简单的CRUD应用几十张表查询都很简单MySQL运行得好好的那迁移纯属给自己找事。尤其是团队里没人真正用过PostgreSQL的情况下迁移后SQL写法、索引设计、调优手段都要重新学隐性成本非常可观。还有一种情况要特别提醒如果核心诉求是“解决慢查询”先别急着迁移。先看是不是SQL本身写得烂、索引没建对、数据模型设计不合理。很多时候把这些基础问题解决了MySQL的性能还远没到瓶颈。为了一个建好索引就能解决的问题去迁移数据库代价太大了。2. 迁移前的准备别急着动手2.1 盘点要迁移的存量对象迁移的第一步不是装PostgreSQL而是把现有MySQL里的家底盘清楚。别嫌这步枯燥后面所有计划都建立在这个清单之上。我一般会先执行几条SQL把全貌摸出来。查看所有数据库和大小统计每个库的表数量找出大表再列出所有视图、存储过程、触发器、定时事件。这些信息决定迁移的复杂度和工作量。比如在MySQL里可以用这样一组查询来盘点-- 查看所有数据库及大小 SELECT table_schema, ROUND(SUM(data_length index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables GROUP BY table_schema; -- 查看每个库的表数量 SELECT table_schema, COUNT(*) AS table_count FROM information_schema.tables WHERE table_type BASE TABLE GROUP BY table_schema; -- 找出超过1GB的大表 SELECT table_schema, table_name, ROUND((data_length index_length) / 1024 / 1024 / 1024, 2) AS size_gb FROM information_schema.tables WHERE table_type BASE TABLE HAVING size_gb 1 ORDER BY size_gb DESC; -- 列出所有存储过程、函数、触发器 SELECT routine_name, routine_type FROM information_schema.routines; SELECT trigger_name, event_object_table FROM information_schema.triggers;这些SQL在迁移规划阶段价值很大。我见过有人迁移到一半发现有个300GB的大表没评估进去导致停机窗口严重超时。还有的发现业务里用了大量存储过程而MySQL的存储过程和PG的PL/pgSQL语法差异不小改造工作量被严重低估。除了数据库对象本身还要梳理应用层的SQL使用情况。这一步同样关键。如果应用是ORM框架比如MyBatis、JPA、Hibernate生成的SQL还好改改方言配置就能跑。但如果项目里有大量手写SQL尤其是复杂查询、动态拼接SQL那就要逐条检查兼容性。我通常会让开发团队跑一个静态扫描把项目代码里所有手写SQL提取出来和类型转换清单做比对提前标记出有风险的语句。2.2 迁移工具选型对比工具选得好迁移成功一半。市面上可以用的方案大致分为四类我按推荐程度排个序工具/方案适用场景优点缺点pgloader中小型库全量迁移开源免费支持自动类型转换、索引转换等对超大库同步速度一般mysqldump 手工转换数据结构简单、表量少可控性强不需要额外安装工具需要大量手工调整DDLNavicat等GUI工具快速小规模迁移操作直观点点鼠标就行复杂对象支持差类型映射粗糙Debezium 自研同步脚本大库、准实时迁移支持增量同步停机短部署复杂度高需要中间件运维我个人的经验是中小型项目首选pgloader它能把MySQL的建表语句自动转换为PostgreSQL格式大部分数据类型都能正确映射索引、外键也能一并处理。真遇到转换不了的特殊类型它会报错并告诉你原因方便手工修正。如果库特别大数据量在TB级别那pgloader在全量阶段会比较吃力通常的做法是先做一次全量初始化再用Debezium监听MySQL binlog做增量同步最后在停机窗口内完成最终切换。这个方案对运维能力要求高但能实现准不停服迁移。还有一种情况是项目里MySQL用了一些PG完全没有对应的功能比如某些存储引擎特性、自定义函数这些对象需要提前在PG里用别的方案重写。这些特殊依赖都应该在盘点阶段标记出来而不是等迁移时才发现。2.3 搭建PostgreSQL目标环境迁移前先把PG环境准备好版本选择上我通常会选当前稳定版PG 16或者更新的版本。Windows环境安装直接去官网下安装包即可安装过程中记得选择安装pgAdmin和Stack Builder后续管理方便很多。Linux环境用发行版的包管理器装也行但建议直接使用PostgreSQL官方提供的APT/YUM源版本更新而且不会有系统自带版本太旧的问题。装完PG后有几个参数我强烈建议在迁移前就调好。shared_buffers设置为核心内存的25%左右work_mem根据并发和内存大小调整maintenance_work_mem在迁移建索引时可以临时调大wal_level如果是后续要做增量同步就必须设置为replica或者logical。另外一定要确认字符集选择UTF8排序规则用合理的选项这个在初始化数据库时就要确定后面改起来非常麻烦。注意数据库字符集和排序规则是迁移中最容易忽略的一项。MySQL的utf8mb4对应PG的UTF8但如果是中文排序、大小写敏感这类需求需要在初始化数据库时规划好LC_COLLATE和LC_CTYPE否则建完库再想改就麻烦了。3. 迁移实操从表结构到数据再到业务逻辑3.1 表结构与数据类型映射这是整个迁移里工作量最大、最琐碎的部分。MySQL和PostgreSQL的数据类型虽然名字看着像但实际语义和长度定义有不少出入。我整理了一张常用的映射表可以直接照着用MySQL类型PostgreSQL类型说明TINYINTSMALLINTMySQL的TINYINT是1字节PG里最接近的是SMALLINTTINYINT(1)BOOLEAN如果业务把它当布尔值用建议直接转BOOLEANSMALLINT/INTSMALLINT/INTEGER长度修饰符省略PG不关心显示宽度BIGINTBIGINT无变化FLOAT/DOUBLEREAL/DOUBLE PRECISION注意浮点精度踩坑DECIMAL/NUMERICNUMERIC基本兼容CHAR/VARCHARCHAR/VARCHAR注意VARCHAR长度定义PG没有长度上限默认值TEXTTEXT无变化DATETIMETIMESTAMP对应不带时区的时间戳TIMESTAMPTIMESTAMPTZ强烈建议用带时区类型DATEDATE无变化TIMETIME无变化ENUMVARCHAR CHECK约束PG有原生ENUM但后续加值要ALTER TYPE不灵活SETTEXT CHECK约束没有直接对应按业务拆解JSONJSONBJSONB支持索引性能更好BLOB/LONGBLOBBYTEA二进制大对象LONGTEXTTEXT无变化这里面最坑的是TINYINT(1)。MySQL里很多开发者用它表示布尔值迁移到PG时如果直接转成SMALLINT应用层用0和1判断还好但如果是通过ORM映射成boolean字段的就会出问题。我在一个项目里遇到过一次迁完后某个接口突然报错排查半天发现就是ORM框架把TINYINT(1)映射为Boolean而PG那边是SMALLINT数据能查出来但类型转换失败。所以迁移前一定要搞清每个TINYINT(1)字段在业务里的真实含义。来看一个实际的DDL转换例子。MySQL的建表语句是这样的CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL, status TINYINT(1) NOT NULL DEFAULT 0 COMMENT 0-待支付 1-已支付, total_amount DECIMAL(10,2) NOT NULL, remark TEXT, extra JSON, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;转换成PostgreSQL的DDLCREATE TABLE orders ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, status BOOLEAN NOT NULL DEFAULT FALSE, total_amount NUMERIC(10,2) NOT NULL, remark TEXT, extra JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uk_order_no UNIQUE (order_no) ); CREATE INDEX idx_user_id ON orders (user_id); COMMENT ON TABLE orders IS 订单表;注意几个细节。MySQL的AUTO_INCREMENT在PG里用GENERATED ALWAYS AS IDENTITY替代这是PG 10开始支持的标准写法比用SERIAL更规范。MySQL的默认值CURRENT_TIMESTAMP对应PG的NOW()。ON UPDATE CURRENT_TIMESTAMP这个行为PG没有原生支持通常的做法是应用层更新时同时更新updated_at字段或者用触发器实现。3.2 约束、索引、自增主键的处理索引迁移这块很多人只关心主键和普通索引其实PG的索引能力远不止这些。如果你之前用MySQL只是为了主键查询和简单过滤那PG的B-Tree索引表现不差。但如果业务里需要全文检索、JSON字段查询、数组包含这类操作PG的GIN索引能带来质的提升这是迁移后的一个红利。不过索引迁移有个容易忽略的坑MySQL里外键约束往往没建因为InnoDB下建外键会影响写入性能很多团队干脆不建。PG里如果迁移时直接把外键约束补上迁移后写入变慢会很明显。我的建议是如果业务确实依赖外键保证数据完整性那就保留如果只是为了“规范化”而建实际应用层已经保证了完整性那就不要建保持和原来一致的性能表现。还有一个需要特别留意的MySQL的唯一索引允许NULL重复而PG默认也是这样。但如果你在MySQL里用了“允许一个字段多个NULL但非NULL唯一”的业务逻辑PG的行为和MySQL是一致的这个不会出问题。可如果业务把NULL当普通值处理那就要注意了最好在迁移时用NULLS NOT DISTINCT选项创建唯一约束PG 15开始支持这个特性。3.3 存储过程与触发器的改造MySQL存储过程和PostgreSQL的PL/pgSQL虽然都是过程化语言但语法差异很大。MySQL的存储过程用BEGIN...END包裹、用DELIMITER声明结束符变量用DECLARE定义、SELECT ... INTO赋值。PL/pgSQL则用$$作为函数体界定符赋值用:返回值用RETURN结构上更接近Oracle的PL/SQL。举一个典型的例子MySQL里一个简单的存储过程DELIMITER $$ CREATE PROCEDURE get_user_orders(IN p_user_id BIGINT) BEGIN SELECT id, order_no, total_amount FROM orders WHERE user_id p_user_id; END$$ DELIMITER ;对应的PostgreSQL函数CREATE OR REPLACE FUNCTION get_user_orders(p_user_id BIGINT) RETURNS TABLE(id INTEGER, order_no VARCHAR, total_amount NUMERIC) AS $$ BEGIN RETURN QUERY SELECT o.id, o.order_no, o.total_amount FROM orders o WHERE o.user_id p_user_id; END; $$ LANGUAGE plpgsql;这还算简单的。如果遇到复杂的存储过程里面使用了游标、动态SQL、异常处理改造的工作量可能会超出想象。所以我一直强调迁移评估阶段要对存储过程按复杂度打分提前预估改造工时。触发器的差异同样值得注意。MySQL的触发器直接用CREATE TRIGGERPG则要先写一个返回TRIGGER类型的函数再用CREATE TRIGGER绑定。逻辑没变但代码结构完全不同。另外PG的触发器函数里通过NEW和OLD行变量访问数据这点和MySQL相似不算难理解。3.4 应用层SQL语句的兼容性调整数据结构和存储过程改造完成后应用层的SQL兼容性调整是另一个大工程。很多应用把MySQL特有的语法用得很顺手到了PG下面这些SQL全部报错。下面这张表是我在项目中总结的高频差异点MySQL写法PostgreSQL写法说明fieldfieldMySQL用反引号PG用双引号LIMIT 10, 20LIMIT 20 OFFSET 10MySQL的偏移分页语法PG不支持GROUP BY宽松模式GROUP BY严格模式PG要求SELECT的列必须出现在GROUP BY中IFNULL(a, b)COALESCE(a, b)功能等价CONCAT(a, b, c)CONCAT(a, b, c)或a || b || c两者都支持但||在MySQL里是逻辑或NOW()NOW()或CURRENT_TIMESTAMP基本兼容INSERT ... ON DUPLICATE KEY UPDATEINSERT ... ON CONFLICT (col) DO UPDATE语义类似语法不同REPLACE INTOINSERT ... ON CONFLICT ... DO NOTHING/UPDATEREPLACE会先删后插容易丢失数据DATE_FORMAT()TO_CHAR()格式化函数差异大%通配符在LIKE中同样适用兼容UNSIGNED整数无对应PG不支持无符号需用CHECK约束模拟这里我重点说两个容易踩坑的地方。第一个是GROUP BY的严格模式。MySQL在ONLY_FULL_GROUP_BY默认关闭时允许SELECT中列出不在GROUP BY里的列而且不报错只是返回的值不确定。很多开发者习惯了这种写法业务代码里处处都是。迁移到PG后这些SQL全部会报错必须把每个非聚合列都加进GROUP BY或者改成聚合函数包裹。这个过程非常折腾我见过一个项目在迁移阶段改了几百条类似的SQL。第二个是INSERT ... ON DUPLICATE KEY UPDATE。很多团队用它实现在并发场景下的幂等写入MySQL里一行就能搞定。PG的等价写法是ON CONFLICT但要注意必须指定冲突的约束或列名。举个例子MySQL的写法INSERT INTO orders (order_no, user_id, total_amount) VALUES (NO10001, 1, 99.00) ON DUPLICATE KEY UPDATE user_id VALUES(user_id), total_amount VALUES(total_amount);PostgreSQL的写法INSERT INTO orders (order_no, user_id, total_amount) VALUES (NO10001, 1, 99.00) ON CONFLICT (order_no) DO UPDATE SET user_id EXCLUDED.user_id, total_amount EXCLUDED.total_amount;注意PG里通过EXCLUDED关键字引用准备插入但发生冲突的那行数据而不是MySQL的VALUES()函数。如果漏掉了ON CONFLICT后面的列名PG会报错让你明确冲突目标。4. 迁移数据全量同步与校验4.1 用pgloader做全量迁移工具层面我用的最多的是pgloader。这个工具专门用于从其他数据库迁移到PostgreSQL对MySQL的支持很成熟能自动做类型转换、关键字转义、索引转换还能并行加载。安装pgloader的方式macOS直接brew install pgloaderLinux下可以从源码编译Deployment版本容易找。Windows下稍微麻烦点我建议在WSL环境里跑或者直接放在Linux服务器上执行。一条最基础的pgloader迁移命令是这样pgloader mysql://user:password127.0.0.1:3306/source_db postgresql://user:password127.0.0.1:5432/target_db它会把整个库的所有表、索引、约束一次性迁移过去并在最后输出一份报告显示每张表的行数、错误数、耗时等。如果你的库比较简单这条命令就够用了。但实际项目中通常需要更精细的控制可以使用.load文件来定义迁移规则LOAD DATABASE FROM mysql://user:password127.0.0.1:3306/source_db INTO postgresql://user:password127.0.0.1:5432/target_db WITH include drop, create tables, create indexes, reset sequences, workers 8, concurrency 4, batch rows 1000 SET PostgreSQL PARAMETERS maintenance_work_mem 256MB, work_mem 16MB CAST type datetime to timestamptz using zero-dates-to-null, type tinyint(1) to boolean when maybe;这里说几个关键参数。create tables让pgloader自动生成目标表结构reset sequences负责重建自增序列workers和concurrency控制并行度数据量大的时候适当调高能显著提升速度。CAST那段是自定义类型转换规则比如把TINYINT(1)转成BOOLEAN把无效的零日期转成NULL这些规则在实际迁移中非常实用因为很多老系统的数据质量并不理想。pgloader跑完后我会先看报告里的错误数。如果错误比较多一般是某个字段的数据格式无法转换比如MySQL的DATETIME里存了0000-00-00这种非法值PG会拒绝导入。处理方式是在CAST规则里指定zero-dates-to-null或者先在MySQL侧把这部分脏数据清理掉。注意pgloader在迁移超大的表时单表加载速度和索引重建速度可能感人。我遇到过一张5亿行的日志表全量加载加索引重建跑了将近10个小时。如果业务有这类大表一定要提前压测给停机窗口留足余量。4.2 数据校验怎么做才靠谱数据迁移完不代表万事大吉。校验是很多人会跳过或者敷衍的一步但我每次都会做得很细。原因很简单数据是公司资产哪怕丢了几行业务上线后出问题就是事故级别的故障。我常用的校验方案分三层。第一层是行数校验最简单但最容易发现问题。分别对MySQL和PG执行COUNT(*)对比每张表的行数是否一致。表多的时候可以用脚本批量比对MySQL和PG都有information_schema写个Python或Shell脚本把每张表的行数拉出来做diff几分钟就能完成。第二层是抽样数据比对。每张表按主键范围或者随机抽取一定比例的行逐字段对比内容。这个可以用工具比如从MySQL导出CSV再在PG里导入临时表对比。数据量大的表做全量比对不现实抽样加明细抽查是性价比最高的方式。第三层是业务探针。这个最灵活也最能发现“数据没丢但业务不对”的问题。挑几个核心业务场景写一些关键查询在MySQL和PG上分别执行对比返回结果。比如查某个用户的订单总数和总金额、查某天的销售汇总、查订单状态分布。这一步能发现字段类型映射导致的内容变化比如浮点精度、时区、布尔值存储差异这些靠行数校验发现不了。数据校验通过后还有一个很容易忽略的步骤对PG的表执行ANALYZE刷新统计信息。PG的查询优化器严重依赖统计信息如果刚迁移完就直接上线跑业务很多SQL会因为统计信息缺失或过旧走错执行计划性能表现会很差。迁移完第一时间对所有表跑一遍ANALYZE这个动作虽然简单但对后续线上性能有直接帮助。-- 对全库所有表执行统计信息收集 ANALYZE;5. 停机切换与回滚预案5.1 切换前检查清单数据同步完成、校验通过之后真正切库的前一刻一定要有一份详尽的检查清单。我见过太多项目在切换当天出问题不是数据库本身不行而是准备工作有遗漏。下面是我每次切换前必查的清单应用层所有连接串是否已改为指向PG的IP和端口能改配置文件的改配置写死在代码里的要提前发版数据库账号权限是否最小化配置完成原来MySQL里的账号对应的PG账号和权限是否一致自增序列是否已同步到正确位置否则插入新数据时主键冲突定时任务、消息队列的持久化表是否已经迁移这部分经常被遗漏监控系统和告警规则是否切换了数据源如果监控还指向旧库切换后就是两眼一抹黑备份策略是否已生效PG的备份机制和MySQL不一样建议切换前先做一次完整备份验证应用服务器的连接池配置是否兼容PG驱动比如连接池的初始化SQL、验证语句是否还兼容还有一个细节容易被忽略原来MySQL的连接串和PG的不一样应用的数据库驱动也要换掉。Java应用的MySQL驱动和PostgreSQL驱动是不同的JAR包切换后要确保驱动版本正确、依赖无冲突。Python应用的pymysql和psycopg2也是完全不同的库。这些前置条件如果不准备好切换时应用会直接连不上数据库。切换流程上我的建议是先做一次“试切换”。找一个业务低峰期把应用从旧库切到新库运行一小段时间观察日志、监控、慢查询确认无异常后再切回旧库。这个动作能提前暴露90%以上的配置问题。正式切换时只是把同样的事情再做一遍而已。5.2 回滚方案设计再充分的准备也必须有回滚方案。数据库迁移是一个高风险操作谁也不能保证一切顺利。我的经验是回滚方案要写在一页纸上而且要让执行切换的人能在一分钟内找到。最简单的回滚方案是原MySQL库从迁移开始就一直保留不做任何删除操作。如果切换后发现问题应用连接串改回MySQL数据层回退业务恢复。这个方案的前提是PG那边没有产生新的业务数据否则两边的数据就分叉了。如果PG侧已经跑了业务、写入了新数据回滚就复杂了。这时候要决定是丢弃PG里这段时间的新数据还是想办法同步回MySQL。丢弃新数据意味着这段时间的业务数据丢失很多业务无法接受。所以现在做迁移项目时我倾向于先让PG以只读或灰度方式运行等稳定期过了再放开写入。这样回滚时只需要切回MySQL不涉及数据分叉。还要强调一点MySQL侧在迁移期间不要停止备份。万一回滚需要用到MySQL确保它的数据副本是完整的。迁移过程中对MySQL只做读操作的话基本上不会影响它的数据安全但备份是你最后的底牌不能省。6. 常见问题与排查技巧实录迁移过程中遇到的问题千奇百怪但很多都是共性的。我把这些年遇到的典型问题整理成一张速查表方便大家按图索骥。现象可能原因解决办法迁移后中文乱码MySQL字符集不是utf8mb4或PG连接串未指定UTF8迁移前统一源库字符集PG连接参数加client_encodingUTF8时间数据少了8小时DATETIME转TIMESTAMPTZ时未处理时区明确PG的timezone配置连接串统一时区TINYINT(1)字段被ORM识别为Boolean类型映射时未处理TINYINT(1)迁移时显式CAST为BOOLEANGROUP BY SQL报错MySQL宽松模式代码未兼容PG严格模式修改SQL把非聚合列加入GROUP BY反引号报错SQL里用了MySQL专属反引号替换为PG的双引号或直接去掉分页数据错乱LIMIT offset, count语法不兼容改成LIMIT count OFFSET offsetON DUPLICATE KEY UPDATE报错PG语法不同改用ON CONFLICT (col) DO UPDATE自增主键冲突序列未重置到正确位置执行setval同步序列值连接串无法连接PG监听配置、pg_hba.conf或驱动版本问题检查listen_addresses、pg_hba.conf放行规则、更换驱动迁移后首次查询慢统计信息未更新执行ANALYZE刷新统计信息大表加载慢pgloader并行度不够或索引重建耗时调整workers参数先导数据后建索引实际项目里我踩过最惨的一个坑是在数据校验环节。当时只做了行数对比所有表行数都对得上就直接切换上线了。结果第二天运营反馈某张表的用户积分数据不对排查发现是MySQL的DECIMAL(10,2)字段里存了一个超出精度范围的值PG加载时自动做了四舍五入。行数没变但数值变了。后来我在校验脚本里加了一个字段级sum校验对关键数值字段做SUM()比对从那以后再没出过类似问题。还有一次是时区问题。MySQL的DATETIME不带时区信息原来的应用在存储时间时用代码在内存里做了时区转换表现没问题。迁移时我把DATETIME转换成了TIMESTAMPTZPG根据服务器的时区设置自动转换了时间结果应用层又做了一次时区偏移所有时间都变成了UTC16的效果。这个问题的排查过程特别痛苦因为单看PG里的数据是对的单看应用逻辑也是对的但合在一起就错了。最后通过打印SQL日志和数据库连接的timezone参数才定位到。最后说一个排查心法迁移出的问题很多时候不在数据库本身而是应用层对SQL方言的依赖。遇到诡异问题第一反应不是去翻PG文档而是先确认应用层有没有写了MySQL特定的SQL、有没有硬编码驱动配置、有没有在代码里拼了数据库特有的函数。这个思路能帮你在排查时少走很多弯路。结尾做了这么多迁移项目我最大的体会是从MySQL迁移到PostgreSQL技术上的类型转换和SQL改造只是表面工作真正决定成败的是前期的对象盘点和数据校验做得到不到位。很多项目迁完跑不起来并不是因为PG不好而是因为准备阶段偷了懒。如果你的项目也准备启动迁移我个人建议先在测试环境完整走两遍全流程。第一遍熟悉工具和流程记录所有报错和耗时第二遍处理掉之前的问题重点验证增量同步和切换步骤。两遍跑完你心里就有底了正式迁移时哪怕出点小状况也知道该往哪个方向查。别嫌这个流程费时间跟线上出事故的代价比起来这点成本真的不值一提。