PGCOPYDB STREAM(1)

📦

version 0.18 · 6 versions

0.18 · ubuntu stonking (latest)
0.17 · nixos 26.05
0.17-unstable-2026-05 · nixpkgs unstable
0.15 · ubuntu noble
0.10 · debian bookworm
0.17 · debian trixie, ubuntu questing

💅 PGCOPYDB STREAM(1) 💅

okay bestie sit DOWN because this is the pgcopydb manual, and today's episode is all about pgcopydb stream. yes, the command that ships with a whole bundle of sub-commands. get cozy, hydrate, we're going in 🫶

✨ NAME ✨

pgcopydb stream — pgcopydb stream

pgcopydb stream — Stream changes from source database

⚠️ WARNING, bestie, read this one fr fr: this mode of operations has been designed for unit testing only. Like, exclusively. So consider using the pgcopydb clone command (with the --follow option) or the pgcopydb follow command instead. that's the main character path 💅

📝 NOTE: some pgcopydb stream commands are still designed for normal operations, rather than unit testing only. so it's not all test-only up in here, okay? The pgcopydb stream sentinel set startpos, pgcopydb stream sentinel set endpos, pgcopydb stream sentinel set apply, and pgcopydb stream sentinel set prefetch commands are necessary to communicate with the main pgcopydb clone --follow or pgcopydb follow process. See Change Data Capture Example 1 for a detailed example using pgcopydb stream sentinel set endpos. By default these commands read/write the sentinel directly in the source SQLite catalog, which requires running where the catalog files live. Pass --host (and optionally --port) to instead talk to the running follow process over TCP, with no shared catalog files. See Sentinel Control. Also the commands pgcopydb stream setup and pgcopydb stream cleanup might be used directly in normal operations. See Change Data Capture Example 2 for a detailed example.

This command prefixes the following sub-commands:

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

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

pgcopydb stream sentinel set: Set the sentinel table values

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

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

Those commands implement a part of the whole database replay operation as detailed in section pgcopydb follow. Only use those commands to debug a specific part, or because you know that you just want to implement that step. no vibes-based guessing, bestie — know which step you're doing 👑

📝 NOTE: the sub-commands stream setup then stream prefetch and stream catchup are higher level commands that use internal information to track their progress in the SQLite Change Data Capture store. The sub-commands stream receive and stream apply are lower level interfaces to the same SQLite store: receive writes decoded changes to the CDC output database, and apply performs the inline transform and writes the resulting statements to the CDC replay database before applying them to the target. two layers, one store, iconic behaviour ✨

🛠️ PGCOPYDB STREAM SETUP 🛠️

pgcopydb stream setup — Setup source and target systems for logical decoding

The command pgcopydb stream setup connects to the target database and creates a replication origin positioned at the LSN position of the logical decoding replication slot that must have been created already. See pgcopydb snapshot to create the replication slot and export a snapshot.

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 CLEANUP 🧹

pgcopydb stream cleanup — cleanup source and target systems for logical decoding

The command pgcopydb stream cleanup connects to the source and target databases to delete the objects created in the pgcopydb stream setup step. yes, it's the undo button and it's lowkey satisfying 💅

pgcopydb stream cleanup: Cleanup source and target systems for logical decoding
usage: pgcopydb stream cleanup

  --source         Postgres URI to the source database
  --target         Postgres URI to the target database
  --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
  --slot-name      Stream changes recorded by this slot
  --origin         Name of the Postgres replication origin

🗑️ PGCOPYDB STREAM PRUNE 🗑️

pgcopydb stream prune — Remove already-applied CDC files from disk to reclaim disk space

The command pgcopydb stream prune removes CDC output.db / replay.db file pairs that have already been fully applied to the target and are no longer needed for resume or restart. bye bestie, you served your purpose 🫡

A file pair is safe to delete when:

its endpos is strictly less than sentinel.replay_lsn (the LSN acknowledged back to the PostgreSQL replication slot as confirmed_flush), and
its done_time_epoch is set (the receive process has closed it).

