pgloader 实战指南：一条命令把 MySQL、SQLite、CSV 数据全部搬进 PostgreSQL

pgloader 实战指南：一条命令把 MySQL、SQLite、CSV 数据全部搬进 PostgreSQL

【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloader

凌晨两点，你盯着屏幕上的迁移日志发呆。第 37 次重跑同一个导入脚本，每次都在同一行脏数据上报错退出——数据里有个 MySQL 特有的0000-00-00日期，还有一个字段带了非法编码。你手动改了这行，重跑，结果 5 分钟后又卡在另一行上。

如果你经历过这个场景，那这篇 pgloader 实战指南就是为你写的。pgloader 是专为 PostgreSQL 打造的数据加载工具，它的核心思路是：把"出错就整体回滚"的迁移逻辑，换成"坏数据进废纸篓、好数据照常入库"的搬运逻辑。它支持的源头包括 MySQL、SQLite、MS SQL Server，以及 CSV、DBF、IXF、固定宽度文件，而你的操作往往只是一条命令。

先搞明白：pgloader 和传统 COPY 到底差在哪

很多人第一次接触 PostgreSQL 的COPY命令时都觉得"这玩意够用了"。确实，在数据干净、格式统一的小规模导入里，COPY又快又简单。但真实世界的数据从来不是这样。

我用一个搬家的比喻来解释：传统 COPY 像一次只能搬一整车货物，只要有一件东西碎了，整车退回；而 pgloader 像专业的搬家公司，碎掉的单独打包送进"损坏物品登记处"，其余完好的继续搬。

这个差异是 pgloader 存在的根本理由。它的设计哲学很明确：迁移过程中"出错"是常态，工具的任务是把错误的影响控制在最小范围，而不是让整个迁移停摆。

案例一：SQLite 迁移，从一条命令开始

先从最简单的场景入手，建立信心。把 SQLite 数据库整个搬进 PostgreSQL，pgloader 只需要一条命令：

createdb newdb pgloader ./test/sqlite/sqlite.db postgresql:///newdb

它背后自动完成了四件事：读取 SQLite 的表结构、在 PostgreSQL 里建表、迁移全部数据、尽可能保持数据类型一致。这是 pgloader 最让人舒服的一点——对简单场景，零配置就是最佳配置。

如果你的 SQLite 文件路径复杂，或者想控制更多行为，可以把它写进.load配置文件。项目里test/sqlite-chinook.load就是一个很好的模板：

load database from 'sqlite/Chinook_Sqlite_AutoIncrementPKs.sqlite' into postgresql:///pgloader with workers = 4, concurrency = 2, on error stop, include drop, create tables, create indexes, reset sequences, foreign keys, preserve index names alter table names matching 'Employee' rename to 'staff' set work_mem to '16MB', maintenance_work_mem to '512 MB', search_path to 'chinook' before load do $$ create schema if not exists chinook; $$;

这个文件几乎展示了.load配置的全部核心语法：with控制迁移行为（是否建表、是否建索引、并发数）、alter table names matching做表名改名、set设置 PostgreSQL 参数、before load do在迁移前执行 SQL。

📌小贴士：.load文件的完整语法规则参见项目中的 docs/command.rst，每种数据源的专属选项在 docs/ref/ 目录下有单独文档。

案例二：MySQL 全库迁移，最经典的实战场景

如果说 SQLite 是热身，那 MySQL 迁移就是 pgloader 的看家本领。它不只是搬数据，还会连表结构、索引、外键、自增序列、表注释一起搬过去。

最简单的方式依然是命令行直接干：

pgloader mysql://root@localhost/sakila postgresql://localhost/sakila

但生产环境没人会这么裸奔。真实项目里你需要控制建表策略、并行度、类型转换规则，这时候就要上配置文件。下面这份摘取自 docs/ref/mysql.rst 的示例，是 MySQL 迁移配置的"全家福"参考：

