企业官网建设流程全解析

这两年，“从MySQL迁到PostgreSQL”几乎成了后端技术群里的高频话题。有人被JSON查询的语法绕得头疼，有人被大表ALTER TABLE卡到凌晨，也有人单纯看中了PG的GIN索引和全文检索能力，想把手上的搜索场景和业务数据库合并在一起。我最近完整带过一遍这种迁移项目，从选型评估、全量导出、增量追平、应用改SQL到最后灰度切换，踩了不止一次坑，也沉淀了一套可以直接拿去用的流程。

这篇文章就是写给准备动手或者正在搬库的同学看的。我会先说清楚什么样的业务适合迁移、什么样的业务别折腾，再给出完整的数据类型映射、工具操作步骤、SQL兼容性改造清单，最后附上我在实战中遇到的隐蔽坑位和排查方法。内容偏落地，不聊虚的，尽量让照着做的读者少走弯路。

1. 迁移前先算账：为什么要从MySQL迁到PostgreSQL

1.1 哪些场景真正适合做这次迁移

不是所有业务都适合从MySQL搬到PostgreSQL。我在评估阶段见过不少因为“PG看起来更先进”就想迁的项目，实际动手后才发现收益远小于成本。根据我的经验，适合迁移的场景大概有这么几类：

第一类是查询复杂度高、需要大量窗口函数、递归CTE、数组操作的业务。MySQL 8.0虽然加了窗口函数，但整体能力和PG比还是有差距，尤其递归查询和中间结果集的处理，PG明显更顺手。第二类是JSON或者全文检索需求重的场景。MySQL的JSON类型只能按文本解析，索引效率有限；PG的JSONB带GIN索引，配合表达式索引和全文检索tsvector，直接能省掉一套独立的Elasticsearch小集群。第三类是需要强数据约束的业务，比如银行转账、订单对账，PG的外键、CHECK约束、排它约束都比MySQL严格得多，数据一致性天然有兜底。

反过来说，有几类业务建议谨慎。比如你已经有成熟的分库分表中间件，几千个MySQL实例都在稳定运行，迁移成本会非常高；比如团队对PG完全不熟，出了问题没人能接；再比如业务重度依赖MySQL的存储引擎特性，像全文索引的Boolean模式或者某些云数据库特有的内核优化，这些在PG里没有等价物。迁移不是技术炫技，算不清ROI就别动手。

1.2 迁移前必须摸透的两边差异

MySQL和PostgreSQL虽然都是关系型数据库，但“性格”差异相当大。我习惯用一句话概括：MySQL像个听话的执行者，你给什么指令它都尽量照做；PG像个严谨的检查官，不符合规范直接报错。这种差异体现在下面几个关键维度上。

第一是数据类型体系。MySQL对类型的容忍度很高，字符串和数字之间经常发生隐式转换；PG对类型极其敏感，类型不匹配直接抛错，绝对不肯“猜”你的意图。映射表结构时，TINYINT、ENUM、SET这些MySQL专用类型在PG里没有直接对应项，必须手工转换。

第二是DDL行为。MySQL在执行ALTER TABLE时虽然8.0版本已经优化成快速加列，但大多数DDL仍然隐式提交，一旦执行就回不去；PG支持事务性DDL，整个表结构变更可以和DML在同一事务里，出错随时回滚。这意味着迁移中改表结构的操作在PG里更安全，但也意味着应用代码里的“SQL模式”思维要跟着调整。

第三是并发控制。MySQL的InnoDB用MVCC加锁机制，默认隔离级别是REPEATABLE READ；PG也用MVCC，但默认隔离级别是READ COMMITTED，而且PG的每一条更新都会生成新的行版本，依赖VACUUM清理旧数据。迁移之后，如果还是按MySQL的习惯长时间开事务，PG这边会出现表膨胀和性能下降。

第四是标识符处理。MySQL里表名在Linux上区分大小写，列名不区分；PG会把没有加引号的标识符自动转成小写，所以原来MySQL里的UserTable迁过去会变成usertable，要么迁移时改代码，要么建表时统一加双引号。这个细节让我在第一次迁移时吃了大亏，后面会细说。

这些差异决定了迁移不只是“倒数据”，而是一次系统的应用改造。提前把差异清单拉出来，后面每一步才不会被意外绊住。

2. 三种迁移路线的选型与取舍