Those transactions have been durably committed on the target, so the slot will never re-deliver them. no cap, they're gone-gone.

The deleted entries are removed from the cdc_files catalog table in source.db. The freed SQLite freelist pages are recycled automatically when the receive process inserts new cdc_files rows, so no explicit VACUUM is needed. the database handles its own glow-up ✨

The command runs in two modes:

Direct mode (--dir, no --host): opens source.db in the work directory directly. Suitable when the follow process is not running or the caller shares the filesystem.
Coordinator mode (--host / --port): sends an IPC message to a running pgcopydb follow (or pgcopydb clone --follow) process. The follow process owns the SQLite catalog exclusively and performs the cleanup safely without requiring shared filesystem access.

The follow process also runs stream prune automatically every 300 seconds (matching PostgreSQL's default checkpoint_timeout), so manual invocation is optional during a live follow operation. it's giving self-care, but automated 🌸

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 STREAM PREFETCH 📥

pgcopydb stream prefetch — Stream changes from the source database into the SQLite CDC store

The command pgcopydb stream prefetch connects to the source database using the logical replication protocol and the given replication slot. it's literally just vibing with the replication stream ✨

The prefetch command receives the changes from the source database in a streaming fashion and writes them to the SQLite Change Data Capture output database (named <timeline>-<startlsn>-output.db). The transform into replay statements happens later, inside the stream catchup (apply) step. not here, not today bestie 💅

pgcopydb stream prefetch: Stream changes from the source database into the SQLite CDC store
usage: pgcopydb stream prefetch

  --source         Postgres URI to the source 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
  --slot-name           Stream changes recorded by this slot
  --endpos              LSN position where to stop receiving changes
  --max-replaydb-size   Rotate CDC files at this size (default 1GB)

🎬 PGCOPYDB STREAM CATCHUP 🎬

pgcopydb stream catchup — Transform and apply prefetched changes to the target database

The command pgcopydb stream catchup connects to the target database, transforms the changes prefetched into the CDC output database into replay statements (stored in the CDC replay database), and applies them to the target. Progress is tracked using the Postgres replication origin. this is the main character step, no debate 🎀

pgcopydb stream catchup: Transform and apply prefetched changes from the SQLite CDC store to the target
usage: pgcopydb stream catchup

  --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
  --slot-name           Stream changes recorded by this slot
  --endpos              LSN position where to stop receiving changes
  --max-replaydb-size   Rotate CDC files at this size (default 1GB)
  --origin              Name of the Postgres replication origin

✨ okay that's part 1 bestie, the rest is coming, don't go anywhere ✨

🎬 PGCOPYDB STREAM REPLAY

pgcopydb stream replay — replay changes from the source to the target database, live 💖

okay bestie so this one? this one is the whole pipeline in one command. pgcopydb stream replay connects to the source database and streams changes using the logical decoding protocol, running the receive and apply stages together as a single live pipeline that applies changes to the target database as they arrive. like, no middleman, no waiting, it's giving ✨streaming✨.

pgcopydb stream replay: Replay changes from the source to the target database, live
usage: pgcopydb stream replay

  --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
  --slot-name           Stream changes recorded by this slot
  --endpos              LSN position where to stop receiving changes
  --max-replaydb-size   Rotate CDC files at this size (default 1GB)
  --origin              Name of the Postgres replication origin

This command is equivalent to running pgcopydb stream receive and pgcopydb stream apply together, no cap: the apply stage performs the inline transform from the CDC output database to the CDC replay database before applying the changes to the target. 🫶

🛰️ PGCOPYDB STREAM SENTINEL GET

pgcopydb stream sentinel get — get the sentinel table values 👑

lowkey the sentinel is the little bookkeeper that knows where everything's at, and this command is how you peek at it:

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)

(yes, --transform-lsn says "tranform" upstream, and we keep it exactly as-is because facts are facts fr fr 🎀)