LOAD DATABASE FROM mysql://root@localhost/sakila INTO postgresql://localhost:54393/sakila WITH include drop, create tables, create indexes, reset sequences, workers = 8, concurrency = 1, multiple readers per thread, rows per range = 50000 SET PostgreSQL PARAMETERS maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'sakila, public, "$user"' SET MySQL PARAMETERS net_read_timeout = '120', net_write_timeout = '120' CAST type bigint when (= precision 20) to bigserial drop typemod, type date drop not null drop default using zero-dates-to-null, type year to integer MATERIALIZE VIEWS film_list, staff_list ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films' ALTER SCHEMA 'sakila' RENAME TO 'pagila' BEFORE LOAD DO $$ create schema if not exists pagila; $$, $$ alter database sakila set search_path to pagila, mv, public; $$;

这里面有几个点值得你重点消化：

CAST 子句是清洗数据的核心武器。看那行type date drop not null drop default using zero-dates-to-null——MySQL 里合法的0000-00-00日期，PostgreSQL 根本不认（日历里没有第 0 年）。pgloader 内置了zero-dates-to-null转换函数，把这类日期自动转成NULL，这正是 README 里强调的招牌功能。

MATERIALIZE VIEWS 处理物化视图。MySQL 的视图迁移过去后往往需要物化，这个子句让你明确指定哪些视图要物化。

ALTER TABLE NAMES MATCHING 支持正则。想统一把以_list结尾的表挪到mvschema？写一行正则匹配即可。

如果你只想迁移部分表，把注释里的INCLUDING ONLY TABLE NAMES MATCHING ~/film/取消注释就行，pgloader 会按正则筛选表名。

案例三：CSV 导入，命令行和配置文件两条路

数据库之间迁移是 pgloader 的强项，但它对文件型数据的支持同样扎实。以 CSV 为例，你可以完全不写配置文件，纯命令行搞定：

pgloader --type csv \ --field id --field field \ --with truncate \ --with "fields terminated by ','" \ ./test/data/matching-1.csv \ postgres:///pgloader?tablename=matching

注意末尾那个?tablename=matching——目标表名是拼在连接串里的。命令行模式下，--field声明每一列，--with描述格式细节，适合"看一眼就导入"的临时任务。

需要处理复杂格式时，用配置文件版。test/csv.load展示了更完整的姿势：

LOAD CSV FROM inline (x, y, a, b, c, "camelCase") INTO postgresql:///pgloader?csv (a, b, "camelCase", c) WITH truncate, skip header = 1, fields optionally enclosed by '"', fields escaped by double-quote, fields terminated by ',' SET client_encoding to 'latin1', work_mem to '12MB' BEFORE LOAD DO $$ drop table if exists csv; $$, $$ create table csv ( a bigint, b bigint, c char(2), "camelCase" text ); $$;

这份配置能直观看到三件事：fields optionally enclosed by处理带引号字段、skip header = 1跳过表头、BEFORE LOAD DO里用两条 SQL 完成"删表重建"。FROM inline甚至允许直接把数据写在配置文件的;之后，测试时非常好用。

CSV 场景的冷门技巧

• 读标准输入：把数据源写成-，配合 Unix 管道，可以流式导入压缩文件，比如gunzip -c source.gz | pgloader --type csv ... - pgsql:///target。详见 docs/quickstart.rst。
• 直接加载 HTTP 上的文件：把源路径换成 URL，pgloader 会自动下载并解压归档文件，同样一条命令。
• 跳过表头：官方测试数据test/data/2013_Gaz_113CDs_national.txt就是带一行表头的 CSV，配--with "skip header = 1"即可。

迁移现场故障排查速查表

数据迁移做多了，你会发现踩的坑高度可预测。下面这份速查表覆盖了 90% 的现场问题：

症状一：导入的字符全是乱码

大概率是源文件编码没声明。两种解法：

# 命令行指定编码 pgloader --type csv --encoding latin1 source.csv postgresql:///targetdb

或者在配置文件的SET段写client_encoding to 'latin1'。pgloader 支持用--list-encodings查看它认识的所有编码名。

