pgloader 数据迁移实战：一条命令搞定 SQLite、MySQL、CSV 到 PostgreSQL 的完整教程

pgloader 数据迁移实战一条命令搞定 SQLite、MySQL、CSV 到 PostgreSQL 的完整教程【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloader如果你手上有一个 SQLite 文件、一套 MySQL 库、或一堆 CSV 数据而老板只给了你一句迁到 PostgreSQL 去那么你需要的工具就一个pgloader。它把自己定位成 Migrate to PostgreSQL in a single command!单命令迁移到 PostgreSQL核心价值就一句话——别人要写脚本熬夜搞的脏活它用一条命令接管。pgloader 最大的魅力不是快而是扛得住脏数据传统 COPY 遇到一行坏数据就整体中止pgloader 却会把坏行写进独立的拒绝日志好数据继续往里灌。这篇文章会用从新手到熟练工的旅程式路线带你走完安装、首个示例、命令行直连、配置文件进阶、性能调优和排错的全过程。全程代码可直接复制运行。出发前先搞清楚 pgloader 到底替你干了什么在动手之前你需要建立一张心智地图。pgloader 本质上是一个管道工它把五花八门的输入MySQL/SQLite/MSSQL 数据库CSV/DBF/IXF/固定宽度文件甚至压缩包和 HTTP 地址统一接到 PostgreSQL 这一个出口。它的分工大致是三层源层负责连接、读取、解析支持 schema 自动发现比如读 MySQL 时自动把表结构搬过来转换层内置一套类型映射和 CAST 规则还能让你自定义表达式做清洗目标层负责建表、建索引、外键、序列、并发写入和错误回收。举个实际例子。传统做法是导出 SQL → 手改类型 → 分批导入 → 写异常处理。用 pgloader一句pgloader mysql://rootlocalhost/app postgresql://postgreslocalhost/app就全部完成中间还会自动处理自增列、时区、编码这类最容易翻车的细节。这里有个小窍门别把 pgloader 想象成加强版 psql它是一条流水线。流水线的每个环节都可用参数微调这正是它强大的地方也是后面几节要带你逐个征服的。第一站5 分钟完成安装先跑起来再说安装是整趟旅程里最简单的一步根据你的系统二选一。方式一系统包安装Debian/Ubuntu 推荐sudo apt-get install pgloader装完直接验证版本pgloader --version方式二源码编译适合其他发行版或想调内存git clone https://gitcode.com/gh_mirrors/pg/pgloader cd pgloader make编译产物在./build/bin/pgloader。如果你机器内存紧张编译时可以用DYNSIZE控制 pgloader 默认内存占用make DYNSIZE1024 # 让 pgloader 默认使用约 1GB 内存注意源码编译要求系统已装好 SBCL 等 Common Lisp 环境和 make具体依赖清单看项目根目录的 INSTALL.md。无论哪种方式装完先跑一遍pgloader --help把--type、--with、--field、--cast这些高频参数扫一眼它们就是后面命令的乐高积木。第二站30 秒跑通第一个迁移SQLite 到 PostgreSQL别急着背参数先获得一次哇真的可以的成就感。SQLite 文件迁移是 pgloader 最拿手的场景因为表结构就藏在文件里几乎零配置。适用环境已安装 pgloader本机有 PostgreSQL 服务。# 1. 创建目标库 createdb mydatabase # 2. 单命令迁移SQLite 文件 → 本地库 pgloader sqlite:///path/to/your.db postgresql:///mydatabase跑完之后你会看到一行汇总读了多少行、写了多少行、报错几行、耗时几秒。就这么简单。pgloader 自动完成了 schema 分析、建表、建索引、序列重置。验证一下确认不是玄学psql mydatabase -c \dt这里有个值得说明的细节目标连接串postgresql:///mydatabase里连主机名都没写表示本机默认 socket。如果你迁移的是远程库写成postgresql://user:passwordhost:5432/mydatabase即可。第一个示例跑通后你可以把项目test/sqlite/目录下的示例库如 Chinook拿来反复练手迁移前先看一眼对应的.load配置文件那是官方给出的最佳姿势。第三站命令行直连 CSV——不写配置也能导入CSV 是数据分析场景的常客。如果目标表已经建好pgloader 允许你完全用命令行参数驱动省去写配置文件的麻烦。适用环境Linux/macOS 终端目标表已存在于 PostgreSQL。pgloader --type csv \ --field id,name,email,created_at \ --with truncate \ --with fields terminated by , \ --with skip header 1 \ ./data/users.csv \ postgresql:///mydb?tablenameusers逐行拆解一下别被反斜杠吓到--type csv声明源类型不加它 pgloader 会去猜猜错就报错--field声明每一列的名字可以一个参数带全部逗号分隔也可以写多个--field--with truncate写入前清空目标表适合全量重灌--with fields terminated by ,分隔符默认就是逗号但写上更保险--with skip header 1跳过 1 行表头目标串里的?tablenameusers告诉 pgloader 往哪张表写。还能玩出什么花样源文件用-表示从标准输入读这意味着可以接管道流式处理压缩文件gunzip -c users.csv.gz | pgloader --type csv \ --field id,name,email,created_at \ --with fields terminated by , \ - \ postgresql:///mydb?tablenameusers这种管道 stdin的思路在超大文件场景尤其好用不用解压落盘边解压边导入磁盘 IO 省一半。当然命令行模式也有天花板复杂类型转换、前后置 SQL、多源合并这些能力它给不了。这时候就该升级到配置文件了。第四站升级武器——手写一份 .load 配置文件pgloader 内置了一套迷你 DSL领域专用语言文件后缀习惯用.load。语法故意长得像 SQL让 DBA 有亲切感。这是整趟旅程最重要的一站值得花时间。先看一个 MySQL 全库迁移的完整配置我加了注释帮你对号入座LOAD DATABASE FROM mysql://root:secretlocalhost:3306/legacy_app INTO postgresql://postgres:secretlocalhost/app WITH include drop, create tables, create indexes, reset sequences, foreign keys, workers 4, concurrency 2 SET work_mem to 32MB, maintenance_work_mem to 64MB, client_encoding to UTF8 CAST type datetime to timestamptz, type tinyint when ( 1 precision) to boolean, type decimal to numeric using float-to-string BEFORE LOAD DO $$ create schema if not exists legacy; $$; AFTER LOAD DO $$ analyze verbose app; $$;保存为migrate.load执行方式同样是单条命令pgloader migrate.load几个关键段位一次讲透WITH 段控制迁移行为开关。create tables建表、create indexes建索引、reset sequences重置自增序列、foreign keys保留外键include drop表示目标里同名表先 DROP 再建配合反复调试非常好用。并发参数workers是读侧线程数concurrency是写侧连接数。SET 段给 pgloader 打开的每个 PostgreSQL 会话设置 GUC 参数比如调大work_mem提升排序性能。CAST 段类型映射规则MySQL 的datetime到 PG 是timestamptz、tinyint(1)转boolean、高精度decimal用float-to-string避免浮点失真。这些规则是从上到下顺序匹配命中即停所以顺序很重要。BEFORE/AFTER LOAD DO加载前后的钩子。典型的用法是BEFORE LOAD DO里建 schema 或清空表AFTER LOAD DO里跑ANALYZE刷新统计信息让优化器拿到新鲜数据。这里放一张命令行模式 vs 配置文件模式的对比表帮你决定什么场景用哪种维度命令行直连模式.load 配置文件模式上手成本低一条命令跑通中等需学 DSL 语法类型转换 CAST不支持支持规则可编程前后置 SQL不支持BEFORE/AFTER DO 任意 SQL复杂投影/清洗仅限列名映射支持 USING 表达式多源、定时复用每次手敲文件即文档可进仓库适用场景一次性快速导入正式迁移、反复执行一句话总结临时导入用命令行正式迁移写 .load 文件。把配置提交进 Git 仓库下次迁移直接复用还能给别人 review这才是专业选手的做法。第五站让脏数据洗白——CAST 规则与转换表达式数据迁移里 80% 的坑都出在类型和编码上。pgloader 的 CAST 机制就是为你止血用的。最常见的三个流行病和对应药方1. MySQL 的0000-00-00日期。PostgreSQL 不认这种零日期直接导入必崩。解决办法是加一条转换规则把零日期转成 NULLCAST type date drop not null drop default using zero-dates-to-null2.tinyint(1)是布尔值。MySQL 老库喜欢用tinyint(1)存布尔迁到 PG 应该变boolean否则下游程序判断 true会翻车CAST type tinyint when ( 1 precision) to boolean drop typemod3. 文本里藏着\0空字符。迁移后 PG 字段里带着看不见的\0查询、导出都可能出幺蛾子CAST type varchar to varchar keep typemod using remove-null-charactersCAST 不止能做类型映射配合using还能塞进任意 Lisp 表达式做深度清洗。你甚至可以用--load参数加载自己写的转换函数。换句话说只要你能想到的清洗逻辑几乎都能挂在迁移流水线上自动执行省去单独写清洗脚本的麻烦。更多内置规则清单可以在 docs/ref/mysql.rst 和 docs/ref/transforms.rst 里查到迁移前花十分钟翻一遍往往能帮你躲掉几个通宵。第六站让大库跑出小库的速度——性能调优三板斧迁移 100 万行和迁移 10 亿行是两种游戏。pgloader 的性能密码全在 WITH 段里三个参数吃透就够了。WITH batch rows 50000, -- 每个批次 5 万行 batch size 100MB, -- 或每批最多 100MB先到先触发 prefetch rows 100000, -- 预读缓冲区 workers 8, -- 源端读取线程 concurrency 4, -- 目标端写入连接 max parallel create index 2第一板斧批次大小。batch rows和batch size控制攒多少写一次。批次太小网络往返和提交开销吃垮你批次太大单批失败的回滚代价也大。经验值从 10000 行 / 10MB 起步观察内存再上调。第二板斧并发模型。workers管读取侧多线程concurrency管写入侧连接数。记住一条铁律PG 端能承受的连接数是有限的concurrency 不要盲目调大2~4 是常见安全区间。第三板斧索引策略。与其让 PG 一边插数据一边维护索引不如先不建索引灌数据、最后并行建。max parallel create index 2就是控制这个收尾阶段的并行度。如果目标表有触发器disable triggers能在加载期间关掉它们速度立竿见影前提是业务允许。实战中可以拿一张 1000 万行的表做基准测试默认参数跑一遍再按上面参数跑一遍对比总耗时。你会发现收益最大的往往不是加并发而是把 batch size 调大。别猜用数据说话。第七站出错了别慌——错误隔离与监控三板斧pgloader 之所以比 COPY 好用核心就在错误处理上。COPY 是一根筋一行报错全表回滚。pgloader 是隔离病房坏行进 reject 文件好行继续走。错误策略开关WITH on error resume next, -- 遇错继续默认行为 max errors 1000 -- 但最多容忍 1000 个错误如果改成on error stop行为就退化成 COPY 那种碰到就停适合必须零容忍的强一致场景。监控三件套# 实时看进度verbose 输出到控制台 pgloader --verbose migrate.load # 日志落盘事后可查 pgloader --logfile migration.log migrate.load # 干跑一遍只解析不执行用来验证配置 pgloader --dry-run migrate.load--dry-run是排错神器配置写错、文件找不到、SQL 语法有问题它都会在你真正动数据之前暴露出来。另外每次迁移结束时输出的汇总表读了/写了/报错的行数本身就是在做质量检查——读数和写数对不上就去看 reject 文件那里躺着所有被隔离的坏行和原因。常见问题速查三个高频报错与解法Q1报 No such file or directory 或路径带空格解析失败把路径用单引号包起来这是 pgloader 的命令行惯例。源文件路径支持单引号包裹和 shell 通配符实在不行就改用配置文件里的FILENAME MATCHING语法。Q2导入中文变成乱码十有八九是源文件编码没声明。CSV 用--encoding latin1或配置文件里SET client_encoding to UTF8MySQL 连接串没带?charset参数也可能导致编码错乱。原则是先确认源编码再指定目标编码。Q3MySQL 迁移时自增主键到 PG 变成普通 int检查 CAST 规则是否覆盖了自增列。标准做法是显式声明CAST type int with extra auto_increment to serial drop typemod没有这条规则时MySQL 的auto_increment可能被映射成普通integer下游插入就撞主键。老司机的避坑清单六个值得收藏的经验迁移前先备份。include drop会在目标库执行级联 DROP它不认识你不想删的表备份是最便宜的后悔药。先用小表验证 CAST 规则。类型映射是顺序匹配、命中即停一条写错的规则会静默污染一整列数据。大表分批迁。单表几亿行时用WHERE条件切片或按分区多次执行出问题能定点回滚不至于全盘重来。监控三件套上齐--verbose看实时进度、--logfile留底、--summary输出结构化统计。迁移完做一致性抽查行数对账COUNT(*) 抽样比对 检查 reject 文件行数三关全过才算完成。把 .load 文件当资产管理。带版本、带注释、可复现下次迁移或同事接手时它就是最好的操作手册。资源与延伸阅读再往深走的路标快速上手docs/quickstart.rst30 分钟读一遍命令行模式全覆盖命令与 DSL 参考docs/command.rst每个 clause 的语法都有说明各数据源细则MySQL 看 docs/ref/mysql.rstCSV 看 docs/ref/csv.rstSQLite 看 docs/ref/sqlite.rst还有 DBF、固定宽度、MSSQL 各有一篇官方可复现示例test/目录下全是 .load 配置和测试数据tests/里有基于 docker-compose 的完整迁移测试场景直接当实验场已知问题与路线图TODO.md 里能看到官方对未来的规划ISSUE_TEMPLATE.md 教你如何提交高质量 bug 报告。现在就动手三分钟完成你的第一次实战理论到此为止接下来是你的事。给你布置一个三分钟的作业用createdb demo建一个空库从项目test/sqlite/里挑一个.sqlite文件比如test_pk.db执行pgloader sqlite:///test/sqlite/test_pk.db postgresql:///demo用 psql 查看生成的表再跑一遍同样的命令体会include drop的作用。做完这四步你对 pgloader 的掌控已经超过了大多数只会 COPY 的人。然后去把你手头最想迁移的那份数据搬过来试试——工具是拿来解决问题的不是拿来收藏的。祝你迁移顺利早日下班。【免费下载链接】pgloaderMigrate to PostgreSQL in a single command!项目地址: https://gitcode.com/gh_mirrors/pg/pgloader创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考