pgloader 保姆级实战指南：一条命令把 MySQL、SQLite、CSV 数据安全迁进 PostgreSQL

pgloader 保姆级实战指南：一条命令把 MySQL、SQLite、CSV 数据安全迁进 PostgreSQL

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

pgloader 是一款专为 PostgreSQL 设计的数据装载工具，核心卖点是"一条命令完成迁移"，它基于 PostgreSQL 原生 COPY 协议工作，最大的与众不同之处在于：遇到坏数据时不会中断整个导入，而是把出错行单独隔离、继续灌入好数据。无论你手头是 MySQL 还是 SQLite 数据库，抑或只是几十 GB 的 CSV 文件，本文都会用最直白的方式带你从零上手，并给出可照抄的生产级配置。

一、先讲一个"迁移翻车"的故事

假设你接到一个任务：把一台运行了五年的 MySQL 服务器搬到 PostgreSQL。你写了脚本逐表导出、再逐表导入，结果跑到第三张表就报了错——某行日期是0000-00-00，PostgreSQL 直接拒绝，整批数据回滚，前功尽弃。你手动把那行删掉重跑，下一张表又冒出编码问题、类型不兼容、外键顺序错乱……一个周末就这么没了。

这类"迁移翻车"几乎是每个 DBA 和开发者的共同记忆。原因很朴素：

• PostgreSQL 原生 COPY 是事务性的，一行坏数据能让整张表一张也进不去；
• 手工迁移最头疼的是类型转换和脏数据清洗，很少有人会事先想到 MySQL 的日期里藏着"零年"；
• 工具链碎片化：导表用一个工具、转换类型写一堆脚本、建索引再来一套，光对接就耗掉大半精力。

pgloader 想解决的正是这三件事。它把"读源、建结构、清洗、灌数据、建索引、修序列、建外键"整条流水线收拢成一条命令，剩下的脏数据问题，由它内置的规则和"错误隔离区"机制兜底。

二、30 秒上手：第一次跑通迁移

先不聊概念，直接感受一下 pgloader 的体感。

2.1 三种快速安装姿势

pgloader 当前主推 v4 版本（Clojure/JVM 重写），产物是一个自包含的单个 JAR 包，只要求本机有Java 21 或更高版本，不再依赖 SBCL 和一堆共享库。

方式一：下载官方预编译 JAR

curl -L -o pgloader.jar https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar java -jar pgloader.jar --version

想全局可用，可以把它装进/usr/local/lib，再写一个薄壳脚本调用即可。

方式二：Docker 一条命令

docker pull ghcr.io/dimitri/pgloader:latest docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version

方式三：源码编译

git clone https://gitcode.com/gh_mirrors/pg/pgloader cd pgloader make

编译完成后可执行文件出现在./build/bin/目录。内存吃紧的机器还可以用make DYNSIZE=1024这类参数调整编译期内存预算。

2.2 最小可用示例：SQLite 一次性搬迁

假设你手里有一个chinook.db，想整体搬到本地 PostgreSQL：

createdb chinook pgloader chinook.db postgresql:///chinook

就这两行。pgloader 会自动完成：读取 SQLite 元数据 → 生成 PostgreSQL 建表语句 → 按类型映射规则转换字段 → 搬数据 → 建索引 → 复位自增序列 → 建外键。跑完后终端会打出一张汇总表，每张源表的read / imported / errors / time一目了然。

官方示例中，一个包含 22 张表、1.5 万行数据的 Chinook 数据库，全程约 1.6 秒完成。

如果你连源文件都懒得下载，pgloader 还支持直接传 HTTP(S) 链接，它会先下载、必要时解压、再执行迁移，一步到位。

三、白话理解 pgloader 的三个关键机制

要让 pgloader 用得顺手，先搞懂它底层是怎么工作的，这里用三个生活化类比讲清楚。

3.1 它本质上是"PostgreSQL COPY 的调度员"

COPY是 PostgreSQL 自带的批量装载命令，速度极快，但非常"娇气"：输入里只要有一行不合法，整个批次的导入就宣告失败。pgloader 并不另起炉灶，而是继续用 COPY 灌数据，只是把自己放在了"上游"——由它负责读取、解析、清洗数据，再交给 COPY 执行。你可以把它想象成快递分拣中心：装车（COPY）还是那套流程，但包裹（数据行）在进车之前已经被分拣和修缮过了。

