pgloader: Usage, Options & Common CI Errors
pgloader bulk-loads and migrates data into PostgreSQL from many sources.
pgloader automates one-shot migrations (MySQL/SQLite/CSV to Postgres) in CI. Its failures are mostly connection URIs for two databases at once and type casts the default rules cannot infer.
What it does
pgloader reads from a source (MySQL, SQLite, CSV, dBase, or another Postgres) and loads it into PostgreSQL, creating the schema and casting types automatically. Simple loads run from the command line; complex ones use a .load command file.
Common usage
pgloader mysql://root:secret@127.0.0.1/app postgresql://postgres:secret@localhost/app
pgloader sqlite://./app.db postgresql://postgres:secret@localhost/app
pgloader migrate.load # a command file with LOAD ... FROM ... INTO ...
pgloader --type csv --field 'id,email' data.csv postgresql://postgres@localhost/appOptions
| Item | What it does |
|---|---|
| <source-uri> <target-uri> | Source and Postgres target connection URIs |
| <file>.load | A command file describing the load |
| --type csv|fixed|... | Source type for file loads |
| --field <list> | CSV field names |
| --with "option" | Load options (e.g. truncate) |
| --cast "rule" | Override a type-cast rule |
Common errors in CI
Database error ... could not be cast / "No matching cast for type" - pgloader could not infer a type mapping; add a CAST rule in the command file. "Failed to connect to ..." means one of the two URIs (source or target) is wrong or that database is not up - both must be reachable. Loading into a non-empty table can collide; use WITH truncate. Large loads can exhaust memory on small runners; tune the batch settings in the .load file.