Postgres Backups Under the Hood
pg_dump, filesystem backups, and continuous WAL archiving — and why the cleverest part of Postgres' backup system is that a backup doesn't have to be a perfect snapshot.
There are three main ways to back up a Postgres database:
- Logical backups with
pg_dump - Filesystem backups
- Continuous archiving with WAL
The interesting part is how these approaches differ once the database becomes large.
pg_dump is basically instructions to rebuild the database
pg_dump takes a consistent snapshot of the database and serializes the schema and rows into a dump.
A simplified version looks something like:
CREATE TABLE users (...);
INSERT INTO users VALUES (1, 'ada');
INSERT INTO users VALUES (2, 'lin');
INSERT INTO users VALUES (3, 'mia');
So the dump isn't really a copy of the database's physical state. It's more like a set of instructions for reconstructing it.
That makes it useful for migrations and relatively small databases, but it becomes expensive at large scale. A multi-terabyte database means reading, serializing, formatting and potentially compressing huge amounts of data while competing with the actual workload for CPU and I/O.
There is also a less obvious problem with pg_dump.
The transaction horizon problem
Postgres uses MVCC, so pg_dump needs to maintain a consistent view of the database while it runs.
It does this by holding a transaction snapshot for the duration of the dump.
Every transaction in Postgres gets an XID, and tuples contain information about which transaction created or modified them. Since XIDs are 32-bit, they eventually wrap around. Postgres therefore has to periodically freeze old tuples so those XIDs can safely be reused.
Normally, autovacuum moves this process forward.
But a long-running snapshot can pin the transaction horizon.
So imagine:
pg_dump starts
│
▼
transaction snapshot stays open
│
▼
transaction horizon cannot move forward
│
▼
autovacuum can't freeze old tuples past it
│
▼
billions of new transactions happen
│
▼
transaction wraparound risk
If enough transactions happen while that old snapshot is still open, Postgres can eventually refuse new transactions that require XIDs and move into a protective read-only state.
So pg_dump isn't bad. It's just a very different tool from what you want for large-scale scheduled disaster-recovery backups.
Filesystem backups are much faster
The obvious alternative is to skip SQL completely and copy the actual files that Postgres stores on disk.
This is substantially faster because you're copying the physical representation directly.
But there's a problem.
If Postgres is running while you're copying those files, the database can change halfway through the backup.
For example:
Backup starts
│
├── copy page A
│
├── UPDATE users ...
│
├── copy page B
│
└── finish
Now page A might represent the old state while page B represents the new state.
Worse, you could theoretically copy a page while it is being modified and end up with corrupted data.
The traditional solution is to shut Postgres down while copying the filesystem.
Obviously, that isn't particularly useful for a production database that is supposed to stay online.
This is where WAL becomes important.
WAL is the database's history
Postgres uses a Write-Ahead Log (WAL).
Before making changes to the actual data files, Postgres records those changes in the WAL.
So instead of thinking about the database as just:
database files
it's more useful to think:
database files + history of changes
That history is continuously written into WAL segments.
The WAL contains information about things like:
- which relation was modified
- which page was modified
- what operation happened
- where the operation exists in the WAL stream
That last part is represented using an LSN — Log Sequence Number.
You can think of an LSN as a position in the database's history.
WAL
──────────────────────────────────────────────►
LSN 100 LSN 200 LSN 300
│ │ │
│ │ │
update insert delete
And this gives us a way to precisely describe where a backup starts and ends.
Continuous archiving
Continuous archiving combines:
Filesystem backup
+
Continuous WAL archiving
The WAL can be continuously streamed to external storage while the database keeps serving traffic.
This means the filesystem backup itself doesn't need to be perfectly consistent.
This sounds wrong at first.
But that's actually the clever part.
The "smeared" backup
Suppose we're copying the database files and an update happens in the middle:
UPDATE users
SET email_confirmed = true
WHERE id = 314159;
There are several things that could happen.
The relevant page might have already been copied.
Or it might not have been copied yet.
Or the page could be copied at exactly the wrong moment and end up corrupted.
So the filesystem backup can essentially be a smeared version of the database.
And that's okay.
Because we also have the WAL.
Full-page writes
This is where full_page_writes becomes important.
After a checkpoint, when a page is modified for the first time, Postgres can write the entire page into the WAL.
A Postgres page is usually 8KB.
So instead of only recording:
"change this part of page 42"
the WAL can contain:
"here is the complete version of page 42"
That full-page image gives recovery enough information to reconstruct a page even if the corresponding page in the filesystem backup is inconsistent or corrupted.
The whole mechanism starts making sense when you put it together:
WAL
│
│ continuous
▼
Database ───────► S3
│
│ filesystem copy
▼
Smeared backup
The backup records two important LSNs:
start_lsn ───────────────────── end_lsn
│ │
backup begins backup finishes
During restoration, Postgres replays the WAL from start_lsn through end_lsn.
So even if the copied files were inconsistent, WAL replay can bring them to a consistent state.
Importantly, the restored database is effectively consistent at the end of the backup, not necessarily at its start.
This is what enables PITR
Once WAL is continuously archived, something much more useful becomes possible:
Point-in-Time Recovery (PITR).
Suppose the latest filesystem backup finished at:
12:00
But the database crashed at:
18:37
We don't need another full filesystem backup from 18:37.
We can:
Restore base backup
│
▼
Replay WAL
│
├── 12:01
├── 13:00
├── 14:00
├── ...
├── 18:36
└── 18:37
│
▼
Desired database state
Because the WAL contains the history after the base backup, we can replay it until an arbitrary point in time.
That's PITR.
The tradeoff is that replaying a huge amount of WAL is expensive. So backups need to happen frequently enough that a restore doesn't require replaying days of WAL.
PlanetScale's setup takes backups every 12 hours by default for this reason.
The really interesting part: backups and resizing use the same machinery
The same mechanism can also be used when resizing a Postgres cluster.
The process is roughly:
New node
│
▼
Restore latest filesystem backup
│
▼
Replay WAL
│
▼
Catch up with primary
│
▼
Stream remaining changes
│
▼
Cut over
And there's a nice recursive idea here.
The backup process itself can use a throwaway node:
Latest backup
│
▼
Throwaway node
│
▼
Replay WAL until caught up
│
▼
Create new filesystem backup
So the primary doesn't have to spend all its CPU, I/O and network bandwidth creating the backup.
The same primitives are being reused for backup creation, restoration, replication catch-up and cluster resizing.
The mental model
The easiest way I think about the whole system is:
Continuous WAL
│
▼
┌──────────────┐
│ Object Store │
└──────────────┘
▲
│
WAL replay
│
│
┌──────────┐ ┌───────────┐
│ Postgres │───►│ Base │
│ Primary │ │ Backup │
└──────────┘ └───────────┘
│
▼
Restored database
A base backup gives you a starting point.
WAL gives you the history after that starting point.
PITR is basically choosing how far through that history you want to replay.
So a backup doesn't necessarily have to be a perfect snapshot of the database.
It can be:
a physical starting point + enough history to reconstruct the state you need.
And that's the part that makes Postgres' backup system much more interesting than just pg_dump > backup.sql.