1. 迁移背景与核心考量
十年前我刚入行时，MySQL几乎是互联网项目的默认选择，但最近三年参与的新项目中，PostgreSQL的使用率明显提升。上周帮某电商平台完成从MySQL 5.7到PostgreSQL 14的迁移后，我决定系统梳理这套经过实战检验的迁移方案。
两种数据库最本质的区别在于架构设计：MySQL采用"可插拔"的存储引擎架构（如InnoDB、MyISAM），而PostgreSQL是统一的关系型数据库管理系统。这种差异导致它们在事务处理、JSON支持、地理空间数据等场景表现迥异。比如在最近的压力测试中，相同硬件环境下PostgreSQL处理复杂查询的吞吐量比MySQL高出40%，特别是在涉及多表关联和窗口函数的场景。
2. 迁移前准备与评估
2.1 环境兼容性检查
首先用pgloader工具的--dry-run参数进行模拟迁移：
pgloader --dry-run mysql://user:pass@source_host:3306/dbname postgresql://user:pass@target_host:5432/dbname这会生成包含以下关键信息的报告：
- 数据类型转换映射表（如MySQL的TINYINT(1)转PostgreSQL的BOOLEAN）
- 需要手动处理的语法差异（如MySQL的
GROUP_CONCAT对应PostgreSQL的STRING_AGG） - 索引和约束的兼容性问题
2.2 性能基准测试方案
建议使用sysbench进行对比测试，重点监控以下指标：
sysbench oltp_read_write --db-driver=mysql --mysql-host=host1 prepare sysbench oltp_read_write --db-driver=pgsql --pgsql-host=host2 prepare测试项应包含：
- 单行查询延迟
- 事务吞吐量（TPS）
- 并发连接稳定性
- 复杂查询响应时间
3. 数据结构迁移实战
3.1 表结构转换策略
使用pgloader进行基础迁移后，需要特别注意这些特殊处理：
- 自增主键转换：
-- MySQL CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY); -- PostgreSQL等效方案 CREATE TABLE users (id SERIAL PRIMARY KEY);- 字符集与排序规则：
-- 将MySQL的utf8mb4转换为PostgreSQL的UTF8 ALTER DATABASE dbname SET encoding = 'UTF8';- 日期时间处理： PostgreSQL的timestamp with time zone比MySQL的TIMESTAMP更严格，建议：
SET timezone = 'Asia/Shanghai';3.2 索引优化方案
迁移后需要重建索引的情况包括：
- MySQL的FULLTEXT索引 → PostgreSQL的GIN索引
- 多列索引的顺序优化（PostgreSQL的BRIN索引对时序数据更高效）
- 函数索引的语法转换
示例重建GIN索引：
CREATE INDEX idx_content_search ON articles USING gin(to_tsvector('english', content));4. 数据迁移的四种方案对比
| 方案 | 工具/方法 | 适用场景 | 速度 | 停机时间 | 复杂度 |
|---|---|---|---|---|---|
| 全量迁移 | pgloader | 小型数据库(<50GB) | 快 | 高 | 低 |
| 逻辑复制 | Debezium + Kafka | 大型OLTP系统 | 中 | 低 | 高 |
| 双写适配层 | 应用层改造 | 微服务架构 | 慢 | 无 | 最高 |
| 文件级导出导入 | mysqldump + pg_restore | 兼容性测试 | 最慢 | 高 | 中 |
在最近的项目中，我们采用方案2实现了<30秒的停机窗口。关键配置如下：
# Debezium MySQL连接器配置 snapshot.mode=initial database.history.kafka.bootstrap.servers=kafka:9092 database.history.kafka.topic=dbhistory.schema5. 应用层适配要点
5.1 SQL方言转换清单
高频差异点处理示例：
-- MySQL SELECT IFNULL(field, 'default') FROM table; -- PostgreSQL SELECT COALESCE(field, 'default') FROM table; -- MySQL分页 SELECT * FROM table LIMIT 10 OFFSET 20; -- PostgreSQL分页(语法兼容但执行计划不同) SELECT * FROM table LIMIT 10 OFFSET 20;5.2 连接池配置优化
PostgreSQL的默认连接数（通常100）比MySQL（通常151）更保守，需要调整：
# Spring Boot配置示例 spring: datasource: hikari: maximum-pool-size: 50 # 根据实际负载调整 connection-timeout: 30000 validation-timeout: 50006. 迁移后验证体系
6.1 数据一致性校验
使用pg-comparator工具进行全表校验：
java -jar pg-comparator.jar \ -source jdbc:mysql://host1/db \ -target jdbc:postgresql://host2/db \ -tables "users,orders,products"6.2 性能回归测试
重点监控指标变化：
- 查询延迟P99值
- 事务失败率
- 锁等待时间
- 备份耗时变化
7. 常见故障处理实录
问题1：迁移后查询变慢10倍
- 原因：MySQL的隐式类型转换在PostgreSQL中失效
- 解决方案：
EXPLAIN ANALYZE定位后，添加显式类型转换
问题2：批量插入报错"too many parameters"
- 原因：PostgreSQL默认限制每个查询32767个参数
- 解决：分批提交或调整
max_prepared_transactions
问题3：应用出现"connection reset"错误
- 检查点：验证TCP keepalive设置
ALTER SYSTEM SET tcp_keepalives_idle = 60; ALTER SYSTEM SET tcp_keepalives_interval = 10;8. 进阶优化方向
对于TB级数据库，建议采用这些增强策略：
- 表分区优化：将大表按时间范围分区
CREATE TABLE measurement ( id SERIAL, logdate DATE NOT NULL ) PARTITION BY RANGE (logdate);- 读写分离：配置PostgreSQL逻辑复制
- 扩展插件：安装TimescaleDB(时序数据)、PostGIS(空间数据)等扩展
迁移完成后三个月内，建议每周进行一次完整的统计信息分析：
ANALYZE VERBOSE;这次迁移过程中最大的收获是：对于核心业务表，提前创建与生产环境数据量相当的测试环境进行压测，能发现80%以上的潜在问题。某张包含2亿记录的用户表，就因为未预见到PostgreSQL的MVCC机制对存储空间的额外消耗，导致初期磁盘规划不足。
