Stream PostgreSQL Logical Changes Safely with pg_recvlogical

You will create a PostgreSQL logical replication slot, stream its decoded output to a file, stop at a known LSN when you need a bounded run, and remove the slot when the experiment is finished. The examples match pg_recvlogical 16.15 on this machine. Allow 15 to 30 minutes, plus time to arrange a test database and an output plugin.

This guide assumes a PostgreSQL server that is already configured for logical replication and a database user allowed to use the required replication features. The server configuration, user privileges and output-plugin options are outside this client command's contract, so confirm them with the database administrator before starting. The commands below are ordinary user commands unless your local PostgreSQL authentication policy requires a different account.

Checkpoint: confirm the installed client

Start by checking the binary and its version. This is read-only and does not contact a server:

$ command -v pg_recvlogical
/usr/bin/pg_recvlogical
$ pg_recvlogical --version
pg_recvlogical (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)

The installed help output reports test_decoding as the default output plugin and 10 seconds as the default for both fsync and status intervals. Use --help if you need to check a different client installation before copying an example.

1. Choose connection values and a slot name

Set shell variables rather than repeating real credentials in a command history. Replace every placeholder with values for the same database connection. The database used to start the stream must be the database used to create the slot.

DB_NAME='appdb'
DB_HOST='db.example.net'
DB_PORT='5432'
DB_USER='logical_reader'
SLOT='appdb_capture_test'
OUT="$PWD/$SLOT.log"

export DB_NAME DB_HOST DB_PORT DB_USER SLOT OUT

--dbname defaults to the operating-system user name, so state it explicitly in automation. --host defaults to PGHOST or a Unix socket, and --port uses PGPORT or PostgreSQL's compiled-in default. A connection string supplied to --dbname can override conflicting command-line connection options. Prefer a protected .pgpass entry or another approved credential mechanism; do not put a password in a shared script.

2. Create the logical slot

Creating a slot changes server state and can retain WAL while a consumer is absent. Use a unique test name and create it only when you are ready to monitor its retention impact:

$ pg_recvlogical \
    --dbname="$DB_NAME" --host="$DB_HOST" --port="$DB_PORT" \
    --username="$DB_USER" --slot="$SLOT" \
    --plugin=test_decoding --create-slot
$ printf 'create exit status: %s\n' "$?"
create exit status: 0

The command exits with status 0 on success. The plugin is selected when the slot is created; supplying a different --plugin later does not change an existing slot. If you intentionally want prepared transactions decoded, add --two-phase to this creation command. That option is valid only with --create-slot.

Checkpoint: if the name may already exist and reusing it is acceptable, add --if-not-exists. Do not use that option as a substitute for checking whether the old slot belongs to another consumer.

3. Run a bounded stream to a new file

Streaming normally continues until a signal. For a controlled test, use an LSN supplied by your replication test plan with --endpos. The record at exactly that LSN is included, and the command exits normally when it reaches the position:

$ END_LSN='0/2000000'
$ pg_recvlogical \
    --dbname="$DB_NAME" --host="$DB_HOST" --port="$DB_PORT" \
    --username="$DB_USER" --slot="$SLOT" --start \
    --file="$OUT" --endpos="$END_LSN" --no-loop
$ printf 'exit status: %s\n' "$?"
exit status: 0

Use an LSN that is meaningful for your server and test data. The command does not discover a safe endpoint for you. The output format is determined by the plugin, so test_decoding output is not a general interchange format. Inspect it as plugin output:

$ test -s "$OUT" && sed -n '1,12p' "$OUT"
$ wc -c "$OUT"
  1234 /current/path/appdb_capture_test.log

--endpos is not transaction-aware. It may stop partway through a transaction. A partially output transaction is not consumed and will be replayed when the slot is read again; individual messages are not truncated. Do not treat a bounded file as a complete transaction export unless your endpoint and plugin semantics guarantee that.

4. Run a continuous consumer when that is the real job

For a consumer process, omit --endpos and choose an explicit output file:

$ pg_recvlogical \
    --dbname="$DB_NAME" --host="$DB_HOST" --port="$DB_PORT" \
    --username="$DB_USER" --slot="$SLOT" --start \
    --file="$OUT" --status-interval=10 --fsync-interval=10

This stays in the foreground. Press Control+C to stop it cleanly; SIGTERM also gives the normal exit status 0. A lost connection is retried in a loop by default. Add --no-loop when a supervisor should receive the failure immediately rather than letting this process retry. Check the status from the shell that launched it, before running another command.

The status interval controls progress reports to the server. The fsync interval controls extra fsync() calls for the output file. Setting --fsync-interval=0 disables those client fsync calls while progress is still reported, which can lose file data after a crash. Keep the default or choose a deliberate durability policy for a real consumer.

5. Rotate the output without restarting the stream

For a long-running process, first rename the current file and then send SIGHUP. The process closes the old file and opens a new file using the same --file name:

$ mv -- "$OUT" "$OUT.$(date +%Y%m%d%H%M%S)"
$ kill -HUP "$PG_RECVLOGICAL_PID"

Replace PG_RECVLOGICAL_PID with the actual process ID, captured by your service manager or an operator. Do not guess a PID. The rotation changes file names and sends a signal to a running process, so verify the new file before deleting any archive:

$ ls -l -- "$OUT" "$OUT."*
$ test -e "$OUT" && printf '%s\n' 'new output file is present'

If the process is supervised, use the supervisor's signal and log-rotation mechanism instead of a hand-written PID lookup. Renaming the file alone is not enough: without SIGHUP, the process can continue writing to the renamed inode.

6. Drop the slot only when its consumer is retired

Dropping a slot is destructive to that slot's pending logical-decoding position. Stop every consumer that uses it, confirm the slot name, and make sure no recovery or replay process still needs it. Then run:

$ pg_recvlogical \
    --dbname="$DB_NAME" --host="$DB_HOST" --port="$DB_PORT" \
    --username="$DB_USER" --slot="$SLOT" --drop-slot
$ printf 'drop exit status: %s\n' "$?"
drop exit status: 0

--drop-slot cannot be combined with another action. There is no undo command in pg_recvlogical; recreating the same name creates a new slot at a new position and does not restore the old stream. If you are unsure whether the slot is still needed, stop here and ask the database owner.

Common failure points

• Wrong database: a slot must be started from the same database used to create it. Check DB_NAME before retrying.
• Slot already exists: do not change the plugin assumption casually. Use a new slot for a test, or explicitly decide whether --if-not-exists matches the intended ownership.
• Password prompt in automation: use an approved non-interactive credential source and add --no-password so a missing credential fails instead of hanging. Use --password only when an operator should be prompted before the connection attempt.
• No output yet: logical decoding reports changes, not an invented heartbeat. Generate known test activity in the selected database and confirm that the plugin and slot are the ones you intended.
• Growing retained WAL: a slot whose consumer is stopped can prevent cleanup. Stop the experiment or arrange monitoring and ownership before leaving a slot behind.