被一条坏数据卡住整个库用 pgloader 把数据迁移变成一次重试的艺术【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloaderpgloader 是一款面向 PostgreSQL 的数据迁移与加载工具它把 COPY 命令的全有或全无改造成坏数据进隔离区、好数据照常入库让从 MySQL、SQLite、CSV 等来源迁移数据变成一条命令的事。本文从一个真实翻车场景出发带你掌握它最值钱的批量重试机制、配置文件玩法与避坑经验。一次让我彻夜加班的数据迁移事故2023 年的一天我接到一个任务把一台跑了五年业务的老 MySQL 库迁到新机房。数据量不大也就 800 万行但表结构混乱、历史数据脏得离谱——日期是0000-00-00金额字段里混着字母编码从 latin1 到 utf8 应有尽有。我最初用的是最朴素的方案pg_dump全量导出再psql导入。结果第一次跑就在第 3 万行撞上一个格式错误整个导入戛然而止。我修掉一条再跑又卡在另一条上。如此往复了十来轮凌晨三点我盯着终端里那段 CONTEXT 报错意识到用事务性的加载方式迁移脏数据本质上是在和无穷无尽的坏行玩打地鼠游戏。后来同事递过来一个工具pgloader。两条命令跑了 40 分钟干净利落地完成。它还交给我两个文件一个放着所有被拒的行一个记着每条拒绝的原因。这就是它最核心的设计哲学——好的迁移工具不是不犯错而是知道怎么和错误共存。为什么 COPY 和 pgloader 的差别这么大PostgreSQL 原生的 COPY 是一次事务只要你这条数据里有一个字符不对整批数据直接回滚。数据库这样设计是为了保证一致性但迁移场景恰恰最怕这种洁癖。而 pgloader 的做法是把数据切成一批批默认 25000 行流式送进 COPY。当某一行出错它读取 PostgreSQL 返回的CONTEXT: COPY errors, line 3这类信息把坏行单独挑出来记入 reject 文件把剩下没问题的部分重新提交。你只需要事后看一眼 reject 文件就知道哪些数据病在哪里。特性pgloader原生 COPYpg_dump psql遇到坏行的处理隔离坏行、继续加载整批回滚整个进程中断数据源种类CSV、DBF、IXF、固定宽度、MySQL、SQLite、SQL Server仅文件仅 PostgreSQL迁移时改数据内置转换规则可现场清洗不支持需预处理建表与索引自动建表、建索引、还原约束需手动需手动加载速度并行批次流式 COPY单线程视情况一句话总结COPY 适合干净的库pgloader 适合真实世界里的库。第一课从一条命令看懂它的工作方式安装好后Linux 下可从源码make编译或直接使用官方发布的 JAR 包最简单的场景是整库迁移。以下命令把 SQLite 文件里的所有表搬进新库# 先建好目标数据库 createdb mydatabase # 从 SQLite 迁移到 PostgreSQL一条命令完成建表导数据 # pgloader 会自动读取 SQLite 的表结构、生成对应建表语句、再流式灌入数据 java -jar pgloader.jar ./test/sqlite/sqlite.db postgresql:///mydatabase迁移完成时终端会打印一份统计表每张表读了多少行、写了多少行、被拒绝多少行、耗时多少。建议第一件事不是看成功了多少而是看被拒了多少。有被拒的行很正常关键是你得知道它们被放在了哪里。再来看一个文件导入的例子这行命令把带表头的 CSV 灌进一个已存在的表# --type 声明文件格式--field 按顺序声明每一列 # --with 里声明分隔符和跳过的表头行数 # 目标 URI 里的 ?tablenamematching 指定要写入哪张表 java -jar pgloader.jar --type csv \ --field id --field name --field email \ --with skip header 1 \ --with fields terminated by , \ people.csv \ postgresql:///mydb?tablenamepeople命令行模式适合一次性的简单导入一旦你的迁移需要清洗数据、建索引、调参数就该换用配置文件了。第二课用 .load 配置文件接管复杂迁移当迁移进入专业级阶段你会需要一种更完整的表达方式。pgloader 定义了一套自己的 DSL语法长得像 SQL放在.load文件里。看一个真实项目里的例子-- 完整迁移一个 MySQL 库结构、索引、外键、序列全部还原 LOAD DATABASE FROM mysql://rootlocalhost/f1db?useSSLfalse INTO postgresql:///plop就这么六行MySQL 里的表结构会被翻译成 PostgreSQL 方言数据以并行批次流入外键和索引在数据落库后才重建——因为先建索引再灌数据会慢很多倍。对于文件类数据配置文件能做得更精细。下面这段来自官方测试用例它干了一件很漂亮的事把 CSV 里的经纬度两个字段当场拼成一个 PostgreSQL 的point类型LOAD CSV FROM data/2013_Gaz_113CDs_national.txt -- 源文件 HAVING FIELDS (usps, geoid, aland, awater, aland_sqmi, awater_sqmi, intptlat, intptlong) -- 声明源列 INTO postgresql:///pgloader TARGET TABLE districts TARGET COLUMNS ( usps, geoid, aland, awater, aland_sqmi, awater_sqmi, -- 把纬度经度两列在飞行中组装成 PG 的 point 类型 location point using (format nil (~a,~a) intptlong intptlat) ) WITH truncate, -- 每次先清空目标表保证可重复执行 skip header 1, -- 跳过头行 batch rows 200, -- 每批 200 行 batch size 1024 kB -- 每批最大 1MBTARGET COLUMNS ... USING是 pgloader 最灵活的地方你可以在这里调用任意转换函数实现读进来 10 列、写进去 8 列的投影或者当场把字符串拼成 JSON、把时间戳格式化——数据清洗在迁移时就完成了事后不用再写一堆 UPDATE。第三课三个你迟早会踩的坑我把自己和社区里常见的翻车点整理成一份避坑清单按优先级排序坑一字符集不对乱码一片。老系统尤其是。latin1 编码的 CSV 直接用默认设置导入中文会变问号。解决办法在配置里显式声明SET client_encoding to latin1或者源文件本身就是 UTF-8 时别忘检查数据库端的编码。坑二把on error resume next当万能药。这个选项让 pgloader 遇错继续文件类加载默认开启。但对数据库迁移MySQL、SQLite默认反而要保证过程可复现所以官方更推荐先用on error stop跑通全量把 cast 规则和脏数据修好最后再来一次干净的执行。先用严格模式跑通再用宽容模式兜底顺序别反。坑三大字段拖垮批次重试。当某一行很大比如 TEXT 里存了整个 HTML批次的隔离和重试会放大开销。如果你的数据单行体积惊人把batch size调小、batch rows也调小避免一个巨型坏行把整个批次的内存撑爆。坑四大迁移别默认建索引。表几十张、数据千万级时边灌数据边建索引会严重拖慢进度。正确姿势是用create indexes让 pgloader 先导数据、再统一建索引配合workers和concurrency参数调出合适的并行度。金句迁移项目的成败从来不取决于好数据跑得多快而取决于坏数据处理得多体面。隐藏技巧三个让迁移效率翻倍的玩法1. 从标准输入流式加载。pgloader 的源文件位置可以写-表示读标准输入。这意味着你可以把解压和导入串成一个管道根本不用先解出中间文件# gunzip 解压后直接经管道喂给 pgloader省掉临时文件的磁盘 IO gunzip -c source.csv.gz | java -jar pgloader.jar --type csv \ --field id,name,email --with skip header 1 \ - postgresql:///mydb?tablenamepeople2. 让导入具备可重跑性。在WITH里加truncate再配合BEFORE LOAD DO $$ drop table if exists ... $$你的整个导入流程就能反复执行而不会越积越多。这在调试配置阶段尤其救命。3. 用--dry-run只查不搬。迁移前先用这个参数做一次连接体检它会验证源和目标都能连通、参数配置是否正确但不会动任何数据。上线前跑一遍 dry-run是成本最低的保险。资源与学习路径如果你打算深入最有效率的是直接看这个仓库里的三类东西官方文档与快速入门docs/quickstart.rst适合第一遍读docs/command.rst完整收录了 DSL 语法和所有 WITH 选项。真实可跑的示例配置test/目录下有几十个.load文件覆盖 CSV、DBF、SQLite、MySQL、MS SQL 等几乎全部场景。遇到不懂的配置项直接去这些例子里搜它的用法比读语法文档直观得多。核心实现源码想理解批次重试到底怎么工作的看src/pg-copy/下的批次相关文件想改转换规则去src/sources/按数据源类型找对应的 cast 规则文件。想自己编译最新版克隆仓库后执行make即可产物在./build/bin/下。仓库地址https://gitcode.com/gh_mirrors/pg/pgloader。迁移这件事值得被认真对待回到开头那个让我加班到凌晨的 MySQL 迁移pgloader 帮我做对的其实只有一件事——它把迁移失败从一场事故降级成了一组可审计的异常数据。那 800 万行里最后有 2000 多行进了 reject 文件我照着.log里的原因逐个修复第二次重跑零拒绝一次通过。如果你正打算把数据搬到 PostgreSQL或者手头积压着永远修不完的脏 CSV我的建议很直接克隆这个项目跑一个最小的 SQLite 示例先亲眼看一次好数据入库、坏数据进 reject的过程。你会在十分钟内明白为什么它能成为 PostgreSQL 生态里被反复推荐的那个迁移利器。好的迁移是让数据带着问题进来带着答案离开。【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloader创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 被一条坏数据卡住整个库？用 pgloader 把数据迁移变成一次重试的艺术
被一条坏数据卡住整个库用 pgloader 把数据迁移变成一次重试的艺术【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloaderpgloader 是一款面向 PostgreSQL 的数据迁移与加载工具它把 COPY 命令的全有或全无改造成坏数据进隔离区、好数据照常入库让从 MySQL、SQLite、CSV 等来源迁移数据变成一条命令的事。本文从一个真实翻车场景出发带你掌握它最值钱的批量重试机制、配置文件玩法与避坑经验。一次让我彻夜加班的数据迁移事故2023 年的一天我接到一个任务把一台跑了五年业务的老 MySQL 库迁到新机房。数据量不大也就 800 万行但表结构混乱、历史数据脏得离谱——日期是0000-00-00金额字段里混着字母编码从 latin1 到 utf8 应有尽有。我最初用的是最朴素的方案pg_dump全量导出再psql导入。结果第一次跑就在第 3 万行撞上一个格式错误整个导入戛然而止。我修掉一条再跑又卡在另一条上。如此往复了十来轮凌晨三点我盯着终端里那段 CONTEXT 报错意识到用事务性的加载方式迁移脏数据本质上是在和无穷无尽的坏行玩打地鼠游戏。后来同事递过来一个工具pgloader。两条命令跑了 40 分钟干净利落地完成。它还交给我两个文件一个放着所有被拒的行一个记着每条拒绝的原因。这就是它最核心的设计哲学——好的迁移工具不是不犯错而是知道怎么和错误共存。为什么 COPY 和 pgloader 的差别这么大PostgreSQL 原生的 COPY 是一次事务只要你这条数据里有一个字符不对整批数据直接回滚。数据库这样设计是为了保证一致性但迁移场景恰恰最怕这种洁癖。而 pgloader 的做法是把数据切成一批批默认 25000 行流式送进 COPY。当某一行出错它读取 PostgreSQL 返回的CONTEXT: COPY errors, line 3这类信息把坏行单独挑出来记入 reject 文件把剩下没问题的部分重新提交。你只需要事后看一眼 reject 文件就知道哪些数据病在哪里。特性pgloader原生 COPYpg_dump psql遇到坏行的处理隔离坏行、继续加载整批回滚整个进程中断数据源种类CSV、DBF、IXF、固定宽度、MySQL、SQLite、SQL Server仅文件仅 PostgreSQL迁移时改数据内置转换规则可现场清洗不支持需预处理建表与索引自动建表、建索引、还原约束需手动需手动加载速度并行批次流式 COPY单线程视情况一句话总结COPY 适合干净的库pgloader 适合真实世界里的库。第一课从一条命令看懂它的工作方式安装好后Linux 下可从源码make编译或直接使用官方发布的 JAR 包最简单的场景是整库迁移。以下命令把 SQLite 文件里的所有表搬进新库# 先建好目标数据库 createdb mydatabase # 从 SQLite 迁移到 PostgreSQL一条命令完成建表导数据 # pgloader 会自动读取 SQLite 的表结构、生成对应建表语句、再流式灌入数据 java -jar pgloader.jar ./test/sqlite/sqlite.db postgresql:///mydatabase迁移完成时终端会打印一份统计表每张表读了多少行、写了多少行、被拒绝多少行、耗时多少。建议第一件事不是看成功了多少而是看被拒了多少。有被拒的行很正常关键是你得知道它们被放在了哪里。再来看一个文件导入的例子这行命令把带表头的 CSV 灌进一个已存在的表# --type 声明文件格式--field 按顺序声明每一列 # --with 里声明分隔符和跳过的表头行数 # 目标 URI 里的 ?tablenamematching 指定要写入哪张表 java -jar pgloader.jar --type csv \ --field id --field name --field email \ --with skip header 1 \ --with fields terminated by , \ people.csv \ postgresql:///mydb?tablenamepeople命令行模式适合一次性的简单导入一旦你的迁移需要清洗数据、建索引、调参数就该换用配置文件了。第二课用 .load 配置文件接管复杂迁移当迁移进入专业级阶段你会需要一种更完整的表达方式。pgloader 定义了一套自己的 DSL语法长得像 SQL放在.load文件里。看一个真实项目里的例子-- 完整迁移一个 MySQL 库结构、索引、外键、序列全部还原 LOAD DATABASE FROM mysql://rootlocalhost/f1db?useSSLfalse INTO postgresql:///plop就这么六行MySQL 里的表结构会被翻译成 PostgreSQL 方言数据以并行批次流入外键和索引在数据落库后才重建——因为先建索引再灌数据会慢很多倍。对于文件类数据配置文件能做得更精细。下面这段来自官方测试用例它干了一件很漂亮的事把 CSV 里的经纬度两个字段当场拼成一个 PostgreSQL 的point类型LOAD CSV FROM data/2013_Gaz_113CDs_national.txt -- 源文件 HAVING FIELDS (usps, geoid, aland, awater, aland_sqmi, awater_sqmi, intptlat, intptlong) -- 声明源列 INTO postgresql:///pgloader TARGET TABLE districts TARGET COLUMNS ( usps, geoid, aland, awater, aland_sqmi, awater_sqmi, -- 把纬度经度两列在飞行中组装成 PG 的 point 类型 location point using (format nil (~a,~a) intptlong intptlat) ) WITH truncate, -- 每次先清空目标表保证可重复执行 skip header 1, -- 跳过头行 batch rows 200, -- 每批 200 行 batch size 1024 kB -- 每批最大 1MBTARGET COLUMNS ... USING是 pgloader 最灵活的地方你可以在这里调用任意转换函数实现读进来 10 列、写进去 8 列的投影或者当场把字符串拼成 JSON、把时间戳格式化——数据清洗在迁移时就完成了事后不用再写一堆 UPDATE。第三课三个你迟早会踩的坑我把自己和社区里常见的翻车点整理成一份避坑清单按优先级排序坑一字符集不对乱码一片。老系统尤其是。latin1 编码的 CSV 直接用默认设置导入中文会变问号。解决办法在配置里显式声明SET client_encoding to latin1或者源文件本身就是 UTF-8 时别忘检查数据库端的编码。坑二把on error resume next当万能药。这个选项让 pgloader 遇错继续文件类加载默认开启。但对数据库迁移MySQL、SQLite默认反而要保证过程可复现所以官方更推荐先用on error stop跑通全量把 cast 规则和脏数据修好最后再来一次干净的执行。先用严格模式跑通再用宽容模式兜底顺序别反。坑三大字段拖垮批次重试。当某一行很大比如 TEXT 里存了整个 HTML批次的隔离和重试会放大开销。如果你的数据单行体积惊人把batch size调小、batch rows也调小避免一个巨型坏行把整个批次的内存撑爆。坑四大迁移别默认建索引。表几十张、数据千万级时边灌数据边建索引会严重拖慢进度。正确姿势是用create indexes让 pgloader 先导数据、再统一建索引配合workers和concurrency参数调出合适的并行度。金句迁移项目的成败从来不取决于好数据跑得多快而取决于坏数据处理得多体面。隐藏技巧三个让迁移效率翻倍的玩法1. 从标准输入流式加载。pgloader 的源文件位置可以写-表示读标准输入。这意味着你可以把解压和导入串成一个管道根本不用先解出中间文件# gunzip 解压后直接经管道喂给 pgloader省掉临时文件的磁盘 IO gunzip -c source.csv.gz | java -jar pgloader.jar --type csv \ --field id,name,email --with skip header 1 \ - postgresql:///mydb?tablenamepeople2. 让导入具备可重跑性。在WITH里加truncate再配合BEFORE LOAD DO $$ drop table if exists ... $$你的整个导入流程就能反复执行而不会越积越多。这在调试配置阶段尤其救命。3. 用--dry-run只查不搬。迁移前先用这个参数做一次连接体检它会验证源和目标都能连通、参数配置是否正确但不会动任何数据。上线前跑一遍 dry-run是成本最低的保险。资源与学习路径如果你打算深入最有效率的是直接看这个仓库里的三类东西官方文档与快速入门docs/quickstart.rst适合第一遍读docs/command.rst完整收录了 DSL 语法和所有 WITH 选项。真实可跑的示例配置test/目录下有几十个.load文件覆盖 CSV、DBF、SQLite、MySQL、MS SQL 等几乎全部场景。遇到不懂的配置项直接去这些例子里搜它的用法比读语法文档直观得多。核心实现源码想理解批次重试到底怎么工作的看src/pg-copy/下的批次相关文件想改转换规则去src/sources/按数据源类型找对应的 cast 规则文件。想自己编译最新版克隆仓库后执行make即可产物在./build/bin/下。仓库地址https://gitcode.com/gh_mirrors/pg/pgloader。迁移这件事值得被认真对待回到开头那个让我加班到凌晨的 MySQL 迁移pgloader 帮我做对的其实只有一件事——它把迁移失败从一场事故降级成了一组可审计的异常数据。那 800 万行里最后有 2000 多行进了 reject 文件我照着.log里的原因逐个修复第二次重跑零拒绝一次通过。如果你正打算把数据搬到 PostgreSQL或者手头积压着永远修不完的脏 CSV我的建议很直接克隆这个项目跑一个最小的 SQLite 示例先亲眼看一次好数据入库、坏数据进 reject的过程。你会在十分钟内明白为什么它能成为 PostgreSQL 生态里被反复推荐的那个迁移利器。好的迁移是让数据带着问题进来带着答案离开。【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloader创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考