Point-in-time recovery starts with a valid physical base backup and replays the WAL required to reach a chosen recovery target. The target must fall within the recoverable history of that backup and its available timelines. Archiving does not create the ability to recover to arbitrary earlier dates, nor does it repair a gap between the backup and the desired endpoint.

When it breaks

A copied data directory is not automatically a valid online backup. Use a supported backup method, preserve the required WAL and timeline history, and verify the backup manifest. pg_verifybackup finds many integrity problems, but its success does not demonstrate that later archived WAL exists or that a business recovery target produces the intended state. That requires starting recovery and checking the result.

Choosing a target also requires precision. Time, transaction, LSN, and named targets express different boundaries. For applicable target types, recovery_target_inclusive controls whether the specified boundary is included. A named restore point avoids clock ambiguity in this drill, but it only exists because an administrator deliberately wrote that marker. Reusing its name can stop recovery at the first matching point instead of the one an operator intended.

Check it quickly

Run this script as a non-root account in a Linux environment with PostgreSQL 18 server and client binaries, cp -a, mktemp, hard links, and sync FILE available. It was executed on PostgreSQL 18.6. Private temporary directories and Unix sockets isolate the clusters; TCP listening is disabled. The local archive exercises recovery mechanics without claiming protection against loss of the host.

#!/bin/sh
set -eu
umask 077
lab=$(mktemp -d /tmp/xbcatm-pitr.XXXXXX)
mkdir "$lab/socket" "$lab/archive"
primary=$lab/primary
restored=$lab/restored
missing=$lab/missing
cleanup() {
    for cluster in "$missing" "$restored" "$primary"; do
        if test -f "$cluster/postmaster.pid"; then
            pg_ctl -D "$cluster" -m fast -w stop >/dev/null
        fi
    done
}
trap cleanup EXIT
initdb -D "$primary" --no-locale --encoding=UTF8 --auth-local=trust --auth-host=reject >/dev/null
cat >> "$primary/postgresql.conf" <<CONF
listen_addresses = ''
unix_socket_directories = '$lab/socket'
port = 56432
wal_level = replica
archive_mode = on
archive_command = '$lab/archive-copy.sh "%p" "%f" "$lab/archive"'
CONF
cat > "$lab/archive-copy.sh" <<'ARCHIVE_HELPER'
#!/bin/sh
set -eu
wal_source=$1
wal_name=$2
archive_directory=$3
archive_target=$archive_directory/$wal_name
if test -e "$archive_target"; then
    cmp -s "$wal_source" "$archive_target"
    sync "$archive_target" "$archive_directory"
    exit 0
fi
archive_temporary=$(mktemp "$archive_directory/.incoming.XXXXXX")
trap 'rm -f "$archive_temporary"' EXIT HUP INT TERM
cp "$wal_source" "$archive_temporary"
sync "$archive_temporary"
ln "$archive_temporary" "$archive_target" || cmp -s "$wal_source" "$archive_target"
sync "$archive_target" "$archive_directory"
ARCHIVE_HELPER
chmod 700 "$lab/archive-copy.sh"
pg_ctl -D "$primary" -l "$lab/primary.log" -w -t 15 start >/dev/null
export PGHOST=$lab/socket PGPORT=56432 PGDATABASE=postgres PGUSER=$(id -un)
export PGCONNECT_TIMEOUT=2
psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
CREATE TABLE marker (id integer PRIMARY KEY,value text);
INSERT INTO marker VALUES (1,'base');
SQL
pg_basebackup -D "$lab/base" -X stream -c fast -w
pg_verifybackup -q "$lab/base"
psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_switch_wal()' >/dev/null
psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
INSERT INTO marker VALUES (2,'keep');
SELECT pg_create_restore_point('before_bad_change');
UPDATE marker SET value='bad' WHERE id=1;
DELETE FROM marker WHERE id=2;
SQL
segment=$(psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_walfile_name(pg_current_wal_lsn())')
psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT pg_switch_wal()' >/dev/null
attempt=0
while ! test -f "$lab/archive/$segment"; do
    attempt=$((attempt+1))
    test "$attempt" -lt 100 || { echo 'archive segment did not arrive' >&2; exit 1; }
    sleep 0.1
done
printf 'primary_now '
psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT string_agg(id||':'||value,',' ORDER BY id) FROM marker"
mkdir "$restored"
cp -a "$lab/base/." "$restored/"
cat >> "$restored/postgresql.conf" <<CONF
port = 56433
archive_mode = off
restore_command = 'cp "$lab/archive/%f" "%p"'
recovery_target_name = 'before_bad_change'
recovery_target_action = 'pause'
CONF
touch "$restored/recovery.signal"
pg_ctl -D "$restored" -l "$lab/restored.log" -w -t 15 start >/dev/null
attempt=0
while test "$(PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT pg_get_wal_replay_pause_state()='paused'")" != t; do
    attempt=$((attempt+1))
    test "$attempt" -lt 100 || { echo 'restore target was not reached' >&2; exit 1; }
    sleep 0.1
