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传统 COPY脏数据处理单独记录到拒绝文件主流程继续一行出错全表中止数据源类型数据库MySQL/SQLite/MSSQL 多种文件格式仅本地文件数据清洗内置转换规则 自定义 CAST需提前预处理事务策略可配置支持断点续传思路全有或全无加载性能并行 worker、批量提交单线程顺序写入这个差异是 pgloader 存在的根本理由。它的设计哲学很明确迁移过程中出错是常态工具的任务是把错误的影响控制在最小范围而不是让整个迁移停摆。案例一SQLite 迁移从一条命令开始先从最简单的场景入手建立信心。把 SQLite 数据库整个搬进 PostgreSQLpgloader 只需要一条命令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://rootlocalhost/sakila postgresql://localhost/sakila但生产环境没人会这么裸奔。真实项目里你需要控制建表策略、并行度、类型转换规则这时候就要上配置文件。下面这份摘取自 docs/ref/mysql.rst 的示例是 MySQL 迁移配置的全家福参考LOAD DATABASE FROM mysql://rootlocalhost/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?tablenamematching注意末尾那个?tablenamematching——目标表名是拼在连接串里的。命令行模式下--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 上的文件把源路径换成 URLpgloader 会自动下载并解压归档文件同样一条命令。跳过表头官方测试数据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 会继续加载并把错误行写入单独文件只有你明确需要严格模式时才启用它。性能翻倍的五参数调优清单同样的迁移任务配置得当与否耗时可能差一个数量级。记住下面这五个参数基本就够用了workers并行读取线程数建议按 CPU 核数设置如workers 8。concurrency同时打开的 PostgreSQL 连接数。workers 和 concurrency 不是一回事——前者管读取后者管写入。batch rows / batch size每批提交的行数与体积上限。调大能减少提交次数但别超过内存承受力。rows per range数据库迁移时按主键范围分片读取适合大表。multiple readers per thread让单个线程可以并发读多个源配合上面参数一起用。关于索引的策略也很关键create indexes默认是先灌数据、后建索引这比边灌边建快得多。加载期间还可以用disable triggers跳过触发器灌完再手动处理约束。版本选择用 v3 还是 v4如果你在部署时看到新旧两个版本这里有个快速决策参考v4Clojure/JVM 重写版单 JAR 分发包需要 Java 21连接串统一用 JDBC 风格jdbc:mysql://、jdbc:postgresql://SSL 通过?sslmoderequire控制。src/、clojure/目录就是 v4 的源码主体。v3Common Lisp 版需要编译Makefile里的DYNSIZE参数控制内存映像大小适合需要make DYNSIZE1024这类定制的老环境。v4 对.load文件语法和命令行参数完全兼容理论上可以无缝替换。新项目直接上 v4老项目建议在测试环境验证后再切。动手之前记住这几条铁律先 dry-run再真跑。连接串写错是最常见的翻车原因--dry-run一秒就能发现。小表验证全流程。挑一两张小表跑通确认类型映射和重命名规则符合预期。先备份后迁移。源库和目标库都备份这是数据工程最基本的素养。看日志要看 summary。迁移完--summary生成的报告会告诉你每个表成功多少行、失败多少行、耗时多少这是验收依据。遇到格式问题查文档。每种数据源在 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创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考