CLI

Once installed, PostgreSQL Migrator provides a single CLI entrypoint with the following sub-commands:

$ pg_migrate --help
PostgreSQL Migrator moves your database to PostgreSQL.

Usage:  pg_migrate [OPTIONS] COMMAND

Commands:
  convert                  Convert source catalog for PostgreSQL
  dump                     Dump source database as PostgreSQL instructions
  init                     Initialize a new migration project
  inspect                  Fetch and analyse source database catalog
  status                   Describe a migration project
  ui                       Interactive audit web interface

General Options:
  -C, --directory string    Change to directory before doing anything
  -?, --help                Print help and exit
      --logfile string      Path to save log messages
      --offline             Prevent source database access
      --plain               Disable log coloration (default true)
      --profile             Write profiling data for debugging
      --skip-date-warning   Suppress the warning about outdated build
  -v, --verbose             Show debug log messages
  -V, --version             Print version and exit

Environment variables:

  PGMDIRECTORY               Change to directory before doing anything.
  PGMOFFLINE                   Set to true prevent source database access.

Report bugs to https://gitlab.com/dalibo/pg_migrate/-/issues.
$

Options right after pg_migrate and before subcommand are general options. Options after subcommand are specific to it. To access help of one subcommand, run:

$ pg_migrate <subcommand> --help

Boolean options accept a boolean value as parameter: pg_migrate --offline=false.

Make sure to run pg_migrate inside your migration project or specify the path with the -C general option.

For some commands, pg_migrate saves log messages in a file. Use --logfile to change the filename. Log filename is relative to project directory. Log file contains debug messages, regardless of --verbose option.

pg_migrate init¶

The init command initializes a migration project with:

pg_migrate.toml: the configuration (e.g. conversion rules, dump behaviours).
.env: the secrets.
.pg_migrate/: directory containing internal data for PostgreSQL Migrator.

$ pg_migrate init --help
Usage: pg_migrate [OPTIONS] init [OPTIONS] [DIRECTORY]

Initializes a new migration project in DIRECTORY. Defaults to the current directory.
Checks connectivity to the source database.

Options:
  -?, --help            Show help
      --source string   DSN for the source database
      --target string   DSN for the target PostgreSQL database

SOURCE is an universal DBURL as documented at https://github.com/xo/dburl.
The format is driver://user:password@host:port/database?opt=...
For example:

  mysql://user:password@localhost:3306/db
  oracle://user:password@localhost:1521/service_name
  oracle://user:password@localhost:1521/?SID=sid

TARGET is a libpq DSN.

Environment variables:

  PGMSOURCE     Equivalent to --source
  PGMTARGET     Equivalent to --target
  PG*           libpq standard environment variables

See pg_migrate --help for more informations.
$

pg_migrate init accepts to re-initialize an existing project by updating DSN in .env and metadata in .pg_migrate/Info.json. PostgreSQL Migrator does not modify existing TOML.

PostgreSQL Migrator refuses to initialize a non-empty directory without existing pg_migrate.toml or .pg_migrate.

To include special characters like #, /, etc. in username or password, encode them for URL as per the RFC: pg_migrate init --source oracle://C%23%23COMMON@localhost. jq implements URL encoding:

$ jq -sRr @uri <<<'Very!Strong#Password&'
Very%21Strong%23Password%26%0A

pg_migrate inspect¶

The inspect command produces the inventory of the database’s object you want to migrate: schemas, tables, routines, etc. inspect analyses objects for conversion and extracts components from composite objects like Oracle packages and partitioned tables. inspect collects statistics for tables and indexes. inspect also embeds convert command.

$ pg_migrate inspect --help
Usage: pg_migrate [OPTIONS] inspect [OPTIONS]

Query objects from the source database. Stores the result in the project
directory. Reinspects objects if configuration changes.

Options:
      --collect-stats   Collect statistics after inspection (default true)
  -?, --help            Show help and exit
      --refresh         Invalidate cache

Use pg_migrate --help for more information.
$

After initializing the migration project, run this command:

$ pg_migrate inspect
15:57:40 INFO   Inspecting source database.      driver=mysql
15:57:40 INFO   Inspected metadata.              software="MySQL Community Server - GPL"
...
$

With pg_migrate --offline, inspect never queries source database. Offline inspect only audits and converts.

With pg_migrate inspect --collect-stats=false, PostgreSQL Migrator skips statistics collection. This is useful when working on a schema without data.

The next step after inspect is dump.

