Backups you haven't restored aren't backups

'Do you have backups?' is the wrong question, because the answer is almost always yes and it tells you nothing. The two questions that carry information are: if the database died right now, how many minutes of writes would be gone, and how long until we're serving traffic again?

Those are RPO and RTO — recovery point objective and recovery time objective. Everything else in backup strategy is implementation detail in service of hitting the two numbers you've decided you can live with.

The two numbers

• RPO — recovery point objective. How much data you can afford to lose, measured in time. A nightly dump gives you an RPO of up to 24 hours: fail at 11pm and you lose the day's work. Continuous WAL archiving gives you an RPO measured in seconds.
• RTO — recovery time objective. How long recovery takes, from the failure to serving traffic. This is the one people wildly underestimate, because they've timed the restore command and not the whole sequence: noticing, deciding, finding the backup, provisioning somewhere to put it, restoring, verifying, and repointing the application.

They trade against cost and complexity, and different data deserves different answers. A financial ledger with an RPO of seconds and an analytics warehouse rebuildable from source events do not need the same strategy, and treating them identically means overpaying for one and underprotecting the other.

Logical and physical backups

Two mechanisms with genuinely different properties.

Logical: pg_dump

# A portable snapshot: SQL to recreate schema and data
pg_dump -Fc "$DATABASE_URL" -f backup.dump
pg_restore -d "$TARGET_URL" --clean --if-exists backup.dump

# Restores across Postgres major versions, and into a different platform

Portable, selective — you can restore a single table — and readable. The problems appear with size. A dump of a 100 GB database takes hours to produce and considerably longer to restore, because restoring means executing inserts and rebuilding every index from scratch. And a dump is a point in time, so your RPO is the interval between dumps.

Physical: base backup plus WAL

Copy the data directory, then continuously archive the write-ahead log. Restoration means putting the base backup back and replaying WAL forward to any moment you choose.

This is what makes point-in-time recovery possible, and PITR is the feature that changes what backups are for. Not just 'the machine died' but 'a migration at 14:32 deleted the wrong rows and we noticed at 15:10' — restore to 14:31 and the mistake never happened. That second scenario is far more common than hardware failure, and only physical backups with WAL can address it.

# Restore to a moment just before the bad migration — into a NEW database,
# leaving the original untouched while you verify
pandastack db clone db_abc123 \
  --target-time '2026-08-16T14:31:00Z' \
  --label recovery-check

The untested-backup problem

This is the part where most strategies fail, and it fails silently. Backups run for months, the job reports success, files accumulate in storage, and nobody discovers until a real incident that:

• The backup contains the schema but a permissions error meant one table's data was empty.
• Large objects or a particular extension weren't included, and the application won't start without them.
• The restore takes six hours, not the forty minutes everyone assumed.
• The retention policy silently deleted the backup you need, three days before you needed it.
• The restore procedure lives in the head of the person currently on a plane.
• The backup was fine but nobody can find the credentials to the storage bucket.

Every one of those is discoverable in advance by restoring, and undiscoverable any other way. A backup that has never been restored is a file whose properties you are guessing at.

Making the drill cheap enough to actually do

The reason teams don't test restores is that it traditionally means provisioning a machine, waiting hours, verifying, and tearing it down — a half-day of work that never wins against shipping features. So it goes on the list and stays there.

Restore-to-a-new-instance changes that calculus. If a restore is one command and the result is a live database you can query in minutes, testing becomes something you do monthly without ceremony — or on a schedule, without a human.

#!/usr/bin/env bash
# Monthly restore drill. Run it from CI on a schedule.
set -euo pipefail

TARGET=$(date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ)
NEW=$(pandastack db clone db_prod --target-time "$TARGET" \
        --label "drill-$(date +%F)" --json | jq -r .id)

# Wait for it, then assert the things that would actually be wrong
URL=$(pandastack db get "$NEW" --json | jq -r .connection_url)
psql "$URL" -v ON_ERROR_STOP=1 <<'SQL'
  SELECT count(*) > 0 AS users_present FROM users;
  SELECT count(*) > 0 AS orders_present FROM orders;
  SELECT max(created_at) FROM orders;   -- is it as recent as expected?
SQL

pandastack db delete "$NEW" --yes
echo "drill passed: restored to $TARGET"

Check row counts and recency, not just that the restore command exited zero. An empty table restores perfectly successfully.

Measuring RTO honestly

When you run the drill, time the whole sequence rather than the restore step. The realistic breakdown for an unrehearsed team:

1. Detection — how long before someone notices. Often the largest term, and entirely a monitoring question rather than a backup one.
2. Decision — is this a restore, or do we investigate more? Under pressure, with incomplete information. Rehearsing removes most of the hesitation.
3. Locating the right backup and the right timestamp.
4. The restore itself. The only step people usually time.
5. Verification — is this data correct, and how do we know?
6. Cutover — repointing the application, rotating credentials if the URL changed, and confirming traffic is being served.

A team that has never rehearsed typically discovers their real RTO is three to five times their estimate, and that almost all of the excess is in steps one, two and six — the human ones. Which is good news, because those improve with a written runbook far more cheaply than with infrastructure.

A workable baseline

• Write down your target RPO and RTO per database, and be honest that they differ. Not every dataset warrants the same protection.
• Use physical backups with continuous WAL archiving for anything where losing a day of writes is unacceptable. That is most production databases.
• Keep periodic logical dumps too. They restore across major versions and to other platforms, which is exactly what you want if the failure is your provider rather than your database.
• Restore to a new instance by default. Never over the top of the thing you are diagnosing.
• Test the restore on a schedule, with assertions on data, and delete the result. Automate it so it survives everyone being busy.
• Write the runbook while calm, including where credentials live and who can approve a cutover. The hard parts of an incident are the human ones.

The uncomfortable summary is that a backup you haven't restored is a hypothesis. Testing it converts it into a fact, and a monthly automated drill is a small price for knowing which one you have.