PostgreSQL Backup and Point-in-Time Recovery

Losing a PostgreSQL database to hardware failure or accidental data deletion is survivable — if you have a proper backup strategy. A dump from pg_dump gets you to the previous night; point-in-time recovery (PITR) gets you to the second before the DROP TABLE. This guide covers both layers: base backups with pg_basebackup, WAL archiving, and a production-grade workflow using pgBackRest, finishing with a concrete PITR restore.

Prerequisites and Concepts

PostgreSQL's PITR relies on two things working together: a base backup (a consistent snapshot of the data directory) and a continuous stream of Write-Ahead Log (WAL) segments that record every change after that snapshot. To recover to a specific moment, Postgres replays WAL on top of the base backup until it reaches your target time or transaction ID.

• Running PostgreSQL 15 or 16 (commands are identical for both; note any 14 differences)
• A separate backup destination — another disk, NFS mount, or object storage
• Enough disk space: base backup ≈ size of your data directory; WAL retention multiplies that
• sudo / root access and the ability to edit postgresql.conf

Step 1: Configure WAL Archiving

WAL archiving tells Postgres to copy completed WAL segments to a safe location instead of recycling them. Edit postgresql.conf — usually at /etc/postgresql/16/main/postgresql.conf on Debian/Ubuntu or /var/lib/pgsql/16/data/postgresql.conf on Fedora/RHEL.

# Find the config file if unsure
sudo -u postgres psql -c "SHOW config_file;"

Add or update these parameters:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/pgarchive/%f && cp %p /mnt/pgarchive/%f'
archive_timeout = 300   # archive incomplete segments every 5 minutes

The archive_command must return exit code 0 only on success. The example above copies to a local path — replace with an rsync or pgbackrest archive-push call in production. Create and permission the archive directory:

sudo mkdir -p /mnt/pgarchive
sudo chown postgres:postgres /mnt/pgarchive
sudo chmod 700 /mnt/pgarchive

Restart PostgreSQL for archive_mode to take effect (a reload is not enough):

# Debian/Ubuntu
sudo systemctl restart postgresql@16-main

# Fedora/RHEL/Rocky
sudo systemctl restart postgresql-16

# Arch
sudo systemctl restart postgresql

Verify archiving is active:

sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('wal_level','archive_mode','archive_command');"

Step 2: Take a Base Backup with pg_basebackup

pg_basebackup connects to a running cluster and streams a consistent backup. The postgres OS user must be able to authenticate as a replication-capable role.

sudo -u postgres pg_basebackup \
  --pgdata=/mnt/pgbackup/base_$(date +%Y%m%d) \
  --format=tar \
  --gzip \
  --checkpoint=fast \
  --wal-method=stream \
  --progress \
  --verbose

• --format=tar --gzip: produces compressed archives (base.tar.gz + WAL tar)
• --wal-method=stream: streams WAL generated during the backup, so the backup is self-contained even if archiving lags
• --checkpoint=fast: starts a checkpoint immediately rather than waiting; fine for backups, but generates I/O

The output directory will contain base.tar.gz and pg_wal.tar.gz. Record the backup label — you'll need it during recovery.

Step 3: Production Workflow with pgBackRest

pg_basebackup is enough for learning PITR, but pgBackRest is what you want for production: parallel compression, incremental backups, built-in WAL archiving, retention policies, and S3/Azure/GCS support.

Install pgBackRest

# Debian/Ubuntu
sudo apt install pgbackrest

# Fedora/RHEL/Rocky (requires PGDG repo)
sudo dnf install pgbackrest

# Arch (AUR)
yay -S pgbackrest

Configure pgBackRest

Create /etc/pgbackrest/pgbackrest.conf:

[global]
repo1-path=/mnt/pgbackup/pgbackrest
repo1-retention-full=2
log-level-console=info
log-level-file=debug
start-fast=y

[mydb]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432

The stanza name (mydb) is arbitrary but must be consistent. Initialise the repository:

sudo -u postgres pgbackrest --stanza=mydb stanza-create

Update archive_command in postgresql.conf to use pgBackRest:

archive_command = 'pgbackrest --stanza=mydb archive-push %p'
restore_command = 'pgbackrest --stanza=mydb archive-get %f "%p"'

Reload Postgres after this change (no restart needed if archive_mode was already on):

sudo -u postgres psql -c "SELECT pg_reload_conf();"

Run a Full Backup

sudo -u postgres pgbackrest --stanza=mydb --type=full backup

Schedule incremental backups via systemd timers or cron. A common pattern: full backup weekly, incremental nightly.

# Verify backup integrity
sudo -u postgres pgbackrest --stanza=mydb info

Output will list backups with timestamps, WAL ranges, and sizes — confirm the latest backup's WAL stop LSN.

Step 4: Point-in-Time Recovery

Scenario: it's 14:37 and someone ran DELETE FROM orders WHERE 1=1; at 14:22. You want to recover to 14:21:59.

Stop PostgreSQL

sudo systemctl stop postgresql@16-main   # Debian/Ubuntu
sudo systemctl stop postgresql-16         # Fedora/RHEL

Restore the Base Backup

With pgBackRest, restore directly over the data directory. The --target flag specifies the recovery timestamp (in cluster timezone):

sudo -u postgres pgbackrest --stanza=mydb \
  --delta \
  --type=time \
  --target="2025-07-15 14:21:59" \
  --target-action=promote \
  restore

--delta only replaces changed files, which is faster when restoring in place. --target-action=promote tells Postgres to become writable once it reaches the target rather than pausing.

Start and Verify

sudo systemctl start postgresql@16-main

# Watch recovery progress
sudo tail -f /var/log/postgresql/postgresql-16-main.log

You'll see lines like recovery stopping before commit of transaction and finally database system is ready to accept connections. Then verify:

sudo -u postgres psql -c "SELECT COUNT(*) FROM orders;"

If the count matches expectations, the recovery succeeded. If you overshot or undershot, stop Postgres, adjust --target, and re-restore.

Manual PITR Without pgBackRest

If using raw pg_basebackup output, extract the tarball to the data directory, then create a recovery.signal file and set parameters in postgresql.conf (PostgreSQL 12+):

touch /var/lib/postgresql/16/main/recovery.signal

# In postgresql.conf:
restore_command = 'cp /mnt/pgarchive/%f %p'
recovery_target_time = '2025-07-15 14:21:59'
recovery_target_action = 'promote'

Start Postgres normally; it will enter recovery mode automatically when it finds recovery.signal.

Verification Checklist

• WAL segments appear in the archive directory within archive_timeout seconds of a checkpoint
• pg_stat_archiver shows last_failed_wal as empty: SELECT * FROM pg_stat_archiver;
• pgBackRest info command shows a valid WAL range covering your backup
• Test restores — schedule a quarterly drill to a separate server

Troubleshooting

• Archive command failing silently: Check pg_stat_archiver.last_failed_wal and last_error. Run the archive_command manually as the postgres user to see the real error.
• Recovery stalls at startup: Missing WAL segment. Ensure your archive path and restore_command are correct. The Postgres log will name the exact missing file.
• pg_basebackup: could not connect to server: The connecting role needs REPLICATION privilege or be a superuser. Check pg_hba.conf for a replication database entry.
• pgBackRest stanza-create fails: Confirm the pg1-path is the actual data directory (SHOW data_directory;) and that the postgres user can write to repo1-path.
• Disk space: WAL archiving accumulates fast on busy systems. Set repo1-retention-archive in pgBackRest to cap WAL retention independently of full backup retention.