3.2 "错误隔离区"：坏数据不拖累好数据

这是 pgloader 对比裸用 COPY 最大的优势。默认行为下，遇到解析失败或数据库报错的行，它不会停止任务，而是：

• 把坏行原样写入独立的reject文件（默认落在/tmp/pgloader/目录下）；
• 在日志和汇总报告里记下错误数量；
• 继续处理后续数据。

于是"有一行0000-00-00导致整库迁移失败"这种事被彻底拆解成了"这一行被标记、其余五十万行照常入库"。迁移结束你只需回头处理那几十行漏网之鱼，工作量完全不在一个量级。

如果你希望严格一些，也可以显式开启--on-error-stop或WITH on error stop，让它在首个错误处停住——调试阶段通常推荐这么做。

3.3 读与写分离的并发模型

pgloader 把工作拆成"读者线程"和"写者线程"：读者负责从源端拉数据（受prefetch rows控制，默认 100000 行），写者负责攒够一批后批量交给 COPY。批次的关闭由两个阈值触发，谁先到谁说了算：

• batch rows：最多攒多少行（默认 25000）；
• batch size：最多攒多大体积（默认 20MB）。

这种"边读边写、攒批提交"的流水线设计，让大表迁移时内存占用可控，吞吐量也明显优于逐行插入。

四、能力全景图：pgloader 能替你干哪些活

把 pgloader 的能力拆成四个面来看，你对它能解决什么、不能解决什么就会非常清楚。

4.1 数据源接入面

• 数据库迁移：MySQL、MariaDB、SQLite、SQL Server，一条命令连结构带数据整体搬迁；
• 文件格式：CSV（含各种方言变体）、DBF（dBase）、IXF（IBM 格式）、定宽文件；
• 特殊通道：标准输入（-代表 stdin，可与gunzip等管道配合）、HTTP 远程文件、归档包（zip 等）自动下载解压；
• 目标端扩展：支持 PostgreSQL 及 Citus 分布式部署形态。

4.2 类型转换与清洗面

内置了大量"翻译规则"，最典型的是把 MySQL 的0000-00-00这类不存在的日期翻译成 PostgreSQL 的NULL。常用内置转换函数包括：

• zero-dates-to-null：全零日期转空值；
• tinyint-to-boolean：把 MySQL 用 tinyint 伪装的布尔值还原成真布尔；
• date-with-no-separator/time-with-no-separator：把20041002152952整理成2004-10-02 15:29:52；
• hex-to-dec、int-to-ip、set-to-enum-array、remove-null-characters等一批实用工具。

更妙的是，转换规则可以通过CAST子句自定义，也能按列、按表名匹配来精准投放。

4.3 迁移行为控制面

通过.load命令文件里的WITH子句，几乎每个环节都有开关：

• 建库建表：create tables、create indexes、reset sequences、foreign keys；
• 覆盖策略：include drop（先删后建）、truncate（先清空再灌）、no truncate；
• 性能参数：workers、concurrency、max parallel create index、batch rows、batch size、prefetch rows；
• 加速手段：disable triggers（灌数据期间停用触发器）、drop indexes（先摘索引再灌、灌完并行重建）。

4.4 运行监控面

• 终端实时输出带进度感的汇总表：每张表读了多少行、成功多少、错误多少、耗时多少；
• --logfile把日志落到文件、--summary单独导出统计报告、--verbose/--debug控制日志级别；
• --dry-run只探测连接不真正导入，适合上线前演练；
• --list-encodings可查询工具认识的所有字符集名称。

五、三个典型场景拆解

下面每个场景都按"目标 → 操作 → 效果验证"三段式展开，你可以直接照着改。

场景 A：SQLite 到 PostgreSQL（小型项目平滑升级）

目标：把应用从嵌入式 SQLite 升级到 PostgreSQL，表结构、外键、自增主键全部保留。

操作：单行命令即可，也支持把规则写进命令文件以便复用：