症状二：大文件迁移时内存被撑爆

老版本 pgloader 有著名的 Lisp 堆内存陷阱，v4 全面重写为 Clojure/JVM 版后，改用标准 JVM 参数控制：

java -Xmx4g -jar pgloader.jar big.load

这是 v4 的重大变化：整个工具现在是单个自包含 JAR，要求 Java 21 及以上，不再依赖 SBCL 和一堆共享库，迁移超大库时用-Xmx调堆大小即可，比旧版好调得多。

症状三：远程数据库连接中途断开

网络不稳时，给连接加上保活参数，同时调小单批次体积降低长事务风险：

with batch rows = 10000, batch size = 10MB set connect_timeout to '120', keepalives to '1', keepalives_idle to '60'

症状四：不想重跑时把脏数据再读一遍

用--summary输出报告文件，用--logfile保存完整日志。排查完错误行后，可以只针对拒绝文件里的数据做二次导入。

症状五：只想验证配置，不想真跑

pgloader --dry-run migration.load

--dry-run只检查连接和数据源可达性，不加载任何数据。上线前必跑。

⚠️注意：命令行选项里--on-error-stop控制遇错行为。默认 pgloader 会继续加载并把错误行写入单独文件，只有你明确需要"严格模式"时才启用它。

性能翻倍的五参数调优清单

同样的迁移任务，配置得当与否，耗时可能差一个数量级。记住下面这五个参数，基本就够用了：

1. workers：并行读取线程数，建议按 CPU 核数设置，如workers = 8。
2. concurrency：同时打开的 PostgreSQL 连接数。workers 和 concurrency 不是一回事——前者管读取，后者管写入。
3. batch rows / batch size：每批提交的行数与体积上限。调大能减少提交次数，但别超过内存承受力。
4. rows per range：数据库迁移时按主键范围分片读取，适合大表。
5. multiple readers per thread：让单个线程可以并发读多个源，配合上面参数一起用。

关于索引的策略也很关键：create indexes默认是先灌数据、后建索引，这比边灌边建快得多。加载期间还可以用disable triggers跳过触发器，灌完再手动处理约束。

版本选择：用 v3 还是 v4？

如果你在部署时看到新旧两个版本，这里有个快速决策参考：

• v4（Clojure/JVM 重写版）：单 JAR 分发包，需要 Java 21+，连接串统一用 JDBC 风格（jdbc:mysql://、jdbc:postgresql://），SSL 通过?sslmode=require控制。src/、clojure/目录就是 v4 的源码主体。
• v3（Common Lisp 版）：需要编译，Makefile里的DYNSIZE参数控制内存映像大小，适合需要make DYNSIZE=1024这类定制的老环境。

v4 对.load文件语法和命令行参数完全兼容，理论上可以无缝替换。新项目直接上 v4，老项目建议在测试环境验证后再切。

动手之前，记住这几条铁律

1. 先 dry-run，再真跑。连接串写错是最常见的翻车原因，--dry-run一秒就能发现。
2. 小表验证全流程。挑一两张小表跑通，确认类型映射和重命名规则符合预期。
3. 先备份，后迁移。源库和目标库都备份，这是数据工程最基本的素养。
4. 看日志要看 summary。迁移完--summary生成的报告会告诉你每个表成功多少行、失败多少行、耗时多少，这是验收依据。
5. 遇到格式问题查文档。每种数据源在 docs/ref/ 下都有独立手册，比如 docs/ref/mysql.rst、docs/ref/copy.rst。测试用例在test/目录，里面几十个.load文件都是可直接套用的活教材。

我认识的一位 DBA 说过一句话："数据迁移的价值不在于把数据搬过去，而在于搬完之后你敢拍胸脯说两边对得上。" pgloader 的价值恰恰就在这里——它把"对得上"这个目标，从一句口号变成了默认行为。

今晚不用再熬夜改脏数据了。打开终端，createdb、写一行连接串、回车，然后去睡觉。剩下的事，交给 pgloader。

【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloader