dimitri/pgcopydb

README

pgcopydb

Introduction

pgcopydb is a tool that automates running pg_dump | pg_restore between two running Postgres servers. To make a copy of a database to another server as quickly as possible, one would like to use the parallel options of pg_dump and still be able to stream the data to as many pg_restore jobs.

The idea would be to use pg_dump --jobs=N --format=directory postgres://user@source/dbname | pg_restore --jobs=N --format=directory -d postgres://user@target/dbname in a way. This command line can't be made to work, unfortunately, because pg_dump --format=directory writes to local files and directories first, and then later pg_restore --format=directory can be used to read from those files again.

Given that, pgcopydb then uses pg_dump and pg_restore for the schema parts of the process, and implements its own data copying multi-process streaming parts. Also, pgcopydb bypasses pg_restore index building and drives that internally so that all indexes may be built concurrently.

Base Copy and Change Data Capture

pgcopydb implements both the base copy of a database and also Change Data Capture to allow replay of changes from the source database to the target database. The Change Data Capture facility is implemented using Postgres Logical Decoding infrastructure. The default plugin is pgoutput, which is built into PostgreSQL core (since version 10) and requires no extension installation. The test_decoding and wal2json plugins are also supported.

WAL changes are stored in structured SQLite databases (output.db and replay.db) in the pgcopydb work directory, enabling reliable processing across process restarts, size-based file rotation, and a stream prune command to reclaim disk space.

The pgcopydb follow command implements a logical replication client for the pgoutput logical decoding plugin (built into PostgreSQL core), as well as test_decoding and wal2json.

The pgcopydb clone --follow command implements a full solution for online migration. Beware that online migrations involve a lot more complexities when compared to offline migration. It is always a good idea to first implement offline migration first. The command pgcopydb clone is used to implement the offline migration approach.

The pgcopydb clone --all-databases command copies an entire Postgres instance (all databases) from source to target in a single operation.

Documentation

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

$ pgcopydb help
  pgcopydb
    clone     Clone an entire database from source to target
    fork      Clone an entire database from source to target
    follow    Replay changes from the source database to the target database
    snapshot  Create and export a snapshot on the source database
  + compare   Compare source and target databases
  + copy      Implement the data section of the database copy
  + dump      Dump database objects from a Postgres instance
  + restore   Restore database objects into a Postgres instance
  + list      List database objects from a Postgres instance
  + stream    Stream changes from the source database
    ping      Attempt to connect to the source and target instances
    help      Print help message
    version   Print pgcopydb version

  pgcopydb compare
    schema  Compare source and target schema
    data    Compare source and target data

  pgcopydb copy
    db           Copy an entire database from source to target
    roles        Copy the roles from the source instance to the target instance
    extensions   Copy the extensions from the source instance to the target instance
    schema       Copy the database schema from source to target
    data         Copy the data section from source to target
    table-data   Copy the data from all tables in database from source to target
    blobs        Copy the blob data from the source database to the target
    sequences    Copy the current value from all sequences in database from source to target
    indexes      Create all the indexes found in the source database in the target
    constraints  Create all the constraints found in the source database in the target

  pgcopydb dump
    schema  Dump source database schema as custom files in work directory
    roles   Dump source database roles as custom file in work directory

  pgcopydb restore
    schema     Restore a database schema from custom files to target database
    pre-data   Restore a database pre-data schema from custom file to target database
    post-data  Restore a database post-data schema from custom file to target database
    roles      Restore database roles from SQL file to target database

  pgcopydb list
    databases    List databases
    extensions   List all the source extensions to copy
    collations   List all the source collations to copy
    tables       List all the source tables to copy data from
    table-parts  List a source table copy partitions
    sequences    List all the source sequences to copy data from
    indexes      List all the indexes to create again after copying the data
    depends      List all the dependencies to filter-out
    schema       List the schema to migrate, formatted in JSON
    progress     List the progress
    toc          List and filter a pg_dump archive's Table of Contents

  pgcopydb stream
    setup          Setup source and target systems for logical decoding
    cleanup        Cleanup source and target systems for logical decoding
    prune          Remove already-applied CDC files from disk to reclaim disk space
    event-trigger  Install the event trigger that emits DDL as logical decoding messages
    prefetch       Stream changes from the source database into the SQLite CDC store
    catchup        Transform and apply prefetched changes from the SQLite CDC store to the target
    replay         Replay changes from the source to the target database, live
  + sentinel       Maintain a sentinel table
    receive        Stream changes from the source database
    apply          Apply changes from the replayDB to the target database, or stdout

  pgcopydb stream sentinel
    setup  Setup the sentinel table
    get    Get the sentinel table values
  + set    Set the sentinel table values

  pgcopydb stream sentinel set
    startpos  Set the sentinel start position LSN
    endpos    Set the sentinel end position LSN
    apply     Set the sentinel apply mode
    prefetch  Set the sentinel prefetch mode