load database from 'sqlite/chinook.sqlite' into postgresql:///pgloader with include drop, create tables, create indexes, reset sequences set work_mem to '16MB', maintenance_work_mem to '512MB';

效果验证：跑完后在 psql 里抽查——\dt看表是否齐全、\d 表名看主键外键是否就位、对比源库的COUNT(*)确认行数一致。终端报告里若某张表errors列非零，去/tmp/pgloader/下找对应的错误文件逐行排查。

场景 B：MySQL 全量搬迁（含结构与约束）

目标：迁移整个库，包括表结构、索引、外键、注释、自增列，并把 MySQL 的脏日期自动清洗成合法值。

操作：先建好目标库，再跑命令：

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

如果源库存在必须特殊处理的字段，用命令文件精细化控制：

load database from mysql://root@localhost/sakila into postgresql:///sakila with include drop, create tables, no truncate, create indexes, reset sequences, foreign keys set maintenance_work_mem to '128MB', work_mem to '12MB', search_path to 'sakila' cast type datetime to timestamptz drop default drop not null using zero-dates-to-null, type date drop not null drop default using zero-dates-to-null materialize views film_list, staff_list before load do $$ create schema if not exists sakila; $$;

这段配置示范了几件高频需求：把datetime映射成带时区的timestamptz并顺手把零日期洗成 NULL；用MATERIALIZE VIEWS把 MySQL 视图连同内容一起"物化"过来；用BEFORE LOAD DO先建目标 schema。真实项目中 f1db 数据集（约 50 万行、33 张表）全程迁移耗时约 5.5 秒。

效果验证：检查汇总表里Create Tables / Create Indexes / Reset Sequences / Foreign Keys各环节是否有报错；再随机挑几张关联表验证外键约束真实生效。

场景 C：CSV 文件入库（含字段映射与清洗）

目标：把一个分隔符不常见、带表头、存在脏值的外部 CSV 灌进指定表，并顺手做列裁剪。

操作：纯命令行也能驱动：

pgloader --type csv \ --field id --field name --field email \ --with "fields terminated by ','" \ --with "skip header = 1" \ --with truncate \ ./data/users.csv \ postgresql:///mydb?tablename=users

注意目标连接串里的tablename参数——它决定了数据落到哪张表。更复杂的解析（比如字段被引号包裹、制表符分隔、指定日期格式、空串转 NULL）建议写命令文件：

LOAD CSV FROM 'GeoLiteCity-Blocks.csv' WITH ENCODING iso-646-us HAVING FIELDS (startIpNum, endIpNum, locId) INTO postgresql://user@localhost/dbname TARGET TABLE geolite.blocks TARGET COLUMNS ( iprange ip4r using (ip-range startIpNum endIpNum), locId ) WITH truncate, skip header = 2, fields optionally enclosed by '"', fields terminated by '\t' SET work_mem to '32MB', maintenance_work_mem to '64MB';

这里TARGET COLUMNS展示了 pgloader 的另一项杀手锏：源文件列与目标表列不必一一对应，可以用USING表达式在导入途中实时计算新列（示例把两个整数 IP 拼成一个ip4r区间）。

效果验证：导入后抽查——空值是否按预期转成 NULL、裁剪掉的列是否真的没进表、带引号字段是否被正确还原。官方 CSV 教程里的标准示例（6 行数据）总耗时约 0.05 秒。

六、新手常踩的坑与对应解法

坑 1：日期/时间值被 PostgreSQL 拒绝

现象：错误文件里满是date/time field value out of range。

原因：MySQL 允许0000-00-00，而日历里没有"公元零年"。

解法：在CAST子句给日期类型挂上using zero-dates-to-null，或在 CSV 的WITH里指定date format模板让解析器按你的格式读。

坑 2：字符集混乱导致乱码

现象：导入的文本出现?或方块字。

解法：CSV 源在FROM行用WITH ENCODING xxx声明文件编码；数据库连接层面用SET client_encoding to 'latin1'之类的会话参数兜底。不确定支持哪些编码先跑pgloader --list-encodings。

坑 3：大文件迁移内存告急

现象：JVM 进程被 OOM 杀掉。

