What is it? #
This is a short lesson tying together the previous six, because the relationship between backups and WAL is the thing that makes recovery strategy make sense.
A backup is a snapshot: the database as it was at one specific moment.
The WAL is a change log: every modification made after that moment.
Neither is sufficient alone. A backup by itself limits you to the moments you happened to take backups. WAL by itself is a list of changes with nothing to apply them to.
Together they give you something much better than either: the ability to reconstruct the database as it was at any moment covered by the archive.
The decision you are really making is how much data you can afford to lose, and everything else follows from that number.
Think of it like this #
A photograph and a diary.
The photograph shows the room exactly as it was on Monday morning. Useful, and completely fixed — it can only ever tell you about Monday morning.
The diary records every change since: what was moved, added, removed, and when.
The photograph alone means going back to Monday and losing the week. The diary alone describes changes to a room you have no picture of. Together, you can set up the room from the photograph and follow the diary forward, stopping at any moment you choose — including one minute before the thing you regret.
Simple example #
Two setups, same incident: data deleted at 14:35 on Wednesday.
Nightly backups only. Restore Tuesday 02:00. Everything from Tuesday morning to Wednesday afternoon is gone — about 36 hours of orders.
Weekly base backup plus WAL archiving. Restore Sunday's base backup, replay WAL to 14:34. A few seconds lost.
Same incident, dramatically different outcome, decided entirely by whether WAL was being archived.
Code #
---------- What each one is ----------
BACKUP WAL
"the state at a point in time" "every change after that point"
Monday 02:00 Monday 02:00 -> now
┌──────────────┐ ─┬──┬──┬──┬──┬──┬──┬──┬──▶
│ SNAPSHOT │ │ │ │ │ │ │ │ │
│ of the DB │ every committed change,
└──────────────┘ in order, with a timestamp
Restore gets you EXACTLY Replay moves you FORWARD from
Monday 02:00. Nothing else. the snapshot to ANY later moment.---------- Recovery windows compared ----------
STRATEGY 1: nightly pg_dump only
02:00 backup ────────────────────────── 02:00 backup
▲ ▲
you can recover to EITHER of these, and nothing between.
Worst case data loss: ~24 hours.
Simple, cheap, fine for many small systems. Be honest that
this is the actual exposure.
STRATEGY 2: nightly pg_dump + WAL archiving
!! A logical dump CANNOT be rolled forward with WAL.
pg_dump produces SQL, not a physical starting point.
This combination does NOT give you PITR.
This is a genuinely common misunderstanding.
STRATEGY 3: base backup + WAL archiving <- this is what gives PITR
base backup ══════════ WAL ══════════════════════▶ now
▲ ▲ ▲ ▲ ▲ ▲
└── recover to ANY of these points ───┘
Worst case data loss: bounded by archive_timeout (e.g. 5 minutes).
STRATEGY 4: base backup + WAL archiving + streaming replica
Adds fast failover for hardware failure.
Does NOT replace backups: the replica copies DROP TABLE too.---------- What each protects against ----------
Backup Backup+WAL Replica
Server hardware dies yes yes yes
Disk corruption yes yes yes
Accidental DELETE / DROP yes* yes NO
Bad migration yes* yes NO
Ransomware (if offsite/immutable) yes yes NO
Recover to an arbitrary moment NO yes NO
Near-instant failover NO NO yes
* only back to the last backup — losing everything since.
A REPLICA IS NOT A BACKUP. It applies destructive statements
within seconds, faithfully. You need both.---------- Choosing, by the only question that matters ----------
"How much data can we afford to lose?"
A day -> nightly logical dumps. Simple and sufficient.
Small apps, internal tools, anything reconstructible.
An hour -> more frequent dumps, or base backup + WAL.
Minutes -> base backup + WAL archiving. There is no other option.
archive_timeout caps the worst case.
Near zero -> base backup + WAL + synchronous replication.
Note: synchronous replication makes every COMMIT wait
for the standby, which costs write latency. That is a
real trade, not a free upgrade.
Ask the people who own the risk, get a number, THEN design for it.# ---------- A common, sensible production combination ----------
# 1. Weekly base backup (physical, restorable + roll-forward capable)
pg_basebackup -D /backup/base_$(date +%F) -Ft -z -Xs -P
# 2. Continuous WAL archiving (the roll-forward capability)
# archive_mode = on
# archive_command = 'aws s3 cp %p s3://wal-archive/%f --only-show-errors'
# archive_timeout = 300 # caps data loss at 5 minutes
# 3. Nightly logical dump (portable, selective restore, cross-version)
pg_dump -F c -d shop -f /backup/shop_$(date +%F).dump
pg_dumpall --globals-only -f /backup/globals_$(date +%F).sql
# 4. A streaming replica (fast failover — NOT a backup)
# Each layer covers what the others do not:
# base + WAL -> recover to any moment
# logical -> restore one table, or move to a new major version
# replica -> survive losing the server without a long restore-- ---------- Knowing your actual recovery window ----------
-- How far back does the archive reach?
-- ls /archive/ | sort | head -1
-- Is archiving healthy right now?
SELECT last_archived_time,
now() - last_archived_time AS since_last_archive,
failed_count
FROM pg_stat_archiver;
-- since_last_archive much larger than archive_timeout -> something is wrong,
-- and your real recovery point is older than you think.
-- The honest questions to be able to answer at any time:
-- 1. What is the oldest point we can recover to?
-- 2. What is the NEWEST point we can recover to? <- people forget this one
-- 3. How long would a full recovery actually take?
-- 4. When did we last prove all of this by doing it?How it works #
The complementary relationship is straightforward: a backup gives you a starting state, and WAL moves you forward from it. Neither capability substitutes for the other.
The detail that catches people is which kind of backup can be rolled forward. A logical dump from pg_dump is a set of SQL statements describing the data — it is not a physical database state, so WAL cannot be applied to it. Only a physical base backup can serve as the starting point for WAL replay. Nightly pg_dump plus WAL archiving sounds like it should give point-in-time recovery, and it does not.
archive_timeout sets your worst-case data loss on an otherwise healthy system. Without it, a segment is only archived when it fills, so a quiet database might leave hours unarchived. With it set to five minutes, the most you can lose is roughly five minutes of changes.
Base backup frequency drives recovery time, not recovery point. The recovery point is determined by WAL coverage; how long recovery takes depends on how much WAL must be replayed. A base backup from a month ago is still a valid starting point, and replaying a month of WAL may take many hours.
Replication solves a different problem entirely. A standby applies changes within seconds, which is exactly what you want when a server dies and exactly what you do not want when someone runs DROP TABLE. It addresses availability, not mistakes. Systems that need both availability and recoverability need both mechanisms, and treating one as the other is a genuinely dangerous assumption.
Each layer in the combined strategy covers a distinct failure. Base backup plus WAL handles "recover to a moment". Logical dumps handle "restore one table" and "move to a different major version". A replica handles "the server is gone and we cannot wait for a restore".
Real-world use #
Start from the number. Ask whoever owns the business risk how much data may be lost and how long recovery may take, get explicit answers, and design to them. Strategies built without those numbers are either more expensive than necessary or quietly inadequate, and nobody discovers which until an incident.
Know your newest recoverable point, not just your oldest. People check that backups exist and forget to check that archiving is still working. A base backup from last week plus an archive that silently stopped on Tuesday gives you a Tuesday recovery point — not the five minutes everyone assumes. Monitoring pg_stat_archiver is what keeps that honest.
Layer deliberately rather than by accident. The combination above — weekly base backup, continuous WAL archiving, nightly logical dump, and a replica — is common because each piece answers a question the others cannot. Understanding which question each one answers is what lets you drop the ones you do not need.
Keep saying out loud that replication is not a backup. It is the most frequently repeated misunderstanding in database operations, and the one with the worst consequences, because it is only discovered at the exact moment a backup was needed.
And the practice that validates all of it remains the same as in the backup lesson: recover something, on a schedule, and record that it worked. A strategy diagram is not evidence. A completed restore is.
Common mistakes #
- Believing pg_dump plus WAL archiving gives point-in-time recovery — only a physical base backup can roll forward.
- Treating a replica as a backup, when it applies DROP TABLE within seconds.
- Knowing the oldest recoverable point but never checking the newest, while archiving has silently stopped.
- Choosing a strategy without agreeing an RPO and RTO with whoever owns the risk.
- Designing a layered strategy and never testing whether any layer actually restores.
Practice #
Write down your current answers to the four questions in the code block: the oldest point you could recover to, the newest, how long a full recovery would take, and when you last proved it. If you do not have a real system to answer for, do it for a test server you set up during this track. Then identify which single change would most improve the weakest answer — for most people it is enabling WAL archiving, or scheduling a restore test.