inspect command saves log messages in inspect.log in project directory. Use global option --logfile to customize the log filename.

pg_migrate convert¶

convert command converts the source catalog for PostgreSQL.

Requires preliminary source inspection with inspect command.

$ pg_migrate convert --help
Usage: pg_migrate [OPTIONS] convert [OPTIONS]

Converts source catalog for PostgreSQL.
Transpiles code. Renames identifiers. Converts column types.
Uses dump to generate DDL.

Options:
  -?, --help      Show help
      --refresh   Refresh target catalog

See pg_migrate --help for more informations.
$

convert generates .pg_migrate/Target.json file.

$ pg_migrate convert
14:23:42 INFO   Converted catalog for PostgreSQL.
14:23:42 INFO   Auditing catalog.                driver=mysql
14:23:42 WARN   Target catalog has pending annotations. count=26
14:23:42 INFO   Run pg_migrate dump to migrate.
$

See Conversion for details on how PostgreSQL Migrator converts source catalog.

convert command saves log messages in convert.log in project directory. Use global option --logfile to customize the log filename.

pg_migrate dump¶

The dump command exports target catalog and queries data from source database for PostgreSQL.

$ pg_migrate dump --help
Usage: pg_migrate [OPTIONS] dump [OPTIONS] [OBJECTS...]

Generates DDL and/or COPY for target PostgreSQL.
Executes statements in defined PostgreSQL target database
or writes to stdout or files hierarchy.
Requires successful convert.

OBJECTS is a list of LIKE pattern against target object path.
Defaults to Dump.Objects value in pg_migrate.toml.
Prefix pattern with "\!" to exclude an object from dump.

Options:
  -c, --clean            clean (drop) objects before recreating
                         or truncate table before copy
  -a, --data-only        dump only the data, not the schema
  -f, --file string      output file or directory name
      --force            ignore unhandled annotations
  -F, --format string    output file format: direct, plain, tree
  -?, --help             Show help
  -j, --jobs int         use this many parallel jobs to dump (default 4)
  -O, --no-owner         skip restoration of object ownership
      --refresh-stats    refresh table statistics before planning dump (default true)
  -s, --schema-only      dump only the schema, no data
      --section string   dump named section (pre-data, data, post-data)

--format can be direct, plain or tree.

--file accepts - to dump to stdout.
Only if stdout is piped or redirected to a file.

If stdout is not a terminal, writes plain queries to stdout.
Else if PGMTARGET is defined, sends to PostgreSQL target database.
Force tree output with --format=tree.

See pg_migrate --help for more informations.
$

If project is offline, data is skipped.

If target catalog has annotations such as not implemented conversion, dump refuses to execute a partial or failing dump. Use –force to dump anyway. As PostgreSQL Migrator matures, you’re likely to use –force often. Use Dump.Force to permanently force dump. Forcing a dump still produces a warning.

OBJECTS is a list of database objects to include or exclude (patterns). Order of objects matters, last matching pattern wins. It may be persisted in TOML using Dump.Objects.

dump command saves log messages in dump.log in project directory. Use global option --logfile to customize the log filename. Set environment variable PGM_TRACE=1 to enable verbose copy messages in log file.

pg_migrate dump CLI is consistent with pg_dump and other tool you’ll likely use when working with PostgreSQL. Don’t hesitate to familiarize with other pg_* commands.

Default dump format and file varies seconding TTY and presence of TERM variable. When TERM is set, pg_migrate checks for TTY to decide whether to dump directly to Postgres or to stdout. Thus, pg_migrate dump > dump.sql will work only in a console. In a cron task or on CI, dumping to stdout requires explicit format: pg_migrate dump --format=plain > dump.sql

pg_migrate ui¶

The ui command starts a web server to display an interactive web interface to audit the migration project.

$ pg_migrate ui --help
Usage: pg_migrate [OPTIONS] ui [OPTIONS]

Serves an interactive web interface to audit the migration project.

Options:
  -?, --help            Show help and exit
      --listen string   Bind to this address and port (default "localhost:8765")
      --open            Open browser

Use pg_migrate --help for more information.
$

The interface is available at localhost:8765.

Use the --open option to automatically open the browser.

pg_migrate status¶

The status command gives information about the status of the migration project.

$ pg_migrate status --help
Usage: pg_migrate [OPTIONS] status [OPTIONS]

Describes the current project.

Options:
  -?, --help   Show help and exit

Use pg_migrate --help for more information.
$

Known statuses are:

initialized.
inspected.
audited when source has been audited.
converted.