Postgres Backup Checklist: What to Verify Before You Need It

There are two families. Logical backups, made with pg_dump, export schema and data as SQL or an archive file. They're portable across versions and good for moving data, but slow to restore on large databases and only capture a moment in time. Physical backups copy the data files, and combined with archived write-ahead logs (WAL) they allow point-in-time recovery to a chosen second.

Most production systems want both: physical backups with WAL archiving for fast recovery and precise rollback, plus periodic logical dumps as a portable safety copy. Managed services such as AWS RDS and Supabase provide automated backups and, on some plans, point-in-time recovery. Confirm exactly which your plan includes and how long it retains them, rather than assuming.

Two numbers drive the design. Recovery point objective (RPO) is how much recent data you can afford to lose. Recovery time objective (RTO) is how long you can be down. Nightly dumps mean an RPO of up to a day; WAL archiving can bring it down to minutes.

Put the numbers in context with your availability goals. A 99.9% availability target allows about 8.76 hours of downtime per year¹. A single slow restore of a large database could consume much of that, so measure your real restore time, not the vendor's brochure figure. A disaster recovery plan sets these targets per service, and each service gets its own pair of numbers.

Go through these and mark each yes or no:

• Automated backups run on a schedule, and you get an alert when one fails, not just when it succeeds.
• WAL archiving or the provider's point-in-time recovery is enabled, and you know the earliest restorable time.
• Backups are encrypted at rest, and you know who can access the keys.
• At least one copy lives in a separate account or project from production, so a compromised credential can't delete both.
• Retention is written down and matches your policy and any customer or legal commitments.
• Roles, extensions and other cluster-wide objects are captured, since pg_dump of one database doesn't include them.
• Someone other than the person who set it up can find and follow the restore steps.

A no on the off-account copy is the most common gap and the most damaging. Ransomware and mistaken deletes both target whatever the production credentials can reach.

Schedule a restore test at least quarterly. It takes a couple of hours the first time and gets faster.

1. Choose a recent backup, and a target time if you're testing point-in-time recovery.
2. Restore into a new, isolated instance, never over production.
3. Time the restore, and time how long the database takes to become consistent and usable.
4. Check contents: row counts on key tables, the newest record's timestamp, and a few application queries.
5. Point a staging copy of the app at it and run a smoke test.
6. Record the result, the duration and anything that surprised you, then update the runbook.

Also test the unpleasant case: someone deleted a table at a known time. Practice recovering to just before it and extracting the data without overwriting current rows.

The patterns behind most failed recoveries:

• Backups stored on the same disk, volume or account as the database.
• Never restoring, so a corrupt or incomplete backup goes unnoticed for months.
• Logical dumps that skip large objects, roles or extensions that the app needs.
• Assuming a managed service's backup covers you against accidental deletion. It restores the whole database, which may not be what you want.
• Ignoring backup size growth until storage or restore time hits a limit.

Related reading: the tradeoffs between hosts in MongoDB Atlas vs AWS RDS vs Supabase, and how to size costs in the managed Postgres cost comparison.