pgloader

pgloader is a data loading tool for PostgreSQL, using the COPY command.

Its main advantage over just using COPY or \copy, and over using a Foreign Data Wrapper, is its transaction behaviour, where pgloader will keep a separate file of rejected data, but continue trying to copy good data in your database.

The default PostgreSQL behaviour is transactional, which means that any erroneous line in the input data (file or remote database) will stop the entire bulk load for the table.

pgloader also implements data reformatting, a typical example of that being the transformation of MySQL datestamps 0000-00-00 and 0000-00-00 00:00:00 to PostgreSQL NULL value (because our calendar never had a year zero).

Version 4 — Clojure/JVM rewrite

pgloader v4 is a full rewrite in Clojure, distributed as a single self-contained JAR requiring Java 21 or later. It is a drop-in replacement for v3 and accepts the same .load file syntax and command-line flags.

Key improvements over v3:

• Single JAR — no native dependencies, no SBCL, no shared libraries
• JDBC connection strings — use jdbc:mysql://, jdbc:postgresql://, jdbc:sqlserver:// etc. everywhere; driver-specific parameters pass through unchanged
• Java heap — no more Common Lisp heap exhaustion on large migrations; tune with standard -Xmx JVM flag
• SSL via URI — use ?sslmode=require / ?sslmode=disable in the connection string instead of --no-ssl-cert-verification

Documentation

Full documentation is available at https://pgloader.readthedocs.io/.

$ java -jar pgloader.jar --help
pgloader [ option ... ] SOURCE TARGET
  --help                          Show usage and exit.
  --version                       Display pgloader version and exit.
  --quiet                         Be quiet.
  --verbose                       Be verbose.
  --debug                         Display debug level information.
  --client-min-messages           Filter logs seen at the console (default: info)
  --log-min-messages              Filter logs seen in the logfile (default: info)
  --logfile                       Filename where to send the logs.
  --summary                       Filename where to copy the summary.
  --root-dir                      Output root directory (default: /tmp/pgloader/).
  --list-encodings                List pgloader known encodings and exit.
  --dry-run                       Check connections only, don't load anything.
  --on-error-stop                 Stop on first error instead of continuing.

Usage

You can either give a command file to pgloader or run it all from the command line, see the pgloader quick start for more details.

$ java -jar pgloader.jar --help
$ java -jar pgloader.jar <file.load>

For example, for a full migration from SQLite:

$ createdb newdb
$ java -jar pgloader.jar ./test/sqlite/sqlite.db postgresql:///newdb

Or for a full migration from MySQL, including schema definition (tables, indexes, foreign keys, comments) and parallel loading of the corrected data:

$ createdb pagila
$ java -jar pgloader.jar mysql://user@localhost/sakila postgresql:///pagila

INSTALL

Java (v4 — recommended)

pgloader v4 requires Java 21 or later (Temurin, OpenJDK, or any compatible distribution).

Download the latest pre-release JAR — this URL always points to the most recent build from the v4-clojure-rewrite branch:

$ curl -L -o pgloader.jar \
    https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar
$ java -jar pgloader.jar --version

To make it available system-wide:

$ sudo install -m 755 pgloader.jar /usr/local/lib/pgloader.jar
$ echo '#!/bin/sh\nexec java -jar /usr/local/lib/pgloader.jar "$@"' \
    | sudo tee /usr/local/bin/pgloader
$ sudo chmod 755 /usr/local/bin/pgloader
$ pgloader --version

Docker

The latest image is built by CI on every push to v4-clojure-rewrite:

$ docker pull ghcr.io/dimitri/pgloader:latest
$ docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version

Debian / Ubuntu (v3)

The v3 package is available in the Debian and Ubuntu repositories:

$ apt-get install pgloader

A v4 Debian package is planned.

LICENCE

pgloader is available under The PostgreSQL Licence.