pgcopydb

https://pgcopydb.readthedocs.io/en/latest/
Admin
pgcopydb is an open-source tool that automates copying a PostgreSQL database to another server,...
Tokens:130,889
Snippets:809
Trust Score:9.6
Update:1 month ago
Show doc for...
Tokens:
Raw
### Start follow mode with clone --follow

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/tutorial.rst.txt

Sets connection strings and runs pgcopydb clone --follow to start initial copy and replication setup for change data capture. Check replication progress with pg_stat_replication.

```bash
$ export PGCOPYDB_SOURCE_PGURI="dbname=pagila"
$ export PGCOPYDB_TARGET_PGURI="postgres://user@target:5432/pagila"

$ pgcopydb clone --follow
```

--------------------------------

### pgcopydb stream sentinel setup usage

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_stream.html

Shows the usage for the pgcopydb stream sentinel setup command, which requires a start LSN and an end LSN.

```text
pgcopydb stream sentinel setup: Setup the sentinel table
usage: pgcopydb stream sentinel setup <start lsn> <end lsn>
```

--------------------------------

### pgcopydb clone --follow workflow commands

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/ref/pgcopydb_clone.rst.txt

Example shell commands implementing the pgcopydb clone --follow workflow: start snapshot, setup stream, run clone --follow, set endpos, copy sequences, cleanup, and kill the snapshot process. The kill command uses %1 to reference the background snapshot job.

```bash
$ pgcopydb snapshot &

$ pgcopydb stream setup

$ pgcopydb clone --follow &

# later when the application is ready to make the switch
$ pgcopydb stream sentinel set endpos --current

# when the follow process has terminated, re-sync the sequences
$ pgcopydb copy sequences

# later when the migration is finished, clean-up both source and target
$ pgcopydb stream cleanup

# now stop holding the snapshot transaction (adjust PID to your environment)
$ kill %1
```

--------------------------------

### Inspect pgcopydb setup information

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/resume.rst.txt

Use sqlite3 to query the local catalog cache and view the current setup information, including the snapshot ID.

```bash
$ sqlite3 /tmp/pgcopydb/schema/source.db
sqlite> .mode line
sqlite> select * from setup;
                      id = 1
           source_pg_uri = postgres:///pagila
           target_pg_uri = postgres:///plop
                snapshot = 00000003-00000048-1
split_tables_larger_than = 0
                 filters = {"type":"SOURCE_FILTER_TYPE_NONE"}
                  plugin = 
               slot_name =
```

--------------------------------

### Install pgcopydb on Debian/Ubuntu

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/install.rst.txt

Install pgcopydb using the apt package manager after setting up the apt.postgresql.org repository.

```bash
$ sudo apt-get install pgcopydb
```

--------------------------------

### Concurrent Index Creation Log Example

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/concurrency.rst.txt

Example log output showing the process of creating unique indexes first, followed by adding constraints using those indexes to avoid exclusive locks.

```text
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";
```

--------------------------------

### pgcopydb schema pattern matching examples (INI)

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/ref/pgcopydb_config.rst.txt

Pattern matching examples for exclude-schema and include-only-schema sections. Shows how to use regex patterns for schema names.

```ini
[exclude-schema]
; excludes every schema whose name starts with "tmp_"
~/^tmp_/

[include-only-schema]
public
; also include every schema whose name matches "audit_YYYY" (four digits)
~/^audit_[0-9]{4}$/
```

--------------------------------

### pgcopydb ping example output

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb.html

Shows an example output of the pgcopydb ping command, including log lines indicating successful connections to target and source databases.

```shell
$ pgcopydb ping
18:04:48 84679 INFO   Running pgcopydb version 0.10.31.g7e5fbb8.dirty from "/Users/dim/dev/PostgreSQL/pgcopydb/src/bin/pgcopydb/pgcopydb"
18:04:48 84683 INFO   Successfully could connect to target database at "postgres://@:/plop?"
18:04:48 84682 INFO   Successfully could connect to source database at "postgres://@:/pagila?"

```

--------------------------------

### pgcopydb stream setup usage

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_stream.html

Usage and options for the pgcopydb stream setup command. It connects to the target database and creates a replication origin positioned at the LSN of an existing logical decoding replication slot (created with pgcopydb snapshot).

```text
pgcopydb stream setup: Setup source and target systems for logical decoding
usage: pgcopydb stream setup

  --source                      Postgres URI to the source database
  --target                      Postgres URI to the target database
  --dir                         Work directory to use
  --restart                     Allow restarting when temp files exist already
  --resume                      Allow resuming operations after a failure
  --not-consistent              Allow taking a new snapshot on the source database
  --snapshot                    Use snapshot obtained with pg_export_snapshot
  --plugin                      Output plugin to use (pgoutput, test_decoding, wal2json)
  --wal2json-numeric-as-string  Print numeric data type as string when using wal2json output plugin
  --replay-no-op-updates        Replay UPDATE statements even when no column values changed
  --slot-name                   Stream changes recorded by this slot
  --origin                      Name of the Postgres replication origin
```

--------------------------------

### pgcopydb stream sentinel sub-commands

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_stream.html

Lists the available sub-commands for pgcopydb stream sentinel, including setup, get, and set.

```text
pgcopydb stream sentinel: Maintain a sentinel table

Available commands:
  pgcopydb stream sentinel
    setup  Setup the sentinel table
    get    Get the sentinel table values
  + set    Set the sentinel table values
```

--------------------------------

### Split migration into privileged and non-privileged parts

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_clone.html