Example

When using pgcopydb it is possible to achieve the result outlined before with this simple command line:

$ export PGCOPYDB_SOURCE_PGURI="postgres://user@source.host.dev/dbname"
$ export PGCOPYDB_TARGET_PGURI="postgres://role@target.host.dev/dbname"

$ pgcopydb clone --table-jobs 8 --index-jobs 2

A typical output from the command would contain lots of lines of logs, and then a table summary with a line per table and some information (timing for the table COPY, cumulative timing for the CREATE INDEX commands), and then an overall summary that looks like the following:

22:37:01.564 22 INFO   Running pgcopydb version 0.18 from "/usr/bin/pgcopydb"
22:37:01.565 22 INFO   [SOURCE] Copying database from "postgres://pagila@source/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
22:37:01.565 22 INFO   [TARGET] Copying database into "postgres://pagila@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
22:37:01.582 22 INFO   Exported snapshot "00000003-0000002D-1" from the source database
22:37:01.583 24 INFO   STEP 1: fetch source database tables, indexes, and sequences
22:37:01.737 24 INFO   Fetched information for 21 tables (including 11 tables split in 30 partitions total), with an estimated total of 49 636 tuples and 5224 kB on-disk
22:37:01.743 24 INFO   Fetched information for 54 indexes (supporting 21 constraints)
22:37:01.756 24 INFO   Fetched information for 7 extensions
22:37:01.795 24 INFO   STEP 2: dump the source database schema (pre/post data)
22:37:01.796 24 INFO    /usr/bin/pg_dump -Fc --snapshot 00000003-0000002D-1 --section=pre-data --section=post-data --file /tmp/pgcopydb/schema/schema.dump 'postgres://pagila@source/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60'
22:37:01.846 24 INFO   STEP 3: restore the pre-data section to the target database
22:37:01.862 24 INFO    /usr/bin/pg_restore --dbname 'postgres://pagila@target/pagila?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60' --section pre-data --jobs 4 --use-list /tmp/pgcopydb/schema/pre-filtered.list /tmp/pgcopydb/schema/schema.dump
22:37:01.956 34 INFO   STEP 4: starting 4 table-data COPY processes
22:37:01.956 36 INFO   STEP 8: starting 4 VACUUM processes
22:37:01.959 24 INFO   STEP 9: reset sequences values
22:37:01.965 35 INFO   STEP 6: starting 4 CREATE INDEX processes
22:37:01.965 35 INFO   STEP 7: constraints are built by the CREATE INDEX processes
22:37:02.895 24 INFO   All step are now done,  1s100 elapsed
22:37:02.895 24 INFO   Printing summary for 21 tables and 54 indexes

  OID | Schema |             Name | Parts | copy duration | transmitted bytes | indexes | create index duration
