PostgreSQL PITR with pgBackRest: prove a named recovery target on an isolated clone

Create the named restore point BEFORE the synthetic delete. Use this name only once in the rehearsal WAL history. After the delete, force a segment switch and run the repository check to confirm required archive delivery.

If you see duplicate target names in your rehearsal history, choose a new public target name before repeating the exercise. A timestamp without timezone is an avoidable ambiguity; this example deliberately uses a name.

'/usr/lib/postgresql/18/bin/psql' -X -v ON_ERROR_STOP=1 -d 'academy_lab' <<'SQL'
SELECT pg_create_restore_point('academy_before_change');
DELETE FROM academy_pitr_probe.marker WHERE id = 1;
SELECT count(*) AS source_rows_after_delete FROM academy_pitr_probe.marker;
SELECT pg_switch_wal();
SQL
pgbackrest --stanza='academy' check

On the isolated restore host as its PostgreSQL OS user, stop before this step if the chosen root exists, is a symlink or is a production mount. The parent must already be a private directory owned by this account, with adequate capacity.

Do not run this on the source. Confirm no PostgreSQL service manager watches the new directory and no production data/tablespace directories are mounted here. This worked example excludes external tablespaces; inspect the backup manifest and reject one that uses them.

The default parent must be PostgreSQL-owned with no group/world write permission. If your host lacks a suitable private parent, an administrator should provision a new dedicated rehearsal parent; do not change permissions on existing directories to bypass this guard.

parent=$(dirname -- '/var/lib/postgresql/academy-pitr-20261028')
test -d "$parent" && test ! -L "$parent" && test -O "$parent" && test -w "$parent" || exit 1
parent_mode=$(stat -c '%a' "$parent")
test $((8#$parent_mode & 8#022)) -eq 0 || exit 1
test ! -e '/var/lib/postgresql/academy-pitr-20261028' && test ! -L '/var/lib/postgresql/academy-pitr-20261028' || exit 1
umask 077
mkdir -- '/var/lib/postgresql/academy-pitr-20261028' || exit 1
mkdir -- '/var/lib/postgresql/academy-pitr-20261028/socket'
df -h '/var/lib/postgresql/academy-pitr-20261028'
'/usr/lib/postgresql/18/bin/postgres' --version
pgbackrest --stanza='academy' info

Use the existing protected repository configuration on the isolated host. Enter the exact completed label in the terminal; it is not a password. An empty label aborts. The explicit pg1-path overrides the source path and reset-pg1-host keeps the restore local.

The restore command writes the chosen new data directory. It must not use delta, force or an existing directory. Keep target-action=pause: inspect recovery before choosing promotion.

printf 'Completed backup label: '
IFS= read -r backup_label
test -n "$backup_label" || exit 1
pgbackrest --stanza='academy' --reset-pg1-host --pg1-path='/var/lib/postgresql/academy-pitr-20261028/data' --set="$backup_label" --type=name --target='academy_before_change' --target-action=pause --archive-mode=off restore

Inspect the restored main configuration, included files, auto configuration, HBA and symlinks locally. Configuration can contain secrets: never paste full output into a ticket or public chat. Reject external data paths, source SSL private-key paths, preload libraries missing here and production connection hooks.

The commands show paths for manual inspection, not automatic rewriting. Use a private editor to remove source-specific hooks/includes safely. Preserve the generated restore_command and recovery target. This guide assumes a self-contained data-directory configuration; distribution-managed external configs require the owning explicit config_file path.

find '/var/lib/postgresql/academy-pitr-20261028/data' -maxdepth 2 -type l -ls
ls -l '/var/lib/postgresql/academy-pitr-20261028/data/recovery.signal' '/var/lib/postgresql/academy-pitr-20261028/data/postgresql.auto.conf'
nano '/var/lib/postgresql/academy-pitr-20261028/data/postgresql.conf'
nano '/var/lib/postgresql/academy-pitr-20261028/data/postgresql.auto.conf'
nano '/var/lib/postgresql/academy-pitr-20261028/data/pg_hba.conf'

Start only this standalone directory with private socket, loopback TCP, archiving off and no streaming connection. Normal startup completion may wait for WAL; inspect the private log if it fails. Do not turn a missing WAL error into promotion.

The HBA must permit the local PostgreSQL account to connect through the private socket. Change neither production authentication nor network rules.

'/usr/lib/postgresql/18/bin/pg_ctl' -D '/var/lib/postgresql/academy-pitr-20261028/data' -l '/var/lib/postgresql/academy-pitr-20261028/recovery.log' -o "-c port=55432 -c listen_addresses=127.0.0.1 -c unix_socket_directories=/var/lib/postgresql/academy-pitr-20261028/socket -c archive_mode=off -c primary_conninfo=" start
'/usr/lib/postgresql/18/bin/psql' -X -v ON_ERROR_STOP=1 -h '/var/lib/postgresql/academy-pitr-20261028/socket' -p '55432' -d 'academy_lab' -c "SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state(); SELECT * FROM academy_pitr_probe.marker;"

If the marker is missing, compare the chosen backup completion with target creation, database identity and exact target name. If recovery is waiting, examine the missing WAL filename and whether retention removed the required archive. A repository info listing alone does not prove a complete WAL chain.

Read the log privately and record only redacted error codes, WAL segment identity and timing. Wrong-major startup errors require compatible binaries, not a forced upgrade. Do not resume replay before collecting the paused-state evidence.

tail -n 80 '/var/lib/postgresql/academy-pitr-20261028/recovery.log'
pgbackrest --stanza='academy' info
'/usr/lib/postgresql/18/bin/psql' -X -h '/var/lib/postgresql/academy-pitr-20261028/socket' -p '55432' -d 'academy_lab' -c "SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn(), pg_get_wal_replay_pause_state();"