引言
2023年双11大促期间，某电商平台的MySQL数据库因“高并发下锁冲突”导致订单接口频繁超时，而同期切换到PostgreSQL的竞品，凭借“多版本并发控制（MVCC）”轻松扛住20万QPS。这并非个例——越来越多企业因功能扩展需求（如JSONB、GIS）、高并发稳定性或开源生态适配，选择从MySQL迁移到PostgreSQL。
本文将通过某“电商订单系统”的真实迁移案例，带你掌握从数据备份→类型转换→应用适配→验证测试的全流程，确保迁移后数据0丢失、业务0中断。
一、为什么选择迁移？MySQL vs PostgreSQL的核心差异
迁移前需明确动机，以下是常见驱动因素：
维度MySQLPostgreSQL迁移必要性数据类型支持基础类型（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无存储引擎概念（统一管理）。
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 IDENTITYid INT AUTO_INCREMENT → id SERIAL PRIMARY KEYENGINE=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_typeUNIQUE 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）
CREATE DATABASE order_db WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
-- 创建业务用户（权限最小化）
CREATE USER app_user WITH PASSWORD '***';
GRANT ALL PRIVILEGES ON DATABASE 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 #
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项目）
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查询行数
SELECT COUNT(*) FROM t_order;
-- PostgreSQL查询行数（更准确）
SELECT reltuples::BIGINT AS 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时提示“类型不存在”。
解决方案：
在PostgreSQL中手动创建ENUM类型：CREATE TYPE order_status AS ENUM ('PAID', 'UNPAID');
修改表结构：ALTER TABLE t_order ALTER COLUMN status TYPE order_status USING status::order_status;
问题2：自增主键值不同步
现象：迁移后新增数据的order_id从1开始（原MySQL已到100000）。
解决方案：同步序列值（PostgreSQL自增主键由序列控制）：
-- 查询当前最大ID
SELECT MAX(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、全文搜索等特性，将为你的业务创新提供更强大的技术支撑！