Someone dropped the table at 15:07. Can we recover 15:06?

Interview question. Explain how a PostgreSQL base backup and continuously archived write-ahead log provide point-in-time recovery. The production database is still accepting legitimate writes when an administrator drops a critical table. Recover the data without quietly discarding everything customers did afterward.

At 15:07, an engineer runs a migration against the wrong environment. The payments table disappears. Someone says, 'Relax, we took a backup last night.' That may be true, but restoring last night's backup would also throw away fifteen hours of unrelated legitimate changes. The real interview question is what you can recover to, what history you can preserve, and how you safely merge the two.

PostgreSQL uses a write-ahead log, or WAL, to record changes before they become durable data-page updates. For this lesson, picture a base backup starting at midnight and completing at 00:30. The base backup provides physical data files. The WAL archive supplies the ordered history needed to bring that base backup forward. Recovery restores the files and replays the required WAL through a selected recovery target. The base backup alone is not a magic midnight SQL dump, and a directory of WAL files without a usable base backup is not a complete recovery set.

The first detail that matters is continuity. You need the WAL generated during the base backup and every required segment up to the recovery target, from the correct database history. A successful daily backup with a six-hour archive gap cannot restore to an arbitrary minute inside that gap. Archive health, missing segments, backup manifests, encryption keys, and restore permissions are part of your effective recoverability. PostgreSQL distinguishes ordinary logical pg_dump output from physical base backups. You cannot take a pg_dump and replay a physical WAL archive onto it.

Suppose the destructive command committed at 15:07:20. We create a separate recovery environment from the latest suitable base backup. We configure the archive-retrieval command and a target just before the destructive transaction. Using a timestamp alone is risky when several legitimate transactions commit close together or clocks differ. If an exact restore point or transaction boundary was recorded, that may be safer. We inspect the logs and verify the target on a staging copy before authorizing any production cutover. The recovered cluster should contain the table as of the selected point and should exclude the destructive change.

Now for the unpleasant part. Legitimate writes to unaffected tables accepted from 15:07:20 until the incident was noticed at 15:20 remain present in the original production history. Payments accepted by an external processor may also have durable receipts outside this cluster; the dropped table cannot itself keep accepting inserts until it is repaired or recreated. If we simply replace the entire database with the recovered old cluster, we lose those later payments too. That may violate a far stricter business invariant than the original table deletion. The safer plan could be to recover the previous table state into an isolated cluster, export the relevant rows, compare identities and versions, and reconcile them into a repaired production database under controlled write conditions. Sometimes a full-cluster cutover is appropriate, but you must account for post-target changes, external side effects, and the possibility of reapplying a payment twice.

PostgreSQL recovery creates a new timeline when archive recovery completes. That's not cosmetic. The recovered history now branches from the original at the chosen point. Retain timeline history files and the original WAL archive so investigators can still examine later commits or try a different recovery boundary. Overwriting the only copy of the incident history to save storage is exactly the sort of shortcut that makes a second restore attempt impossible.

Storage cost follows two variables: the size and frequency of base backups, and the amount of WAL retained to support the oldest recovery target you promise. Frequent base backups reduce the distance you must replay but cost storage and I/O. Less frequent base backups can make restoration slower because replay begins farther back. WAL generation also spikes during bulk loads or index operations. Forecast by actual bytes per day, not by row count alone, and do not delete archive segments until all dependent retained backups and target windows are accounted for.

What does a correct rehearsal look like? Create a test database, insert a known row, mark a restore point, mutate and delete that row, then restore to the earlier position. Verify that the row's earlier value is present, the later changes are absent, and the cluster starts cleanly. Also test a deliberately missing WAL segment and a missing key. It's better for those tests to fail in a lab than during an outage.

A candidate who knows only the words 'base backup plus WAL' gives the beginning of the answer. A strong answer identifies the exact recoverable history, handles the timeline branch, chooses whether to repair one table or replace a whole cluster, and protects valid work committed after the chosen point. A database restored perfectly to 15:06 can still be the wrong production database at 15:21.