pgloader 千万级订单迁移实测：避坑与提速4倍

问题背景：为什么不能只靠默认配置迁移订单库

最近将一套约 2300 万行的 MySQL 订单库迁移到 PostgreSQL，核心表包括订单主表、订单明细表、支付流水表和退款表。初期使用 pgloader 默认配置，全量迁移耗时约 4 小时 10 分，并且在数据校验阶段暴露出多个问题：tinyint(1) 被错误识别为布尔值、金额字段精度异常、datetime 与时间戳默认值不兼容、部分中文字符串变成乱码。

订单系统对字段语义、金额精度和迁移窗口都非常敏感。直接使用默认的 MySQL to PostgreSQL 迁移脚本，虽然能快速跑通，但很难满足生产切换要求。本文记录这次从试跑、问题定位、参数调优到最终 55 分钟完成迁移的全过程。

方案对比：pgloader、逻辑复制与自研 ETL

在迁移前，我们评估了三类方案：

方案	适用场景	优点	风险	适合本次吗
pgloader	一次性全量迁移、结构不复杂	部署简单，自动建表，支持类型重写	默认类型映射需审查，大表参数要调优	是
逻辑复制	低停机增量迁移	可持续同步，切换窗口短	链路复杂，数据类型兼容成本高	二期考虑
自研 ETL	转换逻辑复杂	可控性最高	开发周期长，断点续传要自己实现	否

本次目标是先完成一次性全量迁移，并在业务低峰窗口切换，因此选择 pgloader。后续如果需要缩短停机时间，可以在全量基础上增加增量双写或 CDC 同步。

类型映射陷阱：这些字段不能让 pgloader 猜

pgloader 默认会进行 MySQL 到 PostgreSQL 的类型自动映射，但订单库里的字段通常带有业务语义，默认规则不一定可靠。

1. tinyint(1) 被误判为 boolean

MySQL 中大量状态字段使用 tinyint(1)，例如订单状态、支付状态、退款状态。虽然看起来像布尔值，但业务上通常是枚举：0 待支付、1 已支付、2 已取消、3 已退款。pgloader 默认可能将其映射为 PostgreSQL 的 boolean，导致 2、3 等值无法表达。

建议显式映射为 smallint：

-- PostgreSQL 中推荐的状态字段定义
status smallint NOT NULL DEFAULT 0,
pay_status smallint NOT NULL DEFAULT 0,
refund_status smallint NOT NULL DEFAULT 0;

如果希望提升可读性，可以使用 PostgreSQL 的枚举或检查约束：

ALTER TABLE orders
  ADD CONSTRAINT chk_orders_status
  CHECK (status IN (0, 1, 2, 3, 4));

COMMENT ON COLUMN orders.status IS '订单状态：0待支付，1已支付，2已取消，3已退款，4已完成';

2. 金额字段不要使用浮点类型

MySQL 中金额字段可能是 decimal(12,2)、float 或历史遗留的 double。迁移到 PostgreSQL 后，金额必须统一为 numeric，不能使用 real 或 double precision。

-- PostgreSQL 金额字段示例
ALTER TABLE orders
  ALTER COLUMN total_amount TYPE numeric(14, 2),
  ALTER COLUMN discount_amount TYPE numeric(14, 2),
  ALTER COLUMN freight_amount TYPE numeric(14, 2);

ALTER TABLE order_items
  ALTER COLUMN sale_price TYPE numeric(14, 4),
  ALTER COLUMN quantity TYPE integer;

商品单价可能保留 4 位小数，订单汇总金额保留 2 位小数。不要为了性能把金额改成 float8，浮点误差会导致对账失败。

3. 自增列不能只改成 integer

MySQL 的 AUTO_INCREMENT 迁移后，如果只创建普通 integer 字段，业务插入新订单时不会自动生成主键。PostgreSQL 中推荐使用 identity column 或 bigserial。

-- PostgreSQL 10+ 更推荐 identity 写法
ALTER TABLE orders
  ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

-- 迁移完成后必须同步序列值
SELECT setval(
  pg_get_serial_sequence('orders', 'id'),
  COALESCE((SELECT MAX(id) FROM orders), 1),
  true
);