Example script demonstrating how to run initial migration steps requiring superuser privileges, followed by a non-privileged clone operation.

```bash
 1 $ coproc ( pgcopydb snapshot )
 2
 3 # first two commands would use a superuser role to connect
 4 $ pgcopydb copy roles --source ... --target ...
 5 $ pgcopydb copy extensions --source ... --target ...
 6
 7 # now it's possible to use a non-superuser role to connect
 8 $ pgcopydb clone --skip-extensions --source ... --target ...
 9
10 $ kill -TERM ${COPROC_PID}
11 $ wait ${COPROC_PID}
```

--------------------------------

### Implement online migration with pgcopydb follow

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/intro.rst.txt

Perform an online migration by starting the clone process with the follow option, setting the end position for the stream, and cleaning up after completion.

```bash
$ pgcopydb clone --follow &

   # later when the application is ready to make the switch
   $ pgcopydb stream sentinel set endpos --current

   # later when the migration is finished, clean-up both source and target
   $ pgcopydb stream cleanup
```

--------------------------------

### pgcopydb copy extensions usage

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_copy.html

Shows the usage and options for the pgcopydb copy extensions command. Use it to copy extensions from the source instance to the target instance. The command gets a list of extensions installed on the source database and runs CREATE EXTENSION IF NOT EXISTS for each. It also copies any Extension Configuration Tables user-data to the target database.

```text
pgcopydb copy extensions: Copy the extensions from the source instance to the target instance
usage: pgcopydb copy extensions  --source ... --target ...

  --source              Postgres URI to the source database
  --target              Postgres URI to the target database
  --dir                 Work directory to use
  --requirements        List extensions requirements

```

--------------------------------

### Exclusion based filter configuration example

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_config.html

Example of an INI file with exclusion filters: exclude-schema, exclude-table, exclude-index, exclude-table-data, and exclude-extension.

```ini
[exclude-schema]
foo
bar
expected

[exclude-table]
"schema"."name"
schema.othername
err.errors
public.~/^tmp_/

[exclude-index]
schema.indexname

[exclude-table-data]
public.bar
nsitra.test1

[exclude-extension]
aiven_extras

```

--------------------------------

### Simple CDC with pgcopydb clone --follow

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_clone.html

A basic workflow for performing a base copy followed by continuous change streaming, ending with a cleanup of resources.

```bash
1 $ pgcopydb clone --follow &
2
3 # later when the application is ready to make the switch
4 $ pgcopydb stream sentinel set endpos --current
5
6 # later when the migration is finished, clean-up both source and target
7 $ pgcopydb stream cleanup
```

--------------------------------

### Install build dependencies for pgcopydb

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/install.rst.txt

Install the required build dependencies for pgcopydb on a Debian-based system. The list is long because pgcopydb requires many of the same packages as Postgres.

```bash
$ apt-get install -y --no-install-recommends \
    build-essential \
    autotools-dev \
    libedit-dev \
    libgc-dev \
    libpam0g-dev \
    libreadline-dev \
    libselinux1-dev \
    libxslt1-dev \
    libssl-dev \
    libkrb5-dev \
    zlib1g-dev \
    liblz4-dev \
    libpq5 \
    libpq-dev \
    libzstd-dev \
    postgresql-server-dev-all \
    postgresql-common \
    postgresql \
    python3-sphinx
```

--------------------------------

### pgcopydb dump schema example with output and file listing

Source: https://pgcopydb.readthedocs.io/en/latest/ref/pgcopydb_dump.html

Run pgcopydb dump schema with a source connection string and target directory, then inspect the created files. The command outputs INFO/WARN logs and the pg_dump invocation; the find command shows the resulting directory structure including schema.dump and run subdirectories.

```bash
$ pgcopydb dump schema --source "port=5501 dbname=demo" --target /tmp/target
09:35:21 3926 INFO  Dumping database from "port=5501 dbname=demo"
09:35:21 3926 INFO  Dumping database into directory "/tmp/target"
09:35:21 3926 INFO  Found a stale pidfile at "/tmp/target/pgcopydb.pid"
09:35:21 3926 WARN  Removing the stale pid file "/tmp/target/pgcopydb.pid"
09:35:21 3926 INFO  Using pg_dump for Postgres "12.9" at "/Applications/Postgres.app/Contents/Versions/12/bin/pg_dump"
09:35:21 3926 INFO   /Applications/Postgres.app/Contents/Versions/12/bin/pg_dump -Fc --section pre-data --section post-data --file /tmp/target/schema/schema.dump 'port=5501 dbname=demo'

```

```bash
$ find /tmp/target
/tmp/target
/tmp/target/pgcopydb.pid
/tmp/target/schema
/tmp/target/schema/schema.dump
/tmp/target/run
/tmp/target/run/tables
/tmp/target/run/indexes

```

--------------------------------

### pgcopydb exclude-table pattern matching examples (INI)

Source: https://pgcopydb.readthedocs.io/en/latest/_sources/ref/pgcopydb_config.rst.txt

Pattern matching examples for the exclude-table section. Shows how to use regex patterns for schema and table name components.

```ini
[exclude-table]
; exact schema, regex table — excludes all tables starting with "tmp_" in public
public.~/^tmp_/

; regex schema, exact table — excludes "audit_log" in any schema starting with "log"
~/^log/.audit_log

; regex schema, regex table — excludes any table ending with "_bak" in any "archive" schema
~/archive/.~/_bak$/
```
Tokens:
Raw