📍 PGCOPYDB STREAM SENTINEL SET STARTPOS

pgcopydb stream sentinel set startpos — set the sentinel start position LSN

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)

Heads up bestie: this is an advanced API used for unit-testing and debugging. The operation is automatically covered in normal pgcopydb operations, so you probably never need to touch it yourself 💅

so here's the lore: a logical replication target system registers progress by assigning a current LSN to the --origin node name. When you're creating an origin on the target database system, it is required to provide the current LSN from the source database system, in order to properly bootstrap pgcopydb logical decoding. that's the whole point of this thing ✨

🏁 PGCOPYDB STREAM SENTINEL SET ENDPOS

pgcopydb stream sentinel set endpos — set the sentinel end position LSN 🎯

pgcopydb stream sentinel set endpos: Set the sentinel end position LSN
usage: pgcopydb stream sentinel set endpos [ --source ... ] [ <end lsn> | --current ]

  --source      Postgres URI to the source database
  --current     Use pg_current_wal_flush_lsn() as the endpos
  --host        Reach the follow coordinator over TCP at this host
  --port        Follow coordinator TCP port (default 5442)

This is the LSN to use on the logical replication target. It automatically stops replication and exits with normal exit status 0 when receiving reaches the specified LSN. And if there's a record with an LSN exactly equal to the lsn you gave, that record will be output. periodt 💯

⚠️ okay important caveat, listen up: the --endpos option is not aware of transaction boundaries and may truncate output partway through a transaction. Any partially output transaction will not be consumed and will be replayed again when the slot is next read from. Individual messages are never truncated though, so that part's safe 🌸

See also the documentation for pg_recvlogical <https://www.postgresql.org/docs/current/app-pgrecvlogical.html>. 🔥

🎀 PGCOPYDB STREAM SENTINEL SET APPLY

pgcopydb stream sentinel set apply — set the sentinel apply mode

pgcopydb stream sentinel set apply: Set the sentinel apply mode
usage: pgcopydb stream sentinel set apply

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

flip the apply switch, that's the vibe ✨

🧚 PGCOPYDB STREAM SENTINEL SET PREFETCH

pgcopydb stream sentinel set prefetch — set the sentinel prefetch mode

pgcopydb stream sentinel set prefetch: Set the sentinel prefetch mode
usage: pgcopydb stream sentinel set prefetch

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

and this one flips the prefetch switch 💅 iconic behavior honestly.

📡 PGCOPYDB STREAM RECEIVE

pgcopydb stream receive — stream changes from the source database 🌈

The command pgcopydb stream receive connects to the source database using the logical replication protocol and the given replication slot.

The receive command receives the changes from the source database in a streaming fashion and writes them to the SQLite Change Data Capture output database. It can also stream raw decoded messages to standard output with --to-stdout. it's giving "I write receipts" 💖

Note, bestie, big one: the separate pgcopydb stream transform command has been removed. In the SQLite-based pipeline the transform from the output database to the replay database is performed inline by the apply stage (see pgcopydb stream apply). so don't go looking for it, she's gone 👋

pgcopydb stream receive: Stream changes from the source database
usage: pgcopydb stream receive

  --source              Postgres URI to the source database
  --dir                 Work directory to use
  --to-stdout           Stream logical decoding messages to stdout
  --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
  --slot-name           Stream changes recorded by this slot
  --endpos              LSN position where to stop receiving changes
  --max-replaydb-size   Rotate CDC files at this size (default 1GB)

🦋 PGCOPYDB STREAM APPLY

pgcopydb stream apply — apply changes from the CDC store to the target database 💫

The command pgcopydb stream apply transforms the changes received into the CDC output database into replay statements (stored in the CDC replay database) and applies them to the target database. The apply process tracks progress thanks to the Postgres API for Replication Progress Tracking <https://www.postgresql.org/docs/current/replication-origins.html>. she literally keeps a diary of her progress 🫶

pgcopydb stream apply: Apply changes from the replayDB to the target database, or stdout
usage: pgcopydb stream apply

  --target         Postgres URI to the target database
                   Use '-' to emit SQL to stdout without connecting
  --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
  --origin         Name of the Postgres replication origin

Use --target - to emit the SQL to standard output instead of connecting to a target database (used by the unit tests). yep, a literal dash, no database, just SQL to your eyeballs 🔥

✨ OPTIONS ✨

These are the options 👑 that all the pgcopydb stream sub-commands share. Like, the shared group chat rules, bestie 💖. Same flags everywhere, no cap.

--source — Connection string for the source Postgres instance. The Postgres docs on connection strings live at https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING if you wanna go deep 💅. Short version: both the quoted form "host=... dbname=..." and the URI form postgres://user@host:5432/dbname are supported. pick your fave, we don't judge.
--target — Connection string for the target Postgres instance. same energy as --source, just the other side of the glow-up ✨.
--dir — During its normal operations pgcopydb creates SO many temporary files to track sub-processes progress 😭. Like, a lot. They get created in the directory you name with this option, or they default to ${TMPDIR}/pgcopydb when that environment variable is set, or otherwise to /tmp/pgcopydb. Change Data Capture files get stored in the cdc sub-directory of whatever --dir you gave, otherwise go look at the XDG_DATA_HOME environment variable below 👇.
--restart — So when you run the pgcopydb command again and the work directory already has info from a previous run, the command refuses to proceed and delete stuff that might be useful for diagnostics and forensics 🙅‍♀️. In that case --restart is the glow-up: it lets pgcopydb delete traces from a previous run 💀.
--resume — When a pgcopydb command was terminated before completion — whether by an interrupt signal (like Ctrl+C or SIGTERM) or because it literally crashed — you can resume the database migration 🫶. And to resume a streaming operation in a consistent way, all that's required is re-using the same replication slot as in previous run(s). that's it, that's the whole trick ✨.
--plugin — Which logical decoding output plugin to use. The default is pgoutput (https://www.postgresql.org/docs/current/protocol-logical-replication.html), which is built into PostgreSQL core (since Postgres 10) and needs zero extension installation on the source server 🔥. Using pgoutput avoids needing superuser to install an extension and works with any PostgreSQL publication-based setup — it's giving convenient 💅. You can also use test_decoding (https://www.postgresql.org/docs/current/test-decoding.html) which ships with Postgres core but does not support all data types as well as pgoutput does, or wal2json (https://github.com/eulerto/wal2json/) which is an external extension. Both of those are still supported for backwards compatibility, bestie 🎀.
--wal2json-numeric-as-string — When you're using the wal2json output plugin, you can use this option to instruct wal2json to output numeric values as strings, which prevents some precision loss ✨. You need a wal2json plugin version on the source database that supports the --numeric-data-types-as-string option to use this 🙈. See also the wal2json documentation at https://github.com/eulerto/wal2json/pull/255 for the details on this option.
--replay-no-op-updates — When set, pgcopydb replays UPDATE statements even when literally no column values changed between the old and the new tuple 👀. By default, those no-op UPDATEs are skipped as an optimisation — and that default is appropriate for migration workloads where target triggers do not need to fire for every UPDATE. Turn this on when your target database has UPDATE triggers that must fire for every UPDATE, including the ones that don't change any column values. PostgreSQL's own logical replication always replays all UPDATEs, and this option restores that behaviour 🔥. No-op UPDATEs show up most often with REPLICA IDENTITY FULL tables, where both the old and the new full row are available for comparison 🧚.
--slot-name — Logical decoding slot name to use. that's the whole thing 💅.
--origin — Logical replication target systems need to track which transactions have already been applied, so that if we get disconnected or need to resume operations we can skip already-replayed transactions 🫶. Postgres has this notion of an origin node name, documented in Replication Progress Tracking at https://www.postgresql.org/docs/current/replication-origins.html. This option lets you pick your own node name and it defaults to "pgcopydb". Picking a different name is useful in some advanced scenarios, like migrating several sources into the same target, where each source should have its own unique origin node name 🌈.
--host — Optional follow-coordinator TCP endpoint host. On clone --follow / follow / stream replay (the server side), when set, the follow process starts a TCP coordinator listening on --host:--port — use 0.0.0.0 to accept connections from other hosts/containers 😤. On stream sentinel get/set (the client side), when set, the command talks to that coordinator over TCP instead of opening the SQLite catalog directly, so it works without sharing the catalog files, iconic behavior ✨. See Sentinel Control <#sentinel-protocol>. On the server side this may also be provided via the PGCOPYDB_HOST environment variable 💜.
--port — TCP port for the follow-coordinator endpoint (see --host). Defaults to 5442. On the server side this may also be provided via the PGCOPYDB_PORT environment variable 💜.
--verbose — Increase current verbosity. The default level of verbosity is INFO. In ascending order, pgcopydb knows about these verbosity levels: FATAL, ERROR, WARN, INFO, NOTICE, DEBUG, TRACE. Main character energy, just louder 📢.
--debug — Set current verbosity to DEBUG level. for when you need the tea 🍵.
--trace — Set current verbosity to TRACE level. the most unhinged amount of detail, and we respect it 💀.
--quiet — Set current verbosity to ERROR level. shhh 🤫, only the bad news gets through.
--dry-run — Used with pgcopydb stream prune. It lists the CDC file pairs that would be deleted without actually removing them from disk or from the catalog 🙅‍♀️. Super useful to preview how much disk space would be reclaimed before you commit to the deletion. look before you leap, bestie 🦋.
--max-replaydb-size — Maximum on-disk size of a single SQLite output.db file before it gets rotated to a new file pair 🔄. Accepts human-readable units: 1kB, 500MB, 1GB, etc. Defaults to 1GB. A transaction larger than the threshold is always kept intact in one file — rotation only happens at a transaction commit boundary. Which means a single oversized transaction causes the file to exceed the threshold, and that's expected and safe 💖. no cap.

🌸 ENVIRONMENT 🌸

The env vars pulling up to the function, bestie. Set these and the command line options don't even gotta show up 💅.

`PGCOPYDB_SOURCE_PGURI`

Connection string to the source Postgres instance. When --source is omitted from the command line, then this environment variable is used ✨.

`PGCOPYDB_TARGET_PGURI`

Connection string to the target Postgres instance. When --target is omitted from the command line, then this environment variable is used ✨.

`PGCOPYDB_OUTPUT_PLUGIN`

Logical decoding output plugin to use. When --plugin is omitted from the command line, then this environment variable is used 🎀.

`PGCOPYDB_WAL2JSON_NUMERIC_AS_STRING`

When true (or yes, or on, or 1 — same input as a Postgres boolean, iykyk) then pgcopydb uses the wal2json option --numeric-data-types-as-string when using the wal2json output plugin 🍓.

When --wal2json-numeric-as-string is omitted from the command line, then this environment variable is used 💜.

`TMPDIR`

The pgcopydb command creates all its work files and directories in ${TMPDIR}/pgcopydb, and defaults to /tmp/pgcopydb 🫶.

`XDG_DATA_HOME`

The pgcopydb command creates Change Data Capture files in the standard place XDG_DATA_HOME, which defaults to ~/.local/share ✨. See the XDG Base Directory Specification at https://specifications.freedesktop.org/basedir-spec/basedir-spec-latest.html if you're the kind of bestie who reads specs for fun (same, honestly) 📚.

✨ EXAMPLES ✨

Note: the captured output below predates the SQLite-based Change Data Capture store, and it's kept around purely to show you what the logical decoding messages look like 💅 The current implementation stores received changes in the CDC output SQLite database and the transformed statements in the CDC replay SQLite database, rather than in .json and .sql files on disk. So don't come for me about the file paths, bestie — they're a historical artifact 🫶

Okay so here's the tea ☕ — this is the output generated from running the cdc test case, where a replication slot gets created before the initial copy of the data happens, and then this INSERT statement gets executed:

begin;

with r as
 (
   insert into rental(rental_date, inventory_id, customer_id, staff_id, last_update)
        select '2022-06-01', 371, 291, 1, '2022-06-01'
     returning rental_id, customer_id, staff_id
 )
 insert into payment(customer_id, staff_id, rental_id, amount, payment_date)
      select customer_id, staff_id, rental_id, 5.99, '2020-06-01'
        from r;

commit;

Then the command looks like the following, where the --endpos has been extracted by calling the pg_current_wal_lsn() SQL function. Not me guessing — that's literally how it's done 💖

$ pgcopydb stream receive --slot-name test_slot --restart --endpos 0/236D668 -vv
16:01:57 157 INFO  Running pgcopydb version 0.7 from "/usr/local/bin/pgcopydb"
16:01:57 157 DEBUG copydb.c:406 Change Data Capture data is managed at "/var/lib/postgres/.local/share/pgcopydb"
16:01:57 157 INFO  copydb.c:73 Using work dir "/tmp/pgcopydb"
16:01:57 157 DEBUG pidfile.c:143 Failed to signal pid 34: No such process
16:01:57 157 DEBUG pidfile.c:146 Found a stale pidfile at "/tmp/pgcopydb/pgcopydb.pid"
16:01:57 157 INFO  pidfile.c:147 Removing the stale pid file "/tmp/pgcopydb/pgcopydb.pid"
16:01:57 157 INFO  copydb.c:254 Work directory "/tmp/pgcopydb" already exists
16:01:57 157 INFO  copydb.c:258 A previous run has run through completion
16:01:57 157 INFO  copydb.c:151 Removing directory "/tmp/pgcopydb"
16:01:57 157 DEBUG copydb.c:445 rm -rf "/tmp/pgcopydb" && mkdir -p "/tmp/pgcopydb"
16:01:57 157 DEBUG copydb.c:445 rm -rf "/tmp/pgcopydb/schema" && mkdir -p "/tmp/pgcopydb/schema"
16:01:57 157 DEBUG copydb.c:445 rm -rf "/tmp/pgcopydb/run" && mkdir -p "/tmp/pgcopydb/run"
16:01:57 157 DEBUG copydb.c:445 rm -rf "/tmp/pgcopydb/run/tables" && mkdir -p "/tmp/pgcopydb/run/tables"
16:01:57 157 DEBUG copydb.c:445 rm -rf "/tmp/pgcopydb/run/indexes" && mkdir -p "/tmp/pgcopydb/run/indexes"
16:01:57 157 DEBUG copydb.c:445 rm -rf "/var/lib/postgres/.local/share/pgcopydb" && mkdir -p "/var/lib/postgres/.local/share/pgcopydb"
16:01:57 157 DEBUG pgsql.c:2476 starting log streaming at 0/0 (slot test_slot)
16:01:57 157 DEBUG pgsql.c:485 Connecting to [source] "postgres://postgres@source:/postgres?password=****&replication=database"
16:01:57 157 DEBUG pgsql.c:2009 IDENTIFY_SYSTEM: timeline 1, xlogpos 0/236D668, systemid 7104302452422938663
16:01:57 157 DEBUG pgsql.c:3188 RetrieveWalSegSize: 16777216
16:01:57 157 DEBUG pgsql.c:2547 streaming initiated
16:01:57 157 INFO  stream.c:237 Now streaming changes to "/var/lib/postgres/.local/share/pgcopydb/000000010000000000000002.json"
16:01:57 157 DEBUG stream.c:341 Received action B for XID 488 in LSN 0/236D638
16:01:57 157 DEBUG stream.c:341 Received action I for XID 488 in LSN 0/236D178
16:01:57 157 DEBUG stream.c:341 Received action I for XID 488 in LSN 0/236D308
16:01:57 157 DEBUG stream.c:341 Received action C for XID 488 in LSN 0/236D638
16:01:57 157 DEBUG pgsql.c:2867 pgsql_stream_logical: endpos reached at 0/236D668
16:01:57 157 DEBUG stream.c:382 Flushed up to 0/236D668 in file "/var/lib/postgres/.local/share/pgcopydb/000000010000000000000002.json"
16:01:57 157 INFO  pgsql.c:3030 Report write_lsn 0/236D668, flush_lsn 0/236D668
16:01:57 157 DEBUG pgsql.c:3107 end position 0/236D668 reached by WAL record at 0/236D668
16:01:57 157 DEBUG pgsql.c:408 Disconnecting from [source] "postgres://postgres@source:/postgres?password=****&replication=database"
16:01:57 157 DEBUG stream.c:414 streamClose: closing file "/var/lib/postgres/.local/share/pgcopydb/000000010000000000000002.json"
16:01:57 157 INFO  stream.c:171 Streaming is now finished after processing 4 messages

Then that JSON file contains the following content, from the wal2json logical replication plugin 🦋 And heads up: you're seeing different LSNs here because each run produces different ones, and the captures have not all been made from the same run. So don't panic if the numbers don't match up, babe — it's not a bug, it's just vibes ✨

$ cat /var/lib/postgres/.local/share/pgcopydb/000000010000000000000002.json
{"action":"B","xid":489,"timestamp":"2022-06-27 13:24:31.460822+00","lsn":"0/236F5A8","nextlsn":"0/236F5D8"}
{"action":"I","xid":489,"timestamp":"2022-06-27 13:24:31.460822+00","lsn":"0/236F0E8","schema":"public","table":"rental","columns":[{"name":"rental_id","type":"integer","value":16050},{"name":"rental_date","type":"timestamp with time zone","value":"2022-06-01 00:00:00+00"},{"name":"inventory_id","type":"integer","value":371},{"name":"customer_id","type":"integer","value":291},{"name":"return_date","type":"timestamp with time zone","value":null},{"name":"staff_id","type":"integer","value":1},{"name":"last_update","type":"timestamp with time zone","value":"2022-06-01 00:00:00+00"}]}
{"action":"I","xid":489,"timestamp":"2022-06-27 13:24:31.460822+00","lsn":"0/236F278","schema":"public","table":"payment_p2020_06","columns":[{"name":"payment_id","type":"integer","value":32099},{"name":"customer_id","type":"integer","value":291},{"name":"staff_id","type":"integer","value":1},{"name":"rental_id","type":"integer","value":16050},{"name":"amount","type":"numeric(5,2)","value":5.99},{"name":"payment_date","type":"timestamp with time zone","value":"2020-06-01 00:00:00+00"}]}
{"action":"C","xid":489,"timestamp":"2022-06-27 13:24:31.460822+00","lsn":"0/236F5A8","nextlsn":"0/236F5D8"}

In the SQLite-based pipeline, the equivalent transform is performed inline by the pgcopydb stream catchup (apply) step 🔥 — it reads the changes from the CDC output database, writes parameterised statements to the CDC replay database, and applies them to the target. The resulting replay statements are equivalent to:

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"}

💜 AUTHOR 💜

Dimitri Fontaine — icon, legend, the moment 👑

🎀 COPYRIGHT 🎀

2022-2024, Dimitri Fontaine

And that's a wrap on PGCOPYDB STREAM(1), bestie 🎉 This page is pgcopydb version 0.18, dated Jun 28, 2026, and it says PGCOPYDB STREAM(1) up at the top like a little crown 👑 Go stream some changes, you absolute main character 💅✨🌈

📦 where this page comes from

ubuntu stonking: pgcopydb 0.18-1 · PostgreSQL
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-11-03: a language model's rewrite of the original, under its terms