2.1 全量离线迁移：适合可停服的业务

如果业务允许在凌晨关闭写入口，哪怕只是停一两个小时，最稳妥的方式就是全量离线迁移。流程很朴素：停服务、导出MySQL数据、导入PG、校验、切换DNS或配置中心、恢复服务。

我推荐这种方案给中小型系统，原因很简单：链路最短，中间没有增量同步的复杂性，数据一致性最容易验证，出了问题也最容易回滚。缺点同样明显，停机时间受数据量影响，几GB的数据还好，几百GB甚至上TB，单靠导出导入根本跑不完，凌晨那几个小时根本不够用。

实操时通常组合使用两种工具：一种是pgloader，它直接连接MySQL和PG，自动把表结构、数据、索引、序列都搬过去，一条命令搞定绝大多数工作；另一种是先用mysqldump导出SQL文件，再手工改写建表语句和插入语句，最后用psql灌入PG。前者省事，后者适合结构复杂、需要精细控制转换规则的场景。

2.2 增量同步方案：不停机的平滑迁移

流量比较大的业务不可能为了迁移专门停机，这时候需要增量同步方案。核心思路是：先把MySQL里的存量数据全量复制到PG，同时开启MySQL的Binlog监听，把这段时间内新增的修改持续同步到PG，直到两边数据完全追平，最后再进行切换。

实现增量同步的常见手段是使用CDC（Change Data Capture）工具。开源生态里有很多优秀方案，原理都差不多：把自己伪装成MySQL的从库，解析Binlog中的INSERT、UPDATE、DELETE事件，再转换成PG的SQL执行。配置Binlog时需要注意三点：Binlog格式必须设置成ROW，否则拿不到完整的前后镜像；server_id不能和现有从库冲突；必须开启gtid_mode以便处理位点续传。

这套方案的复杂度明显高于全量迁移，因为要处理DDL过滤、主键变更、大事务拆分、网络抖动断点续传等问题。我的建议是：如果团队没有成熟的运维平台，尽量借助商业或云厂商提供的同步服务，至少在断点管理、延迟监控上能省很多力气；自己从零搭一套CDC管道，光排查“为什么某张表少了几条数据”就够折腾一周。

2.3 双写切换策略：最后一公里怎么走

无论是全量还是增量，最终切换那一刻都需要谨慎设计。我习惯把切换分成三步走：第一步把读流量切到PG，观察一段时间，确认查询性能、数据正确性没问题；第二步在应用里同时写MySQL和PG，持续校验两边数据是否一致；第三步停掉MySQL写入，彻底定稿。

这里的关键是“双写窗口”不要拖太长。双写意味着应用事务里同时操作两个数据库，任何一边失败都会造成数据不一致，而且两边网络延迟不同，整体响应时间必然增加。我见过有团队为了保险，双写了一周，结果每天都有一堆对不上的订单，排查到崩溃。我的建议是双写控制在24小时内，配合凌晨低峰期完成切换，只要全量校验通过，剩下的一致性交给短窗口内的补偿脚本解决。

3. 手把手实操：从建库建表到数据灌入

3.1 环境准备与参数预设

动手迁移之前，先把PG环境调好，别一上来就默认配置。版本选择上，建议直接上最新的稳定版本，老旧版本的生命周期和性能都有短板。安装时字符集一定要选UTF8，排序规则en_US.UTF-8或者C.UTF-8都可以，但千万别默认成SQL_ASCII，否则中文排序和字符串比较会出现诡异问题。

几个核心参数我会提前改掉。shared_buffers设置为物理内存的25%左右，这是PG共享缓存的核心；maintenance_work_mem至少调到1GB以上，因为迁移时要重建大量索引；wal_level设置为logical，否则后面想开逻辑复制做增量会后悔；max_wal_senders和max_replication_slots各设成10左右，为可能的同步链路留出余量。如果是全量离线迁移，fsync可以先临时关掉加速导入，但导入完成后必须立刻改回on，否则异常断电会造成数据损坏。

连接层面建议在前面加一层pgbouncer连接池。PG和MySQL不同，每个连接是一个独立进程，几百个应用连接直接把数据库拖垮。加了连接池之后，数据库端只需要维护几十个真实连接，应用端随便开连接都不怕。

3.2 表结构与数据类型的映射规则

