Backup and disaster recovery: RPO, RTO, and restore drills for PostgreSQL

A backup without restore is not enough#

Many teams only verify that backup jobs completed. During real incidents, they may discover missing permissions, missing WAL files, wrong encryption keys, or restore times that exceed business expectations. A professional backup strategy includes backup, verification, restore drills, and access control.

For blogs, CMS, CRM, and LMS products, the database is usually the most valuable asset. Code can be rebuilt; user data, comments, orders, and learning records are much harder to recover.

RPO and RTO#

RPO is the maximum acceptable data loss. RTO is the maximum acceptable time to restore service. These numbers determine architecture. Daily backups may be acceptable for a personal blog but not for a transaction-heavy system.

• Content blog: RPO and RTO of several hours may be acceptable.
• Operational CRM: RPO of 15-60 minutes and RTO of a few hours may be needed.
• Payments or orders: plan for PITR, replicas, runbooks, and regular drills.

PostgreSQL backup options#

Logical backups such as pg_dump are easy for small databases and migrations between versions. Physical backups with WAL archiving support point-in-time recovery for more serious production needs.

pg_dump --format=custom --file=app.dump "$DATABASE_URL"
pg_restore --clean --if-exists --dbname="$RESTORE_DATABASE_URL" app.dump

Managed platforms still require operational understanding: retention, PITR limits, backup region, restore workflow, and restore testing into a separate environment.

Run restore drills#

A restore drill measures real recovery time and reveals missing steps. Restore into a separate database, run smoke tests, compare against RTO, and update the runbook.

Secure backups like production#

Backups often contain all sensitive data. Encrypt them, limit access, audit reads, define retention, and avoid copying production backups to personal laptops or public buckets.

Checklist#

• Define RPO and RTO for each system.
• Automate backups and alert on failed jobs.
• Use PITR where important data changes frequently.
• Run restore drills into isolated environments.
• Encrypt backups and restrict access.
• Document commands, owners, smoke tests, and rollback steps.

Primary references#

• PostgreSQL Docs: Backup and Restore
• PostgreSQL Docs: Continuous Archiving and PITR
• Supabase Docs: Backups