Postgres point-in-time recovery, explained

There are two kinds of database recovery. The first is 'the machine is gone, restore last night's backup' — you lose up to a day of writes and everyone accepts it because the alternative is losing everything. The second is 'at 14:32 someone ran an UPDATE without a WHERE clause, put it back to 14:31' — and a nightly dump cannot do that at all.

Point-in-time recovery is the second kind. It's the difference between losing a day and losing a minute, and between 'we restored the backup' and 'we restored to the moment before the incident.' The mechanism is not complicated. The operational details are where teams discover their PITR doesn't work, usually at the worst possible time.

How it works: a base backup plus a log of everything since

Postgres never writes a change directly to a data file first. It writes it to the write-ahead log — an append-only record of every modification — and only later does the change reach the data files. This exists for crash safety: after an unclean shutdown, Postgres replays the WAL to reconstruct anything that hadn't made it to disk.

PITR takes that crash-recovery mechanism and stretches it across time. Take a full copy of the data directory — the base backup — and then continuously archive every WAL segment produced afterwards. Now you can reconstruct the database at any moment: start from the base backup, replay the archived WAL forward, and stop at the timestamp you want.

1. A base backup: a consistent full copy of the cluster, taken periodically.
2. Continuous WAL archiving: every segment shipped off the machine as it's completed.
3. A recovery target: 'replay until this timestamp' — or until a named restore point, a specific transaction id, or the end of the log.
4. Replay: Postgres starts from the base backup, applies WAL in order, stops at the target, and opens.

The consequence people find surprising: recovery time depends on how much WAL must be replayed, which means it depends on how old your most recent base backup is and how write-heavy your workload was in between. A week-old base backup and a busy database can mean hours of replay. This is why base backups are taken regularly rather than once.

The two numbers that matter

Every backup conversation should start with these, and most start with 'do we have backups?' instead.

• RPO — recovery point objective. How much data can you lose? With nightly dumps it's up to 24 hours. With continuous WAL archiving it's roughly the time since the last archived segment, typically seconds to a couple of minutes.
• RTO — recovery time objective. How long until you're serving again? This is base-backup restore time plus WAL replay time plus the human time spent deciding, which is usually the largest term and is never in anyone's estimate.

Choosing a recovery target

Timestamp is the obvious target and the one you'll use. But 'the moment before the incident' is harder to identify than it sounds — the bad UPDATE happened at 14:32:07 according to a log line whose clock may not match the database's, and recovering to 14:32:06 might land mid-transaction for something else entirely.

Three practical notes. Recovery targets are exclusive of the transaction at the target if you ask for that, and the semantics are worth reading carefully rather than assuming. Named restore points, created before a risky migration, are far more reliable than guessing a timestamp afterwards — a one-line call before the migration saves an hour of guessing after it. And recovering to a transaction id is the precise option when you know exactly which transaction did the damage.

-- Before a risky migration: create a named target you can aim at later.
-- Vastly easier than reconstructing 'what time was it before this went wrong'.
SELECT pg_create_restore_point('before_orders_backfill_v3');

-- Afterwards, if it went badly, recover to that named point rather than
-- to a timestamp you inferred from an application log with a different clock.

-- Useful when picking a target: how far back does the archive actually go?
SELECT pg_current_wal_lsn();
-- and check your archive's oldest retained segment -- retention, not the
-- existence of archiving, is what bounds how far back you can recover.

Recover into a copy, never in place

The most important operational habit: never recover over the live database. Recover into a new one, inspect it, and only then decide.

The reason is that you're usually wrong about the target on the first attempt. You recover to 14:31 and discover the damage started at 14:26. If you recovered in place, you've now destroyed the evidence and the intervening writes, and your second attempt starts from a worse position. If you recovered into a copy, you shrug and run it again with a different timestamp while production carries on serving stale-but-real data.

This is why PandaStack exposes PITR as a clone: recovery produces a brand-new database with its own id and connection string, and the source is untouched. You point a psql session at the clone, confirm the row counts look right, and then decide whether to switch over, export a table from it, or throw it away and try another timestamp. The target must be at least a couple of minutes in the past, because the relevant WAL has to have reached the archive first — asking for ten seconds ago asks for data that may still be in flight.

# Recover to just before the bad migration -- as a NEW database.
# The source keeps serving throughout; nothing is overwritten.
curl -sS -X POST https://api.pandastack.ai/v1/databases/$DB_ID/clone \
  -H "Authorization: Bearer $PANDASTACK_API_KEY" \
  -H 'Content-Type: application/json' \
  -d '{"label": "recover-1431", "target_time": "2026-08-16T14:31:00Z"}'

# Then verify BEFORE switching anything over:
#   psql "$CLONE_URL" -c "select count(*) from orders where created_at > '...'"
#   psql "$CLONE_URL" -c "select * from orders where id = 41982"
# Wrong timestamp? Clone again with a different one. The source is fine.

How PITR fails in practice

Almost never because the mechanism is broken. Almost always because of one of these.

• Archiving silently stopped. The archive command started failing weeks ago — credentials expired, bucket permissions changed, disk filled. Postgres logs it and keeps serving, and nobody reads that log. Monitor archiving success as a first-class metric, not as a log line.
• Retention is shorter than you think. Archiving works perfectly and segments older than seven days are deleted, so you cannot recover to last month no matter how correct everything else is. Know your window and write it down where the incident commander will find it.
• The base backup is too old. Recovery works but replays four weeks of WAL and takes six hours. Your RTO was 'about an hour' in the runbook.
• Nobody has ever tested a restore. This is the big one. An untested backup is a hypothesis about a file, and roughly half the teams I've talked to discover their first real problem during their first real restore.
• Recovery succeeded, the application didn't. The database is back at 14:31 but the queue processed those messages, the payment provider charged the cards, and the search index still has the deleted rows. Database recovery is one component of incident recovery, and the reconciliation afterwards is usually the longer job.

PITR is also a development tool

Once recovery produces a copy rather than overwriting the original, the same mechanism does things that have nothing to do with disasters. Clone production as of last night into a staging database with realistic data. Reproduce a bug by recovering to the moment it was reported, with exactly the data that triggered it. Test a migration against a real copy and time it before running it for real.

That last one has saved me twice. A migration that takes 200ms on a seeded dev database and 40 minutes with a table lock on the production shape is a well-known genre of outage, and the only reliable way to know which one you have is to run it against a copy of production first. If cloning is a single API call, there's no excuse not to.