PostgreSQL：详解 MySQL数据迁移，如何将数据平滑迁移到PostgreSQL
文章目录
- 一、迁移前评估与准备
- 1.1 明确迁移目标
- 1.2 兼容性分析
- 1.3 数据量与停机窗口评估
- 二、迁移工具选型与对比
- 2.1 主流工具概览
- 三、使用 pgloader 进行自动化迁移（推荐方案）
- 3.1 安装 pgloader
- 3.2 编写迁移配置文件（`migrate.load`）
- 3.3 关键配置说明
- 3.4 执行迁移
- 四、数据类型与函数转换详解
- 4.1 常见数据类型映射
- 4.2 函数与操作符转换
- 五、处理自增主键与序列
- 5.1 MySQL 的 AUTO_INCREMENT
- 5.2 PostgreSQL 的实现方式
- 方式一：`SERIAL`（旧版）
- 方式二：`IDENTITY`（SQL 标准，PG 10+ 推荐）
- 5.3 迁移后修复序列值
- 六、索引、约束与性能调优
- 6.1 索引转换
- 6.2 外键与约束
- 6.3 性能调优建议
- 七、应用层适配与 SQL 改造
- 7.1 ORM 适配
- 7.2 常见 SQL 改造点
- 7.3 事务与锁行为差异
- 八、数据一致性验证
- 8.1 行数校验
- 8.2 校验和比对（关键表）
- 8.3 抽样比对
- 8.4 应用端冒烟测试
- 九、上线切换策略
- 9.1 停机迁移（小系统）
- 9.2 双写 + 切读（中大型系统）
- 9.3 CDC 实时同步（零停机）
- 十、常见问题
- 10.1 字符集与排序规则
- 10.2 0000-00-00 日期问题
- 10.3 反引号与关键字冲突
- 10.4 大对象（BLOB）迁移
将数据从 MySQL 平滑迁移到 PostgreSQL 是许多企业因成本、开源策略、功能需求或云架构调整而采取的关键举措。尽管两者均为关系型数据库，但在SQL 语法、数据类型、事务模型、索引机制、函数生态等方面存在显著差异。若处理不当，迁移过程易出现数据丢失、性能下降、应用中断等问题。
参考资源：
- pgloader 官网：https://pgloader.io/
- PostgreSQL vs MySQL 对比：https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
- AWS DMS 文档：https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.MySQL.html
一、迁移前评估与准备
1.1 明确迁移目标
- 降低成本：摆脱 MySQL 商业许可（如 Oracle MySQL Enterprise）
- 功能增强：利用 PG 的 JSONB、GIS（PostGIS）、全文检索、窗口函数、物化视图等高级特性
- 架构统一：已有 PG 技术栈，希望统一数据库平台
- 云战略：迁移到 AWS RDS/Aurora PostgreSQL、Azure Database for PostgreSQL 等托管服务
1.2 兼容性分析
使用工具扫描 MySQL 模式，识别潜在问题：
| 差异点 | MySQL | PostgreSQL | 迁移影响 |
|---|---|---|---|
| 标识符大小写 | 默认不区分（Linux 区分） | 严格区分（"Name"≠name） | 需统一命名规范 |
| 字符串比较 | 忽略尾部空格 | 严格比较 | 可能导致 JOIN/UNIQUE 失败 |
| 布尔类型 | 无原生 BOOLEAN，用 TINYINT(1) | 原生 BOOLEAN | 需转换字段类型 |
| 自增主键 | AUTO_INCREMENT | SERIAL/IDENTITY | DDL 需重写 |
| LIMIT/OFFSET | LIMIT offset, count | LIMIT count OFFSET offset | 应用 SQL 需调整 |
| 字符串连接 | CONCAT()或 ` | `（需开启） | |
| 日期时间 | DATETIME无时区 | TIMESTAMP/TIMESTAMPTZ | 时区处理需谨慎 |
| 默认值函数 | NOW(),CURDATE() | CURRENT_TIMESTAMP,CURRENT_DATE | 函数替换 |
| 反引号 | `table` | 双引号"table"或无引号 | 需转义或移除 |
推荐工具：Ora2Pg（虽名含 Ora，但支持 MySQL 模式解析）、pgloader内置检查、自定义脚本。
1.3 数据量与停机窗口评估
- 小数据量（< 100 GB）：可接受短暂停机（分钟级）
- 中大数据量（100 GB ~ 10 TB）：需采用在线迁移 + 增量同步，最小化停机时间
- 超大数据量（> 10 TB）：考虑分库分表迁移、并行导出导入
二、迁移工具选型与对比
2.1 主流工具概览
| 工具 | 类型 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|---|
| pgloader | 开源 ETL | 自动转换 DDL/DML，支持在线迁移，速度快 | 复杂函数/存储过程不支持 | 中小规模，结构简单 |
| AWS DMS | 托管服务 | 支持 CDC（变更数据捕获），零停机 | 费用高，需 AWS 环境 | 云上迁移，大企业 |
| MySQL to PostgreSQL (from Intelligent Converters) | 商业软件 | 图形界面，支持触发器/存储过程 | 闭源，收费 | 无开发资源团队 |
| 自定义脚本（mysqldump + sed + psql） | 手动 | 完全可控 | 易出错，维护成本高 | 特殊定制需求 |
| Debezium + Kafka + Sink Connector | 流式架构 | 实时同步，高可用 | 架构复杂 | 超大规模，高可用要求 |
推荐组合：
- 中小项目：
pgloader（主力） + 手动修正- 大型项目：
AWS DMS或Debezium（增量） +pgloader（全量）
三、使用 pgloader 进行自动化迁移（推荐方案）
3.1 安装 pgloader
# Ubuntu/Debiansudoaptinstallpgloader# macOSbrewinstallpgloader# 源码编译（推荐最新版）gitclone https://github.com/dimitri/pgloader.gitcdpgloadermakepgloader3.2 编写迁移配置文件（migrate.load）
LOAD DATABASE FROM mysql://user:pass@mysql-host:3306/source_db INTO postgresql://pguser:pgpass@pg-host:5432/target_db WITH include only table names like'users','orders', create tables, create indexes, reset sequences, workers = 8, concurrency =1CAST type datetime to timestamptz drop default drop not null, type date to date drop default, type tinyint to boolean using inline cast, type varchar to varchar drop typemod AFTER LOAD DO $$ ALTER TABLE users ALTER COLUMN created_at SET DEFAULT NOW();CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);$$;3.3 关键配置说明
include only table names：按需迁移表，避免无关数据create tables：自动建表（基于转换后 DDL）reset sequences：修复自增 ID 序列值CAST：类型映射规则（核心！）datetime → timestamptz：保留时区信息tinyint(1) → boolean：将 0/1 转为 true/falsevarchar(N) → varchar：移除长度限制（PG 支持无限长）
AFTER LOAD DO：迁移后执行 SQL（如重建索引、设默认值）
3.4 执行迁移
pgloader migrate.load输出包含错误日志、性能统计、未处理对象列表，便于排查。
四、数据类型与函数转换详解
4.1 常见数据类型映射
| MySQL 类型 | PostgreSQL 推荐类型 | 注意事项 |
|---|---|---|
INT,BIGINT | INT4,INT8 | 直接映射 |
TINYINT(1) | BOOLEAN | 仅当表示真假时 |
TINYINT(n>1) | SMALLINT | 避免误转 BOOLEAN |
VARCHAR(N) | VARCHAR或TEXT | PG 无性能差异，建议用 TEXT |
TEXT,MEDIUMTEXT | TEXT | 统一为 TEXT |
DATETIME | TIMESTAMP WITHOUT TIME ZONE | 若无时区需求 |
DATETIME | TIMESTAMPTZ | 若需时区转换（推荐） |
DATE | DATE | 直接映射 |
DECIMAL(M,D) | NUMERIC(M,D) | 精确数值 |
FLOAT,DOUBLE | REAL,DOUBLE PRECISION | 直接映射 |
ENUM | TEXT+ CHECK 约束 或 自定义 DOMAIN | PG 不推荐 ENUM |
JSON | JSONB | 推荐 JSONB（支持索引、更快） |
BLOB | BYTEA | 二进制数据 |
4.2 函数与操作符转换
| MySQL 表达式 | PostgreSQL 等效写法 |
|---|---|
NOW() | CURRENT_TIMESTAMP |
CURDATE() | CURRENT_DATE |
IFNULL(a, b) | COALESCE(a, b) |
ISNULL(a) | a IS NULL |
CONCAT(a, b) | `a |
SUBSTRING(str, pos, len) | SUBSTR(str, pos, len)（pos 从 1 开始） |
RAND() | RANDOM() |
LIMIT 10, 20 | LIMIT 20 OFFSET 10 |
`column` | "column"或直接column（若无关键字冲突） |
建议：在应用层统一替换，或通过 PostgreSQL 的自定义函数兼容：
CREATEFUNCTIONifnull(anyelement,anyelement)RETURNSanyelementAS$$SELECTcoalesce($1,$2);$$LANGUAGEsql;
五、处理自增主键与序列
5.1 MySQL 的 AUTO_INCREMENT
- 每张表独立计数器
- 插入时可显式指定 ID（需开启
NO_AUTO_VALUE_ON_ZERO）
5.2 PostgreSQL 的实现方式
方式一：SERIAL（旧版）
CREATETABLEusers(idSERIALPRIMARYKEY,-- 自动创建 sequence users_id_seqnameTEXT);方式二：IDENTITY（SQL 标准，PG 10+ 推荐）
CREATETABLEusers(idINTGENERATEDBYDEFAULTASIDENTITYPRIMARYKEY,nameTEXT);5.3 迁移后修复序列值
若数据已导入但序列未更新，会导致主键冲突：
-- 方法1：pgloader 自动处理（启用 reset sequences）-- 方法2：手动重置SELECTsetval('users_id_seq',(SELECTMAX(id)FROMusers));注意：
IDENTITY列的序列名可通过pg_get_serial_sequence('users', 'id')获取。
六、索引、约束与性能调优
6.1 索引转换
- 普通索引：
CREATE INDEX直接迁移 - 唯一索引：
UNIQUE约束自动创建索引 - 全文索引：MySQL 的
FULLTEXT需重写为 PG 的tsvector+ GIN/GiST 索引ALTERTABLEarticlesADDCOLUMNtitle_ts tsvector;UPDATEarticlesSETtitle_ts=to_tsvector('english',title);CREATEINDEXidx_title_ftsONarticlesUSINGGIN(title_ts); - 前缀索引：MySQL 支持
INDEX(col(10))，PG 不支持，需用表达式索引：CREATEINDEXONusers((left(email,10)));
6.2 外键与约束
- PG 默认启用外键约束，确保参照完整性
- 若 MySQL 未启用外键，迁移后需评估是否添加
- 使用
NOT VALID延迟验证大表外键：ALTERTABLEordersADDCONSTRAINTfk_userFOREIGNKEY(user_id)REFERENCESusers(id)NOTVALID;ALTERTABLEorders VALIDATECONSTRAINTfk_user;-- 后台验证
6.3 性能调优建议
- 批量插入：关闭 autovacuum、增大
maintenance_work_mem - 并行迁移：pgloader 的
workers和concurrency参数 - 目标库配置：
shared_buffers = 4GB effective_cache_size = 12GB work_mem = 64MB max_wal_size = 4GB
七、应用层适配与 SQL 改造
7.1 ORM 适配
- Sequelize / TypeORM / Django ORM：切换 dialect 为
postgres，调整连接字符串 - MyBatis：修改 XML 中的函数（如
NOW()→CURRENT_TIMESTAMP） - Laravel Eloquent：基本兼容，注意分页语法（
skip/takevsoffset/limit）
7.2 常见 SQL 改造点
| 场景 | MySQL | PostgreSQL |
|---|---|---|
| 分页 | LIMIT 10 OFFSET 20 | 同左（但 OFFSET 不能为负） |
| 插入返回 ID | INSERT ...; SELECT LAST_INSERT_ID(); | INSERT ... RETURNING id; |
| 字符串转日期 | STR_TO_DATE('20250101','%Y%m%d') | TO_DATE('20250101','YYYYMMDD') |
| 随机排序 | ORDER BY RAND() | ORDER BY RANDOM() |
| 获取当前数据库 | DATABASE() | current_database() |
7.3 事务与锁行为差异
- 默认隔离级别：MySQL（RR），PG（Read Committed）
- 幻读处理：PG 在 RC 下无幻读（MVCC 实现不同）
- 锁粒度：PG 行锁更精细，死锁检测更主动
- 建议：测试高并发场景，必要时显式设置隔离级别：
BEGINISOLATIONLEVELREPEATABLEREAD;
八、数据一致性验证
8.1 行数校验
-- MySQLSELECTCOUNT(*)FROMusers;-- PostgreSQLSELECTCOUNT(*)FROMusers;8.2 校验和比对（关键表）
-- MySQLSELECTMD5(GROUP_CONCAT(CONCAT(id,name,email)ORDERBYid))FROMusers;-- PostgreSQLSELECTMD5(STRING_AGG(CONCAT(id,name,email),''ORDERBYid))FROMusers;注意：需处理 NULL（
CONCAT在 PG 中遇 NULL 返回 NULL，可用COALESCE）
8.3 抽样比对
- 随机抽取 1000 行，逐字段比对
- 重点验证：时间戳、浮点数、布尔值、JSON 内容
8.4 应用端冒烟测试
- 核心业务流程（注册、下单、查询）端到端测试
- 监控错误日志、慢查询
九、上线切换策略
9.1 停机迁移（小系统）
- 停写 MySQL
- 执行最终增量同步（如有）
- 切换应用连接字符串至 PG
- 验证后恢复服务
9.2 双写 + 切读（中大型系统）
- 应用同时写 MySQL 和 PG（双写）
- 数据校验工具持续比对
- 逐步切读流量至 PG（如 10% → 50% → 100%）
- 确认稳定后停写 MySQL
9.3 CDC 实时同步（零停机）
- 使用Debezium捕获 MySQL binlog
- 通过 Kafka 将变更同步至 PG
- 切换瞬间仅需处理秒级延迟数据
工具链：MySQL → Debezium → Kafka → Kafka Connect JDBC Sink → PostgreSQL
十、常见问题
10.1 字符集与排序规则
- MySQL 默认：
utf8mb4+utf8mb4_general_ci（不区分大小写） - PostgreSQL 默认：
UTF8+en_US.UTF-8（区分大小写） - 解决方案：
- 应用层统一转小写比较
- 或创建不区分大小写的 collation（PG 12+）：
CREATECOLLATION case_insensitive(provider=icu,locale='und-u-ks-level2');CREATETABLEusers(nameTEXTCOLLATE"case_insensitive");
10.2 0000-00-00 日期问题
- MySQL 允许
0000-00-00，PG 不允许 - 迁移前清洗：
UPDATEtableSETdate_col=NULLWHEREdate_col='0000-00-00';
10.3 反引号与关键字冲突
- MySQL 用反引号包裹关键字（如
`order`） - PG 用双引号（
"order"），但建议重命名字段避免冲突
10.4 大对象（BLOB）迁移
- pgloader 支持
BYTEA，但超大文件（>1GB）建议单独迁移 - 或存储为文件路径，数据库仅存 URL
总结：MySQL 到 PostgreSQL 的迁移是一项系统工程，成功的关键在于：
- 充分评估：识别语法、类型、行为差异
- 工具赋能：以
pgloader为核心，自动化 DDL/DML 转换 - 渐进切换：通过双写、CDC 实现零停机
- 全面验证：行数、校验和、业务逻辑三层保障
- 长期优化：迁移后利用 PG 特性（JSONB、分区表、扩展）提升系统能力
PostgreSQL 以其标准兼容性、功能丰富性、社区活跃度，已成为 MySQL 的强大替代者。通过科学规划与严谨执行，迁移不仅是一次技术栈升级，更是系统架构现代化的重要契机。