Backups and point-in-time recovery
A replica copies mistakes as fast as it copies everything else, so it is not a backup. Follow one shop's day in PostgreSQL: a base backup at 02:00, every change written to the write-ahead log (WAL) and archived, a DELETE with no WHERE at 14:05, and point-in-time recovery that replays the WAL and stops at 14:04:59, just before the mistake.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01At 14:05 someone runs DELETE FROM orders; on the primary. What happens on a normal streaming replica?
A replica repeats every change it receives, mistakes included. That is why replication is not a backup. (A standby can be told to lag on purpose with recovery_min_apply_delay, but it still keeps only one copy, close to now.)
02Which two pieces does PostgreSQL need to bring a database back to any moment since the backup?
Point-in-time recovery restores a base backup (for example from pg_basebackup), then replays the archived write-ahead log up to the target. pg_dump output cannot be combined with the WAL.
03The WAL holds changes committed at 09:10, 11:30, 13:45, 14:05. With recovery_target_time set to 14:04:59 that day, which changes are replayed?
Replay runs in commit order and stops at the first transaction that commits after the target. The 14:05 delete is never replayed, so all 5 orders come back.
04What tells a PostgreSQL server, when it starts, that it must run a targeted archive recovery?
recovery.signal starts archive recovery; restore_command fetches each archived WAL file and recovery_target_time says where to stop. archive_mode is the setting on the live server that archives the WAL in the first place.
05Recovery has paused at the target and the 5 orders look right. What does SELECT pg_wal_replay_resume(); do?
With the default recovery_target_action = pause, resuming ends recovery. The server starts timeline 2, so its new WAL files never overwrite the old history.
Glossary
- Backup
- A copy of the data from the past that you can go back to.
- Base backup
- A copy of all of a server's data files, taken while it runs, for example with pg_basebackup. Recovery starts from it.
- Write-ahead log (WAL)
- The log where PostgreSQL records every change before it changes the table files. Replaying it repeats the changes in order.
- WAL archive
- Somewhere safe, usually another machine, where archive_command copies each finished WAL file (16 MB by default).
- Point-in-time recovery (PITR)
- Restoring a base backup and replaying the archived WAL up to a chosen moment, such as just before a mistake.
- Recovery target
- Where replay stops, for example recovery_target_time = '2026-03-02 14:04:59+00'. It is compared with each transaction's commit time.
- pg_dump
- A tool that writes a database out as SQL (or an archive file): a consistent snapshot of one moment, portable but not usable for point-in-time recovery.
- Timeline
- A numbered branch of WAL history. Ending a recovery starts a new timeline so new changes never mix with the old ones.
Notes and sources
- The WAL does not store SQL text: it stores low-level changes to table and index pages. The figure writes each record as the statement that made it, so you can read it.
- A standby can be told to lag on purpose (recovery_min_apply_delay), which gives a window to stop it before a mistake arrives. It still is not a backup: it keeps only one copy, close to now.
- A base backup copies the whole database cluster, not one table, and its files change while it is taken. pg_basebackup also saves the WAL written during the copy (-X stream, the default), which makes the copy consistent.
- recovery_target_inclusive is on by default, so a transaction that commits exactly at the target is still replayed. The target is compared with commit times only, so a change is replayed whole or not at all.
- Real WAL file names, commit times and backup paths will differ. The times here are rounded so the story is easy to follow.
- pg_dump is still useful: it is small, portable between PostgreSQL versions, and can restore a single table. It cannot be combined with the WAL for point-in-time recovery.
- The Python sketch only imitates replay with a list of statements. It prints the 5 restored orders, one per line.
- PostgreSQL documentation: Backup and Restore, SQL Dump
- PostgreSQL documentation: Continuous Archiving and Point-in-Time Recovery (PITR)
- PostgreSQL documentation: pg_basebackup
- PostgreSQL documentation: Write Ahead Log settings (archiving, recovery, recovery target)
- PostgreSQL documentation: System Administration Functions (recovery control)
- PostgreSQL documentation: Write-Ahead Logging (WAL)
- PostgreSQL documentation: Replication settings (recovery_min_apply_delay)
- Python Library Reference: sqlite3
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapter 19: Recovery System
Quiz Unlocks after the last step.