字
字节笔记本
2026年7月20日
MySQL 迁移到 PostgreSQL 实战：pgloader 与备选方案
API中转
¥120
MySQL 迁移到 PostgreSQL 实战：pgloader 与备选方案
将 MySQL 数据库迁移到 PostgreSQL 是常见的架构升级路径。本文记录了实际迁移过程中的配置方法、踩过的坑，以及当工具不可行时的备选方案。
方案一：pgloader
pgloader 是专门做数据库迁移的工具，支持 MySQL → PostgreSQL 的直接迁移。
安装
bash
# macOS
brew install pgloader
# Ubuntu/Debian
apt install pgloader
# Docker
docker pull dimitri/pgloader基础配置
创建一个 .load 配置文件：
text
LOAD DATABASE
FROM mysql://root:password@host:3306/blog
INTO postgres://postgres:password@host:5432/blog
WITH include drop, create tables, create indexes,
reset sequences, foreign keys
SET PostgreSQL PARAMETERS
maintenance_work_mem to '128MB',
work_mem to '12MB';运行：
bash
pgloader mysql_to_postgres.load类型转换（CAST）
MySQL 和 PostgreSQL 的类型有差异，需要用 CAST 规则处理：
text
CAST
type datetime when default "0000-00-00 00:00:00"
to timestamptz drop default using zero-dates-to-null,
type timestamp when default "0000-00-00 00:00:00"
to timestamptz drop default using zero-dates-to-null,
type date when default "0000-00-00"
to date drop default using zero-dates-to-null,
type tinyint when (= 1 precision)
to boolean using tinyint-to-booleanMySQL 的零日期 0000-00-00 在 PostgreSQL 中不合法，需要转成 NULL。tinyint(1) 通常对应 PostgreSQL 的 boolean。
常见问题
1. 密码包含特殊字符
如果 MySQL 密码里有 @ 符号，会导致 URL 解析出错。解决方案：
- URL 编码：
@→%40 - 或者使用展开式语法避免 URL 解歧义
2. 托管数据库限制
如果目标 PostgreSQL 是 Supabase 等托管服务，无法修改服务器级参数（如 wal_buffers、max_wal_senders），需要从 SET 子句中移除这些参数，只保留会话级参数：
text
-- 可以设置（会话级）
SET maintenance_work_mem to '128MB'
SET work_mem to '12MB'
SET statement_timeout TO 0
-- 不能设置（服务器级，需要重启）
SET wal_buffers TO '64MB' -- 报错
SET max_wal_senders TO 0 -- 报错3. SET 子句中数值需要加引号
pgloader 的配置语法比较严格，数值参数也需要用引号包裹：
text
-- 错误
SET statement_timeout TO 0
-- 正确
SET statement_timeout TO '0'方案二：mysqldump + psql（备选）
如果 pgloader 遇到无法解决的问题（比如特定版本的 bug），可以用传统的导出导入方式。
导出 MySQL
bash
mysqldump -h mysql_host -P 3306 -u root -p'password' \
--no-tablespaces \
--column-statistics=0 \
--compatible=postgresql \
blog > blog_dump.sql--compatible=postgresql 会做一些语法兼容处理，但通常不够彻底。
清理 MySQL 特有语法
bash
# 移除 ENGINE 指定
sed -i 's/ENGINE=InnoDB//g' blog_dump.sql
# 移除 LOCK/UNLOCK TABLES
sed -i 's/LOCK TABLES.*WRITE;//g' blog_dump.sql
sed -i 's/UNLOCK TABLES;//g' blog_dump.sql
# 处理零日期
sed -i "s/0000-00-00 00:00:00/NULL/g" blog_dump.sql
# 处理反引号标识符（MySQL 用反引号，PG 用双引号）
sed -i 's/`/"/g' blog_dump.sql
# 处理 AUTO_INCREMENT
sed -i 's/AUTO_INCREMENT=.*//g' blog_dump.sql导入 PostgreSQL
bash
psql postgres://postgres:password@pg_host:5432/blog < blog_dump.sqlMySQL 与 PostgreSQL 的主要差异
迁移时需要特别注意以下语法和类型的差异：
数据类型映射
| MySQL | PostgreSQL | 说明 |
|---|---|---|
TINYINT(1) | BOOLEAN | 布尔值 |
INT UNSIGNED | INTEGER | PostgreSQL 无 UNSIGNED |
BIGINT UNSIGNED | BIGINT | 同上 |
DATETIME | TIMESTAMP 或 TIMESTAMPTZ | 带时区更安全 |
VARCHAR(n) | VARCHAR(n) | 兼容 |
TEXT | TEXT | 兼容 |
JSON | JSONB | PostgreSQL 推荐 JSONB，支持索引 |
ENUM('a','b') | TEXT + CHECK 约束 | PG 的 ENUM 类型改动成本高 |
AUTO_INCREMENT | SERIAL 或 GENERATED ALWAYS AS IDENTITY | 自增方式不同 |
TINYBLOB/MEDIUMBLOB | BYTEA | 二进制数据 |
SQL 语法差异
sql
-- 字符串引号
-- MySQL: 反引号
SELECT `name` FROM `users`
-- PostgreSQL: 双引号
SELECT "name" FROM "users"
-- 限制行数
-- MySQL
SELECT * FROM users LIMIT 10 OFFSET 20
-- PostgreSQL（兼容 LIMIT/OFFSET）
SELECT * FROM users LIMIT 10 OFFSET 20
-- 字符串拼接
-- MySQL
CONCAT(first_name, ' ', last_name)
-- PostgreSQL
first_name || ' ' || last_name
-- 当前时间
-- MySQL
NOW()
-- PostgreSQL（兼容）
NOW()
-- IF/ELSE 逻辑
-- MySQL
SELECT IF(status = 1, 'active', 'inactive')
-- PostgreSQL
SELECT CASE WHEN status = 1 THEN 'active' ELSE 'inactive' END迁移前检查清单
- 检查零日期：MySQL 允许
0000-00-00，PostgreSQL 不允许 - 检查大小写敏感：PostgreSQL 默认大小写敏感，MySQL 通常不敏感
- 检查自增序列：迁移后需要
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)) - 检查索引：确保所有常用查询字段都有索引
- 检查存储过程/触发器：PostgreSQL 的 PL/pgSQL 语法与 MySQL 差异很大，通常需要重写
- 检查字符集：确保 PostgreSQL 数据库编码为 UTF8
迁移后验证
sql
-- 比对记录数
SELECT 'users' AS table_name, count(*) FROM users
UNION ALL
SELECT 'orders', count(*) FROM orders
UNION ALL
SELECT 'products', count(*) FROM products;
-- 检查序列
SELECT c.relname, s.last_value
FROM pg_sequences s
JOIN pg_class c ON c.oid = s.seqrelid;
-- 修复序列（如果值不对）
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));总结
pgloader 理论上是最便捷的迁移方案，但在实际使用中可能会遇到 URL 解析、托管数据库权限限制等问题。mysqldump + psql 的方式虽然需要手动清理语法差异，但胜在可控性强、问题容易排查。无论用哪种方案，迁移前做好备份、迁移后做好数据校验是必须的。
分享: