### 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}$/
```

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

### Configure include-only-extension

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

Example configuration for restricting extension handling to only the listed extensions.

```ini
[include-only-extension]
postgis
uuid-ossp
```

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

### 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
```

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

### 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}
```

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

### 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
```

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

### 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 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$/
```

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

### Build pgcopydb from source with make

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

Build pgcopydb from source using make. The simplest form is 'make clean install', but a more fancy approach is shown here.

```bash
$ make -s clean
$ make -s -j12 install
```

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

### 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

```

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

### Configure exclude-extension

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

Example configuration for excluding specific PostgreSQL extensions by name.

```ini
[exclude-extension]
aiven_extras
timescaledb
```

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

### 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

```

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

### 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

```

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

### pgcopydb stream sub-commands

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

Lists the available sub-commands for pgcopydb stream, including setup, cleanup, prune, prefetch, catchup, replay, sentinel, receive, and apply.

```text
pgcopydb stream: Stream changes from the source database

Available commands:
  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
    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
```

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

### Replay SQL statement format

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

Example of the SQL statements generated during the catchup process, including transaction boundaries and metadata.

```sql
BEGIN; -- {"xid":489,"lsn":"0/236F5A8"}
INSERT INTO "public"."rental" (rental_id, rental_date, inventory_id, customer_id, return_date, staff_id, last_update) VALUES (16050, '2022-06-01 00:00:00+00', 371, 291, NULL, 1, '2022-06-01 00:00:00+00');
INSERT INTO "public"."payment_p2020_06" (payment_id, customer_id, staff_id, rental_id, amount, payment_date) VALUES (32099, 291, 1, 16050, 5.99, '2020-06-01 00:00:00+00');
COMMIT; -- {"xid": 489,"lsn":"0/236F5A8"}
```

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

### Same-table Concurrency via Integer Range Partitioning

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

Example of SELECT queries used by COPY processes to fetch data in parallel when an integer primary key is available.

```sql
COPY (SELECT * FROM source.table WHERE id BETWEEN      1 AND 123456)
COPY (SELECT * FROM source.table WHERE id BETWEEN 123457 AND 234567)
COPY (SELECT * FROM source.table WHERE id BETWEEN 234568 AND 345678)
...
```

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

