Backup and PITR with pg_basebackup + WAL archive (without pgBackRest)

Physical backup with PostgreSQL's own tools, no extra software: continuous WAL archiving with archive_command, a nightly base backup with pg_basebackup, retention with pg_archivecleanup. Recovery to a point in time / transaction / restore point (recovery.signal, restore_command, recovery_target_*), timelines and a regular restore drill; the result is an archived WAL stream, a nightly backup and a tested way back. Alternatives: if you need incremental/parallel/encrypted backups, S3 and built-in retention management, use the pgBackRest guide; to move a table/database or bring back a single table, use a logical backup (pg_dump, no PITR).

Topology: 1 server; with streaming or Patroni the archive directory is shared by all nodes (NFS)

Scenarios

• Set up WAL archiving and a nightly base backup — Initial setup: continuous WAL archiving with archive_command, a pg_basebackup script, scheduling in /etc/cron.d, retention and WAL cleanup. archive_mode needs one short restart.
• Recover to a specific point (PITR: time, xid, LSN, restore point) — Data was deleted/changed by mistake (DROP, DELETE, TRUNCATE) or the server/disk was lost: the base backup is restored and archived WAL is replayed up to the target. The whole cluster goes down and returns to the target; everything after the target is lost.
• Restore drill (in a separate directory, without touching production) — To prove that the backup actually restores (monthly recommended): the latest base backup is verified (pg_verifybackup), extracted into a separate directory and started on a separate port with the archive; the time (RTO) is measured. Production is not touched.
• Clean up old base backups and the WAL archive — The archive or backup disk is filling up, or retention has changed: old base backups are deleted and pg_archivecleanup removes WAL older than the oldest backup (dry run first). The database stays open.

Before you start

• PostgreSQL … is installed and running (pg-install); data directory ….
• … and … are on a disk/NFS share separate from the data disk, mounted persistently (fstab)… and at the same path on every PostgreSQL server (after a failover the new primary must write to the same archive)…. Space: (number of kept backups + 1) × compressed data size + daily WAL volume × retention days.
• No other archive/backup tool (pgBackRest, Barman, WAL-G) is configured; if two tools ship the same WAL to different places, both end up incomplete.
• Clocks are synchronized with NTP (the PITR target time is interpreted against the server clock).
• A short restart window is available for the archive_mode change.
• Applications are stopped; it has been accepted that all changes after the target will be lost.… Standbys/replicas will be rebuilt after the restore.…

Phases of the guide

• WAL archive and base backup setup — Prepare the archive and backup directories · Write the WAL archive script · Make the script executable and test it · Write the PostgreSQL archive settings · Restart PostgreSQL · Write the archive settings to Patroni (edit-config) …
• Restore drill — Prepare the drill directory and select the latest backup · Extract and verify the backup (pg_verifybackup) · Start the drill instance with the archive · Verify the data · Shut down and clean up the drill instance
• Retention and WAL cleanup — Backups, archive size and the oldest WAL needed · Delete base backups outside the retention · Show the WAL cleanup with a dry run first · Clean up the WAL archive · Verify that archiving still works