数据迁移中最容易出错的就是类型映射。我在迁移时整理了一张对照表，基本覆盖了业务常见的类型，建议直接保存参考。

有两处最容易踩坑。第一是MySQL的TIMESTAMP类型会自动把时间从会话时区转换成UTC存储再转回来，而PG的TIMESTAMP就是纯粹的字面时间，不带时区概念。如果原来的业务时间全部依赖MySQL的时区自动转换，迁到PG后必须统一改成TIMESTAMPTZ，否则查询结果的“小时数”会平白无故差8个小时。第二是ENUM和SET，PG确实可以创建自定义枚举类型，但业务中频繁增删枚举值会导致表重写，远不如直接用TEXT + CHECK约束灵活，我最后都推荐这种方式。

3.3 使用pgloader快速完成全量导入

pgloader是我最喜欢的迁移工具，一条命令不仅能搬数据，还能生成表结构、索引、外键和序列。它可以在官方源里直接安装，安装后准备一个配置文件，语法类似下面这样：

LOAD DATABASE FROM mysql://mig_user:pass@mysql_host:3306/mysql_db INTO postgresql://pg_user:pass@pg_host:5432/pg_db WITH include drop, create tables, create indexes, reset sequences, workers = 8, concurrency = 1, multiple readers per thread, batch rows = 5000, batch size = 100 MB SET PostgreSQL PARAMETERS maintenance_work_mem = '1GB', work_mem = '128MB' CAST type datetime to timestamptz drop typemod keep default;

配置文件里的重点在WITH子句。include drop会先清理PG里可能存在的同名表，create tables自动建表，create indexes迁移索引，reset sequences自动把自增序列的起点设置成当前表的MAX(id)+1，这一步特别关键，否则导入后插入新数据会直接主键冲突。CAST type datetime to timestamptz表示把MySQL的DATETIME转成带时区的TIMESTAMPTZ，转移时区语义。

执行命令很简单：

pgloader migration.load

看到Finished字样并且错误计数为0，全量导入就完成了。如果遇到个别表导入失败，pgloader会生成详细的日志，定位后再单独补导那一两张表，不用整体重跑。

3.4 自增主键、索引与序列的重建

全量导入完成不代表万事大吉。PG里用SERIAL或者GENERATED AS IDENTITY创建的字段，序列的初始值不会因为数据导入而自动更新。比如MySQL里最后一条记录的id是100000，PG里的序列可能还停在1，下一次插入新记录就会报主键冲突。pgloader的reset sequences参数会自动处理，但如果你是用mysqldump手工导入，就必须手动执行：

SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT MAX(id) FROM orders));

另外，PG的索引名有63个字符长度限制，超过后会静默截断。MySQL里有些自动生成的索引名非常长，迁移后可能出现两个索引被截断成同一个名字的问题。遇到这种情况需要在应用层手工重命名索引，虽然不影响使用，但后续维护会非常混乱。

索引也不是“照搬就行”。原MySQL里的联合索引顺序、覆盖索引设计不一定适配PG的查询规划器。我建议全量迁移后跑一遍核心业务的慢查询日志，对照PG的EXPLAIN ANALYZE结果重新设计索引，尤其是把MySQL的字符串前缀索引改成PG的expression index或text_pattern_ops索引，效果会更好。

4. SQL兼容性与应用层改造清单

4.1 高频SQL语法差异与改写

数据搬完了，应用却不改等于白干。SQL方言差异是迁移过程中最磨人的部分，我把实际项目里遇到的高频场景整理成了对照表：

最关键的是ON DUPLICATE KEY UPDATE和INSERT IGNORE这两类写入逻辑，在互联网业务里太常见了。PG统一收敛成ON CONFLICT语法，但比MySQL更强：可以指定冲突在哪个唯一键上，还可以在DO UPDATE里排除某几列不更新。改造时建议抽公共函数封装，避免到处散落SQL。

4.2 函数、运算符与存储过程的差异

MySQL的存储过程和PG的PL/pgSQL差别非常大。MySQL的存储过程更接近“批处理脚本”，PG的则更像一个正经的编程语言，支持变量声明、异常捕获、游标、动态SQL。如果原来的业务逻辑大量写在存储过程里，这部分没法自动转换，只能手工用PL/pgSQL重写，工作量要提前评估。好消息是现在很多新应用都彻底放弃了存储过程，全部用ORM或业务代码直连，这类项目迁移起来轻松很多。

