Backup and Restore

In one line

Backups are only as good as your last successful restore. Combine physical base backups (full plus incremental or differential) with continuous WAL archiving for point-in-time recovery, keep copies in another account and region, and restore-test automatically, measuring time and verifying data. Logical dumps are for migrations and small databases, not large-scale recovery.

0/5 · 0%

Think of it like this

A fire drill. Having extinguishers (backups) proves nothing; practising evacuation (restores) tells you whether people can actually get out, and how long it takes.

Key ideas

01
Logical backups (pg_dump): portable SQL or custom format, per database or table, slow to restore for large data (indexes rebuilt), consistent snapshot. Good for small databases, migrations between versions, and extracting single tables.
02
Physical backups (pgBackRest, WAL-G, Barman, cloud snapshots): copy data files plus WAL. Full, differential (changes since last full) and incremental (changes since last backup) balance storage against restore time. PostgreSQL 17 added native incremental backup (pg_basebackup --incremental with WAL summarization).
03
PITR = base backup + archived WAL. Retention is set by business needs (e.g. 35 days of PITR, monthly fulls for a year).
04
3-2-1 rule: three copies, two media, one off-site; plus immutability (object lock) and a separate account so ransomware or a compromised admin can't delete backups.
05
Verification: automated regular restores into a scratch environment, pgbackrest verify, checksums (data_checksums enabled), running application smoke queries and row counts, and recording actual restore duration against the RTO.

Code & diagrams

pgbackrest.confini

[global]
repo1-type=s3
repo1-s3-bucket=acme-pg-backups
repo1-s3-region=ap-south-1
repo1-retention-full=4
repo1-cipher-type=aes-256-cbc
# second repository: another region, separate account
repo2-type=s3
repo2-s3-bucket=acme-pg-backups-dr
repo2-s3-region=ap-southeast-1
process-max=8
compress-type=zst

[main]
pg1-path=/var/lib/postgresql/17/main
# postgresql.conf: archive_mode=on, archive_command='pgbackrest --stanza=main archive-push %p'

backup-schedule.shbash

# weekly full, daily differential, WAL archived continuously
0 1 * * 0   pgbackrest --stanza=main --type=full backup
0 1 * * 1-6 pgbackrest --stanza=main --type=diff backup
# weekly automated restore test into a scratch host
0 4 * * 3   /opt/dr/restore-test.sh   # restore latest, start, run checks, report duration

pgbackrest --stanza=main info
# full backup: 20260913-010002F  size: 1.9TB  repo size: 412GB  duration: 01:42:10

Interview problem

The problem

Design backups for a 5 TB payments database

Requirements: restore to any point in the last 30 days, RPO ≤ 1 minute, RTO ≤ 2 hours, protection against ransomware and region loss. Design the backup strategy and verification.

When it breaks

Backups never restored

What you see

During an incident the team discovers backups were missing WAL (archive failing for weeks) or take 14 hours to restore, far beyond the RTO.

Fix & prevent

Automated restore tests with alerts; monitor archiver failures; measure RTO regularly.

Explain it without notes

Why are replicas not backups?

Practice

Estimate restore time for 3 TB at 400 MB/s plus 50 GB of WAL replayed at 100 MB/s.

Trade-offs

↔
More frequent fulls shorten restores but cost storage and I/O; incrementals save space but lengthen restore chains.

Done when you can

I can design and verify a backup strategy that meets a stated RPO and RTO.

Command Palette