pgloader 迁移数据到 PostgreSQLSQLite/MySQL/CSV 实操与 3 个调优参数【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloader上周我们要把一台 MySQL 5.7 业务库和一批 latin1 编码的历史 CSV 挪进 PostgreSQL原生 COPY 一遇到脏行就整表回滚手工写 ETL 脚本又得维护好几天。最后是用pgloader完成的它读.load配置文件或连接串把 SQLite、MySQL、SQL Server 以及 CSV、DBF、固定宽度文件的数据迁入 PostgreSQL坏行写入 reject 文件而不是中断整个加载。这篇文章按“装好 → 跑通 → 调参 → 排错”的顺序把这套流程完整走一遍。一句话看懂 pgloader 是什么、适合谁pgloader 是跑在一条命令上的 PostgreSQL 数据迁移工具底层走 COPY 协议。三件核心事源端支持 SQLite、MySQL、MSSQL、PG 同构迁移以及 CSV / DBF / 固定宽度文件整库结构表、索引、外键和数据一次搬完加载过程中的坏行被隔离进 reject 文件正常行继续写入不会因单行脏数据回滚整张表内置 CAST 规则把 MySQL 的0000-00-00之类无法进入 PG 日历的值自动转成NULL。适合两类人要整库换数据库的 DBA定期批量灌历史数据文件的后端或数据工程师。只导一个干净的本地 CSV 且不想装新工具的场景直接psql \copy更快pgloader 的价值在“脏数据 结构迁移”这个组合上。安装到首次跑通一条命令迁移一个 SQLite 库pgloader v4 是单 JAR 包要求 Java 21 或更高版本Temurin、OpenJDK 均可没有本地 C 依赖。# 下载 v4 版单文件 JARURL 固定指向最新开发构建 curl -L -o pgloader.jar https://gitcode.com/gh_mirrors/pg/pgloader/raw/master/pgloader.jar # 验证运行环境应输出版本号 java -jar pgloader.jar --version如果提示 class file 版本错误就是本机 Java 低于 21先升级 JDK。接着写一个最小迁移目标库先建空库源用仓库自带的示例 SQLite 文件也可以换成你自己的.db文件。createdb newdb # 一条命令完成建表、迁索引和外键、灌数据 java -jar pgloader.jar ./test/sqlite/sqlite.db postgresql:///newdb结束时终端会打印每张表的汇总读入行数、写入库行数、reject 行数、耗时。判断成功看三点最后汇总里 rejected 为 0或有明确解释、日志文件默认在/tmp/pgloader/下没有 ERROR 级记录、行数对得上# 源端行数 sqlite3 ./test/sqlite/sqlite.db SELECT count(*) FROM album; # 目标端行数两个数字应相等 psql -d newdb -c SELECT count(*) FROM album;行数一致、reject 为 0首次迁移就算跑通。跑通之后你会发现真正耗时的是把.load文件里的选项配明白下面看三个高频场景。三个高频场景MySQL 整库、CSV 脏数据、类型转换场景一MySQL 整库迁移先 dry-run 再实跑前提目标 PG 已建好空库源库账号对要迁的库有 SELECT 权限。关键操作先只检连接不灌数据确认无误再实跑。createdb erp_pg # 只检查两端连接和权限不写任何数据 java -jar pgloader.jar --dry-run mysql://your-useryour-host/erp_db postgresql:///erp_pg # 正式迁移结构表、索引、外键、注释和数据一次完成 java -jar pgloader.jar mysql://your-useryour-host/erp_db postgresql:///erp_pg验证dry-run 输出里每个源表都报连接成功实跑结束对比information_schema.tables的表数量与select count(*)抽检 2~3 张关键表。注意存储过程和触发器不会自动转换这部分需要人工改写pgloader 只负责表和索引。场景二CSV 脏数据导入坏行进 reject 文件前提目标表已存在可以先用 DDL 建好文件里可能有编码异常的行。关键操作用.load文件声明文件特征打开 reject 文件。LOAD CSV FROM data/2023_sales.csv (x, y, sale_date, amount) INTO postgresql:///your-database?sales_data WITH skip header 1, fields terminated by ,, fields optionally enclosed by , reject file /tmp/sales_reject -- 坏行落盘不中断导入验证跑完看终端汇总里的 rejected 行数再直接打开 reject 文件逐行确认是不是真的脏数据。比如 100 万行只 reject 37 行说明隔离机制正常工作如果 rejected 接近总量别继续跑先回去查编码或分隔符配置。场景三CAST 规则处理 MySQL 零日期前提源库来自 MySQLdate/datetime列里存在0000-00-00。关键操作在.load文件里加 CAST 段零值转 NULL。CAST table orders column created_at to date using default, table orders column amount to numericpgloader 默认转换规则会把0000-00-00映射为NULLusing default显式走默认规则即可对个别列要自定义转换比如把电话号强转 text时在该列上写using (expression)。完整 CAST 语法可参考仓库内 docs/ref/transforms.rst。验证迁移后查目标列的 NULL 比例是否与源库零日期占比一致。类型转换只解决了“能不能进去”大库迁移时“进得快不快、出错回滚多大范围”由下一节的参数决定。关键参数默认值是多少、什么时候调先给全默认值以下均摘自仓库源码src/params.lispv3 与 v4 一致参数默认值何时调建议值batch rows25000唯一约束冲突、想减小回滚范围10000~50000batch size20MB单行很宽长文本列不超过 50MBworkers1源是多张百万行级大表2~8对齐 CPU 核数prefetch rows100000跨机房读远程源库200000值得单独讲三个。workers何时加并行。它控制并行加载的线程数默认 1即单线程串行灌表。如果源是几十张百万行级的表、机器有 4 核以上空闲调到 4各表可以并行 COPY如果源就 1~2 张小表或目标库磁盘是瓶颈iostat看 util 接近 100%加了只会让 IO 更挤保持 1 即可。batch rows何时调回滚粒度。每个 COPY 事务提交一批默认 25000 行。批内任何一行违反约束整批都回滚——这正是脏数据多的库反复报错时好行跟着陪葬的原因。冲突频繁就把 25000 减到 10000数据很干净、单行很宽可加到 50000 减少提交次数。prefetch rows何时预读。读取线程提前拉入队列的行数默认 100000。跨网络读 MySQL/MSSQL 时网络往返占比大调到 200000 能填满管道读本地文件时磁盘速度远快于网络默认值够用不必动。避坑手册三个高频报错现象invalid byte sequence for encoding UTF8: 0xa3。原因源数据是 latin1常见于欧洲老系统导出的 CSVPG 目标库只收 UTF8。修复LOAD CSV FROM data/latin1.csv (id, memo) INTO postgresql:///your-database?legacy_data WITH encoding latin1 -- 源端声明字符集边读边转码现象duplicate key value violates unique constraint且每次丢的行数比坏行数多很多。原因违反约束发生在哪一批哪一批 25000 行整体回滚好行也被丢掉。修复整表重灌的场景在 WITH 里加truncate先清空目标表增量合并场景把batch rows降到 10000 缩小陪葬范围同时把坏行送进 reject 文件单独处理。现象could not connect to server或连接超时。原因连接串里的端口、sslmode或账号权限不对不是 pgloader 本身的问题。修复# 先用 dry-run 单独验证两端连通性和 SELECT 权限 java -jar pgloader.jar --dry-run mysql://your-useryour-host/your-database postgresql:///your-databasedry-run 报的错和实跑一致修好连接再重跑省得在日志文件里翻。选型对比pgloader、原生 COPY、通用 ETL维度pgloader原生 COPY / \copy通用 ETL 工具适用数据量小表到 TB 级整库迁移单表/单文件GB 级以内任意长流程管道脏数据处理隔离到 reject 文件继续加载单行报错整表中止视工具配置普遍需要写代码结构迁移表、索引、外键自动转换不支持只灌数据需逐个映射学习成本低.load文件几十行零高判断条件一句话整库迁移或一批文件里有脏数据用 pgloader只有一个干净 CSV 要进现有表psql \copy更快要跨异构库做周期性管道编排再上 ETL 平台。行前三件事目标库磁盘可用空间 ≥ 源数据量的 2 倍要容纳表、临时文件和后续索引用--dry-run验证过两端连接、账号权限和 CAST 规则关键表准备了一条select count(*)对比 SQL迁移完逐表核对官方资源仓库内本地文档快速上手、CSV 加载参考、转换规则参考、命令语法总览。【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloader创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考