------+--------+------------------+-------+---------------+-------------------+---------+----------------------
17182 | public |           rental |     5 |         250ms |           5477 kB |       3 |                  34ms
17071 | public |             film |     4 |         124ms |           1336 kB |       5 |                  26ms
17130 | public |        inventory |     4 |         104ms |            680 kB |       2 |                  17ms
17082 | public |       film_actor |     2 |          60ms |            320 kB |       2 |                  15ms
17200 | public |            staff |     3 |          96ms |            718 kB |       1 |                   5ms
17157 | public | payment_p2022_02 |     2 |          46ms |            289 kB |       4 |                  28ms
17161 | public | payment_p2022_03 |     2 |          60ms |            326 kB |       4 |                  20ms
17165 | public | payment_p2022_04 |     2 |          28ms |            306 kB |       4 |                  22ms
17169 | public | payment_p2022_05 |     2 |          54ms |            322 kB |       4 |                  26ms
17173 | public | payment_p2022_06 |     2 |          40ms |            319 kB |       4 |                  25ms
17177 | public | payment_p2022_07 |     2 |          60ms |            280 kB |       1 |                   6ms
17086 | public |    film_category |     1 |          14ms |             69 kB |       1 |                   6ms
17043 | public |         customer |     1 |          15ms |             67 kB |       4 |                  20ms
17096 | public |          address |     1 |          17ms |             55 kB |       2 |                   7ms
17153 | public | payment_p2022_01 |     1 |          16ms |             43 kB |       4 |                  19ms
17104 | public |             city |     1 |          13ms |             24 kB |       2 |                   9ms
17209 | public |            store |     1 |          10ms |             18 kB |       2 |                  11ms
17055 | public |            actor |     1 |          14ms |           8328  B |       2 |                  13ms
17063 | public |         category |     1 |          15ms |            540  B |       1 |                   3ms
17112 | public |          country |     1 |          14ms |           3829  B |       1 |                   5ms
17136 | public |         language |     1 |          14ms |            297  B |       1 |                   6ms


                                               Step   Connection    Duration    Transfer   Concurrency
 --------------------------------------------------   ----------  ----------  ----------  ------------
   Catalog Queries (table ordering, filtering, etc)       source       124ms                         1
                                        Dump Schema       source        50ms                         1
                                     Prepare Schema       target        94ms                         1
      COPY, INDEX, CONSTRAINTS, VACUUM (wall clock)         both       834ms                        12
                                  COPY (cumulative)         both       455ms     3213 kB             4
                          CREATE INDEX (cumulative)       target       318ms                         4
                           CONSTRAINTS (cumulative)       target         5ms                         4
                                VACUUM (cumulative)       target       185ms                         4
                                    Reset Sequences         both        12ms                         1
                         Large Objects (cumulative)         both         0ms                         4
                                    Finalize Schema         both       112ms                         4
 --------------------------------------------------   ----------  ----------  ----------  ------------
                          Total Wall Clock Duration         both       1s100                        20

Installing pgcopydb

See our documentation.

Design Considerations (why oh why)

The reason why pgcopydb has been developed is mostly to allow two aspects that are not possible to achieve directly with pg_dump and pg_restore, and that requires just enough fiddling around that not many scripts have been made available to automate around.

Bypass intermediate files for the TABLE DATA

First aspect is that for pg_dump and pg_restore to implement concurrency they need to write to an intermediate file first.

The docs for pg_dump say the following about the --jobs parameter:

You can only use this option with the directory output format because this is the only output format where multiple processes can write their data at the same time.

The docs for pg_restore say the following about the --jobs parameter:

Only the custom and directory archive formats are supported with this option. The input must be a regular file or directory (not, for example, a pipe or standard input).

So the first idea with pgcopydb is to provide the --jobs concurrency and bypass intermediate files (and directories) altogether, at least as far as the actual TABLE DATA set is concerned.

The trick to achieve that is that pgcopydb must be able to connect to the source database during the whole operation, when pg_restore may be used from an export on-disk, without having to still be able to connect to the source database. In the context of pgcopydb requiring access to the source database is fine. In the context of pg_restore, it would not be acceptable.

