1. 为什么需要从MySQL迁移到PostgreSQL？
十年前我刚入行时，MySQL几乎是所有项目的默认选择。但最近五年，越来越多的团队开始考虑PostgreSQL。上周我刚帮一个日活百万的电商平台完成了数据库迁移，整个过程踩了不少坑，也积累了不少实战经验。
PostgreSQL相比MySQL有几个显著优势：更完善的SQL标准支持、更强大的JSON处理能力、更丰富的索引类型（如GIN/GiST）、原生的分区表功能。特别是在处理复杂查询和地理空间数据时，PostgreSQL的性能优势非常明显。我最近经手的一个物流系统迁移后，轨迹查询速度提升了3倍以上。
2. 迁移前的准备工作
2.1 环境评估与兼容性检查
首先用pgloader工具的dry-run模式进行兼容性测试：
pgloader --dry-run mysql://user:pass@mysql_host/dbname postgresql://user:pass@pg_host/dbname重点关注以下兼容性问题：
- MySQL的datetime默认值语法
- 自增列的实现差异（MySQL的AUTO_INCREMENT vs PostgreSQL的SERIAL）
- 字符串排序规则（COLLATE）
- 索引长度的限制（PostgreSQL没有3072字节的限制）
2.2 制定迁移方案
根据我的经验，迁移方案主要取决于业务规模：
| 数据规模 | 推荐方案 | 预估停机时间 | 风险等级 |
|---|---|---|---|
| <10GB | 一次性迁移 | <30分钟 | 低 |
| 10-100GB | 全量+增量 | 1-2小时 | 中 |
| >100GB | 双写过渡 | 按需控制 | 高 |
重要提示：无论选择哪种方案，务必在相同配置的测试环境完整演练至少3次
3. 实战迁移步骤详解
3.1 结构迁移
使用pgloader进行基础结构迁移：
pgloader \ --with "create no indexes" \ --with "create no triggers" \ --with "foreign keys deferred" \ mysql://user:pass@source/db \ postgresql://user:pass@target/db这个命令先迁移基础表结构，跳过索引和触发器（后续单独处理）。我遇到过因为索引导致迁移速度下降10倍的情况。
3.2 数据迁移优化技巧
对于大表（>1000万行），采用分批次迁移：
-- 在PostgreSQL创建临时表 CREATE UNLOGGED TABLE temp_orders (LIKE orders); -- 分批导入数据 pgloader \ --with "batch size=100MB" \ --with "prefetch rows=5000" \ mysql://user:pass@source/db \ postgresql://user:pass@target/db?table=temp_orders迁移完成后：
-- 切换表（原子操作） BEGIN; ALTER TABLE orders RENAME TO orders_old; ALTER TABLE temp_orders RENAME TO orders; COMMIT;3.3 索引与约束重建
PostgreSQL的索引创建策略与MySQL不同：
-- 并发创建索引（不锁表） CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id); -- 外键建议迁移后添加 ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) DEFERRABLE INITIALLY DEFERRED;4. 迁移后的关键调整
4.1 参数优化
postgresql.conf关键参数调整：
# 连接数（通常比MySQL设置小30%） max_connections = 200 # 内存分配（根据服务器内存调整） shared_buffers = 4GB work_mem = 16MB maintenance_work_mem = 1GB # 针对从MySQL迁移的特殊设置 enable_nestloop = off # 对JOIN多的查询更友好4.2 应用层适配
最常见的应用层修改点：
- LIMIT子句语法：
LIMIT 10 OFFSET 20→ PostgreSQL原生支持 - 日期函数：
DATE_FORMAT→TO_CHAR - 字符串连接：
CONCAT()→||操作符 - 分页查询：建议改用PostgreSQL更高效的
keyset pagination
5. 常见问题解决方案
5.1 字符集问题
遇到乱码时检查：
-- 查看当前编码 SHOW server_encoding; -- 创建数据库时指定编码 CREATE DATABASE new_db WITH ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';5.2 性能下降排查
使用EXPLAIN ANALYZE定位问题：
EXPLAIN ANALYZE SELECT * FROM large_table WHERE json_column->>'property' = 'value'; -- 通常需要创建GIN索引 CREATE INDEX idx_gin_json ON large_table USING GIN (json_column);5.3 存储过程重写
示例：MySQL存储过程转换
-- MySQL DELIMITER // CREATE PROCEDURE update_stats(IN user_id INT) BEGIN -- 逻辑代码 END // DELIMITER ; -- PostgreSQL等效 CREATE OR REPLACE FUNCTION update_stats(user_id integer) RETURNS void AS $$ BEGIN -- 逻辑代码 END; $$ LANGUAGE plpgsql;6. 迁移后的监控与优化
部署以下监控指标：
- 长事务监控：
SELECT * FROM pg_stat_activity WHERE state <> 'idle' AND now() - xact_start > interval '5 minutes' - 索引使用率：
SELECT * FROM pg_stat_all_indexes WHERE idx_scan = 0 - 缓存命中率：
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) FROM pg_statio_user_tables
我在实际项目中总结的黄金法则：迁移后第一周必须每天检查pg_stat_statements，找出TOP 10耗时查询进行优化。
