Database Backups & Recovery: PITR, RPO/RTO & Tested Restores

Q: Logical or physical backups?

Use both. Logical backups (pg_dump) are portable and great for restoring a single database or moving across versions, but slow for large data. Physical backups plus continuous archiving give fast restores and point-in-time recovery for production-scale databases.

Database Backups & Recovery

A backup you've never restored is a hope, not a strategy. Backups protect against the failures replication can't — accidental DELETEs, bad migrations, ransomware, and bugs that corrupt data — by letting you roll the database back to a known-good point.

The two questions that drive every decision: how much data can you afford to lose (RPO), and how fast must you recover (RTO)? Everything else — backup type, frequency, storage — follows from those numbers.

TL;DR

Combine logical (portable) and physical/continuous (fast, point-in-time) backups.
Define your RPO (acceptable data loss) and RTO (acceptable downtime).
Follow 3-2-1: 3 copies, 2 media, 1 offsite.
Test restores regularly — an untested backup may not work.

Quick Example

A logical backup pushed offsite, with local cleanup — the bones of a daily job:

Core Concepts

Backup types

Point-in-time recovery (PITR)

A base backup plus archived WAL/binlogs lets you restore to any moment — e.g. one second before a bad DELETE. This is how you achieve a near-zero RPO.

RPO and RTO

RPO (Recovery Point Objective) — how much data loss is acceptable (drives backup frequency).
RTO (Recovery Time Objective) — how fast you must be back (drives backup type and automation).

Best Practices

Follow the 3-2-1 rule: 3 copies, on 2 different media, with 1 offsite.
Use continuous archiving / PITR for critical databases.
Test restores on a schedule and measure your actual RTO.
Store backups in multiple regions; tier old ones to cold storage for compliance.

Common Mistakes

Never testing the restore

A single copy in one place

FAQ

Logical or physical backups?

Use both. Logical backups (pg_dump) are portable and great for restoring a single database or moving across versions, but slow for large data. Physical backups plus continuous archiving give fast restores and point-in-time recovery for production-scale databases.

What are RPO and RTO?

RPO is how much data you can afford to lose (e.g. "at most 5 minutes"), which sets backup frequency. RTO is how quickly you must recover (e.g. "within 1 hour"), which sets backup type and automation. They define your strategy.

How often should I back up?

Frequently enough to meet your RPO. For most production systems that means continuous WAL/binlog archiving for point-in-time recovery, plus periodic full backups. A daily-only backup implies you can lose up to a day of data.

Do managed databases make backups unnecessary?

They automate backups and PITR, which helps — but you're still responsible for retention, cross-region copies for disaster recovery, and (crucially) testing that you can actually restore. Don't assume "managed" means "handled."