运算符方面，PG的||既是字符串拼接也是数组拼接，MySQL里||默认是逻辑或（不启用PIPES_AS_CONCAT的话），这个语义差异容易在迁移后产生逻辑错误。另一个经典坑是除法和取模，MySQL里5 / 2直接返回2.5000，PG里整数相除会先转成数值类型再除，结果一致，但配合取整函数时行为可能有差异，测试时多留个心眼。

函数层面，日期函数是重灾区。MySQL的DATE_FORMAT、DATE_ADD、DATEDIFF在PG里有不同的等价实现，尤其时区相关的CONVERT_TZ，PG没有直接对应函数，必须结合AT TIME ZONE语法处理。建议在应用层定义一个时间工具类，统一封装日期格式化，这样改造的时候只动一个文件。

4.3 ORM与连接层的适配思路

大部分ORM框架支持多数据库方言，理论上切换数据库只需要改连接串，但实际操作时会遇到几个预料之外的坑。

第一个坑是字段类型映射。很多ORM在生成实体类时把MySQL的datetime映射成LocalDateTime，到PG里如果字段类型是timestamptz，带上时区信息后与代码里的无时区类型可能会产生序列化问题。我的建议是实体类里统一用Instant或者OffsetDateTime，接收数据库返回时让驱动自动处理时区。第二个坑是分页和乐观锁。MySQL的LIMIT分页写法在PG里依然适用，但PG的分页在深分页场景性能衰减比MySQL更明显，建议趁迁移的机会把业务里的深分页改成游标分页或键集分页。乐观锁方面，PG对UPDATE ... RETURNING语法的支持非常完善，可以利用这个特性做原子性的状态流转，比MySQL的“先查后改”更可靠。

连接层改造也不复杂。驱动类名从com.mysql.cj.jdbc.Driver换成PG的驱动，连接串前缀从jdbc:mysql://换成jdbc:postgresql://，把MySQL的?useSSL=true&serverTimezone=UTC参数改成PG的?stringtype=unspecified（这个参数能减少很多类型推断报错）。如果用了连接池组件，像HikariCP这类本来就兼容多数据库的，基本改个配置就好。

5. 数据校验、对比与回滚预案

5.1 迁移后如何系统化验证数据一致

数据灌进去不等于迁移成功，校验环节必须严格。我习惯分三层做校验，逐层加码。

第一层是基础数量校验。每张表对比COUNT(*)，这个量级在千万级表上会跑一会儿，可以并行跑或者抽样。第二层是聚合值校验。对关键数值字段分别对比SUM、MAX、MIN，能在不扫描全表的情况下发现问题。但SUM只适用于小数和整数，浮点数会有精度差异，所以碰到DECIMAL字段要单独比对精度，PG的NUMERIC和MySQL的DECIMAL实现不同，存量数据可能有细微差别。

第三层是内容级校验。最稳妥的方案是导出抽样行的MD5指纹做比对。例如对每张表随机抽取几百行，拼接所有字段生成哈希，然后对比两边的哈希值。实际操作时注意字段顺序、NULL值的拼接方式要保持一致，否则拿到不同的哈希也没法定位问题。对于关键业务表，我甚至会写一个对账脚本，在迁移后的几天内持续跑，对比MySQL和PG之间每个主键的字段差异，确保没有漏网之鱼。

5.2 回滚方案与灰度切换设计

回滚预案必须在开始迁移之前写好，而不是切换失败后才去想。我的标准做法是：MySQL旧库保留至少一周，不删除不回收，所有迁移期间产生的额外存储空间都算到项目成本里。同时把旧库设置成只读，防止程序的补偿任务偷偷往里写数据。

切换过程要留好“退出按钮”。具体来说：

1. 先在低峰期把读流量灰度切到PG，观察半小时，主要看慢查询和错误日志。
2. 确认读流量稳定后，停掉MySQL的写流量，应用切换到PG写入，同时启动一个反向校验任务，对比最后一刻的增量数据。
3. 开一个短时间的双写窗口，把MySQL侧的新增数据同步回PG（或反向），做最后核对。
4. 确认无误后回收MySQL写权限，宣告切换完成。

