A backup is only a hope until you restore it

A problem

We had backups. Every night a job ran, a file appeared in a bucket, and a green check showed up somewhere. For a long time, that was enough to stop me worrying.

Then one day, someone asked me a simple question:

“If we lost the database at 2 PM, what could we get back?”

I started to answer, and realised I didn’t actually know.

The honest answer was: “last night’s dump, probably.” That meant losing everything from midnight to 2 PM. A whole morning of orders and payments, gone. And “probably”, because nobody had ever tried restoring one.

That question stuck with me, because it’s really two questions:

How much data can we lose? That’s the RPO (recovery point objective). With nightly dumps, ours was up to 24 hours.
How long until we’re back? That’s the RTO (recovery time objective). Ours was “no idea”.

“Losing the database” isn’t only a server dying

When I first thought about this, I imagined the dramatic version: the disk dies, the server catches fire. And honestly, for that case we were already in decent shape. We had replicas and automatic failover. If the primary disappeared, a replica would take over within seconds.

But then I thought about the other ways a database gets “lost”, and they worried me a lot more:

A migration that drops the wrong column, or runs UPDATE orders SET status = 'cancelled' without a WHERE.
A bug in the application that slowly writes wrong data for hours before anyone notices.
Someone running a “quick fix” by hand on production.

None of these kill the server. The database stays up, healthy and fast. It just has the wrong data in it.

And here’s the part that really got me: replicas don’t help at all. Replication copies every change to the standbys within milliseconds, including the bad ones. A dropped column is dropped everywhere. High availability protects you from losing a server. It does nothing for losing data.

That’s when I understood that backups and HA solve completely different problems.

Two kinds of backup

I ended up with two, because they answer different questions:

	answers	how
Physical backup + WAL archive	“Give me the whole database as it was at 13:59:30”	a base backup, plus every WAL file since, archived continuously
Logical dump	“Give me just that one table, or that one customer’s data”	a scheduled `pg_dump`

How the WAL archive works

PostgreSQL writes every change to its write-ahead log (WAL) before changing the actual data files. That’s how it recovers after a crash. The WAL is a complete, ordered record of everything that happened.

So if you keep:

a base backup, a full copy of the data directory at some point in time, and
every WAL file generated since then,

you can rebuild the database at any moment after the base backup, by starting from the copy and replaying the log up to exactly the point you want. That’s point-in-time recovery (PITR).

Turning on archiving is a few lines in postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = '... copy %p to the archive bucket ...'
archive_timeout = 60   # ship a WAL file at least every minute, even when it's quiet

Plus a regular base backup (pg_basebackup, or a backup tool that handles both parts). With WAL shipped at least every minute, our RPO went from 24 hours to about one minute.

The part that actually mattered: restoring

Configuring archiving took an afternoon. Learning to restore took much longer, because that’s where every wrong assumption showed up.

The drill I do now, on a separate machine, never on production:

Fetch the latest base backup and unpack it into an empty data directory.

Tell PostgreSQL where to get WAL from, and where to stop:

restore_command = '... fetch %f from the archive bucket into %p ...'
recovery_target_time = '2026-06-14 13:59:30+07'
recovery_target_action = 'promote'

Create an empty recovery.signal file in the data directory, so PostgreSQL starts in recovery mode.
Start it, and watch the logs as it replays WAL up to that moment.
Check the data is really there. Row counts, the last few orders before the target time, a couple of known records.
Write down how long it took.

The first time I did this, it failed twice. Once because the restore_command didn’t have permission to read the bucket from that machine. Once because I’d underestimated how long replaying a day of WAL would take. Neither would have been a fun discovery during a real incident.

Step 6 surprised me the most. “We have backups” and “we can be back in 40 minutes” are very different statements, and only the second one is useful when something is on fire.

Recovering from a bad migration

With PITR in place, I went back to the scenario that worried me most: a migration that destroys data.

Say a migration runs at 14:02 and drops a column it shouldn’t have. Here’s what recovery looks like now:

Find the exact moment. The deploy log or the migration tool’s history tells me when it ran. To be safe, I aim a little before it: 14:01:30.
Restore to a new, separate instance with recovery_target_time = '... 14:01:30'. Not on top of production. Production keeps running, and everything else that happened after 14:02 is still there.
Copy back only what was lost, the dropped column’s data, from the restored instance into production.
Throw away the restored instance.

Restoring the whole production database to 14:01 would be the wrong move. It would also throw away every legitimate order since then. PITR into a side instance lets you surgically take back just the missing piece.

Making bad migrations less likely in the first place

Recovering is good. Not needing to is better. A few habits I picked up:

Destructive migrations are reviewed differently. Anything with DROP, TRUNCATE, or UPDATE/DELETE on existing rows gets a second pair of eyes, and a sentence in the PR explaining why it’s safe.
Expand, then contract. Don’t drop a column in the same release that stops using it. Leave it for a later release, after you’re sure nothing needs it. (More on this in Deploying during business hours.)
Take a fresh backup right before a risky migration, so the restore point is seconds before it, not hours.
Test the migration on a restored copy of production first. It’s also a free restore drill.

What I took away

Replication is not a backup. It copies your mistakes just as faithfully as your data.
A backup job that succeeds only tells you a file was written. It doesn’t tell you that you can get your data back.
The only way to know is to restore, regularly, and time it.

And now, when someone asks “if we lost the database at 2 PM, what could we get back?”, I have an answer: “Everything up to about 1:59, and we’d be back in under an hour. Here’s the last time we tested it.”

References

PostgreSQL docs: Continuous Archiving and Point-in-Time Recovery