### 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
```

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

### List schema in JSON format

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

Generates a JSON representation of the database schema, including setup configuration and table details.

```bash
$ pgcopydb list schema --split-at 200kB
```

```json
{
    "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
                },
                {

```

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

### pgcopydb --all-databases progress log lines with database prefix

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

Example progress log output when --all-databases is active. Each DDL and DML line is prefixed with the database name to distinguish interleaved output from workers handling different databases.

```text
pagila: CREATE UNIQUE INDEX actor_pkey ON actor USING btree (actor_id)
f1db:   TRUNCATE ONLY "public"."constructorstandings"
f1db:   COPY "public"."constructorstandings"
chinook: VACUUM ANALYZE "public"."Track"
```

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

### Manual CDC Workflow with pgcopydb

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

A granular approach to CDC that involves manually managing snapshots, stream setup, sequence synchronization, and resource cleanup.

```bash
 1 $ pgcopydb snapshot &
 2
 3 $ pgcopydb stream setup
 4
 5 $ pgcopydb clone --follow &
 6
 7 # later when the application is ready to make the switch
 8 $ pgcopydb stream sentinel set endpos --current
 9
10 # when the follow process has terminated, re-sync the sequences
11 $ pgcopydb copy sequences
12
13 # later when the migration is finished, clean-up both source and target
14 $ pgcopydb stream cleanup
15
16 # now stop holding the snapshot transaction (adjust PID to your environment)
17 $ kill %1
```

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

### Create a snapshot in the background

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

Runs pgcopydb snapshot in the background, exporting a snapshot and printing it to stdout. The output shows the process starting, using a work directory, and exporting the snapshot.

```shell
$ pgcopydb snapshot &
[1] 72938
17:31:52 72938 INFO  Running pgcopydb version 0.7.13.gcbf2d16.dirty from "/Users/dim/dev/PostgreSQL/pgcopydb/./src/bin/pgcopydb/pgcopydb"
17:31:52 72938 INFO  Using work dir "/var/folders/d7/zzxmgs9s16gdxxcm0hs0sssw0000gn/T//pgcopydb"
17:31:52 72938 INFO  Removing the stale pid file "/var/folders/d7/zzxmgs9s16gdxxcm0hs0sssw0000gn/T//pgcopydb/pgcopydb.aux.pid"
17:31:52 72938 INFO  Work directory "/var/folders/d7/zzxmgs9s16gdxxcm0hs0sssw0000gn/T//pgcopydb" already exists
17:31:52 72938 INFO  Exported snapshot "00000003-000CB5FE-1" from the source database
00000003-000CB5FE-1
```

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

### pgcopydb restore parse-list with filters

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

Review filtering options with pgcopydb restore parse-list. The example shows an include.ini filter file and the resulting parse-list output, which lists commented-out catalog entries for constraints and indexes.

```shell
$ cat ./tests/filtering/include.ini
[include-only-table]
public.actor
public.category
public.film
public.film_actor
public.film_category
public.language
public.rental

[exclude-index]
public.idx_store_id_film_id

[exclude-table-data]
public.rental

$ pgcopydb restore parse-list --dir /tmp/pagila/pgcopydb --resume --not-consistent --filters ./tests/filtering/include.ini
11:41:22 75175 INFO  Running pgcopydb version 0.5.8.ge0d2038 from "/Users/dim/dev/PostgreSQL/pgcopydb/./src/bin/pgcopydb/pgcopydb"
11:41:22 75175 INFO  [SOURCE] Restoring database from "postgres://@:54311/pagila?"
11:41:22 75175 INFO  [TARGET] Restoring database into "postgres://@:54311/plop?"
11:41:22 75175 INFO  Using work dir "/tmp/pagila/pgcopydb"
11:41:22 75175 INFO  Removing the stale pid file "/tmp/pagila/pgcopydb/pgcopydb.pid"
11:41:22 75175 INFO  Work directory "/tmp/pagila/pgcopydb" already exists
11:41:22 75175 INFO  Schema dump for pre-data and post-data section have been done
11:41:22 75175 INFO  Restoring database from existing files at "/tmp/pagila/pgcopydb"
11:41:22 75175 INFO  Using pg_restore for Postgres "12.9" at "/Applications/Postgres.app/Contents/Versions/12/bin/pg_restore"
11:41:22 75175 INFO  Exported snapshot "00000003-0003209A-1" from the source database
3242; 2606 317973 CONSTRAINT public actor actor_pkey postgres
;3258; 2606 317975 CONSTRAINT public address address_pkey postgres
3245; 2606 317977 CONSTRAINT public category category_pkey postgres
;3261; 2606 317979 CONSTRAINT public city city_pkey postgres
;3264; 2606 317981 CONSTRAINT public country country_pkey postgres
;3237; 2606 317983 CONSTRAINT public customer customer_pkey postgres
3253; 2606 317985 CONSTRAINT public film_actor film_actor_pkey postgres
3256; 2606 317987 CONSTRAINT public film_category film_category_pkey postgres
3248; 2606 317989 CONSTRAINT public film film_pkey postgres
;3267; 2606 317991 CONSTRAINT public inventory inventory_pkey postgres
3269; 2606 317993 CONSTRAINT public language language_pkey postgres
3293; 2606 317995 CONSTRAINT public rental rental_pkey postgres
;3295; 2606 317997 CONSTRAINT public staff staff_pkey postgres
;3298; 2606 317999 CONSTRAINT public store store_pkey postgres
3246; 1259 318000 INDEX public film_fulltext_idx postgres
3243; 1259 318001 INDEX public idx_actor_last_name postgres
;3238; 1259 318002 INDEX public idx_fk_address postgres
;3259; 1259 318003 INDEX public idx_fk_city_id postgres
;3262; 1259 318004 INDEX public idx_fk_country_id postgres
;3270; 1259 318005 INDEX public idx_fk_customer_id postgres
3254; 1259 318006 INDEX public idx_fk_film_id postgres
3290; 1259 318007 INDEX public idx_fk_inventory_id postgres
3249; 1259 318008 INDEX public idx_fk_language_id postgres
```

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

### Single-process COPY with TRUNCATE and FREEZE

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

Synthetic example of the transaction used when a single COPY process is used, allowing TRUNCATE and COPY FREEZE optimizations. This is not possible with same-table concurrency.

```sql
BEGIN;
TRUNCATE table ONLY;
COPY table FROM stdin WITH (FREEZE);
COMMIT
```

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

### pgcopydb stream sentinel set startpos usage

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

Displays the usage and options for the pgcopydb stream sentinel set startpos command. Use this advanced API for unit-testing and debugging; normal pgcopydb operations cover this automatically. The start LSN is required to bootstrap logical decoding on the target system.

```text
pgcopydb stream sentinel set startpos: Set the sentinel start position LSN
usage: pgcopydb stream sentinel set startpos <start lsn>

  --host        Reach the follow coordinator over TCP at this host
  --port        Follow coordinator TCP port (default 5442)

```

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

### 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
```

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

### List progress command

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

Displays the current migration progress for tables and indexes.

```bash
$ pgcopydb list progress 2>/dev/null
             |  Total Count |  In Progress |         Done
------------+--------------+--------------+-------------
      Tables |           21 |            4 |            7
     Indexes |           48 |           14 |            7
```

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

### PGCOPYDB_LOG_JSON example log output

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

Example JSON log output produced when PGCOPYDB_LOG_JSON is enabled. Shows the fields timestamp, pid, error_level, error_severity, file_name, file_line_num, and message.

```json
{
  "timestamp": "2023-04-13 16:53:14",
  "pid": 87956,
  "error_level": 4,
  "error_severity": "INFO",
  "file_name": "main.c",
  "file_line_num": 165,
  "message": "Running pgcopydb version 0.11.19.g2290494.dirty from \"/Users/dim/dev/PostgreSQL/pgcopydb/src/bin/pgcopydb/pgcopydb\""
}
```

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

### pgcopydb stream sentinel get command usage

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

Displays the usage and available options for the pgcopydb stream sentinel get command, used to retrieve sentinel table values.

```bash
pgcopydb stream sentinel get: Get the sentinel table values
usage: pgcopydb stream sentinel get

  --json           Format the output using JSON
  --startpos       Get only the startpos value
  --endpos         Get only the endpos value
  --apply          Get only the apply value
  --write-lsn      Get only the write LSN value
  --flush-lsn      Get only the flush LSN value
  --transform-lsn  Get only the tranform LSN value
  --replay-lsn     Get only the replay LSN value
  --host           Reach the follow coordinator over TCP at this host
  --port           Follow coordinator TCP port (default 5442)
```

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

### Inclusion based filter configuration example

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

Example of an INI file with inclusion filters: include-only-schema, include-only-table, exclude-index, and exclude-table-data. Option values are not used; only sections and option keys matter.

```ini
[include-only-schema]
public
~/^audit_/

[include-only-table]
public.allcols
public.csv
public.serial
public.xzero

[exclude-index]
public.foo_gin_tsvector

[exclude-table-data]
public.csv

```

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

### pgcopydb stream sentinel get output

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

Shows the default output of the pgcopydb stream sentinel get command, displaying the sentinel table fields: startpos, endpos, apply, write_lsn, flush_lsn, and replay_lsn.

```text
$ pgcopydb stream sentinel get
startpos   1/8D173AF8
endpos     0/0
apply      disabled
write_lsn  0/0
flush_lsn  0/0
replay_lsn 0/0
```

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

### pgcopydb compare command help

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

Shows the available subcommands for pgcopydb compare: schema and data. Use this to see the command structure.

```text
pgcopydb compare: Compare source and target databases

Available commands:
  pgcopydb compare
    schema  Compare source and target schema
    data    Compare source and target data

```

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

### pgcopydb stream prune usage and options

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

Shows the command-line usage and options for `pgcopydb stream prune`. Use `--dry-run` to preview deletions without removing files, `--dir` to specify the work directory in direct mode, and `--host`/`--port` to connect to a running follow process in coordinator mode.

```text
pgcopydb stream prune: Remove already-applied CDC files from disk to reclaim disk space
usage: pgcopydb stream prune  [ --dir ] [ --dry-run ] [ --host ] [ --port ]

  --dir            Work directory to use
  --dry-run        List files that would be removed without deleting them
  --host           Host of the running pgcopydb follow process to connect to
  --port           Port of the running pgcopydb follow process to connect to

```

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

### pgcopydb clone usage and options

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

Full usage line and list of command-line options for pgcopydb clone, including --source, --target, --table-jobs, --index-jobs, and many others.

```text
pgcopydb clone: Clone an entire database from source to target
usage: pgcopydb clone  --source ... --target ... [ --table-jobs ... --index-jobs ... ]

  --source                      Postgres URI to the source database
  --target                      Postgres URI to the target database
  --dir                         Work directory to use
  --table-jobs                  Number of concurrent COPY jobs to run
  --index-jobs                  Number of concurrent CREATE INDEX jobs to run
  --restore-jobs                Number of concurrent jobs for pg_restore
  --large-objects-jobs          Number of concurrent Large Objects jobs to run
  --split-tables-larger-than    Same-table concurrency size threshold
  --split-max-parts             Maximum number of jobs for Same-table concurrency
  --estimate-table-sizes        Allow using estimates for relation sizes
  --drop-if-exists              On the target database, clean-up from a previous run first
  --roles                       Also copy roles found on source to target
  --no-role-passwords           Do not dump passwords for roles
  --no-owner                    Do not set ownership of objects to match the original database
  --no-acl                      Prevent restoration of access privileges (grant/revoke commands).
  --no-comments                 Do not output commands to restore comments
  --no-tablespaces              Do not output commands to select tablespaces
  --skip-large-objects          Skip copying large objects (blobs)
  --skip-extensions             Skip restoring extensions
  --skip-ext-comments           Skip restoring COMMENT ON EXTENSION
  --skip-collations             Skip restoring collations
  --skip-vacuum                 Skip running VACUUM ANALYZE
  --skip-analyze                Skip running vacuumdb --analyze-only
  --skip-db-properties          Skip copying ALTER DATABASE SET properties
  --skip-split-by-ctid          Skip spliting tables by ctid
  --requirements <filename>     List extensions requirements
  --filters <filename>          Use the filters defined in <filename>
  --fail-fast                   Abort early in case of error
  --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
  --follow                      Implement logical decoding to replay changes
  --plugin                      Output plugin to use (pgoutput, test_decoding, wal2json)
  --wal2json-numeric-as-string  Print numeric data type as string when using wal2json output plugin
  --slot-name                   Use this Postgres replication slot name
  --create-slot                 Create the replication slot
  --origin                      Use this Postgres replication origin node name
  --endpos                      Stop replaying changes when reaching this LSN
  --use-copy-binary             Use the COPY BINARY format for COPY operations
  --all-databases               Clone all databases found on the source instance

```

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

### pgcopydb usage and available commands

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

Shows the command-line usage and the list of available pgcopydb commands. Use this to see the main operations supported by the tool.

```text
pgcopydb: pgcopydb tool
usage: pgcopydb [ --verbose --quiet ]


Available commands:
  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 list command overview

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

Shows the available subcommands under pgcopydb list.

```text
pgcopydb list: List database objects from a Postgres instance

Available commands:
  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
```

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

### Dump database schema

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

Example of using pgcopydb dump schema to export the schema from a source database to a target directory.

```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'
```

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

### pgcopydb restore command overview

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

Displays the available sub-commands for the pgcopydb restore utility.

```bash
pgcopydb restore: Restore database objects into a Postgres instance

Available commands:
  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
    parse-list  Parse pg_restore --list output from custom file
```

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

### Compare schema with notice output

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

Executes the schema comparison command with the --notice flag to display detailed inspection logs.

```bash
$ pgcopydb compare schema --notice
INFO   Running pgcopydb version 0.12.28.g34343c8.dirty from "/Users/dim/dev/PostgreSQL/pgcopydb/src/bin/pgcopydb/pgcopydb"
NOTICE Using work dir "/var/folders/d7/zzxmgs9s16gdxxcm0hs0sssw0000gn/T//pgcopydb"
NOTICE Work directory "/var/folders/d7/zzxmgs9s16gdxxcm0hs0sssw0000gn/T//pgcopydb" already exists
INFO   A previous run has run through completion
INFO   SOURCE: Connecting to "postgres:///pagila"
INFO   Fetched information for 1 extensions
INFO   Fetched information for 25 tables, with an estimated total of 5179  tuples and 190 MB
INFO   Fetched information for 49 indexes
INFO   Fetching information for 16 sequences
NOTICE Skipping target catalog preparation
NOTICE Storing migration schema in JSON file "/var/folders/d7/zzxmgs9s16gdxxcm0hs0sssw0000gn/T//pgcopydb/compare/source-schema.json"
INFO   TARGET: Connecting to "postgres:///plop"
INFO   Fetched information for 6 extensions
INFO   Fetched information for 25 tables, with an estimated total of 5219  tuples and 190 MB
INFO   Fetched information for 49 indexes
INFO   Fetching information for 16 sequences
NOTICE Skipping target catalog preparation
NOTICE Storing migration schema in JSON file "/var/folders/d7/zzxmgs9s16gdxxcm0hs0sssw0000gn/T//pgcopydb/compare/target-schema.json"
INFO   [SOURCE] table: 25 index: 49 sequence: 16
INFO   [TARGET] table: 25 index: 49 sequence: 16
NOTICE Matched table "public"."test": 1 columns ok, 0 indexes ok
NOTICE Matched table "public"."rental": 7 columns ok, 3 indexes ok
NOTICE Matched table "public"."film": 14 columns ok, 5 indexes ok
NOTICE Matched table "public"."film_actor": 3 columns ok, 2 indexes ok
NOTICE Matched table "public"."inventory": 4 columns ok, 2 indexes ok
NOTICE Matched table "public"."payment_p2022_03": 6 columns ok, 3 indexes ok
NOTICE Matched table "public"."payment_p2022_05": 6 columns ok, 3 indexes ok
NOTICE Matched table "public"."payment_p2022_06": 6 columns ok, 3 indexes ok
NOTICE Matched table "public"."payment_p2022_04": 6 columns ok, 3 indexes ok
NOTICE Matched table "public"."payment_p2022_02": 6 columns ok, 3 indexes ok
NOTICE Matched table "public"."payment_p2022_07": 6 columns ok, 0 indexes ok
NOTICE Matched table "public"."customer": 10 columns ok, 4 indexes ok
NOTICE Matched table "public"."address": 8 columns ok, 2 indexes ok
NOTICE Matched table "public"."city": 4 columns ok, 2 indexes ok
NOTICE Matched table "public"."film_category": 3 columns ok, 1 indexes ok
NOTICE Matched table "public"."payment_p2022_01": 6 columns ok, 3 indexes ok
NOTICE Matched table "public"."actor": 4 columns ok, 2 indexes ok
NOTICE Matched table "public"."bar": 2 columns ok, 1 indexes ok
NOTICE Matched table "public"."bin": 2 columns ok, 0 indexes ok
NOTICE Matched table "public"."category": 3 columns ok, 1 indexes ok
NOTICE Matched table "public"."country": 3 columns ok, 1 indexes ok
NOTICE Matched table "public"."foo": 2 columns ok, 1 indexes ok
NOTICE Matched table "public"."staff": 11 columns ok, 1 indexes ok
NOTICE Matched table "public"."language": 3 columns ok, 1 indexes ok
NOTICE Matched table "public"."store": 4 columns ok, 2 indexes ok
NOTICE Matched sequence "public"."actor_actor_id_seq" (last value 200)
NOTICE Matched sequence "public"."address_address_id_seq" (last value 605)
NOTICE Matched sequence "public"."bar_id_seq" (last value 1)
NOTICE Matched sequence "public"."bin_id_seq" (last value 17)
NOTICE Matched sequence "public"."category_category_id_seq" (last value 16)
NOTICE Matched sequence "public"."city_city_id_seq" (last value 600)
NOTICE Matched sequence "public"."country_country_id_seq" (last value 109)
NOTICE Matched sequence "public"."customer_customer_id_seq" (last value 599)
NOTICE Matched sequence "public"."film_film_id_seq" (last value 1000)
NOTICE Matched sequence "public"."foo_id_seq" (last value 1)
NOTICE Matched sequence "public"."inventory_inventory_id_seq" (last value 4581)
NOTICE Matched sequence "public"."language_language_id_seq" (last value 6)
NOTICE Matched sequence "public"."payment_payment_id_seq" (last value 32102)
NOTICE Matched sequence "public"."rental_rental_id_seq" (last value 16053)
NOTICE Matched sequence "public"."staff_staff_id_seq" (last value 2)
NOTICE Matched sequence "public"."store_store_id_seq" (last value 2)
INFO   pgcopydb schema inspection is successful
```

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

### Get sentinel table status in JSON format

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

Fetches the sentinel table status as a JSON object for programmatic access.

```json
{
  "startpos": "1/8D173AF8",
  "endpos": "1/8D173AF8",
  "apply": false,
  "write_lsn": "0/0",
  "flush_lsn": "0/0",
  "replay_lsn": "0/0"
}
```

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

### pgcopydb version and version --json output

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

Use `pgcopydb version` to print the version string and build details. The `--json` option outputs the same information as a JSON object, including the libpq client library version used to build pgcopydb.

```text
$ pgcopydb version
pgcopydb version 0.13.1.g868ad77
compiled with PostgreSQL 13.11 (Debian 13.11-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
compatible with Postgres 14, 15, 16, 17, and 18
```

```json
$ pgcopydb version --json
{
    "pgcopydb": "0.13.1.g868ad77",
    "pg_major": "13",
    "pg_version": "13.11 (Debian 13.11-0+deb11u1)",
    "pg_version_str": "PostgreSQL 13.11 (Debian 13.11-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit",
    "pg_version_num": 130011
}
```

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

### CDC Test Case SQL Transaction

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

An example of SQL statements executed during a CDC test case, involving an insert into rental followed by an insert into payment.

```sql
 1 begin;
 2
 3 with r as
 4  (
 5    insert into rental(rental_date, inventory_id, customer_id, staff_id, last_update)
 6         select '2022-06-01', 371, 291, 1, '2022-06-01'
 7      returning rental_id, customer_id, staff_id
 8  )
 9  insert into payment(customer_id, staff_id, rental_id, amount, payment_date)
10       select customer_id, staff_id, rental_id, 5.99, '2020-06-01'
11         from r;
12
13 commit;
```

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

### JSON sequence metadata output

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

Example of the JSON structure returned by pgcopydb list for database sequences, including OID, schema, name, and restore list name.

```json
1202            "last-value": 32099,
1203            "is-called": true,
1204            "restore-list-name": "public payment_payment_id_seq postgres"
1205        },
1206        {
1207            "oid": 317932,
1208            "schema": "public",
1209            "name": "rental_rental_id_seq",
1210            "last-value": 16050,
1211            "is-called": true,
1212            "restore-list-name": "public rental_rental_id_seq postgres"
1213        },
1214        {
1215            "oid": 317944,
1216            "schema": "public",
1217            "name": "staff_staff_id_seq",
1218            "last-value": 2,
1219            "is-called": true,
1220            "restore-list-name": "public staff_staff_id_seq postgres"
1221        },
1222        {
1223            "oid": 317955,
1224            "schema": "public",
1225            "name": "store_store_id_seq",
1226            "last-value": 2,
1227            "is-called": true,
1228            "restore-list-name": "public store_store_id_seq postgres"
1229        }
1230    ]
1231}
```

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

### Clone database with environment variables

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

Sets source and target connection strings via environment variables and runs pgcopydb clone. Both databases must already exist. The --source and --target options can be used instead, but environment variables are useful with Docker.

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

$ pgcopydb clone
```

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

### pgcopydb list progress usage

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

Shows the usage and options for the pgcopydb list progress command. Use it to list the progress of the migration. The --source option is required, --summary requires --json, and --dir specifies the work directory.

```text
pgcopydb list progress: List the progress
usage: pgcopydb list progress  --source ...

  --source  Postgres URI to the source database
  --summary List the summary, requires --json
  --json    Format the output using JSON
  --dir     Work directory to use

```

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

### Run vacuumdb --analyze on target database

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

Runs VACUUM ANALYZE on each table that has just been filled with data, using the vacuumdb command from Postgres. The --dbname option uses the $PGCOPYDB_TARGET_PGURI environment variable, and --jobs 4 runs four parallel jobs. The output shows the vacuuming of the "pagila" database.

```shell
$ vacuumdb --analyze --dbname "$PGCOPYDB_TARGET_PGURI" --jobs 4
vacuumdb: vacuuming database "pagila"

```

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

### Verify dump output directory

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

Use the find command to inspect the directory structure created after a successful schema dump.

```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
```

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

### Run pgcopydb compare commands

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

Execute schema and data comparison commands for Postgres instances.

```bash
$ pgcopydb compare schema
$ pgcopydb compare data
```