Dumping schema

This guide presents advanced use of the command pg_migrate dump. In some cases, you will need a little more control about what should be dumped or not.

To reproduce any of following commands, please enable the Dump.Force option in pg_migrate.toml file. Thus, the previous guide must be achieved.

# pg_migrate.toml
[Dump]
Force = true

1. Use `--section` flag¶

PostgreSQL Migrator mimics pg_dump’s section concept. Separate dump into sections is crucial for performance by reducing CPU overhead due to index maintenance or constraint validation. You should always copy your data into tables without them.

You should be familiar with following flags, provided by the pg_migrate dump command.

  -a, --data-only        dump only the data, not the schema
  -s, --schema-only      dump only the schema, no data
      --section string   dump named section (pre-data, data, post-data)

Flag --data-only is an alias for --section=data, whereas --schema-only is a compaction for --section=pre-data and --section=post-data.

Under the hood, the pg_migrate dump command, when run without --section option, respects this order: create empty tables and sequence first, then copy data and sequence values, then create indexes and activate constraints.

You may restart your migration from the section you want by specifying the --clean option. The --clean option will emit a proper DROP TABLE if pre-data section is enabled or a TRUNCATE TABLE if data section is enabled.

$ pg_migrate dump --section=pre-data --clean
...
15:41:00 INFO   Drop schema.                     path=Schemas/sakila sn=00001
...
15:41:00 INFO   Dump completed.                  elapsed=250ms tasks=25 jobs=4 mem=63.3MB sections=pre annotations=62
15:41:00 INFO   Databases connections closed.    source=0 target=4

$ pg_migrate dump --data-only --clean
...
15:42:42 INFO   Truncate table.                  path=Tables/sakila.payment sn=10029
...
15:42:42 INFO   Dump completed.                  elapsed=1.08s tasks=47 jobs=4 mem=68.8MB tables=23 copied=2.9MB throughput=2.7MB/s sections=data annotations=62
15:42:42 INFO   Databases connections closed.    source=5 target=4

$ pg_migrate dump --section=post-data
...
15:43:29 INFO   Dump completed.                  elapsed=620ms tasks=115 jobs=4 mem=60.2MB sections=post annotations=62
15:43:29 INFO   Databases connections closed.    source=0 target=4

Note

Cleaning behaviour is not supported during post-data section.

2. Filtering Objects with CLI¶

You may need to iterate over table construction or data loading for a few identified objects, like very complex or very large ones.

The pg_migrate dump accepts path-patterns as arguments. For instance, you want to load data into films table.

$ pg_migrate dump --section=pre-data --clean Tables/public.film
$ pg_migrate dump --section=data --clean Tables/public.film

PostgreSQL Migrator completes path from target catalog with shell autocompletion. Type <TAB> while typing path to see available options.

Negate an objet with ! will exclude it. For instance, you want to recreate the entire public schema with only tables that match with film% pattern. Arguments will be:

Schemas/public to include this schema and all objects in this schema in dumping plan
!Tables/ to exclude all tables
Tables/public.film% … except all that match the pattern

$ pg_migrate dump --section=pre-data --clean Schemas/public \!Tables Tables/public.film%
16:09:42 INFO   Auditing catalog.                driver=mysql catalog=PostgreSQL
16:09:42 WARN   Ignoring unhandled annotations in target model. len=18
16:09:42 INFO   Executing queries in target PostgreSQL.
16:09:42 INFO   Drop schema.                     path=Schemas/public sn=00001
16:09:42 INFO   Create schema.                   path=Schemas/public sn=00002
16:09:42 INFO   Create table.                    path=Tables/public.film sn=00003
16:09:42 INFO   Create table.                    path=Tables/public.film_category sn=00005
16:09:42 INFO   Create table.                    path=Tables/public.film_text sn=00006
16:09:42 INFO   Create table.                    path=Tables/public.film_actor sn=00004
16:09:42 INFO   Dump completed.                  elapsed=230ms tasks=6 jobs=4 mem=61.9MB sections=pre annotations=18
16:09:42 INFO   Databases connections closed.    source=0 target=4

Note

Note that escaped exclamation mark \! is only relevant for bash or zsh shells.

3. Configure Objects to migrate¶

You may select which objects to migrate because some are useless in your new schema or to workaround PostgreSQL Migrator. Do this using Dump.Objects pattern list. This option selects target objects from dump. You will always see excluded objects in Target catalog.

Dump.Objects receives catalog path and accepts LIKE pattern. To find catalog path of an object, open Web UI using ui command, click PostgreSQL to browse Target catalog, search object, click the copy button in object details.

For example:

Dump.Objects = [
  "!Roles", # exclude all roles.
  "!Tables/sakila.address", # exclude a table.
  "!Views/%.bi_%", # exclude all views prefixed with BI.
]

Role alice will not be created in target database.
Table sakila.film will be migrated but not sakila.address.
View sakila.bi_film will not be migrated.

See TOML reference for Dump.Objects for details. Paths given as arguments to pg_migrate dump override Dump.Objects configuration.

4. Profiling your migration¶

PostgreSQL Migrator comes with a profiling feature that provide tasks and error events visualization. Use the global --profile flag during dump to produce a Chrome performance trace as JSON file.

$ pg_migrate --profile dump --clean
17:17:29 INFO   Opening profiling file.          path=profile.json
17:17:29 INFO   Auditing catalog.                driver=mysql catalog=PostgreSQL
17:17:29 WARN   Ignoring unhandled annotations in target model. len=51
17:17:29 INFO   Collected tables statistics.     count=24 missing=0
17:17:29 INFO   Collected partitions statistics. count=7
17:17:29 INFO   Collected subpartitions statistics. count=6
17:17:29 INFO   All statistics collected.        size=7MB
17:17:30 INFO   Executing queries in target PostgreSQL.
17:17:30 INFO   Drop schema.                     path=Schemas/public sn=00001
...
17:17:31 INFO   Dump completed.                  elapsed=1.79s tasks=129 jobs=4 mem=64.9MB tables=18 copied=2.9MB throughput=1.6MB/s sections=pre,data,post annotations=51
17:17:31 INFO   Databases connections closed.    source=4 target=4
17:17:31 INFO   App profiling data saved.        path=profile.json

Grab the profile.json file in your project directory. Use a trace visualizer tool like ui.perfetto.dev to understand how tasks have been orchestrated and how long a task took.

Visual representation of sakila migration