GitHub - dimitri/pgloader: Migrate to PostgreSQL in a single command! (original) (raw)

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 tocopy 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 and0000-00-00 00:00:00 to PostgreSQL NULL value (because our calendar never had a year zero).

Documentation

Full documentation is available online, including manual pages of all the pgloader sub-commands. Check outhttps://pgloader.readthedocs.io/.

$ pgloader --help
pgloader [ option ... ] SOURCE TARGET
  --help -h                       boolean  Show usage and exit.
  --version -V                    boolean  Displays pgloader version and exit.
  --quiet -q                      boolean  Be quiet
  --verbose -v                    boolean  Be verbose
  --debug -d                      boolean  Display debug level information.
  --client-min-messages           string   Filter logs seen at the console (default: "warning")
  --log-min-messages              string   Filter logs seen in the logfile (default: "notice")
  --summary -S                    string   Filename where to copy the summary
  --root-dir -D                   string   Output root directory. (default: #P"/tmp/pgloader/")
  --upgrade-config -U             boolean  Output the command(s) corresponding to .conf file for v2.x
  --list-encodings -E             boolean  List pgloader known encodings and exit.
  --logfile -L                    string   Filename where to send the logs.
  --load-lisp-file -l             string   Read user code from files
  --dry-run                       boolean  Only check database connections, don't load anything.
  --on-error-stop                 boolean  Refrain from handling errors properly.
  --no-ssl-cert-verification      boolean  Instruct OpenSSL to bypass verifying certificates.
  --context -C                    string   Command Context Variables
  --with                          string   Load options
  --set                           string   PostgreSQL options
  --field                         string   Source file fields specification
  --cast                          string   Specific cast rules
  --type                          string   Force input source type
  --encoding                      string   Source expected encoding
  --before                        string   SQL script to run before loading the data
  --after                         string   SQL script to run after loading the data
  --self-upgrade                  string   Path to pgloader newer sources
  --regress                       boolean  Drive regression testing

Usage

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

$ ./build/bin/pgloader --help
$ ./build/bin/pgloader <file.load>

For example, for a full migration from SQLite:

$ 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:

$ createdb pagila
$ pgloader mysql://user@localhost/sakila postgresql:///pagila

LICENCE

pgloader is available under The PostgreSQL Licence.

INSTALL

Please see full documentation athttps://pgloader.readthedocs.io/.

If you're using debian, it's already available:

$ apt-get install pgloader

If you're using docker, you can use the latest version built by the CI at each commit to the master branch:

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