从MySQL到PostgreSQL迁移实战：5步搞定，数据0丢失的详细指南
引言
2023年双11大促期间，某电商平台的MySQL数据库因“高并发下锁冲突”导致订单接口频繁超时，而同期切换到PostgreSQL的竞品，凭借“多版本并发控制（MVCC）”轻松扛住20万QPS。这并非个例——越来越多企业因功能扩展需求（如JSONB、GIS）、高并发稳定性或开源生态适配，选择从MySQL迁移到PostgreSQL。
本文将通过某“电商订单系统”的真实迁移案例，带你掌握从数据备份→类型转换→应用适配→验证测试的全流程，确保迁移后数据0丢失、业务0中断。
一、为什么选择迁移？MySQL vs PostgreSQL的核心差异
迁移前需明确动机，以下是常见驱动因素：
| 维度 | MySQL | PostgreSQL | 迁移必要性 |
|---|---|---|---|
| 数据类型支持 | 基础类型（VARCHAR、INT） | 支持JSONB、ARRAY、HSTORE、GIS | 需存储JSON日志、地理信息时，PostgreSQL无需额外开发 |
| 并发控制 | 行锁（InnoDB），锁冲突概率高 | MVCC（无读写锁冲突） | 高并发场景（如订单支付）下，PostgreSQL性能更稳定 |
| 函数与扩展 | 内置函数有限 | 支持自定义函数、存储过程、插件 | 需实现复杂聚合（如STRING_AGG）或使用PostGIS时，MySQL需自研 |
| 事务特性 | 支持ACID，但DDL操作会锁表 | DDL操作支持事务（可回滚） | 架构升级时，PostgreSQL允许“试错→回滚”，降低生产风险 |
| 开源生态 | Oracle主导，社区版功能受限 | 完全开源，社区驱动 | 规避商业授权风险（如MySQL企业版收费），适配云原生（K8s、Docker）更友好 |
案例背景：某电商企业的订单系统原用MySQL 5.7，随着业务增长出现：
- 订单日志表（JSON格式）查询慢（需拆分为字段存储）；
- 大促期间“下单→支付”流程因行锁导致10%的超时；
- 需对接GIS地图服务（MySQL无原生支持）。
因此选择迁移至PostgreSQL 15。
二、迁移前的准备：评估与方案选型
2.1 环境评估（关键步骤！）
- 数据量评估：订单表2亿条（1.2TB），用户表5000万条（300GB）；
- 业务停机窗口：允许4小时停机（需选择逻辑迁移）；
- 应用适配成本：Java应用使用Spring Data JPA，需修改JDBC驱动、方言配置；
- 兼容性检查：
- MySQL的
AUTO_INCREMENT自增主键 → PostgreSQL的SERIAL（或IDENTITY）； - MySQL的
ENUM类型 → PostgreSQL的ENUM类型（需手动创建）或VARCHAR； - MySQL的
GROUP_CONCAT函数 → PostgreSQL的STRING_AGG(字段, 分隔符)； - 存储引擎：MySQL的InnoDB → PostgreSQL无存储引擎概念（统一管理）。
- MySQL的
2.2 迁移方案选择
| 方案 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 逻辑迁移 | 小数据量（<1TB）、允许停机 | 工具成熟（如pgloader），可控性高 | 停机时间较长（依赖导出/导入速度） |
| 物理迁移 | 大数据量（>1TB）、低停机要求 | 迁移速度快（直接复制数据文件） | 需同构环境（OS、数据库版本），风险高 |
| 实时迁移 | 零停机（如生产环境） | 业务无感知 | 架构复杂（需双写+数据校验） |
本案例选择逻辑迁移（数据量1.5TB，允许4小时停机），工具选用pgloader（支持MySQL到PostgreSQL的自动化迁移）。
三、迁移实战：5步完成数据与业务切换
步骤1：MySQL数据备份与预处理
1.1 全量数据备份（停机前）
# 使用mysqldump导出结构+数据（排除外键约束，避免导入时阻塞） mysqldump \--host=192.168.1.10 \--user=root \--password=*** \--database=order_db \ --single-transaction \# 事务内导出（保证一致性） --skip-add-locks \# 不添加表锁（适合InnoDB） --set-gtid-purged=OFF \# 关闭GTID（PostgreSQL不支持） --default-character-set=utf8mb4 \ --result-file=order_db_dump.sql 1.2 预处理SQL脚本（关键！）
MySQL与PostgreSQL的语法差异需提前修正，否则导入会报错。常见修改点：
| MySQL语法 | PostgreSQL兼容写法 | 示例修正前/后 |
|---|---|---|
AUTO_INCREMENT自增主键 | SERIAL或GENERATED BY DEFAULT AS IDENTITY | id INT AUTO_INCREMENT → id SERIAL PRIMARY KEY |
ENGINE=InnoDB | 直接删除（PostgreSQL无存储引擎） | CREATE TABLE t (id INT) ENGINE=InnoDB; → CREATE TABLE t (id INT); |
COMMENT '注释' | 保留（PostgreSQL支持） | 无需修改 |
ENUM('男','女') | 创建ENUM类型或改用VARCHAR | 原：gender ENUM('男','女') → 新：CREATE TYPE gender_type AS ENUM('男','女'); gender gender_type |
UNIQUE KEY (col) | 保留（语法兼容） | 无需修改 |
工具辅助：使用sed命令批量替换（示例修正自增主键）：
sed-i's/AUTO_INCREMENT/SERIAL/g' order_db_dump.sql sed-i's/ENGINE=InnoDB//g' order_db_dump.sql 步骤2：PostgreSQL环境初始化
2.1 安装与配置PostgreSQL 15
# CentOS 7安装（其他系统参考官方文档）sudo yum install-y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install-y postgresql15 postgresql15-server sudo /usr/pgsql-15/bin/postgresql-15-setup initdb sudo systemctl start postgresql-15 sudo systemctl enable postgresql-15 2.2 创建目标数据库与用户
-- 以postgres用户登录psql psql -U postgres -- 创建数据库（字符集必须为UTF8）CREATEDATABASE order_db WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;-- 创建业务用户（权限最小化）CREATEUSER app_user WITH PASSWORD '***';GRANTALLPRIVILEGESONDATABASE order_db TO app_user;步骤3：数据导入与类型转换（pgloader工具）
pgloader是专门用于数据库迁移的工具，支持自动处理类型映射、索引迁移。
3.1 安装pgloader（Mac/Linux）
# Mac（需先安装brew） brew install pgloader # Linux（需EPEL源）sudo yum install-y pgloader 3.2 编写迁移配置文件（mysql2pg.load）
LOAD DATABASE FROM mysql://root:***@192.168.1.10:3306/order_db INTO postgresql://app_user:***@192.168.1.20:5432/order_db WITH include no drop, create tables, create indexes, reset sequences, data only, workers = 8, concurrency = 4; ALTER SCHEMA 'order_db' RENAME TO 'public'; # MySQL默认schema映射到PostgreSQL的public SET MySQL PARAMETERS net_read_timeout = 600, net_write_timeout = 600; SET PostgreSQL PARAMETERS work_mem = '64MB', maintenance_work_mem = '1GB'; 3.3 执行迁移（停机窗口内）
pgloader mysql2pg.load 关键日志解读：
2024-06-15T14:00:00 INFO pgloader: Migrating from #<MYSQL-CONNECTION ...> 2024-06-15T14:05:00 INFO pgloader: Creating tables... 2024-06-15T14:20:00 INFO pgloader: Copying data for table "t_order" (200,000,000 rows)... 2024-06-15T15:30:00 INFO pgloader: Creating indexes... 2024-06-15T15:45:00 INFO pgloader: Migration completed successfully. 步骤4：应用适配与联调
迁移后需修改应用代码，适配PostgreSQL的JDBC驱动和方言。
4.1 修改pom.xml（Maven项目）
<!-- 移除MySQL驱动，添加PostgreSQL驱动 --><dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><version>42.6.0</version></dependency><!-- 修改Hibernate方言（Spring Boot自动配置时需显式指定） --><properties><hibernate.dialect>org.hibernate.dialect.PostgreSQLDialect</hibernate.dialect></properties>4.2 调整数据库连接配置（application.properties）
spring.datasource.url=jdbc:postgresql://192.168.1.20:5432/order_db spring.datasource.username=app_user spring.datasource.password=*** spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect 4.3 修复SQL兼容问题
- 原MySQL分页：
LIMIT 10 OFFSET 20→ PostgreSQL兼容（无需修改）； - 原MySQL函数：
GROUP_CONCAT(items SEPARATOR ',')→ 改为STRING_AGG(items, ',')； - JSON字段操作：MySQL的
JSON_EXTRACT→ PostgreSQL的->和->>操作符（如order_info->>'user_id'）。
步骤5：数据验证与性能测试（迁移后核心）
5.1 数据一致性校验
- 工具校验：使用
pgDDL（https://github.com/laurenz/pgDDL）对比MySQL和PostgreSQL的表结构； - 数据抽样：随机抽取1000条订单数据，对比关键字段（如
order_id、amount、create_time）；
行数校验：
-- MySQL查询行数SELECTCOUNT(*)FROM t_order;-- PostgreSQL查询行数（更准确）SELECT reltuples::BIGINTAS row_count FROM pg_class WHERE relname ='t_order';5.2 功能正确性测试
- 覆盖所有CRUD接口（新增、查询、更新、删除）；
- 验证事务回滚（如“下单→扣库存”场景，模拟异常后检查数据一致性）；
- 测试JSON字段操作（如
WHERE order_info->>'status' = 'PAID'）。
5.3 性能压测（JMeter示例）
模拟双11峰值（1万并发用户），对比迁移前后的：
- 接口响应时间（目标：≤500ms）；
- 数据库CPU/内存使用率（目标：CPU≤70%）；
- 锁等待次数（PostgreSQL应无锁等待）。
压测结果：迁移后订单接口平均响应时间从800ms降至350ms，CPU使用率从90%降至55%，无锁等待日志。
四、迁移中的常见问题与解决方案
问题1：ENUM类型迁移失败
现象：MySQL的ENUM('PAID','UNPAID')导入PostgreSQL时提示“类型不存在”。
解决方案：
修改表结构：
ALTERTABLE t_order ALTERCOLUMNstatusTYPE order_status USINGstatus::order_status;在PostgreSQL中手动创建ENUM类型：
CREATETYPE order_status ASENUM('PAID','UNPAID');问题2：自增主键值不同步
现象：迁移后新增数据的order_id从1开始（原MySQL已到100000）。
解决方案：同步序列值（PostgreSQL自增主键由序列控制）：
-- 查询当前最大IDSELECTMAX(order_id)FROM t_order;-- 假设结果为100000-- 设置序列起始值SELECT setval('t_order_order_id_seq',100000);问题3：应用连接池报错“SSL连接拒绝”
现象：Spring Boot启动时报FATAL: no pg_hba.conf entry for host ...。
解决方案：修改PostgreSQL的pg_hba.conf（通常在/var/lib/pgsql/15/data/pg_hba.conf），允许应用服务器IP连接：
# 新增行（允许192.168.1.30通过密码认证连接） host order_db app_user 192.168.1.30/32 scram-sha-256 修改后重启PostgreSQL：
sudo systemctl restart postgresql-15 五、总结与最佳实践
从MySQL迁移到PostgreSQL是“技术驱动业务”的典型场景，但需严谨规划。以下是关键总结：
迁移前
- 明确迁移动机（功能、性能、成本）；
- 完成数据量、业务停机窗口、应用适配成本评估；
- 预演迁移脚本（建议在测试环境模拟3次以上）。
迁移中
- 优先选择逻辑迁移（可控性高），大数据量考虑实时迁移；
- 重点处理数据类型、函数、语法差异（建议编写自动化转换脚本）；
- 停机窗口内关闭业务写入（可通过MySQL的
FLUSH TABLES WITH READ LOCK锁定表）。
迁移后
- 严格验证数据一致性（抽样+工具校验）；
- 压测关键业务接口（确保性能达标）；
- 保留MySQL实例30天（作为回滚保障）。
最后记住：迁移不是终点，而是架构升级的起点。PostgreSQL的JSONB、GIS、全文搜索等特性，将为你的业务创新提供更强大的技术支撑！
