A Practical Postgres Backup and Restore Playbook
A backup you have never restored is a rumour. Here is how to take backups you can trust, and rehearse the restore before you need it.
Every team believes it has backups until the afternoon it needs one. The difference between a bad hour and a company-ending day is whether you have actually restored from those backups before, on purpose, when nothing was on fire.
Two kinds of backup, and you want both
- Logical dumps with
pg_dumpare portable and great for moving a single database or version upgrade. - Physical base backups plus WAL archiving give you point-in-time recovery, so you can rewind to the second before the bad
DELETE.
A nightly logical dump is the easy win. Compress it, timestamp it, and ship it off the server the same night.
pg_dump --format=custom --compress=9 \
--dbname="$DATABASE_URL" \
--file="backup-$(date +%F).dump"
# ship it off-box the same night
rclone copy backup-*.dump digitel:db-backups/Rehearse the restore
The restore is the part nobody practises. Do it into a scratch database on a schedule so the runbook is muscle memory, not archaeology.
createdb restore_check
pg_restore --dbname=restore_check --jobs=4 backup-2026-04-29.dump
psql restore_check -c "select count(*) from orders;" # sanity check
dropdb restore_checkThe only backup that counts is the one you have restored. Everything else is a hopeful file on a disk somewhere.
— Anonymous, Database Reliability Engineer
On Managed PostgreSQL we run continuous WAL archiving and automated point-in-time recovery for you, and we test restores on a rolling schedule. But even then, keep one independent logical dump of your own. Owning a copy you can read without us is good hygiene, not distrust.
Write the whole thing down as a runbook, note who has access to the backup bucket, and set a calendar reminder to run a real restore drill each quarter.