pgloader is a data loading tool for PostgreSQL, using the
Its main advantage over just using
\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 00:00:00 to PostgreSQL
NULL value (because our calendar
never had a year zero).
pgloader version 1.x is quite old and was developed in
When faced with maintaining that code, the new emerging development
team (hi!) picked
python instead because that made sense at the
time. So pgloader version 2.x was written in python.
The current version of pgloader is the 3.x series, which is written in
Common Lisp for better development flexibility,
runtime performance, and support of real threading.
The versioning is now following the Emacs model, where any X.0 release
number means you're using a development version (alpha, beta, or release
candidate). The next stable versions are going to be
When using a development snapshot rather than a released version the version
number includes the git hash (in its abbreviated form):
pgloader version "3.0.99"
Release candidate 9 for pgloader version 3.1, with a git tag named
v3.0.99 so that it's easy to checkout the same sources as the
pgloader version "3.0.fecae2c"
Development snapshot again git hash
fecae2c . It's possible to have
the same sources on another setup with using the git command
checkout fecae2c .
pgloader version "3.1.0"
pgloader is available under The PostgreSQL Licence.
You can install pgloader directly from
apt.postgresql.org and from official
debian repositories, see
normal$ apt-get install pgloader
You can also use a docker image for pgloader at
normal$ docker pull dimitri/pgloader
$ docker run --rm --name pgloader dimitri/pgloader:latest pgloader --version
$ docker run --rm --name pgloader dimitri/pgloader:latest pgloader --help
Build from sources
pgloader is now a Common Lisp program, tested using the
SBCL (>= 1.1.14) and
Clozure CL implementations with
normal$ apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev
$ cd /path/to/pgloader
$ make pgloader
$ ./build/bin/pgloader --help
When building from sources, you should always build from the current git
HEAD as it's basically the only source that is managed in a way to ensure it
builds aginst current set of dependencies versions.
More options when building from source
pgloader knows how to produce a Self Contained
Binary file for pgloader, found at
normal$ make pgloader
By default, the
Makefile uses SBCL to compile your
binary image, though it's possible to build using
normal$ make CL=ccl pgloader
SBCL and it supports core compression, the make process will
use it to generate a smaller binary. To force disabling core
compression, you may use:
normal$ make COMPRESS_CORE=no pgloader
--compress-core is unique to SBCL, so not used when
CC is different
You can also tweak the default amount of memory that the
will allow itself using when running through your data (don't ask for more
than your current RAM tho):
normal$ make DYNSIZE=8192 pgloader
make pgloader command when successful outputs a
file for you to use.
You can either give a command file to pgloader or run it all from the
command line, see the
pgloader quick start on
http://pgloader.io for more details.
normal$ ./build/bin/pgloader --help
$ ./build/bin/pgloader <file.load>
For example, for a full migration from SQLite:
normal$ createdb newdb
$ pgloader ./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:
normal$ createdb pagila
$ pgloader mysql://user@localhost/sakila postgresql:///pagila
See the documentation file
pgloader.1.md for details. You can compile that
file into a manual page or an HTML page thanks to the
normal$ apt-get install ruby-ronn
$ make docs