这一步非常容易遗漏。现象是迁移后查询正常，但业务一写入就报 null value in column id violates not-null constraint 或主键冲突。

pgloader 配置：先建表，再批量加载

为了避免自动建表带来的类型不可控问题，我们采用“手工创建 PostgreSQL 表结构 + pgloader 只负责数据加载”的方式。

示例配置如下：

-- 先在 PostgreSQL 中创建目标表，字段类型按业务语义定义
CREATE TABLE IF NOT EXISTS orders (
  id bigint PRIMARY KEY,
  order_no varchar(64) NOT NULL,
  user_id bigint NOT NULL,
  status smallint NOT NULL DEFAULT 0,
  total_amount numeric(14,2) NOT NULL DEFAULT 0,
  discount_amount numeric(14,2) NOT NULL DEFAULT 0,
  freight_amount numeric(14,2) NOT NULL DEFAULT 0,
  created_at timestamp NOT NULL,
  updated_at timestamp NOT NULL,
  paid_at timestamp NULL
);

CREATE INDEX IF NOT EXISTS idx_orders_user_id_created_at
  ON orders (user_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_orders_status_created_at
  ON orders (status, created_at DESC);

pgloader 的 .load 文件示例：

LOAD DATABASE
     FROM mysql://migration_user:password@mysql-host:3306/order_db
     INTO pgsql://pgloader_user:password@pg-host:5432/order_pg

WITH include drop,
     create no,
     create tables no,
     truncate false,
     workers = 8,
     concurrency = 1,
     batch rows = 50000,
     preserve index names,
     drop indexes,
     drop triggers,
     reset sequences no,
     enable triggeraction = disable

CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
     type tinyint to smallint,
     type tinyint(1) to smallint,
     type int unsigned to bigint,
     type bigint unsigned to numeric,
     type double to numeric drop default

ALTER schema 'order_db' rename to 'public'

ALTER TABLE NAMES MATCHING ~/orders/, 'order_items', 'payments', 'refunds'
SET work_mem = '64MB'
;

注意：示例中的 drop indexes 用于大批量加载阶段临时删除目标端二级索引，加载完成后再统一创建。主键不建议删除，否则容易引入重复数据。

提速 4 倍：从 4 小时降到 55 分钟

第一次试跑耗时过长，主要瓶颈不在网络，而在 PostgreSQL 每批提交时维护索引、WAL 刷盘和过小批次。我们通过以下调整将耗时压缩到约 55 分钟。

1. 合理调整批量大小

默认批次偏保守，在高延迟网络下往返成本明显。将 batch rows 从默认值提高到 50000，并按表大小单独控制。订单明细表最大，单独设置 100000 行一批。

经验值如下：

参数	初始值	调优后	说明
workers	2	8	控制表级并发
batch rows	10000	50000	降低提交次数
concurrency	1	1	单表内避免过多争抢
work_mem	4MB	64MB	排序、哈希更充分

不要盲目把并发调到几十。pgloader 的并发更多体现在多表并行，单大表并发过高会让目标库出现大量等待和锁竞争。

2. 先加载数据，再建二级索引

边插入边维护多个组合索引，会导致每批数据都产生大量索引页变更。对于千万级订单表，建议先保留主键约束，删除非必要二级索引，数据完成后再创建。

-- 数据加载完成后创建核心查询索引
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_order_no
  ON orders (order_no);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_status_time
  ON orders (user_id, status, created_at DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payments_order_id
  ON payments (order_id);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_order_items_order_id
  ON order_items (order_id);

如果是在停机窗口内由迁移账号独占操作，可以不用 CONCURRENTLY，普通 CREATE INDEX 更快；如果目标库仍有只读连接，使用 CONCURRENTLY 更安全。

3. 加载期使用非同步提交

全量加载属于可重跑任务，在确认业务未切流、迁移失败可重新导入的前提下，可以临时降低事务提交等待。

-- 仅在迁移窗口临时设置，迁移后恢复
ALTER SYSTEM SET synchronous_commit = off;
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET max_wal_size = '8GB';
SELECT pg_reload_conf();

迁移完成后恢复：

-- 生产环境恢复持久化和维护内存参数
ALTER SYSTEM SET synchronous_commit = on;
ALTER SYSTEM SET maintenance_work_mem = '64MB';
ALTER SYSTEM SET max_wal_size = '2GB';
SELECT pg_reload_conf();

这类参数只适合受控的批量导入窗口，不建议在正常业务运行时长时间关闭同步提交。

4. 大表按主键区间拆分观察

对于单表超过 1500 万行的明细表，建议先按主键区间做试跑，观察单批耗时、失败行和目标库负载。虽然 pgloader 支持失败数据落盘，但订单迁移不应该依赖错误文件“兜底”，更重要的是提前发现类型和字符集问题。

数据校验：迁移成功不能只看行数

行数一致不代表金额、时间和关联关系正确。建议至少做四类校验。

-- 1. 行数校验
SELECT COUNT(*) AS total_count,
       MIN(id) AS min_id,
       MAX(id) AS max_id
FROM orders;

-- 2. 金额汇总校验，需与 MySQL 侧结果对比
SELECT status,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_amount_sum,
       SUM(discount_amount) AS discount_amount_sum
FROM orders
GROUP BY status
ORDER BY status;

-- 3. 空值与异常时间校验
SELECT COUNT(*) FILTER (WHERE order_no IS NULL) AS null_order_no,
       COUNT(*) FILTER (WHERE total_amount IS NULL) AS null_amount,
       COUNT(*) FILTER (WHERE created_at IS NULL) AS null_created_at,
       COUNT(*) FILTER (WHERE paid_at < created_at) AS paid_before_created
FROM orders;

-- 4. 孤儿明细校验
SELECT COUNT(*) AS orphan_items
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;

时间字段还要重点检查 MySQL 的零日期。0000-00-00 00:00:00 在 PostgreSQL 中不是合法时间戳，必须在 pgloader 中使用 zero-dates-to-null，或者在迁移前先清洗为 NULL。

避坑总结

坑 1：状态值 2、3 变成 true 或直接报错

常见错误现象是订单状态字段迁移后只剩真假值，或者 pgloader 报布尔类型转换失败。原因是 MySQL 的 tinyint(1) 被当成布尔字段。修正方法是在 pgloader 中显式配置 type tinyint(1) to smallint，并在 PostgreSQL 建表时使用 smallint，再通过 CHECK 约束限定合法状态。

坑 2：迁移后新订单无法插入

常见报错是 null value in column id violates not-null constraint，或者新订单主键与历史数据冲突。原因是自增主键只迁移了数值，没有建立 identity/sequence。修正方法是为 id 添加 identity 属性，并执行 setval(pg_get_serial_sequence(...), max(id), true) 同步序列。

坑 3：金额汇总与 MySQL 对不上

现象是总额差异很小但持续存在，通常来自浮点类型或精度截断。修正方法是统一使用 PostgreSQL 的 numeric，订单金额使用 numeric(14,2)，商品单价按业务使用 numeric(14,4)，并在迁移后按状态、日期维度汇总对账。

坑 4：索引边导边维护导致加载很慢

现象是 pgloader CPU 不高，但 PostgreSQL 写入延迟很高，等待事件集中在索引和 WAL。修正方法是加载前删除非必要二级索引，保留主键和必要唯一约束，数据完成后再批量创建索引，并适当提高 maintenance_work_mem。

坑 5：零日期和非法 UTF-8 字符中断加载

现象是部分批次失败，错误信息包含 date/time field value out of range 或字符编码异常。修正方法是对 datetime 使用零日期转空值规则，提前确认 MySQL 连接字符集为 utf8mb4，并在迁移前清洗历史脏数据。

最终结论

pgloader 非常适合 MySQL 到 PostgreSQL 的千万级批量迁移，但不要把它当成“一条命令全自动”的工具。真正可靠的流程应该是：先手工审查并创建 PostgreSQL 表结构，再显式处理类型映射，加载期调整批量参数和索引策略，最后完成行数、金额、时间和关联完整性校验。

本次订单库迁移通过显式类型映射、删除二级索引后重建、提高批大小和临时 WAL 参数，将全量耗时从 4 小时 10 分降到 55 分钟，整体提速约 4.5 倍。对于后续需要进一步缩短停机时间的业务，可以在这套全量方案上增加增量捕获和双写切换。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。