For each table, build all indexes concurrently

The other aspect that pg_dump and pg_restore are not very smart about is how they deal with the indexes that are used to support constraints, in particular unique constraints and primary keys.

Those indexes are exported using the ALTER TABLE command directly. This is fine because the command creates both the constraint and the underlying index, so the schema in the end is found as expected.

That said, those ALTER TABLE ... ADD CONSTRAINT commands require a level of locking that prevents any concurrency. As we can read on the docs for ALTER TABLE:

Although most forms of ADD table_constraint require an ACCESS EXCLUSIVE lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE lock. Note that ADD FOREIGN KEY also acquires a SHARE ROW EXCLUSIVE lock on the referenced table, in addition to the lock on the table on which the constraint is declared.

The trick is then to first issue a CREATE UNIQUE INDEX statement and when the index has been built then issue a second command in the form of ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY USING INDEX ..., as in the following example taken from the logs of actually running pgcopydb:

...
21:52:06 68898 INFO  COPY "demo"."tracking";
21:52:06 68899 INFO  COPY "demo"."client";
21:52:06 68899 INFO  Creating 2 indexes for table "demo"."client"
21:52:06 68906 INFO  CREATE UNIQUE INDEX client_pkey ON demo.client USING btree (client);
21:52:06 68907 INFO  CREATE UNIQUE INDEX client_pid_key ON demo.client USING btree (pid);
21:52:06 68898 INFO  Creating 1 indexes for table "demo"."tracking"
21:52:06 68908 INFO  CREATE UNIQUE INDEX tracking_pkey ON demo.tracking USING btree (client, ts);
21:52:06 68907 INFO  ALTER TABLE "demo"."client" ADD CONSTRAINT "client_pid_key" UNIQUE USING INDEX "client_pid_key";
21:52:06 68906 INFO  ALTER TABLE "demo"."client" ADD CONSTRAINT "client_pkey" PRIMARY KEY USING INDEX "client_pkey";
21:52:06 68908 INFO  ALTER TABLE "demo"."tracking" ADD CONSTRAINT "tracking_pkey" PRIMARY KEY USING INDEX "tracking_pkey";
...

This trick is worth a lot of performance gains on its own, as has been discovered and experienced and appreciated by pgloader users already.

Dependencies

At run-time pgcopydb depends on the pg_dump and pg_restore tools being available in the PATH. The tools version should match the Postgres version of the target database.

When you have multiple versions of Postgres installed, consider exporting the PG_CONFIG environment variable to the version you want to use. pgcopydb then uses the PG_CONFIG from the path and runs ${PG_CONFIG} --bindir to find the pg_dump and pg_restore binaries it needs.

Manual Steps

The pgcopydb command line also includes entry points that allows implementing any step on its own.

pgcopydb snapshot &
pgcopydb dump schema
pgcopydb restore pre-data
pgcopydb copy table-data
pgcopydb copy blobs
pgcopydb copy sequences
pgcopydb copy indexes
pgcopydb copy constraints
pgcopydb restore post-data
kill %1

Using individual commands fails to provide the advanced concurrency capabilities of the main pgcopydb clone command, so it is strongly advised to prefer that main command.

Also when using separate commands, one has to consider the --snapshot option that allows for consistent operations. A background process should then export the snapshot and maintain a transaction opened for the duration of the operations. See documentation for pgcopydb snapshot.

Authors

Dimitri Fontaine

License

This project is licensed under the PostgreSQL License, see LICENSE file for details.

This project includes bundled third-party dependencies, see NOTICE file for details.

Maintenance & Support

This project is maintained through OSS Maintenance — a funded model designed for enterprises running these tools in production.

Priority bug fixes and improvements via a paid queue
Quarterly releases with predictable delivery
Upstream-first: fixes land in the project itself

PostgreSQL consultancies can also subscribe at the Partner tier to cover their entire client base under a single plan.

View support plans