Most data-loss incidents don't happen at the moment of the failure, but at the moment of restoring: the backup exists, the script has been running for months, and it's only in a real situation that you discover it's corrupted, incomplete, or doesn't cover the time window you actually need. This post covers how to build a PostgreSQL backup strategy that holds up, and why "we have a cron job running pg_dump" is not a strategy.
The problem: pg_dump alone doesn't cover every scenario
A daily pg_dump is the most common reflex, and it has a structural limit: it only captures the database's state at the moment it runs. If an incident happens (an accidental table drop, a botched migration, corruption) sixteen hours after the last dump, sixteen hours of data are gone, full stop. No partial restore to "ten minutes ago."
The second problem is restore time itself. A custom-format pg_dump restored with pg_restore has to rebuild indexes and constraints from scratch. On a database of several hundred gigabytes, that can take hours, which is incompatible with a recovery time objective (RTO) measured in minutes.
Why it happens: confusing logical backups with physical backups plus PITR
PostgreSQL offers two fundamentally different backup families, and the choice between them changes everything.
Logical backup (pg_dump / pg_dumpall): exports data as SQL commands or a custom format, independent of the target PostgreSQL version or architecture. Portable, but slow to restore on large volumes, and unable to restore to a precise moment between two dumps.
Physical backup with PITR (Point-In-Time Recovery): combines a binary copy of the data files (pg_basebackup or a disk snapshot) with continuous archiving of transaction logs (WAL, Write-Ahead Log). By replaying WAL from the base backup up to a precise instant T, you can restore the database exactly as it was at that moment, down to the second - typically right before an accidental DROP TABLE.
It's this second approach that gives a genuinely fine-grained recovery point objective (RPO), but it requires explicit configuration that many teams never turn on because pg_dump "appears" to work.
Setting up PITR: WAL archiving
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
max_wal_senders = 3
Without a dedicated backup manager, archive_command can simply copy WAL segments to durable storage (an S3 bucket, remote NFS):
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
In practice, managing WAL archiving yourself with plain shell scripts quickly becomes a source of errors (missing segments, no integrity checks, no retention management). Dedicated tools like pgBackRest or WAL-G exist precisely for this: they handle compression, encryption, retention, integrity verification, and parallelized restores.
# pgBackRest - full backup followed by an incremental one
pgbackrest --stanza=main --type=full backup
pgbackrest --stanza=main --type=incr backup
# Point-in-time restore, right before an accidental DROP TABLE at 2:31:55 PM
pgbackrest --stanza=main --type=time \
--target="2026-02-10 14:31:55" \
--delta restore
Diagnosing it: know what you're actually restoring before the incident
A backup script that finishes without an error proves nothing about the validity of its content. A pg_dump can complete successfully on a database where some tables were locked and silently excluded from a concurrent transaction, or produce a truncated export if the destination disk filled up mid-run without the exit code properly reflecting the issue.
#!/usr/bin/env bash
# Minimal check after a pg_dump: the file must be non-empty
# and pg_restore must be able to read its table of contents without error
set -euo pipefail
DUMP_FILE="/backups/prod_$(date +%Y%m%d).dump"
pg_dump -Fc -d prod_db -f "$DUMP_FILE"
if [ ! -s "$DUMP_FILE" ]; then
echo "ERROR: backup file is empty" >&2
exit 1
fi
pg_restore --list "$DUMP_FILE" > /dev/null
echo "Valid backup: $(pg_restore --list "$DUMP_FILE" | wc -l) objects"
This check stays superficial: it confirms the file is readable, not that the data inside it is correct. The only check that actually matters is a full restore on a separate environment, followed by business-level consistency queries (expected row counts, application-level constraint checks).
The fix: test the restore, not just the backup
Automate an end-to-end restore, regularly
#!/usr/bin/env bash
# Restores the latest backup onto a disposable test instance
# and runs consistency checks before declaring the test successful
set -euo pipefail
RESTORE_DB="restore_test_$(date +%s)"
createdb "$RESTORE_DB"
pg_restore -d "$RESTORE_DB" --jobs=4 /backups/latest.dump
ROW_COUNT=$(psql -d "$RESTORE_DB" -t -c "SELECT count(*) FROM orders;")
if [ "$ROW_COUNT" -lt 1000 ]; then
echo "ALERT: abnormally low order count after restore ($ROW_COUNT)" >&2
exit 1
fi
echo "Restore validated: $ROW_COUNT orders found"
dropdb "$RESTORE_DB"
Run weekly (or nightly for critical data) against an isolated environment, this script turns "we assume the backup works" into "we know it worked X hours ago." --jobs=4 on pg_restore parallelizes index rebuilding, which also measures a realistic restore time - essential data for setting a credible RTO.
Measure and document real RPO and RTO
RPO (how much data you can afford to lose) and RTO (how long a restore takes) aren't numbers you decide upfront, they're measurements from real tests. Continuous WAL archiving with PITR gives an RPO close to zero (a few seconds, the time it takes for the last WAL segment to be archived), but RTO depends on how much WAL has to be replayed since the last full backup - which is the reason to take regular full backups rather than relying solely on continuous archiving from the very beginning.
Monitoring the backup pipeline itself
A backup job is a process that can fail silently just like any other: a cron job that stops running after a server migration, a disk that fills up on the archive target, a credential that expires. None of these failures produce an error anyone sees unless the backup pipeline is monitored the same way any other production process would be.
#!/usr/bin/env bash
# Checks that the most recent full backup is not older than 25 hours
# and that pgBackRest reports no errors in its last check
set -euo pipefail
LAST_BACKUP_AGE_SECONDS=$(pgbackrest --stanza=main info --output=json \
| jq -r '.[0].backup[-1].timestamp.stop')
NOW=$(date +%s)
AGE=$(( NOW - LAST_BACKUP_AGE_SECONDS ))
if [ "$AGE" -gt 90000 ]; then
echo "ALERT: last backup is $(( AGE / 3600 )) hours old" >&2
exit 1
fi
pgbackrest --stanza=main check
Wiring this check into the same alerting pipeline as application metrics (a Prometheus textfile exporter writing the backup age as a gauge, for instance) turns a backup failure into a page within minutes instead of a discovery made during an actual restore attempt, which is the worst possible moment to learn that the last valid backup is three weeks old.
# node_exporter textfile collector - written by the check script above
# postgres_backup_age_seconds 3600
The tradeoffs
Continuous WAL archiving increases the storage volume needed and operational complexity (monitoring disk space on the archive server, managing retention). For a non-critical database with an acceptable 24-hour RPO, a daily pg_dump with a tested restore remains a reasonable solution that's much simpler to maintain.
Tools like pgBackRest add an extra dependency and a learning curve, but avoid reinventing retention management, checksum verification, and parallel archiving - aspects that are hard to get right with homegrown scripts.
When the standard plan isn't enough
For a replicated database with high-availability requirements, backups alone aren't enough: a physical standby replica (streaming replication) handles availability (failing over in seconds if the primary goes down), while a PITR-based backup handles a different scenario, logical corruption (an UPDATE without a WHERE clause run by mistake, which gets replicated just as faithfully to the standby). The two mechanisms are complementary, not substitutes for one another.
The point to keep: a backup that has never been restored isn't a backup, it's an assumption. PITR via WAL archiving gives the granularity needed to roll back to just before the mistake, but its real value is only proven by regularly testing a full restore on a separate environment.
