pgcopydb Documentation

pgcopydb provides several optimizations for database migration:

• Bypass intermediate files: Unlike pg_dump | pg_restore with -j, pgcopydb streams COPY buffers directly from source to target with zero processing, avoiding on-disk intermediate files.
• Use COPY FREEZE: Uses the FREEZE option during import to reduce post-migration VACUUM work (unless same-table concurrency is used).
• Create Index Concurrency: Leverages Postgres's synchronize_seqscans optimization by running multiple CREATE INDEX commands on the same table concurrently. Controlled via --index-jobs.
• Same Table Concurrency: For very large tables, pgcopydb can partition the table and run multiple COPY commands using non-overlapping WHERE clauses. Controlled via the --split-table-larger-than option.
• Change Data Capture (CDC): For migrations requiring minimal downtime, pgcopydb uses Postgres Logical Decoding APIs (available since Postgres 9.4) to replicate changes. Use the pgcopydb fork --follow command for this.
• Extension-aware migration: Automatically detects and handles setup/restore hooks for extensions like TimescaleDB and Citus.

The pgcopydb follow command operates using three concurrent subprocesses (prefetch, transform, and replay/catchup) in two distinct modes. The command automatically switches between these modes in a loop.

1. Prefetch and Catchup

Used at the start of the process. It follows these steps:

1. Prefetch: Fetches changes from the source via Logical Decoding and saves them as local JSON files.
2. Transform: Converts JSON files into SQL. Uses a Unix System V message queue to communicate LSN positions.
3. Catchup: Applies the SQL files to the target database. Uses the Postgres Replication Progress Tracking API to skip already applied transactions upon restart.

2. Live Replay

Once the catchup process exhausts the available SQL files, the command switches to live replay:

1. Prefetch: Receives changes via Logical Decoding, saves them to local JSON files, and writes them to a Unix pipe.
2. Transform: Reads JSON from the Unix pipe, converts it to SQL, and writes it to a Unix pipe. It still writes to SQL files to allow for recovery if the process is interrupted.
3. Replay: Reads SQL from the Unix pipe and applies it to the target database, using the Replication Progress Tracking API for reliability.

The --all-databases option allows pgcopydb clone to enumerate and clone all non-template user databases on a source instance in a single invocation.

Usage Requirements:

• The source and target URIs must point to the instance level (e.g., postgres://host/postgres) rather than a specific database.
• pgcopydb will derive per-database connection strings by substituting the database name.

Execution Phases:

1. Phase I (Snapshot & Pre-data): Roles are copied once at the instance level. A snapshot holder exports a REPEATABLE READ snapshot per database. Pre-data workers process databases in parallel.
2. Phase II (Global COPY, Indexes, & VACUUM): All tables from all databases are pooled together. Tables are sorted by size (largest first) and processed by a shared pool of --table-jobs COPY workers, --index-jobs workers, and --table-jobs VACUUM workers.
3. Phase III (Post-data): Databases are finalized one at a time (triggers, rules, comments, etc.) and sequences are reset.

The pgcopydb list command provides a JSON representation of the current state of a migration setup. This includes the configuration used for the snapshot and detailed metadata about the objects being migrated.

Setup Metadata

The setup object contains the configuration parameters used for the current snapshot, such as:

• snapshot: The unique identifier for the snapshot.
• source_pguri: The connection URI for the source database.
• target_pguri: The connection URI for the target database.
• table-jobs: Number of concurrent jobs for table data migration.
• index-jobs: Number of concurrent jobs for index creation.
• split-tables-larger-than: Threshold for splitting large tables into parts.

Tables Metadata

The tables array contains an object for every table in the migration. Key fields include:

• oid: The PostgreSQL Object ID.
• schema and name: The table's location.
• reltuples: Estimated number of rows.
• bytes and bytes-pretty: Size of the table.
• exclude-data: Boolean indicating if data is being skipped.
• restore-list-name: The identifier used to reference this table during restoration.
• part-key: The column used to split the table into parts (if applicable).
• parts: An array of objects describing how the table was split (e.g., number, total, min, max, count).

Indexes Metadata

The indexes array contains details about indexes to be recreated on the target, including:

• isPrimary and isUnique: Boolean flags for index properties.
• sql: The exact SQL command used to create the index.
• table: An object identifying the parent table via oid, schema, and name.

{
       "setup": {
           "snapshot": "00000003-00051AAE-1",
           "source_pguri": "postgres://@:/pagila?",
           "target_pguri": "postgres://@:/plop?",
           "table-jobs": 4,
           "index-jobs": 4,
           "split-tables-larger-than": 204800
       },
       "tables": [
           {
               "oid": 317934,
               "schema": "public",
               "name": "rental",
               "reltuples": 16044,
               "bytes": 1253376,
               "bytes-pretty": "1224 kB",
               "exclude-data": false,
               "restore-list-name": "public rental postgres",
               "part-key": "rental_id",
               "parts": [
                   {
                       "number": 1,
                       "total": 7,
                       "min": 1,
                       "max": 2294,
                       "count": 2294
                   }
               ]
           }
       ],
       "indexes": [
           {
               "oid": 378283,
               "schema": "pgcopydb",
               "name": "sentinel_expr_idx",
               "isPrimary": false,
               "isUnique": true,
               "columns": "",
               "sql": "CREATE UNIQUE INDEX sentinel_expr_idx ON pgcopydb.sentinel USING btree ((1))",
               "restore-list-name": "pgcopydb sentinel_expr_idx dim",
               "table": {
                   "oid": 378280,
                   "schema": "pgcopydb",
                   "name": "sentinel"
               }
           }
       ]
}

When --all-databases is active, pgcopydb executes the cloning in three sequential phases using a global worker pool for resource optimization:

Phase I: Snapshot Export and Pre-data

• Snapshot Holder: A long-lived subprocess connects to each source database to export a REPEATABLE READ snapshot. These snapshots are held open until Phase III completes to ensure consistent reads.
• Pre-data Workers: A supervisor starts --table-jobs workers. These workers dequeue database names and run the pre-data pipeline: fetching schema from the source, running pg_dump --pre-data, and pg_restore --pre-data on the target.

Phase II: Global COPY, Indexes, and Vacuum

• Global Queue: All tables from all databases are collected and sorted by size (largest first) into a single global queue.
• Global Workers: The --table-jobs (COPY) and --index-jobs (index/constraints) workers are created once and serve the entire workload across all databases. This ensures high worker utilization even if database sizes are uneven.
• Connection Management: COPY workers use a small connection cache keyed by database name to reuse connections for consecutive tables in the same database. Index and vacuum workers open per-database connections on demand.
• Logging: DDL and DML logs are prefixed with the database name (e.g., dbname: command) to distinguish interleaved output.

Phase III: Post-data

• Sequential Execution: After the global COPY phase, post-data work runs sequentially, one database at a time.
• Tasks: For each database, pg_restore --post-data is called (to handle triggers, rules, comments, etc.) and sequences are reset to match the source values captured in Phase I.
• Completion: Once all databases are processed, the snapshot holder commits its held transactions and exits.