done
PGPORT=56433 psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL'
SELECT pg_is_in_recovery(),pg_get_wal_replay_pause_state();
SELECT id,value FROM marker ORDER BY id;
DO $$ BEGIN
  IF (SELECT string_agg(id||':'||value,',' ORDER BY id) FROM marker)
     IS DISTINCT FROM '1:base,2:keep' THEN
    RAISE EXCEPTION 'point-in-time result mismatch';
  END IF;
END $$;
SQL
test ! -e "$lab/base/pg_wal/$segment"
mkdir "$lab/archive-missing" "$missing"
for archived_file in "$lab/archive/"*; do
    if test "${archived_file##*/}" != "$segment"; then
        cp "$archived_file" "$lab/archive-missing/"
    fi
done
cp -a "$lab/base/." "$missing/"
cat >> "$missing/postgresql.conf" <<CONF
port = 56434
archive_mode = off
restore_command = 'cp "$lab/archive-missing/%f" "%p"'
recovery_target_name = 'before_bad_change'
recovery_target_action = 'pause'
CONF
touch "$missing/recovery.signal"
if pg_ctl -D "$missing" -l "$lab/missing.log" -w -t 15 start >/dev/null 2>&1; then
    echo 'missing-segment server was briefly available'
fi
attempt=0
while pg_ctl -D "$missing" status >/dev/null 2>&1; do
    attempt=$((attempt+1))
    test "$attempt" -lt 100 || { echo 'missing-segment server did not stop' >&2; exit 1; }
    sleep 0.1
done
grep -F 'recovery ended before configured recovery target was reached' "$lab/missing.log"
test -f "$lab/archive/$segment"
echo missing_segment_target_unreachable
printf 'lab_directory %s\nrequired_segment %s\n' "$lab" "$segment"

The primary first contains row one with value base. After taking and verifying the base backup, the script switches WAL segments, inserts row two with value keep, and creates before_bad_change. Only afterward does it change row one to bad and delete row two. primary_now 1:bad confirms the unwanted changes really reached the source database.

Recovery starts from a separate copy of the base backup. recovery.signal requests archive recovery; restore_command supplies archived files; the named target and pause action stop replay for inspection. The script waits for pg_get_wal_replay_pause_state() to report paused. Merely receiving true from pg_is_wal_replay_paused() would show that a pause was requested, not necessarily that replay has stopped.

Expected recovered output is t|paused, followed by 1|base and 2|keep. A SQL assertion checks that exact combined result. This demonstrates preservation of the earlier good transaction and exclusion of the later bad changes. It is stronger evidence than a server accepting connections, which can happen while recovery is still progressing.

The negative control makes another archive copy while excluding the required post-backup segment. It preserves the original archive and confirms that the base backup did not already contain that segment. Starting recovery against the incomplete copy must end with the fatal message that recovery ended before the configured target was reached. The script checks the server has stopped and inspects the log, rather than interpreting only the initial pg_ctl return value. missing_segment_target_unreachable records this expected failure.

All experimental clusters stop on exit. Their printed directory retains logs and data for inspection. No recovered copy is promoted or exposed to applications by this drill.

Verify before accepting recovery

A paused target provides an opportunity to compare business invariants before completing recovery. Check more than a row count: transaction boundaries, related records, and the last acceptable operation matter. Resuming a target pause completes recovery; it is not a request to keep applying arbitrary later changes. To pursue a later target, follow the documented shutdown and target-change procedure.

Record the base backup identity, available timeline, chosen target, recovered facts, and elapsed recovery time. Keep configuration and required external software alongside the recovery plan, since WAL does not restore every external dependency. Protect the original backup and archive while experimenting. A tested target establishes one recovery result; recurring drills across the retention interval establish whether the promised recovery window remains usable as backups rotate.

Further reading

Sources