如果中途发现PG侧有严重问题，比如特定查询性能无法接受、数据校验找不到原因，要果断回滚。回滚不是“把流量切回去”这么简单，还要把双写期间PG产生的新数据反向同步回MySQL，否则切换期间的用户数据就丢了。这也是为什么我不建议双写窗口超过24小时，窗口越长，回滚成本越高。

6. 实战避坑：我踩过的那些坑

6.1 字符集与时区导致的隐性错误

第一次迁移时，我犯了一个特别基础的错误：连接PG时没有显式指定客户端字符集。MySQL那边连接串默认是utf8mb4，PG这边的客户端编码如果不设置，会用数据库默认编码。当时数据库是SQL_ASCII，导入中文数据后乱码一片。后来我把库和连接串都统一成UTF8，问题才彻底消失。教训是：迁移前必须核对三处编码，数据库字符集、连接串编码、应用请求编码，三处不一致就会出诡异问题。

时区问题同样隐蔽。MySQL的TIMESTAMP类型会自动把“会话时区的时间”转成UTC再转回来，相当于存储层做了时区管理。PG的TIMESTAMP是纯字面量，TIMESTAMPTZ才会真正记录时区。迁移时如果直接把MySQL的TIMESTAMP映射成PG的TIMESTAMP，查询结果看着一样，但应用在计算“今天是第几天”这类逻辑时会平白无故差8小时。最稳妥的方案是全库统一使用TIMESTAMPTZ，应用侧全部用UTC时间传输，只在展示层转换时区。

6.2 大小写敏感与标识符陷阱

PG对未加引号的标识符一律转成小写，这是最容易让MySQL老手翻车的点。我们当时有张表叫OrderDetail，在MySQL里大小写混用完全正常，迁移后PG里出现的是orderdetail，应用里的SQL映射却一直找不到表。

排查了好几个小时才意识到，PG要求如果坚持用大写或混合大小写的表名，必须每次都加双引号，而且加了双引号之后大小写就变成敏感的了。这个问题只能在应用层统一解决：要么把所有表名改成小写，代码里同步改；要么规范建表，全部加双引号。我更推荐前者，因为小写命名在PG生态里是默认习惯，后续维护不会时不时踩雷。

6.3 事务隔离级别与锁行为差异

MySQL默认隔离级别是REPEATABLE READ，PG默认是READ COMMITTED。如果应用原本依赖“同一个事务内多次查询结果一致”这个特性，迁到PG后必须显式把隔离级别调到REPEATABLE READ。否则高并发场景下会出现两次查询结果不一致，排查起来非常像灵异事件。

另外，PG在UPDATE同一行时如果发生冲突，会直接抛出could not serialize access due to concurrent update异常，而MySQL通常会在锁等待后继续执行或者超时。这就导致原本在MySQL下能正常跑的高并发更新逻辑，在PG下突然开始报错。解决办法是应用层增加SQL执行失败后的重试机制，利用PG的乐观并发控制特点，重试后通常能成功。

6.4 迁移后的性能调优要点

迁移完成后，别急着把MySQL的参数习惯带过来。PG的默认配置偏保守，需要针对性调整。我遇到过最典型的性能问题是work_mem设置过小，导致大量排序和哈希操作落到磁盘上，一个本来几十毫秒的查询膨胀到几秒钟。这个参数不是越大越好，因为它是按每个连接分配的，几百个连接同时排序会把内存撑爆。建议从64MB开始试，观察EXPLAIN ANALYZE里的排序策略，逐步调整。

另一个需要重点监控的是表膨胀。PG的更新机制是“写新版本、标旧版本”，如果业务更新频繁且没有及时VACUUM，表占用的磁盘空间会不断增长，查询性能随之劣化。迁移后建议配置好autovacuum的参数，把autovacuum_vacuum_scale_factor调低到0.05左右，并且监控最活跃的几张表，必要时手动VACUUM。很多从MySQL迁过来的团队一开始完全意识不到这个区别，等发现磁盘报警才去补课。

最后再分享一个我个人的习惯：迁移完成后，不要立刻删除旧库的备份和监控。把两边的关键指标，比如连接数、慢查询数、磁盘增长速率、最热表的尺寸变化，放到同一个监控大盘里对比一周。性能稳定之后，再安排旧库的清理计划。数据库迁移不是“搬数据”，而是“搬运行体系”，把运行节奏也一起搬过去，才算真正迁移完成。