Taking backups
A backup is a copy of the data from the past that you can go back to. A replica is not one: it repeats every change within moments, mistakes included. Here, a shop keeps its orders in PostgreSQL.
At 02:00 pg_basebackup copies the server’s data files while it keeps running. This base backup holds the orders as they were then: tea, mug and kettle. On its own it can only bring back 02:00.
During the day the orders change. PostgreSQL writes each change to its write-ahead log (WAL) before the table: 09:10 order 4, 11:30 order 5, 13:45 order 4 is sent. The WAL is a diary of every change.
With archive_mode on, PostgreSQL runs archive_command for each finished WAL file (16 MB by default) to copy it to the archive, another machine. Base backup plus archived WAL can rebuild any moment since 02:00.
A mistake at 14:05
At 14:05 someone runs DELETE FROM orders; and forgets the WHERE. All 5 orders are gone, and the delete is logged and archived like any other change. The 02:00 backup alone would lose the whole day.
Choose a target just before the mistake: 14:04:59 that day. PostgreSQL compares it with each transaction’s commit time. Everything committed by then is wanted; the 14:05 delete is not.
Recovering to a point in time
On a new server, copy in the base backup’s files, set restore_command and the target, and add an empty file called recovery.signal. When it starts, it holds the 02:00 orders and knows it must recover.
PostgreSQL fetches each archived WAL file with restore_command and replays its records in order, as they first happened: order 4 at 09:10, order 5 at 11:30, order 4 sent at 13:45.
The next commit, at 14:05, comes after the target, so recovery stops before it. The delete is never replayed. All 5 orders are back, as they were at 14:04:59.
By default recovery pauses at the target, so you can check the data with read-only queries. Then SELECT pg_wal_replay_resume(); ends recovery: the server starts timeline 2, a new branch of history, and takes writes.
You can reach any moment from the oldest base backup to the newest archived WAL file. A gap in the WAL ends the chain there. Take base backups often, keep every WAL file since, and practise restores.
What happens
;; step 1/11 · why
backup a copy of the data from the past
that you can go back to
replica a second server that repeats every change
as it arrives: a mistake reaches it too
table orders
column id type integer
column item type text
column status type text
pg_dump writes a database out as SQL: a snapshot
of one moment. To reach ANY moment, PostgreSQL
needs two pieces: a base backup and the WAL.
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. Real WAL file names, commit times and backup paths will differ. The times here are rounded so the story is easy to follow.