PGCOPYDB FOLLOW(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 FOLLOW(1) — pgcopydb ✨

okay so welcome to the pgcopydb manual, bestie, and buckle up because this one is a whole page. we're doing PGCOPYDB FOLLOW(1) today and it's giving main character energy fr fr 💅

🎀 NAME 🎀

pgcopydb follow — literally the command called pgcopydb follow. that's it, that's the name, we move.

The way this command works is: it replays the database changes that were registered at the source database, using whichever logical decoding plugin you feel like choosing. the default is pgoutput (which has been built right into PostgreSQL core since version 10, iconic behavior), but test_decoding and wal2json are also supported. pick your fighter. 🦋

Important, no cap: even though pgcopydb follow is a full client for logical decoding all on its own, the general use case is actually to use pgcopydb clone --follow, as documented in the page about Change Data Capture using Postgres Logical Decoding.

When you're out here using Logical Decoding with pgcopydb or honestly with any other tool at all, please consider making sure you're familiar with the Logical Replication Restrictions that apply. specifically, and this part matters:

DDL are not replicated. 💀 When you're using DDL for partition scheme maintenance — like, say, when you're using the pg_partman extension — then consider creating a week or a month of partitions in advance, so that creating new partitions does not happen during the migration window. plan ahead, queen.
Sequence data is not replicated. When you're using pgcopydb clone --follow (starting with pgcopydb version 0.9, so we been knew) then the sequence data is synced at the end of the operation, after the cutover point that gets implemented via the pgcopydb stream sentinel set endpos command. Updating the sequences manually is also possible, by running the command pgcopydb copy sequences.
Large Objects are not replicated.

See the Postgres documentation page for Logical Replication Restrictions to read the exhaustive list of restrictions. the full list, every crumb of it. 🌸

🌈 PGCOPYDB FOLLOW 🌈

pgcopydb follow: Replay changes from the source database to the target database — usage: pgcopydb follow --source ... --target ...

pgcopydb follow: Replay changes from the source database to the target database
usage: pgcopydb follow  --source ... --target ...

  --source                      Postgres URI to the source database
  --target                      Postgres URI to the target database
  --dir                         Work directory to use
  --filters <filename>          Use the filters defined in <filename>
  --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
  --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

💖 DESCRIPTION 💖

So, the tea: this command runs three concurrent subprocesses in two possible modes of operation. three girls, two moods.

The first mode of operation is named prefetch and catchup, where the changes from the source database are stored in intermediate JSON and SQL files, to be later replayed one file at a time in the catchup process.
The second mode of operation is named live replay, where the changes from the source database are streamed from the receiver process to the transform process using a Unix pipe, and then with the same mechanism from the transform process to the replay process.

Only one mode of operation may be active at any given time, and pgcopydb automatically switches from one mode to the other one, in a loop. it's a rotation. 🔄

The follow command always starts using the prefetch and catchup mode, and as soon as the catchup process can't find the next SQL file to replay, then it exits — which triggers the switch to the live replay mode. And before entering the new mode, to make sure it replays all the changes that have been received, pgcopydb implements an extra catchup phase without concurrent activity. a little solo moment, as a treat. 🙈

🌼 Prefetch and Catchup 🌼

In the prefetch and catchup mode of operations, the three processes are implementing the following approach:

The first process pre-fetches the changes from the source database using the Postgres Logical Decoding protocol, and saves the JSON messages in local JSON files.
The second process transforms the JSON files into SQL. A Unix system V message queue is used to communicate LSN positions from the prefetch process to the transform process.
The third process catches-up with changes happening on the source database by applying the SQL files to the target database system. The Postgres API for Replication Progress Tracking is used in that process, so that we can skip already applied transactions at restart or resume.

🦋 Live Replay 🦋

In the live replay mode of operations, the three processes are implementing the following approach:

The first process receives the changes from the source database using the Postgres Logical Decoding protocol, and saves the JSON messages in local JSON files. Additionally, the JSON changes are written to a Unix pipe shared with the transform process.
The second process transforms the JSON lines into SQL. A Unix pipe is used to stream the JSON lines from the receive process to the transform process. The transform process in that mode still writes the changes to SQL files, so that it's still possible to catchup with received changes if the apply process is interrupted. safety first, always. 💅
The third process replays the changes happening on the source database by applying the SQL commands to the target database system. The SQL commands are read from the Unix pipe shared with the transform process. The Postgres API for Replication Progress Tracking is used in that process, so that we can skip already applied transactions at restart or resume.

🧚 Remote control of the follow command 🧚

It is possible to start the pgcopydb follow command and then later — while it's still running, mid-slay — set the LSN for the end position with the same effect as using the command line option --endpos, or switch from prefetch mode only to prefetch and catchup mode. For that, see the commands pgcopydb stream sentinel set endpos, pgcopydb stream sentinel set apply, and pgcopydb stream sentinel set prefetch.

Note that in many cases the --endpos LSN position is not known at the start of this command. Also, before entering the prefetch and apply mode, it is important to make sure that the initial base copy is finished. do not skip this, bestie, I'm serious. 😭

Finally, it is also possible to set up the streaming replication options before using the pgcopydb follow command: see the pgcopydb stream setup and pgcopydb stream cleanup commands.

👑 REPLICA IDENTITY AND LACK OF PRIMARY KEYS 👑

Postgres Logical Decoding works with replaying changes using SQL statements, and for that it exposes the concept of Replica Identity, as described in the documentation for the ALTER TABLE ... REPLICA IDENTITY command.

To quote the Postgres docs, and I'm quoting them verbatim because this part is important:

This form changes the information which is written to the write-ahead log to identify rows which are updated or deleted. In most cases, the old value of each column is only logged if it differs from the new value; however, if the old value is stored externally, it is always logged regardless of whether it changed. This option has no effect except when logical replication is in use.

To support Change Data Capture with Postgres Logical Decoding for tables that do not have a Primary Key, then it is necessary to use the ALTER TABLE ... REPLICA IDENTITY command for those tables.

In practice the two following options are to be considered:

REPLICA IDENTITY USING INDEX index_name This form is preferred when a UNIQUE index exists for the table without a primary key. The index must be unique, not partial, not deferrable, and include only columns marked NOT NULL.
REPLICA IDENTITY FULL When this is used on a table, then the WAL records contain the old values of all columns in the row.

🌟 LOGICAL DECODING PRE-FETCHING 🌟

When using pgcopydb clone --follow, a logical replication slot is created on the source database before the initial COPY, using the same Postgres snapshot. This ensures data consistency. it's the whole point, honestly.

Within the pgcopydb clone --follow approach, it is only possible to start applying the changes from the source database after the initial COPY has finished on the target database.

Also, from the Postgres documentation we read that Postgres replication slots provide an automated way to ensure that the primary does not remove WAL segments until they have been received by all standbys.

Accumulating WAL segments on the primary during the whole duration of the initial COPY involves capacity hazards, which translate into potential File System is Full errors on the WAL disk of the source database. It is crucial to avoid such a situation. like, genuinely crucial. 😭

This is why pgcopydb implements CDC pre-fetching. In parallel to the initial COPY, the command pgcopydb clone --follow pre-fetches the changes in local JSON and SQL files. Those files are placed in the XDG_DATA_HOME location, which could be a mount point for an infinite Blob Storage area. infinite storage, infinite vibes. ✨

The pgcopydb follow command is a convenience command that's available as a logical decoding client, and it shares the same implementation as the pgcopydb clone --follow command. As a result, the pre-fetching strategy is also relevant to the pgcopydb follow command.

and that's literally just the beginning, bestie 🫶

🎀 THE SENTINEL TABLE, OR THE REMOTE CONTROL 🎀

ok so like, to keep track of where we're at and to be able to resume stuff later, pgcopydb uses a sentinel table. it's literally one (1) single row, and that row has these fields:

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

and bestie, you can also grab it as JSON with pgcopydb stream sentinel get --json, which gives you something like this:

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

the first three fields (startpos, endpos, apply) are like, very pgcopydb specific 💅, and then the next three (write_lsn, flush_lsn, replay_lsn) are following the Postgres replication protocol — you can see them in the docs for the pg_stat_replication function at https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW.

startpos

The startpos field is the current LSN on the source database at the time when the Change Data Capture is setup in pgcopydb, like when you're using the pgcopydb stream setup command.

heads up: both the pgcopydb follow and the pgcopydb clone --follow command implement the setup parts if the pgcopydb stream setup has not been used already. so like, no need to double up 🫶

endpos

The endpos field is the last LSN position from the source database that pgcopydb replays. and the command pgcopydb follow (or pgcopydb clone --follow) stops when reaching beyond this LSN position. that's the finish line fr fr 🏁

The endpos can be set at the start of the process — which is super useful for unit testing — or while the command is running, which is the move in production to define a cutover point.

To define the endpos while the command is running, use pgcopydb stream sentinel set endpos.

apply

The apply field is a boolean (enabled/disabled) that controls the catchup process. The pgcopydb catchup process replays the changes only when the apply boolean is set to true.

The pgcopydb clone --follow command automatically enables the apply field of the sentinel table as soon as the initial COPY is done. so you don't have to babysit it 😌

To manually control the apply field, use the pgcopydb stream sentinel set apply command.

write_lsn

The Postgres documentation for pg_stat_replication.write_lsn is: Last write-ahead log location written to disk by this standby server.

In the pgcopydb case, the sentinel field write_lsn is the position that has been written to disk (as JSON) by the streaming process. ✍️

flush_lsn

The Postgres documentation for pg_stat_replication.flush_lsn is: Last write-ahead log location flushed to disk by this standby server.

In the pgcopydb case, the sentinel field flush_lsn is the position that has been written and then fsync'ed to disk (as JSON) by the streaming process. that's the committed to it energy 💾

replay_lsn

The Postgres documentation for pg_stat_replication.replay_lsn is: Last write-ahead log location replayed into the database on this standby server.

In the pgcopydb case, the sentinel field replay_lsn is the position that has been applied to the target database, as kept track from the WAL.json and then the WAL.sql files, and using the Postgres API for Replication Progress Tracking at https://www.postgresql.org/docs/current//replication-origins.html.

The replay_lsn is also shared by the pgcopydb streaming process that uses the Postgres logical replication protocol, so the pg_stat_replication entry (docs at https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW) associated with the replication slot used by pgcopydb can be used to monitor replication lag. 👀

and here's the cute lil bonus: since the pgcopydb streaming processes maintain the sentinel table on the source database, you can also use it to keep track of the logical replication progress. multitasking queen 👑

✨ OPTIONS ✨

okay bestie, here's the full menu of flags 🎀 every single one, no skips, i got you:

--source — the connection string to your source Postgres instance 💖. the Postgres docs on connection strings have all the deets, but tl;dr you can use the quoted form "host=... dbname=..." or the URI form postgres://user@host:5432/dbname, both are supported fr.
--target — connection string to the target Postgres instance. that's it, that's the whole flag 🌸.
--dir — during its normal little daily routine, pgcopydb creates so many temporary files to keep track of sub-process progress 🙈. those temp files go in the directory you name here, or they default to ${TMPDIR}/pgcopydb when that env var is set, or otherwise to /tmp/pgcopydb.
--restart — when you run the pgcopydb command again and the work directory already has info from a previous run, the command refuses to proceed and refuses to delete stuff that might be useful for diagnostics and forensics (respect honestly 🫶). in that case you whip out --restart to let pgcopydb delete the traces from that previous run. it's giving "yes i'm sure, nuke it" 💅.
--resume — when the pgcopydb command got terminated before it finished — either by an interrupt signal (like Ctrl+C or SIGTERM) or because it straight up crashed 😭 — you can resume the database migration. here's the fine print though, and it matters: when you resume from a previous run, table data that was fully copied over to the target server does not get sent again. but table data that got interrupted during the COPY has to start from scratch even with --resume, because the COPY command in Postgres is transactional and got rolled back. same reasoning applies to the CREATE INDEX commands and ALTER TABLE commands that pgcopydb issues — those get skipped on a --resume run only if they're known to have run all the way through to completion on the previous one. and finally: using --resume requires the use of --not-consistent.
--not-consistent — okay so to be consistent, pgcopydb exports a Postgres snapshot by calling the pg_export_snapshot() function on the source database server, then that snapshot gets re-used in every connection to the source by using the SET TRANSACTION SNAPSHOT command. and per the Postgres documentation about pg_export_snapshot, here's the quote so you have it word for word:

Saves the transaction's current snapshot and returns a text string identifying the snapshot. This string must be passed (outside the database) to clients that want to import the snapshot. The snapshot is available for import only until the end of the transaction that exported it.

now: when the pgcopydb process got interrupted (or crashed) on a previous run, you can resume operations, but the snapshot that was exported does not exist anymore. so pgcopydb can only resume with a brand new snapshot, which means it can't guarantee consistency of the whole data set, because each run is now using its own separate snapshot. hence the name, bestie 💀.

--snapshot — instead of exporting its own snapshot by calling the PostgreSQL function pg_export_snapshot(), pgcopydb can re-use an already exported snapshot. hand it one you already have ✨.
--plugin — the logical decoding output plugin to use. the default is pgoutput, which is built into PostgreSQL core (since Postgres 10) and doesn't need any extension installed on the source server. using pgoutput means nobody needs superuser just to install an extension, and it works with any PostgreSQL publication-based setup 👑. you can also use test_decoding — it ships with Postgres core but doesn't support all data types as well as pgoutput does — or wal2json, which is an external extension. both of those are still supported for backwards compatibility.
--wal2json-numeric-as-string — when you're using the wal2json output plugin, you can pass this to tell wal2json to output numeric values as strings, which prevents some precision loss 🔥. caveat tho: you need a wal2json plugin version on the source database that supports the --numeric-data-types-as-string option for this to work. the wal2json documentation has the full details on that option.
--replay-no-op-updates — when set, pgcopydb replays UPDATE statements even when no column values changed between the old and new tuple. by default those no-op UPDATEs get skipped as an optimisation (and the default is the appropriate choice for migration workloads, where target triggers don't need to fire for every single 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; this option restores that behaviour 🫡. fun fact: no-op UPDATEs show up most often with REPLICA IDENTITY FULL tables, where both the old and new full row are available for comparison.
--slot-name — the logical decoding slot name to use. defaults to pgcopydb. which is… unfortunate when your use-case involves migrating more than one database from the source server 🙈. name your slots, besties.
--create-slot — instruct pgcopydb to create the logical replication slot to use. you're welcome ✨.
--endpos — the logical decoding target LSN to use. automatically stops replication and exits with normal exit status 0 when receiving reaches the specified LSN. if there's a record with an LSN exactly equal to the lsn, that record will be output. heads up though: --endpos 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, at least. also see the documentation for pg_recvlogical.
--origin — the logical replication target system needs to track which transactions have already been applied, so that if we get disconnected or need to resume operations we can skip the transactions we already replayed. Postgres uses a notion of an origin node name, as documented in Replication Progress Tracking. 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 🌈.
--verbose — increase current verbosity. the default level is INFO. in ascending order, pgcopydb knows about these verbosity levels: FATAL, ERROR, WARN, INFO, NOTICE, DEBUG, TRACE.
--debug — set current verbosity to DEBUG level. for when you need to see everything 🔍.
--trace — set current verbosity to TRACE level. maximum yapping, and honestly? iconic.
--quiet — set current verbosity to ERROR level. shhh 🤫.

🌿 ENVIRONMENT 🌿

the env vars, the whole set, keep scrolling:

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) 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.
PGCOPYDB_SNAPSHOT — Postgres snapshot identifier to re-use, see also --snapshot.
TMPDIR — the pgcopydb command creates all its work files and directories in ${TMPDIR}/pgcopydb, and defaults to /tmp/pgcopydb.
XDG_DATA_HOME — the standard XDG Base Directory Specification defines several environment variables that let you control where programs should store their files. and here's the spec's own words on this one:

XDG_DATA_HOME defines the base directory relative to which user-specific data files should be stored. If $XDG_DATA_HOME is either not set or empty, a default equal to $HOME/.local/share should be used.

when using Change Data Capture (through the --follow option and Postgres logical decoding) then pgcopydb pre-fetches changes into JSON files and transforms them into SQL files to apply to the target database. those files are stored at the following location, tried in this order:

when --dir is used, then pgcopydb uses the cdc subdirectory of the --dir location,
when XDG_DATA_HOME is set in the environment, then pgcopydb uses that location,
when neither of the previous settings have been used then pgcopydb defaults to using ${HOME}/.local/share.

👑 Author 👑

Dimitri Fontaine

📜 Copyright 📜

2022-2024, Dimitri Fontaine

and that's the whole page, bestie 💖 you made it to the very end, so proud of you 🥹 this was pgcopydb follow(1) — the version 0.18 bag, dated Jun 28, 2026 🎀 go forth and follow that logical decoding, 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