CLI

Once installed, PostgreSQL Migrator provides the following commands:

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

Usage:  pg_migrate [OPTIONS] COMMAND

Commands:
  convert                  Convert source catalog for PostgreSQL
  dump                     Dumps a source database as text files or to stdin
  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
      --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 behaviors).
.env: the secrets.
.pg_migrate/: directory containing internal data for PostgreSQL Migrator.

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

Initializes a new migration project in PATH. 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/db

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.

pg_migrate inspect¶

The inspectcommand 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 change.

Options:
      --collect-stats   Collect statistics after inspection
  -?, --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 schema.

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 queries data from source database and exports catalog and data to 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 (default true)
  -a, --data-only        dump only the data, not the schema
  -f, --file string      output file or directory name
      --force            ignore unhandled annotations (default true)
  -F, --format string    output file format: 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
  -s, --schema-only      dump only the schema, no data
      --section string   dump named section (pre-data, data, post-data)

--format can be 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 configured 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.

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
      --open   Open browser

Use pg_migrate --help for more information.
$

The interface is available at http://localhost:8765/.

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

pg_migrate status¶

The statuscommand 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