解法：v4 版本改用 Java 堆管理，直接用-Xmx调大堆即可，比如java -Xmx4g -jar pgloader.jar ...；同时在WITH里收紧batch rows/batch size/prefetch rows，让内存使用更平缓。

坑 4：远程数据库迁移中途断连

现象：导入跑到一半连接超时。

解法：在SET子句里配置会话参数：

SET connect_timeout = 120, keepalives = 1, keepalives_idle = 60, keepalives_interval = 10;

坑 5：DROP TABLE IF EXISTS警告刷屏

现象：日志里满屏table "xxx" does not exist, skipping。

原因：这是include drop选项的正常行为——目标库是空的，删表命令自然找不到表。属于预期噪音，不是错误。

坑 6：CSV 里带引号但字段没闭合

现象：字段值被截断或列错位。

解法：按文件实际方言配置解析，如fields optionally enclosed by '"'、fields escaped by double-quote、fields not enclosed，必要时用csv escape mode调整转义策略。

七、进阶技巧：把 pgloader 用到飞起

1. 用环境变量让命令文件可移植。命令文件支持 Mustache 模板，能读取进程环境变量：

export DBPATH=sqlite/sqlite.db pgloader ./sqlite-env.load

命令文件里写成from '{{DBPATH}}'，同一份.load就能在不同环境间复用，密码、路径这类敏感信息也不必硬编码。也可以用--context file.ini把 INI 文件当作模板上下文。

2. 按表名批量筛选迁移范围。大库不必全量搬，用正则精确圈定：

INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor' EXCLUDING TABLE NAMES MATCHING ~<ory>

正则支持多种成对定界符（~//、~[]、~<>等），选不与表达式冲突的那组即可。

3. 加载前先摘索引、加载后并行重建。在WITH里同时启用drop indexes与max parallel create index = 2，让索引构建阶段充分吃满多核，主键从唯一索引回填生成，整体提速明显。

4. 用管道流式处理超大文件。对于 pgloader 不认识或不宜落盘的压缩格式，用 Unix 管道把解压和导入串起来，系统负责缓冲，pgloader 负责把数据直接喂给 PostgreSQL：

curl -sL http://example.com/data.csv.gz \ | gunzip -c \ | pgloader --type csv --field "a,b,c" - postgresql:///db?tablename=t

5. 生产上线前必做--dry-run演练。该模式只验证两端连接、打印将要执行的计划而不碰数据，再配合--logfile与--summary，把每次迁移都沉淀成可审计的记录。

6. 保留错误现场，回填缺失数据。迁移完成不等于结束——把/tmp/pgloader/下的 reject 文件当资产：逐行修复后可用同样的命令文件重跑一次，pgloader 的幂等设计（配合truncate或include drop）让"补跑"几乎零成本。

八、资源导航与收尾

• 入门导读：仓库根目录的README.md给出了 v4 的定位、安装方式和两个最小示例；docs/quickstart.rst是官方快速上手手册，覆盖 CSV、stdin、HTTP 源、SQLite、MySQL、DBF 六类快速用法；
• 完整参考：docs/command.rst是命令语言全参考，涉及FROM/INTO/WITH/SET/CAST等所有子句与批量行为参数；docs/ref/目录按 CSV、DBF、fixed、IXF、MySQL、MSSQL、SQLite、transforms 等主题拆开细讲；
• 教程：docs/tutorial/提供手把手的 CSV、SQLite、MySQL 迁移教程，配了真实数据和运行输出；
• 测试资产：test/目录里躺着一批官方.load示例文件，这是学习命令写法的金矿——几乎每种特性都有对应的可运行样例；
• 问题反馈：仓库内的ISSUE_TEMPLATE.md说明了如何规范地提交问题；TODO.md记录了官方规划中的功能。

pgloader 的意义不在于"又一个数据迁移脚本"，而在于它把迁移中最容易出事的脏数据、类型映射、并发控制这些环节标准化、可复现了。下次再有人问你"把 MySQL 搬到 PostgreSQL 要多久"，你可以底气十足地回一句：一条命令的时间。

从今天手边最小的一张表开始，跑通它，感受一次"迁库如搬文件"的畅快——然后你大概率就回不去了。🚀

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