Implementing Point-in-Time Recovery

# postgresql.conf
archive_mode    = on
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'

archive_command = 'rsync %p backup-host:/wal_archive/%f'
# Production: use pgBackRest / WAL-G / barman instead of hand-rolling.

wal_level = replica         # minimum for PITR
# (logical also works)

pg_basebackup -D /backups/base -Ft -z -P -X stream -U repl

-- Low-level (non-exclusive, PG 15+ uses base backup protocol)
SELECT pg_backup_start('label', false);   -- PG 15+ (renamed from pg_start_backup)
-- copy data directory ...
SELECT pg_backup_stop();

rm -rf $PGDATA
tar -xzf /backups/base/base.tar.gz -C $PGDATA

touch $PGDATA/recovery.signal

# postgresql.conf (or .auto.conf)
restore_command         = 'cp /wal_archive/%f %p'
recovery_target_action  = 'promote'

recovery_target_time = '2025-03-15 14:23:00+00'

recovery_target_xid       = '1234567'
recovery_target_inclusive = false

recovery_target_name = 'pre_deploy'

SELECT pg_create_restore_point('pre_deploy');

pg_ctl -D $PGDATA start
tail -f $PGDATA/log/postgresql-*.log    # watch for "recovery complete"

SELECT pg_is_in_recovery();          -- f after promote
SELECT pg_last_wal_replay_lsn();
SELECT pg_last_xact_replay_timestamp();