How to Recover PostgreSQL 18 to the Moment Before a Bad DELETE: WAL Archiving, pg_basebackup, Point-in-Time Recovery, and a Restore Drill You Can Test
Turn on WAL archiving in PostgreSQL 18, take a base backup, find the exact moment of a bad DELETE with pg_waldump, restore a copy to just before it, put the lost rows back, break recovery five ways...
Sooner or later every database gets a bad statement. Someone runs a cleanup without its WHERE clause, a migration drops the wrong table, or a script points at production instead of staging. The question is then not whether you have a backup, but how much of the last few hours you can get back. A nightly dump returns last night’s data and nothing newer. Point-in-time recovery, usually shortened to PITR, returns the database as it was at a moment you choose, such as “a fraction of a second before the DELETE.”
Table Of Content
- How point-in-time recovery works, in plain words
- Prerequisites
- Step 1: Create a safe archive helper
- Step 2: Create a lab cluster with archiving switched on
- Step 3: Load data and prove that archiving works
- Step 4: Take a base backup
- Step 5: A busy morning, and a bad DELETE
- Step 6: Find the moment in the WAL
- Step 7: Restore a copy to the moment before the mistake
- Copy the backup and describe where to stop
- Start the server and wait for the pause
- Check the data, then promote
- Step 8: Choose the right kind of recovery target
- Step 9: Put the lost rows back and keep the new ones
- Step 10: Break it on purpose
- Forgetting recovery.signal
- Choosing a target older than the backup
- Choosing a target after the end of the archive
- A segment missing from the middle of the archive
- An archive command that keeps failing
- Step 11: Plan restore time and archive size
- Step 12: Turn the checklist into a restore drill
- Confirm that the whole thing works end to end
- Where to go next
- Sources
In this tutorial you will build that ability on a throwaway PostgreSQL 18 server and prove that it works. You will turn on WAL archiving, take a base backup, break the database on purpose, find the exact moment of the mistake in the write-ahead log, restore a separate copy to the moment before it, and put the lost rows back into the running server without losing the orders that arrived afterwards. Then you will cause five realistic recovery failures so you recognise them when they happen, and finish with a restore drill script and a test suite, because a backup that has never been restored is only a hope.
How point-in-time recovery works, in plain words
Six terms carry the whole idea, so here they are before the first command.
- The write-ahead log (WAL) is the journal PostgreSQL writes every change to before it touches the data files. It lives in the
pg_walfolder as files of 16 MiB (by default) called WAL segments. Every position in the log has an address called an LSN, which looks like0/158D7198. - A base backup is a copy of the whole data directory taken while the server keeps running. On its own it is not consistent, because files change while they are being copied. It becomes consistent when the WAL written during the backup is replayed on top of it, which is why a small file called
backup_labelrecords where that replay must start. - The WAL archive is a folder (or any storage) that receives a copy of every finished WAL segment. PostgreSQL hands each finished segment to a command you configure, and it only recycles the segment once that command reports success.
- Recovery means starting a copy of a base backup in a special mode in which the server fetches archived segments one after another and replays them.
- A recovery target tells the server where to stop replaying: a clock time, a named marker, an LSN, or a transaction ID.
- A timeline is one branch of the database’s history. When recovery stops at a target and the server starts accepting writes, it begins a new timeline, so WAL from the old history and from the new one can never be confused.
A useful picture is a film. The base backup is one photograph of the set, the archive holds every frame shot after that photograph, and recovery plays the film forward from the photograph until the frame you asked for. Three things must be true for that to work: the archive holds every segment from the backup onward with no gap, the backup is older than the moment you want, and the server can fetch each segment when it asks. Most of this tutorial is about checking those three things.
Prerequisites
- Operating system: I ran every PostgreSQL command on Windows 11 with the PostgreSQL 18.6 binaries and the Python 3.13.14 that this lab uses. The commands are written for a POSIX shell (Git Bash on Windows). A few small steps in the transcripts, namely
ls,cp -r,touchandcat, were executed through equivalent Python calls because the lab driver is a Python script; the PostgreSQL tools were run with the options shown (a few through their short forms, such as-Dfor--pgdata). I did not run any of this on Linux or macOS, so treat paths and the Python command name (pythonhere, oftenpython3on Linux) as the parts to adapt. - PostgreSQL tools on your PATH:
initdb,pg_ctl,psql,pg_basebackup,pg_verifybackupandpg_waldump. Some packages keep the server tools outside the default PATH, so add their folder for this session if a command is not found. - Python: I ran 3.13.14. The helper scripts use only the standard library and, as far as I know, nothing newer than Python 3.8 (
Path.unlink(missing_ok=True)), but I did not test older versions. The tests needpytest; I used 9.1.1. - Disk and ports: about 1.5 GB free, because the lab archive grows past 350 MiB, and free TCP ports 55432, 55433, 55440 and 55450 to 55456 (the last range is for the tests).
- Knowledge: you can run SQL in
psqland use a shell. You do not need to know PostgreSQL internals.
Everything below happens in a scratch folder with its own throwaway server on its own port. Do not point these commands at a production data directory. If your real server was installed from a package, the service name, the data directory and the user that owns it will differ, and you would add the archive settings there instead of using initdb.
Step 1: Create a safe archive helper
PostgreSQL does not copy finished WAL segments by itself. You give it a command in the archive_command setting, and it runs that command once per segment. The contract is strict, and the PostgreSQL 18 documentation states it plainly: “It is important that the archive command return zero exit status if and only if it succeeds.” A zero tells the server the segment is safe, and the server may then delete or recycle it. A command that returns zero without having stored the file therefore loses WAL silently.
The same documentation asks for a second property: “Archive commands and libraries should generally be designed to refuse to overwrite any pre-existing archive file.” The reason is that two servers pointed at the same folder, or an administrator’s mistake, could otherwise replace good history with different history. There is one wrinkle. The documentation says: “In rare cases, PostgreSQL may attempt to re-archive a WAL file that was previously archived.” That can happen after a crash, so a good command accepts a byte-identical copy of a file that is already there and refuses only a different one.
The documentation’s own examples are one-liners. The Unix form is test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f and the Windows form is copy "%p" "C:\\server\\archivedir\\%f". I did not run either of these. The helper below does the same job in a way that behaves identically on every operating system, and it adds the details that matter: it writes to a temporary name and renames the file into place, so a half-copied file never looks like a finished segment, and it flushes the data to disk before it answers zero.
# walarch.py
"""walarch.py: a small, safe WAL archive helper for PostgreSQL 18 (tested on 18.6).
python walarch.py push "%p" "%f" ARCHIVE_DIR # use as archive_command
python walarch.py fetch "%f" "%p" ARCHIVE_DIR # use as restore_command
push copies the segment to a temporary name, flushes it to disk, renames it into place and (on Linux and macOS) flushes
the folder, so a half-copied file never looks like a finished segment. It refuses to overwrite a file that has different content (exit 1),
but accepts a byte-identical re-send (exit 0), because PostgreSQL may send a segment again after a crash.
fetch exits 1 when the name is not in the archive, which is how PostgreSQL learns there is no more WAL.
"""
import filecmp
import os
import shutil
import sys
def push(src, name, archive):
if not os.path.isdir(archive):
print(f"walarch: archive folder {archive!r} does not exist", file=sys.stderr)
return 1
dest = os.path.join(archive, name)
if os.path.exists(dest):
if filecmp.cmp(src, dest, shallow=False):
return 0
print(f"walarch: {name} already exists with different content, refusing to overwrite", file=sys.stderr)
return 1
tmp = dest + ".tmp"
shutil.copyfile(src, tmp)
with open(tmp, "rb+") as f:
os.fsync(f.fileno())
os.replace(tmp, dest)
if os.name != "nt":
fd = os.open(archive, os.O_RDONLY)
try:
os.fsync(fd)
finally:
os.close(fd)
return 0
def fetch(name, dest, archive):
src = os.path.join(archive, name)
if not os.path.exists(src):
return 1
shutil.copyfile(src, dest)
return 0
def main(argv):
if len(argv) == 5 and argv[1] == "push":
return push(argv[2], argv[3], argv[4])
if len(argv) == 5 and argv[1] == "fetch":
return fetch(argv[2], argv[3], argv[4])
print('usage: walarch.py push "%p" "%f" ARCHIVE | walarch.py fetch "%f" "%p" ARCHIVE', file=sys.stderr)
return 2
if __name__ == "__main__":
sys.exit(main(sys.argv))
The script has two commands. push is for archive_command and fetch is for restore_command, which you will meet in Step 7. Try the four cases that matter, using two tiny files in place of real segments, so you see the exit codes before PostgreSQL depends on them.
$ printf 'first version of the segment' > demo/seg1
$ printf 'a different segment with the same name' > demo/seg1_changed
$ python walarch.py push demo/seg1 000000010000000000000001 demo_archive
$ echo $?
0
$ python walarch.py push demo/seg1 000000010000000000000001 demo_archive # the same bytes again
$ echo $?
0
$ python walarch.py push demo/seg1_changed 000000010000000000000001 demo_archive
walarch: 000000010000000000000001 already exists with different content, refusing to overwrite
$ echo $?
1
$ python walarch.py fetch 000000010000000000000009 demo/restored demo_archive
$ echo $?
1
The first push stores the file and returns 0. Pushing the same bytes again also returns 0, which is the crash-retry case. Pushing different bytes under the same name returns 1 and prints why, which is the safety rule. Fetching a name that is not in the archive returns 1, and that is exactly how PostgreSQL learns that there is no more WAL to read. If your outputs match, the helper is ready.
Step 2: Create a lab cluster with archiving switched on
Create a working folder, put walarch.py in it, and make two empty folders next to it: archive for finished segments and backups for base backups. Then create a fresh cluster in a folder called primary. A cluster in PostgreSQL is one data directory served by one server process, and initdb creates it.
mkdir archive backups
export PGHOST=127.0.0.1 PGUSER=postgres PGDATABASE=postgres
The second line sets three standard environment variables so that every psql command below connects to this machine as the postgres user without repeating those options. I use the numeric address 127.0.0.1 on purpose: on Windows, localhost can try IPv6 first, which makes each connection wait when the server listens on IPv4 only.
$ initdb --pgdata=primary --username=postgres --auth=trust --encoding=UTF8 --locale=C
The database cluster will be initialized with locale "C".
creating directory primary ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
--auth=trust lets local connections in without a password, which is acceptable for a throwaway lab on your own machine and wrong for anything shared. Now append the archive settings and start the server.
$ cat >> primary/postgresql.conf <<'EOF'
port = 55432
archive_mode = on
archive_command = 'python ../walarch.py push "%p" "%f" ../archive'
archive_timeout = 60
EOF
$ pg_ctl --pgdata=primary --log=primary.log start
waiting for server to start.... done
server started
$ psql -p 55432 -c "SHOW archive_mode;"
archive_mode
--------------
on
(1 row)
$ psql -p 55432 -c "SHOW archive_command;"
archive_command
------------------------------------------------
python ../walarch.py push "%p" "%f" ../archive
(1 row)
$ psql -p 55432 -c "SELECT version();"
version
-------------------------------------------------------------------------
PostgreSQL 18.6
(1 row)
Each setting has a job. port = 55432 keeps this server away from any PostgreSQL you already run. archive_mode = on turns archiving on, and PostgreSQL only reads it at server start, which is why it is set before the first start. In archive_command, %p is replaced by the path of the segment to archive and %f by its file name only. The documentation adds that the path is “relative to the current working directory, i.e., the cluster’s data directory”, so the ../walarch.py and ../archive in the lab are the folders next to primary. In production you would use an absolute path on a different disk or machine than the data directory, because an archive on the same disk as the database protects you from nothing the disk does to both.
archive_timeout = 60 forces a switch to a new segment at least once a minute while there is activity. Segments are only archived once they are finished, so on a quiet server the newest changes would otherwise wait in an unfinished 16 MiB file. The documentation’s advice is that “archive_timeout settings of a minute or so are usually reasonable”, because a segment that is archived early is still a full 16 MiB file. The last block of output also confirms the server version, and that SHOW reports the settings the server actually uses.
Step 3: Load data and prove that archiving works
Create an orders table and load 100,000 rows. The table is the data you will later destroy and recover.
$ psql -p 55432 -c "CREATE TABLE orders (id bigserial PRIMARY KEY, customer text NOT NULL, status text NOT NULL, total numeric(10,2) NOT NULL, created_at timestamptz NOT NULL DEFAULT now());"
CREATE TABLE
$ psql -p 55432 -c "INSERT INTO orders (customer, status, total) SELECT 'customer-' || (g % 500), CASE WHEN g % 10 = 0 THEN 'pending' ELSE 'paid' END, (g % 97) + 0.5 FROM generate_series(1, 100000) AS g;"
INSERT 0 100000
$ psql -p 55432 -c "SELECT count(*), max(id) FROM orders;"
count | max
--------+--------
100000 | 100000
(1 row)
That one load wrote 16.4 MiB of WAL, measured by reading the server’s WAL position before and after. Now force a segment switch and ask PostgreSQL whether the archiver did its job. pg_switch_wal() closes the current segment immediately, and pg_stat_archiver is the view that counts archive successes and failures.
$ psql -p 55432 -c "SELECT pg_switch_wal();"
pg_switch_wal
---------------
0/289CE50
(1 row)
$ psql -p 55432 -x -c "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;"
-[ RECORD 1 ]-----+-------------------------
archived_count | 2
failed_count | 0
last_archived_wal | 000000010000000000000002
$ ls archive
000000010000000000000001
000000010000000000000002
Read the three numbers: archived_count says how many files were stored, failed_count must stay at 0, and last_archived_wal names the newest one. The file name has 24 hexadecimal digits: the first eight are the timeline, and the rest encode the position in the log. Names sort in WAL order, which is what makes replay by name possible. Do not move on until failed_count is 0 and the folder has files in it, because everything later depends on this working.
Step 4: Take a base backup
A base backup is the photograph in the film picture, and pg_basebackup takes it over a normal replication connection while the server keeps serving queries.
$ pg_basebackup -p 55432 --pgdata=backups/base1 --format=plain --wal-method=stream --checkpoint=fast --verbose
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2456"
pg_basebackup: write-ahead log end point: 0/3000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
$ pg_verifybackup backups/base1
backup successfully verified
$ ls backups/base1
PG_VERSION backup_label backup_manifest base global pg_commit_ts pg_dynshmem pg_hba.conf pg_ident.conf pg_logical pg_multixact pg_notify pg_replslot pg_serial pg_snapshots pg_stat pg_stat_tmp pg_subtrans pg_tblspc pg_twophase pg_wal pg_xact postgresql.auto.conf postgresql.conf
$ cat backups/base1/backup_label
START WAL LOCATION: 0/3000028 (file 000000010000000000000003)
CHECKPOINT LOCATION: 0/3000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-11-18 15:32:24 EST
LABEL: pg_basebackup base backup
START TIMELINE: 1
The options matter. --format=plain writes ordinary files, so the backup is a folder you can start a server from. --wal-method=stream streams the WAL generated during the backup into the backup itself, which makes the backup consistent on its own. --checkpoint=fast asks for an immediate checkpoint instead of the default spread-out one, so the backup starts without waiting. --verbose prints the start and end positions: the backup began at 0/3000028 and ended at 0/3000120, and the replay during recovery must cover that range.
One option to avoid for this purpose is -R. The documentation says it “Creates a standby.signal file and appends connection settings to the postgresql.auto.conf file in the target directory”, which turns the copy into a replica that follows another server. That is what you want for building a standby and not what you want for point-in-time recovery.
The backup took 2.2 seconds and is 49.1 MiB, including the 16 MiB WAL segment it carries. pg_verifybackup checks every file against the backup_manifest that pg_basebackup wrote, so you learn about a damaged copy now and not during an emergency. The backup_label file is what tells a restored server that it is a backup and where its replay must begin.
The archive received one more file because of the backup, a small history file that records its start and stop positions. Looking at the start of the archive you can see it, and you can see that every segment is exactly 16 MiB:
$ cd archive && stat -c '%s %n' * | head -n 5
16777216 000000010000000000000001
16777216 000000010000000000000002
16777216 000000010000000000000003
338 000000010000000000000003.00000028.backup
16777216 000000010000000000000004
Step 5: A busy morning, and a bad DELETE
The backup is a few seconds old. Now simulate normal life after it: 150,000 more orders and four rounds of price updates, enough to make the archive grow by several segments. After the busy period, note three ways of naming “the last good moment”: a clock time in UTC, a restore point that you create on purpose, and the current WAL position. You rarely get to prepare these before a real accident, so Step 6 shows how to find them afterwards, but it is worth seeing all three.
$ psql -p 55432 -c "INSERT INTO orders (customer, status, total) SELECT 'customer-' || (g % 500), CASE WHEN g % 10 = 0 THEN 'pending' ELSE 'paid' END, (g % 97) + 0.5 FROM generate_series(100001, 250000) AS g;"
INSERT 0 150000
$ psql -p 55432 -c "UPDATE orders SET total = total + 1;" # run four times
UPDATE 250000
$ psql -p 55432 -c "SELECT count(*), max(total) FROM orders;"
count | max
--------+--------
250000 | 100.50
(1 row)
The inserts wrote 24.7 MiB of WAL and each of the four UPDATE statements wrote about 61 to 71 MiB, because an update writes a new version of every row it touches. The first update was the largest at 71.0 MiB, probably because the first change to a page after a checkpoint also writes a full copy of that page (PostgreSQL’s full-page writes); I did not break the number down. Now the accident.
$ psql -p 55432 -Atc "SELECT to_char(clock_timestamp() AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.US') || '+00'"
2026-11-18 20:32:32.274606+00
$ psql -p 55432 -c "SELECT pg_create_restore_point('before_cleanup');"
pg_create_restore_point
-------------------------
0/158D7198
(1 row)
$ psql -p 55432 -Atc "SELECT pg_current_wal_insert_lsn()"
0/158D7198
$ psql -p 55432 -c "SELECT count(*) FROM orders;"
count
--------
250000
(1 row)
$ psql -p 55432 -c "DELETE FROM orders;"
DELETE 250000
$ psql -p 55432 -c "INSERT INTO orders (customer, status, total) SELECT 'late-' || g, 'paid', 9.99 FROM generate_series(1, 50) AS g;"
INSERT 0 50
$ psql -p 55432 -c "SELECT count(*), max(id) FROM orders;"
count | max
-------+--------
50 | 250050
(1 row)
The cleanup statement DELETE FROM orders; had no WHERE clause and removed all 250,000 rows in 0.22 seconds. Orders kept arriving, and the 50 “late” orders now sit in the table. PostgreSQL did exactly what it was told, and nothing in the database says that anything is wrong. Finish by switching to a new segment so the segment holding the accident reaches the archive.
$ psql -p 55432 -c "SELECT pg_switch_wal();"
pg_switch_wal
---------------
0/166D7D18
(1 row)
$ psql -p 55432 -x -c "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;"
-[ RECORD 1 ]-----+-------------------------
archived_count | 23
failed_count | 0
last_archived_wal | 000000010000000000000016
$ ls archive | wc -l
23
$ ls archive | tail -n 3
000000010000000000000014
000000010000000000000015
000000010000000000000016
The archive now holds 23 files: 22 segments of 16 MiB and the small backup history file, 352 MiB in total. The segments contain everything since the base backup, including the delete.
Step 6: Find the moment in the WAL
In a real incident you would not have a timestamp copied from a terminal. You would have a colleague saying that orders vanished a little after lunch. The WAL is the evidence, and pg_waldump reads it. Start at a position shortly before the problem, filter to the Heap records that change table rows, and look at the first three.
$ pg_waldump --path=archive --start=0/158D7198 --rmgr=Heap --limit=3
rmgr: Heap len (rec/tot): 54/ 54, tx: 759, lsn: 0/158D7198, prev 0/158D7130, desc: DELETE xmax: 759, off: 156, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/16385 blk 2290
rmgr: Heap len (rec/tot): 54/ 54, tx: 759, lsn: 0/158D71D0, prev 0/158D7198, desc: DELETE xmax: 759, off: 157, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/16385 blk 2290
rmgr: Heap len (rec/tot): 54/ 54, tx: 759, lsn: 0/158D7208, prev 0/158D71D0, desc: DELETE xmax: 759, off: 158, infobits: [KEYS_UPDATED], flags: 0x00, blkref #0: rel 1663/5/16385 blk 2290
$ pg_waldump --path=archive --start=0/158D7198 --end=0/166D7D18 --rmgr=Transaction --xid=759
rmgr: Transaction len (rec/tot): 34/ 34, tx: 759, lsn: 0/1663B1D8, prev 0/1663B1A0, desc: COMMIT 2026-11-18 15:32:32.706628 Eastern Standard Time
The first Heap record after the good position is a DELETE by transaction 759. The second command asks for the Transaction records of that one transaction and finds its COMMIT, with a timestamp (pg_waldump prints it in the local time zone of the machine; this one shows Eastern Standard Time). So the transaction ID and the commit time of the mistake are now known. The statistics mode shows the size of the damage:
$ pg_waldump --path=archive --start=0/158D7198 --end=0/166D7D18 --stats=record --rmgr=Heap
WAL statistics between 0/158D7198 and 0/1663D1B8:
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
Heap/INSERT 49 ( 0.02) 4655 ( 0.03) 0 ( 0.00) 4655 ( 0.03)
Heap/DELETE 250000 ( 99.98) 13500000 ( 99.96) 0 ( 0.00) 13500000 ( 99.96)
Heap/INSERT+INIT 1 ( 0.00) 95 ( 0.00) 0 ( 0.00) 95 ( 0.00)
-------- -------- -------- --------
Total 250050 13504750 [100.00%] 0 [0.00%] 13504750 [100%]
Of the Heap records in that stretch of the log, 250,000 are DELETE records, which is 99.98 percent of them, and that is not an ordinary afternoon. The PostgreSQL documentation observes, in its passage on recovery targets, that “there are no tools to help you identify with any accuracy which transaction ID to use.” pg_waldump comes close to filling that gap: it reads the log files without starting a server, so you can use it on the archive while you are deciding what to restore.
Step 7: Restore a copy to the moment before the mistake
This is the main event. The most important rule is to restore into a new folder on a different port, and not over the damaged server. The damaged server still has the 50 late orders and might hold evidence, and you want the freedom to try again with a different target.
Copy the backup and describe where to stop
Copy the base backup to a new folder and append four settings to its postgresql.conf. Then create an empty file named recovery.signal. That file is what switches the server from “start normally” to “run recovery.”
$ cp -r backups/base1 restore1
$ cat >> restore1/postgresql.conf <<'EOF'
restore_command = 'python ../walarch.py fetch "%f" "%p" ../archive'
recovery_target_time = '2026-11-18 20:32:32.274606+00'
recovery_target_action = 'pause'
archive_mode = off
EOF
$ touch restore1/recovery.signal
$ pg_ctl --pgdata=restore1 --log=restore1.log --options="-p 55433" start
(server started)
Here is what each setting does:
restore_commandis how the server fetches archived segments.%fis the name it wants and%pis where to put the copy. The documentation’s rule for this command mirrors the archive side: “The command will be asked for file names that are not present in the archive; it must return nonzero when so asked.” Ourfetchdoes that.recovery_target_timeis the stopping time. The documentation says it cannot use a time zone abbreviation unless one is configured and that the “Preferred style is to use a numeric offset from UTC,” so the value ends in+00.recovery_target_action = 'pause'makes the server pause at the target and wait for you. In the documentation’s words, “The intended use of the pause setting is to allow queries to be executed against the database to check if this recovery target is the most desirable point for recovery.” This is the safe choice, because after the server is promoted you can no longer move the target forward.archive_mode = offin the restored copy stops it from pushing its own WAL into the real archive. A restored copy should never write into the archive it was restored from, and the tests in Step 12 enforce that.
Start the server and wait for the pause
The command line gives the copy its own port, so it does not collide with the damaged server. A successful start does not mean recovery has finished. Look at the order of the lines in the log below: the server reports “ready to accept read-only connections” as soon as it is consistent, which happens before replay reaches the target. Always wait for the pause state and not for the start command.
$ grep -E 'recovery|redo|ready|timeline|consistent' restore1.log
LOG: starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
LOG: redo starts at 0/3000028
LOG: completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
LOG: consistent recovery state reached at 0/3000120
LOG: database system is ready to accept read-only connections
LOG: recovery stopping before commit of transaction 759, time 2026-11-18 15:32:32.706628-05
LOG: pausing at the end of recovery
HINT: Execute pg_wal_replay_resume() to promote.
Each line has a meaning. starting backup recovery and redo starts at 0/3000028 show that replay began where the backup label said. consistent recovery state reached is the point from which the copy is usable. recovery stopping before commit of transaction 759 is the target at work: the target time lies before that commit, so the server stops just before it. The statement that deleted the rows was replayed up to its end, but its commit was never applied, so it does not exist in this copy. pausing at the end of recovery and the hint tell you what to do next.
Check the data, then promote
Because the copy is paused, it accepts read-only queries. Check it before you decide anything.
$ psql -p 55433 -c "SELECT pg_get_wal_replay_pause_state();"
pg_get_wal_replay_pause_state
-------------------------------
paused
(1 row)
$ psql -p 55433 -c "SELECT count(*), max(id) FROM orders;"
count | max
--------+--------
250000 | 250000
(1 row)
$ psql -p 55433 -c "SELECT pg_is_in_recovery();"
pg_is_in_recovery
-------------------
t
(1 row)
$ psql -p 55433 -c "SELECT pg_wal_replay_resume();"
pg_wal_replay_resume
----------------------
(1 row)
$ psql -p 55433 -c "SELECT pg_is_in_recovery();"
pg_is_in_recovery
-------------------
f
(1 row)
$ psql -p 55433 -c "SELECT timeline_id FROM pg_control_checkpoint();"
timeline_id
-------------
2
(1 row)
$ psql -p 55433 -c "INSERT INTO orders (customer, status, total) VALUES ('restored-1', 'paid', 1.00) RETURNING id;"
id
--------
250019
(1 row)
INSERT 0 1
The pause state is paused and the table has all 250,000 rows, the same count as just before the accident. The documentation says the paused state “can be resumed by using pg_wal_replay_resume()”, which then ends recovery. After that call pg_is_in_recovery() is f, the server accepts writes, and it has moved to timeline 2, which is the new branch of history. The test INSERT succeeded, so the restored copy is a normal read-write database.
One detail looks odd and is not a problem: the new row got id 250019, not 250001. PostgreSQL logs sequence values in advance, 32 at a time (the constant is SEQ_LOG_VALS in sequence.c), so after a recovery a sequence can skip some numbers. Gaps in generated IDs are normal.
Step 8: Choose the right kind of recovery target
The time target is the one people think of first, but PostgreSQL offers five ways of saying “stop here,” and each has a use. Set exactly one of them at a time: the documentation says that if more than one is specified in the configuration file, an error is raised. The lab restored the same base backup once per kind. In every case I counted the rows once the server had stopped replaying, and read the stop line from its log.
target result after the pause
time 250000 rows, max id 250000, max total 100.50
name 250000 rows, max id 250000, max total 100.50
lsn 250000 rows, max id 250000, max total 100.50
xid, exclusive 250000 rows, max id 250000, max total 100.50
xid, inclusive 0 rows, max id -, max total -
immediate 100000 rows, max id 100000, max total 96.50
time: LOG: recovery stopping before commit of transaction 759, time 2026-11-18 15:32:32.706628-05
name: LOG: recovery stopping at restore point "before_cleanup", time 2026-11-18 15:32:32.336173-05
lsn: LOG: recovery stopping after WAL location (LSN) "0/158D7198"
xid, exclusive: LOG: recovery stopping before commit of transaction 759, time 2026-11-18 15:32:32.706628-05
xid, inclusive: LOG: recovery stopping after commit of transaction 759, time 2026-11-18 15:32:32.706628-05
immediate: LOG: recovery stopping after reaching consistency
- Time (
recovery_target_time) is the natural choice when you know roughly when things went wrong. The server stops before the first commit that is later than the target, which is why it stopped “before commit of transaction 759.” - Name (
recovery_target_name) uses a marker you created withpg_create_restore_point(). It is the most precise option and requires foresight. A good habit is to create one before every risky migration. - LSN (
recovery_target_lsn) is exact, and you get the value frompg_current_wal_insert_lsn()or frompg_waldump. With the default inclusive setting the server replays the record that starts at that position and then stops; here that record was the firstDELETErecord of an uncommitted transaction, so the data matched the good state. - Transaction ID (
recovery_target_xid) names a transaction. The inclusive setting decides what “stop at transaction 759” means. Withrecovery_target_inclusive = offthe server stops before the commit and the table has 250,000 rows. With the default inclusive behaviour it stops after the commit, which applies the accident, and the table has 0 rows. The same ID with the wrong inclusive setting gives the opposite answer, so always check the data after the pause. - Immediate (
recovery_target = 'immediate') stops as soon as the copy is consistent. The result is the base backup itself, here 100,000 rows. It is useful to test that a backup is usable without replaying any archive.
Step 9: Put the lost rows back and keep the new ones
The restored copy has the 250,000 lost orders and none of the 50 late ones. The running server has the reverse. Pointing the application at the restored copy would lose the late orders; keeping the running server loses the old ones. The better move here is to take the lost rows from the copy and insert them into the running server. For one table, \copy is enough: it streams rows to a CSV file on your machine and back.
# restored copy from the time target is paused on port 55433, production is still running on 55432
$ psql -p 55432 -c "SELECT count(*), max(id) FROM orders;"
count | max
-------+--------
50 | 250050
(1 row)
$ psql -p 55433 -c "\copy orders TO 'orders_restored.csv' CSV"
COPY 250000
$ psql -p 55432 -c "\copy orders FROM 'orders_restored.csv' CSV"
COPY 250000
$ psql -p 55432 -c "SELECT count(*), max(id), count(*) FILTER (WHERE customer LIKE 'late-%') AS late_rows FROM orders;"
count | max | late_rows
--------+--------+-----------
250050 | 250050 | 50
(1 row)
The CSV holds 14.4 MiB. After the second \copy the production table has 250,050 rows, and the 50 late rows are still there. The restored IDs (1 to 250000) and the late IDs (250001 to 250050) did not overlap, which is what made this simple. If they overlapped you would load into a staging table first and merge with INSERT ... ON CONFLICT, and for several tables or for ready-made restore scripts pg_dump on the restored copy is the usual tool. It was not part of the small binary set I used, so this lab used \copy.
When the restored copy has served its purpose, stop it and delete its folder. A forgotten second server is a quiet way to fill a disk.
Step 10: Break it on purpose
The recovery that worked is the easy part. The valuable part is knowing what the failures look like, because several of them do not look like failures. All five below were reproduced in the lab.
Forgetting recovery.signal
Copy the base backup and start it with no recovery.signal and no restore_command.
$ cp -r backups/base1 restore_a # and nothing else: no recovery.signal, no restore_command
$ pg_ctl --pgdata=restore_a --log=restore_a.log start
(server started)
$ psql -p 55433 -c "SELECT count(*), max(id) FROM orders;"
count | max
--------+--------
100000 | 100000
(1 row)
$ grep -E 'recovery|redo|ready' restore_a.log
LOG: starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
LOG: redo starts at 0/3000028
LOG: completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
LOG: consistent recovery state reached at 0/3000120
LOG: redo done at 0/3000120 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOG: database system is ready to accept connections
The server started cleanly and has 100,000 rows. It replayed only the WAL inside the backup and opened for business. The 150,000 later orders and the four updates were in the archive the whole time, unused, and nothing printed an error. You can recognise this by the log: there is no mention of archive recovery, redo done sits at the end of the backup, and the server accepted writes at once. The fix is simply the signal file.
Choosing a target older than the backup
Now use a target time from just before the base backup started.
# recovery_target_time = '2026-11-18 20:32:23.921161+00' (a moment before the base backup started)
$ pg_ctl --pgdata=restore_a --log=restore_a.log --options="-p 55433" start
(server started)
$ psql -p 55433 -c "SELECT pg_get_wal_replay_pause_state();"
pg_get_wal_replay_pause_state
-------------------------------
paused
(1 row)
$ psql -p 55433 -c "SELECT count(*), max(id) FROM orders;"
count | max
--------+--------
100000 | 100000
(1 row)
$ grep -E 'FATAL|recovery|redo|consistent' restore_a.log
LOG: starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
LOG: redo starts at 0/3000028
LOG: completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
LOG: consistent recovery state reached at 0/3000120
LOG: database system is ready to accept read-only connections
LOG: recovery stopping before commit of transaction 754, time 2026-11-18 15:32:26.841126-05
LOG: pausing at the end of recovery
HINT: Execute pg_wal_replay_resume() to promote.
This is not an error either. The server paused, in this lab at before commit of transaction 754, and the table has 100,000 rows, the state of the backup itself. PostgreSQL cannot go back before the end of the backup, so it stops at the first opportunity after it. The documentation puts it this way: “The stop point must be after the ending time of the base backup”, and it continues: “You cannot use a base backup to recover to a time when that backup was in progress. (To recover to such a time, you must go back to your previous base backup and roll forward from there.)” The practical rules are to keep more than one base backup and to check the data after every pause.
Choosing a target after the end of the archive
Next, a target one hour after the newest archived WAL.
# recovery_target_time = '2026-11-18 21:33:36+00' (one hour after the last archived WAL)
$ pg_ctl --pgdata=restore_a --log=restore_a.log start
waiting for server to start.... done
server started
$ grep -E 'FATAL|recovery|redo|last completed' restore_a.log
LOG: starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
LOG: redo starts at 0/3000028
LOG: completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
LOG: consistent recovery state reached at 0/3000120
LOG: database system is ready to accept read-only connections
LOG: redo done at 0/174E53D8 system usage: CPU: user: 0.98 s, system: 0.43 s, elapsed: 3.20 s
LOG: last completed transaction was at log time 2026-11-18 15:33:23.579199-05
FATAL: recovery ended before configured recovery target was reached
$ pg_ctl --pgdata=restore_a status
pg_ctl: no server running
This one is loud, and it has a trap. pg_ctl reported that the server started, because it started successfully and was serving read-only connections while it replayed. It then reached the end of the archive and stopped with recovery ended before configured recovery target was reached, and pg_ctl status afterwards says no server is running. The line before it is the most useful one: last completed transaction was at log time tells you how far the archive really reaches. If that time is earlier than you hoped, the newest segment never reached the archive; run pg_switch_wal() on the old server if it is still alive, or copy its last pg_wal files into place.
A segment missing from the middle of the archive
Delete one segment from the middle of a copy of the archive and restore with no target at all.
$ rm archive_gap/00000001000000000000000D # one segment in the middle of the archive is lost
# restore1 settings, but with no recovery target at all and restore_command pointing at archive_gap
$ pg_ctl --pgdata=restore_a --log=restore_a.log start
(server started)
$ psql -p 55433 -c "SELECT count(*), max(id), max(total) FROM orders;"
count | max | max
--------+--------+-------
250000 | 250000 | 97.50
(1 row)
$ grep -E 'recovery|redo|last completed|ready' restore_a.log
LOG: completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
LOG: consistent recovery state reached at 0/3000120
LOG: database system is ready to accept read-only connections
LOG: redo done at 0/CFFFF90 system usage: CPU: user: 0.34 s, system: 0.26 s, elapsed: 1.33 s
LOG: last completed transaction was at log time 2026-11-18 15:32:28.007287-05
LOG: selected new timeline ID: 2
LOG: archive recovery complete
LOG: database system is ready to accept connections
This is the quiet one. The row count still says 250,000 (updates do not change a count), but max(total) is 97.50 where the good state has 100.50, so only one of the four update rounds survived. Recovery replayed up to the gap, found no next segment, treated that as the end of the WAL, chose a new timeline and opened the database. The log says exactly where it ended: last completed transaction was at log time is 15:32:28, a few seconds before the accident at 15:32:32. With a target the same gap would have produced the fatal error of the previous section, so a missing segment together with a target fails loudly and a missing segment without one fails silently. Defences are a freshness query in every restore test and a monitor that checks the archive for gaps.
An archive command that keeps failing
Finally, break archiving on the running server by pointing it at a folder that does not exist, and generate some WAL.
$ psql -p 55432 -c "ALTER SYSTEM SET archive_command = 'python ../walarch.py push \"%p\" \"%f\" ../no_such_folder';"
ALTER SYSTEM
$ psql -p 55432 -c "SELECT pg_reload_conf();"
pg_reload_conf
----------------
t
(1 row)
$ psql -p 55432 -x -c "SELECT archived_count, failed_count, last_failed_wal FROM pg_stat_archiver;"
-[ RECORD 1 ]---+-------------------------
archived_count | 26
failed_count | 6
last_failed_wal | 00000001000000000000001A
$ ls primary/pg_wal/archive_status
00000001000000000000001A.ready
00000001000000000000001B.ready
00000001000000000000001C.ready
$ grep -E 'archive command|walarch' primary.log
LOG: parameter "archive_command" changed to "python ../walarch.py push "%p" "%f" ../no_such_folder"
walarch: archive folder '../no_such_folder' does not exist
LOG: archive command failed with exit code 1
DETAIL: The failed archive command was: python ../walarch.py push "pg_wal\00000001000000000000001A" "00000001000000000000001A" ../no_such_folder
$ psql -p 55432 -c "ALTER SYSTEM RESET archive_command;"
ALTER SYSTEM
$ psql -p 55432 -c "SELECT pg_reload_conf();"
pg_reload_conf
----------------
t
(1 row)
$ psql -p 55432 -x -c "SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;"
-[ RECORD 1 ]-----+-------------------------
archived_count | 30
failed_count | 6
last_archived_wal | 00000001000000000000001D
$ ls primary/pg_wal/archive_status | grep -c ready
0
Within seconds failed_count climbed to 6, and three finished segments were waiting in archive_status with a .ready marker, because PostgreSQL keeps a segment until it is archived. The server log shows the error from our helper and the exact command that failed. The documentation describes the consequence: it “will cause WAL files to accumulate in pg_wal/ until a working archive_command is re-established.” On a busy server that is how disks fill up. After the setting was reset and reloaded, archive_command took effect without a restart (as the documentation says, “archive_command and archive_library can be changed with a configuration file reload”), the backlog drained in order, and the .ready count returned to 0. Monitor failed_count and the age of last_archived_time, and alert on both.
| Mistake | What PostgreSQL does | How you notice |
|---|---|---|
No recovery.signal |
Opens at the end of the backup, 100,000 rows | Log has no archive recovery lines; a freshness query |
| Target older than the backup | Pauses right after the backup, no error | Row count or newest timestamp is too old |
| Target after the end of the archive | Starts, replays everything, then a fatal error | pg_ctl status; the “last completed transaction” log line |
| Missing segment, no target | Ends recovery at the gap and opens | “last completed transaction” time; a freshness query |
Failing archive_command |
Keeps WAL in pg_wal and retries |
failed_count, .ready files, the server log |
Step 11: Plan restore time and archive size
Two numbers decide whether your setup is good enough: how much archive space you need, and how long a restore takes. The lab measured both. The WAL cost of each action was read from the WAL position before and after it:
| Action | WAL written |
|---|---|
| Load 100,000 rows | 16.4 MiB |
| Insert 150,000 more rows | 24.7 MiB |
| Update all 250,000 rows (four times) | 71.0, 61.3, 61.7 and 61.5 MiB |
| Delete all 250,000 rows | 13.4 MiB |
Writes that rewrite many rows are expensive in WAL, and a delete is cheaper than an update of the same rows. Your archive grows with the amount of change and not with the size of the database. Restore time has two parts: copying the base backup (0.6 seconds for 49.1 MiB here) and starting the server and replaying WAL. For the replay, I restored to three points after the backup and timed each:
target WAL replayed (MiB) copy backup (s) start + replay (s)
after_insert 40.7 0.61 1.88
after_update2 173.0 0.63 3.05
after_update4 296.2 0.63 4.23
Fitting a straight line through the first and last rows by hand gives about 1.5 seconds of fixed start-up cost plus about 9 milliseconds per MiB, or roughly 109 MiB per second of replay on this machine. That is a rough estimate from three points on one computer with a fast disk, so measure your own. The practical lesson is the shape: restore time is the copy time plus the replay time, replay time grows with the amount of WAL since the backup, and you control that by taking base backups more often.
The other planning number is how much you can lose. The archive only receives finished segments, so the newest changes sit in an unfinished segment until archive_timeout or a manual pg_switch_wal() closes it. With a 60 second timeout you can lose up to about a minute of the most recent activity if the server’s own disk is destroyed. If the server’s disk survives, you can copy its unarchived pg_wal files into the restore and lose nothing. A standby server fed by streaming replication shrinks this window much further, and that is outside the scope of this tutorial.
Step 12: Turn the checklist into a restore drill
Everything above can be forgotten by the time you need it. A restore drill is a script that does the restore for you, on a schedule, and fails loudly if the result is wrong. The script below copies a base backup into a scratch folder, writes the recovery settings, starts a server on a spare port, waits for the pause, runs your checks, promotes the copy to prove it is writable, and always stops the server and deletes the scratch folder. It refuses to overwrite an existing folder, it turns archiving off in the copy, and its exit code tells you the outcome: 0 means the checks passed, 1 means a check failed, and 2 means recovery never reached the target.
# pitr_drill.py
"""pitr_drill.py: restore a base backup plus a WAL archive into a scratch folder, stop at a recovery target,
run checks against the result, and clean up. A backup you have never restored is a hope, not a backup.
python pitr_drill.py --backup backups/base1 --archive archive --scratch drill1 --port 55440 \\
--target "time=2026-11-18 20:28:49.486521+00" --check "SELECT count(*) FROM orders" --expect 250000
Needs pg_ctl and psql on PATH and a Python that walarch.py can run under (the restore_command calls `python`).
"""
import argparse
import re
import shutil
import subprocess
import sys
import tempfile
import time
from pathlib import Path
WALARCH = Path(__file__).with_name("walarch.py")
class DrillError(Exception):
pass
def parse_target(text):
if text == "immediate":
return ["recovery_target = 'immediate'"]
kind, _, value = text.partition("=")
names = {"time": "recovery_target_time", "name": "recovery_target_name", "lsn": "recovery_target_lsn",
"xid": "recovery_target_xid", "xid-before": "recovery_target_xid"}
if kind not in names or not value:
raise DrillError(f"unknown target {text!r}: use time=, name=, lsn=, xid=, xid-before= or immediate")
lines = [f"{names[kind]} = '{value}'"]
if kind == "xid-before":
lines.append("recovery_target_inclusive = off")
return lines
def build_settings(archive, target):
archive = Path(archive).resolve().as_posix()
walarch = WALARCH.resolve().as_posix()
return [
f"restore_command = 'python {walarch} fetch \"%f\" \"%p\" {archive}'",
*parse_target(target),
"recovery_target_action = 'pause'",
"archive_mode = off",
]
def lsn_int(text):
hi, lo = text.strip().split("/")
return (int(hi, 16) << 32) + int(lo, 16)
def run(argv, timeout=180):
with tempfile.TemporaryFile() as f:
p = subprocess.run(argv, stdout=f, stderr=subprocess.STDOUT, stdin=subprocess.DEVNULL, timeout=timeout)
f.seek(0)
return p.returncode, f.read().decode("utf-8", "replace")
def psql(port, sql):
rc, out = run(["psql", "-X", "-At", "-h", "127.0.0.1", "-U", "postgres", "-p", str(port), "-d", "postgres", "-c", sql])
return rc, out.strip()
def rmtree(path):
for _ in range(10):
try:
shutil.rmtree(path)
return
except FileNotFoundError:
return
except OSError:
time.sleep(0.5)
shutil.rmtree(path, ignore_errors=True)
def log_tail(logfile):
try:
lines = Path(logfile).read_text(encoding="utf-8", errors="replace").splitlines()
except FileNotFoundError:
return "(no log)"
keep = [re.sub(r"^\S+ [\d:.]+ \S+ \[\d+\] ", "", ln) for ln in lines if re.search(r"FATAL|ERROR|recovery", ln)]
return " | ".join(keep[-4:]) or "(nothing useful in the log)"
def wait_paused(scratch, port, logfile, timeout):
end = time.time() + timeout
while time.time() < end:
rc, state = psql(port, "SELECT pg_get_wal_replay_pause_state()")
if state == "paused":
return
if not (Path(scratch) / "postmaster.pid").exists():
raise DrillError("the server stopped during recovery: " + log_tail(logfile))
time.sleep(0.25)
raise DrillError("recovery did not reach the target in time: " + log_tail(logfile))
def drill(backup, archive, scratch, port, target, checks, timeout=180):
scratch = Path(scratch).resolve()
logfile = str(scratch) + ".log"
if scratch.exists():
raise DrillError(f"{scratch} already exists; the drill never overwrites a folder")
report = {"target": target, "checks": [], "ok": False}
t0 = time.time()
shutil.copytree(backup, scratch)
report["copy_s"] = round(time.time() - t0, 1)
try:
with open(scratch / "postgresql.conf", "a", newline="\n") as f:
f.write("\n" + "\n".join(build_settings(archive, target)) + "\n")
(scratch / "recovery.signal").touch()
start_lsn = re.search(r"START WAL LOCATION: (\S+)", (scratch / "backup_label").read_text()).group(1)
t1 = time.time()
rc, out = run(["pg_ctl", "-D", str(scratch), "-l", logfile, "-o", f"-p {port}", "-t", "120", "start"])
if rc != 0:
raise DrillError("the server did not start: " + log_tail(logfile))
wait_paused(scratch, port, logfile, timeout)
report["replay_s"] = round(time.time() - t1, 1)
report["replayed_mib"] = round((lsn_int(psql(port, "SELECT pg_last_wal_replay_lsn()")[1]) - lsn_int(start_lsn)) / 1048576, 1)
for sql, expected in checks:
rc, got = psql(port, sql)
ok = rc == 0 and got == str(expected)
report["checks"].append({"sql": sql, "expected": str(expected), "got": got, "ok": ok})
psql(port, "SELECT pg_wal_replay_resume()")
for _ in range(80):
if psql(port, "SELECT pg_is_in_recovery()")[1] == "f":
break
time.sleep(0.25)
report["writable"] = psql(port, "CREATE TABLE drill_probe AS SELECT 1 AS x")[0] == 0
report["timeline"] = psql(port, "SELECT timeline_id FROM pg_control_checkpoint()")[1]
report["ok"] = all(c["ok"] for c in report["checks"]) and report["writable"]
finally:
run(["pg_ctl", "-D", str(scratch), "-m", "immediate", "-w", "stop"])
rmtree(scratch)
Path(logfile).unlink(missing_ok=True)
report["total_s"] = round(time.time() - t0, 1)
return report
def main(argv=None):
ap = argparse.ArgumentParser(description="Restore a backup and a WAL archive into a scratch folder and check the result.")
ap.add_argument("--backup", required=True)
ap.add_argument("--archive", required=True)
ap.add_argument("--scratch", default="drill_scratch")
ap.add_argument("--port", type=int, default=55440)
ap.add_argument("--target", required=True)
ap.add_argument("--check", action="append", default=[])
ap.add_argument("--expect", action="append", default=[])
a = ap.parse_args(argv)
if len(a.check) != len(a.expect):
ap.error("give one --expect for every --check")
try:
rep = drill(a.backup, a.archive, a.scratch, a.port, a.target, list(zip(a.check, a.expect)))
except DrillError as err:
print(f"DRILL FAILED: {err}")
return 2
print(f"target {rep['target']}: copy {rep['copy_s']} s, start and replay {rep['replay_s']} s, "
f"replayed {rep['replayed_mib']} MiB, new timeline {rep['timeline']}, writable {'yes' if rep['writable'] else 'NO'}")
for c in rep["checks"]:
print(f" {'PASS' if c['ok'] else 'FAIL'} {c['sql']} expected {c['expected']} got {c['got']}")
print(f"RESULT: {'PASS' if rep['ok'] else 'FAIL'} in {rep['total_s']} s")
return 0 if rep["ok"] else 1
if __name__ == "__main__":
sys.exit(main())
Run it against the lab’s backup and archive with the same time target as in Step 7, and with two checks that describe the state you expect:
$ python pitr_drill.py --backup backups/base1 --archive archive --scratch drill1 --port 55440 --target "time=2026-11-18 20:32:32.274606+00" --check "SELECT count(*) FROM orders" --expect 250000 --check "SELECT max(total) FROM orders" --expect 100.50
target time=2026-11-18 20:32:32.274606+00: copy 0.6 s, start and replay 4.3 s, replayed 310.2 MiB, new timeline 2, writable yes
PASS SELECT count(*) FROM orders expected 250000 got 250000
PASS SELECT max(total) FROM orders expected 100.50 got 100.50
RESULT: PASS in 6.7 s
$ echo $?
0
The copy took 0.6 seconds, and start plus replay took 4.3 seconds. The “replayed” figure of 310.2 MiB is about 14 MiB larger than the 296.2 MiB of the last row in the Step 11 table, which is roughly the size of the delete’s own WAL (13.4 MiB): a time target stops before the commit of the delete, so the delete’s records have already been replayed. Now run the mistake from the earlier section, a target that is too old, to see why the checks matter:
$ python pitr_drill.py --backup backups/base1 --archive archive --scratch drill1 --port 55440 --target "time=2000-01-01 00:00:00+00" --check "SELECT count(*) FROM orders" --expect 250000 --check "SELECT max(total) FROM orders" --expect 100.50
target time=2000-01-01 00:00:00+00: copy 0.5 s, start and replay 1.7 s, replayed 40.7 MiB, new timeline 2, writable yes
FAIL SELECT count(*) FROM orders expected 250000 got 100000
FAIL SELECT max(total) FROM orders expected 100.50 got 96.50
RESULT: FAIL in 4.0 s
$ echo $?
1
The drill did not raise any error, because as Step 10 showed, PostgreSQL did not either. Recovery paused, and only the checks noticed that the data is from the backup and not from the target. The drill printed FAIL and returned 1. A drill without checks would have reported success here. The third outcome is a target after the end of the archive:
$ python pitr_drill.py --backup backups/base1 --archive archive --scratch drill1 --port 55440 --target "time=2999-01-01 00:00:00+00" --check "SELECT count(*) FROM orders" --expect 250000
DRILL FAILED: the server stopped during recovery: LOG: starting point-in-time recovery to 2998-12-31 19:00:00-05 | LOG: completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120 | LOG: consistent recovery state reached at 0/3000120 | FATAL: recovery ended before configured recovery target was reached
$ echo $?
2
Exit code 2 and the fatal line from the log. Now the tests. The first block of code builds a small cluster once (archiving, a base backup, an accident), and then drills it in several ways.
# test_pitr_drill.py
"""test_pitr_drill.py: build a small cluster once (archiving, a base backup, an accident), then drill it.
Set PGBIN to the folder that holds initdb, pg_ctl, psql and pg_basebackup."""
import os
import shutil
import socket
import time
from pathlib import Path
import pytest
import pitr_drill as d
PGBIN = os.environ.get("PGBIN", "")
if PGBIN:
os.environ["PATH"] = PGBIN + os.pathsep + os.environ["PATH"]
PORT = 55450
WORK = Path(__file__).with_name("pt_work")
def port_closed(port):
s = socket.socket()
s.settimeout(0.5)
try:
return s.connect_ex(("127.0.0.1", port)) != 0
finally:
s.close()
@pytest.fixture(scope="session")
def lab():
d.rmtree(WORK)
WORK.mkdir()
arch, prim, base = WORK / "archive", WORK / "primary", WORK / "base"
arch.mkdir()
rc, out = d.run(["initdb", "-D", str(prim), "-U", "postgres", "--auth=trust", "-E", "UTF8", "--locale=C"])
assert rc == 0, out
with open(prim / "postgresql.conf", "a", newline="\n") as f:
f.write(f"\nport = {PORT}\narchive_mode = on\n"
f"archive_command = 'python {d.WALARCH.resolve().as_posix()} push \"%p\" \"%f\" {arch.resolve().as_posix()}'\n")
rc, out = d.run(["pg_ctl", "-D", str(prim), "-l", str(WORK / "primary.log"), "-w", "start"])
assert rc == 0, out
def q(sql):
rc, text = d.psql(PORT, sql)
assert rc == 0, text
return text
q("CREATE TABLE orders (id bigserial PRIMARY KEY, status text NOT NULL)")
q("INSERT INTO orders (status) SELECT 'paid' FROM generate_series(1, 1000)")
rc, out = d.run(["pg_basebackup", "-h", "127.0.0.1", "-U", "postgres", "-p", str(PORT), "-D", str(base), "--checkpoint=fast"])
assert rc == 0, out
q("INSERT INTO orders (status) SELECT 'paid' FROM generate_series(1, 500)")
t_good = q("SELECT to_char(clock_timestamp() AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.US') || '+00'")
q("SELECT pg_create_restore_point('before_cleanup')")
xid = q("DELETE FROM orders RETURNING xmax::text").splitlines()[0]
q("INSERT INTO orders (status) SELECT 'late' FROM generate_series(1, 5)")
q("SELECT pg_switch_wal()")
last = q("SELECT pg_walfile_name(pg_current_wal_lsn() - 1)")
for _ in range(60):
if q("SELECT last_archived_wal FROM pg_stat_archiver") == last:
break
time.sleep(0.5)
else:
pytest.fail("the last segment was never archived")
yield {"base": base, "archive": arch, "t_good": t_good, "xid": xid, "good": 1500, "base_rows": 1000}
d.run(["pg_ctl", "-D", str(prim), "-m", "immediate", "-w", "stop"])
COUNT = "SELECT count(*) FROM orders"
def test_parse_target_forms():
assert d.parse_target("time=2026-11-18 20:28:49+00") == ["recovery_target_time = '2026-11-18 20:28:49+00'"]
assert d.parse_target("name=before_cleanup") == ["recovery_target_name = 'before_cleanup'"]
assert d.parse_target("lsn=0/158D6000") == ["recovery_target_lsn = '0/158D6000'"]
assert d.parse_target("xid=731") == ["recovery_target_xid = '731'"]
assert d.parse_target("xid-before=731") == ["recovery_target_xid = '731'", "recovery_target_inclusive = off"]
assert d.parse_target("immediate") == ["recovery_target = 'immediate'"]
@pytest.mark.parametrize("bad", ["", "time=", "when=now", "xid 731"])
def test_parse_target_rejects_nonsense(bad):
with pytest.raises(d.DrillError):
d.parse_target(bad)
def test_settings_pause_turn_archiving_off_and_use_an_absolute_archive_path():
s = d.build_settings("some_archive", "name=x")
assert "recovery_target_action = 'pause'" in s and "archive_mode = off" in s
cmd = next(line for line in s if line.startswith("restore_command"))
assert Path(Path("some_archive").resolve().as_posix()).is_absolute() and Path("some_archive").resolve().as_posix() in cmd
def test_time_target_restores_the_last_good_state_and_leaves_no_trace(lab, tmp_path):
before = sorted(os.listdir(lab["archive"]))
rep = d.drill(lab["base"], lab["archive"], tmp_path / "s1", 55451, f"time={lab['t_good']}", [(COUNT, lab["good"])])
assert rep["ok"] and rep["writable"] and rep["replayed_mib"] > 0
assert sorted(os.listdir(lab["archive"])) == before
assert not (tmp_path / "s1").exists()
assert port_closed(55451)
def test_xid_before_leaves_the_bad_transaction_out(lab, tmp_path):
rep = d.drill(lab["base"], lab["archive"], tmp_path / "s2", 55452, f"xid-before={lab['xid']}", [(COUNT, lab["good"])])
assert rep["ok"], rep
def test_a_wrong_expectation_fails_the_drill(lab, tmp_path):
rep = d.drill(lab["base"], lab["archive"], tmp_path / "s3", 55453, f"time={lab['t_good']}", [(COUNT, lab["good"] + 1)])
assert not rep["ok"] and rep["checks"][0]["got"] == str(lab["good"])
def test_a_target_beyond_the_archive_is_an_error_and_still_cleans_up(lab, tmp_path):
with pytest.raises(d.DrillError):
d.drill(lab["base"], lab["archive"], tmp_path / "s4", 55454, "time=2999-01-01 00:00:00+00", [(COUNT, 0)])
assert not (tmp_path / "s4").exists() and port_closed(55454)
def test_a_target_older_than_the_backup_is_not_an_error_so_the_checks_must_catch_it(lab, tmp_path):
rep = d.drill(lab["base"], lab["archive"], tmp_path / "s6", 55456, "time=2000-01-01 00:00:00+00", [(COUNT, lab["base_rows"])])
assert rep["ok"] and rep["checks"][0]["got"] == str(lab["base_rows"])
assert rep["checks"][0]["got"] != str(lab["good"])
def test_the_drill_never_overwrites_a_folder(lab, tmp_path):
(tmp_path / "s5").mkdir()
with pytest.raises(d.DrillError):
d.drill(lab["base"], lab["archive"], tmp_path / "s5", 55455, "immediate", [])
assert (tmp_path / "s5").exists()
$ python -m pytest -q test_pitr_drill.py
............ [100%]
12 passed in 26.00s
The tests check that the drill restores the last good state and leaves no trace (the archive listing is identical before and after, the scratch folder is gone, and the port is closed), that a transaction ID target with xid-before leaves the bad transaction out, that a wrong expectation fails the drill, that a target beyond the archive is an error with cleanup, that a target older than the backup silently gives the backup state, and that the drill never overwrites a folder. The last one is deliberate: the test documents PostgreSQL’s behaviour so nobody mistakes it for a bug in the drill.
Tests that never fail prove nothing, so I planted eight bugs in the drill one at a time and ran the suite against each:
$ python mutants.py
CONTROL (no bug planted): 12 passed in 24.56s
CAUGHT no recovery.signal: 1 failed, 6 passed in 33.24s
CAUGHT archive_mode left on: 1 failed, 5 passed in 0.03s
CAUGHT xid-before forgets inclusive = off: 1 failed in 0.03s
CAUGHT scratch folder not removed: 1 failed, 6 passed in 9.40s
CAUGHT every check passes: 1 failed, 8 passed in 16.83s
CAUGHT server not stopped: 1 failed, 6 passed in 14.95s
CAUGHT relative archive path: 1 failed, 5 passed in 0.03s
CAUGHT overwrites an existing folder: 1 failed, 11 passed in 23.78s
8 of 8 planted bugs caught
Each line is one change to pitr_drill.py. A line that says CAUGHT means a test failed because of the planted bug. The runner first runs the suite on the unchanged code as a control, stops each mutant’s run at its first failing test, and lowers the drill’s wait from 180 to 25 seconds in the mutated copy so that mutants which would hang fail faster. Some bugs were caught in a fraction of a second by the tests of the generated settings, before any server started, and others only by a live restore.
Confirm that the whole thing works end to end
Before you trust any of this on a real server, check these five things on the real server, in this order:
SELECT archived_count, failed_count, last_archived_wal, last_archived_time FROM pg_stat_archiver;showsfailed_countat 0 and a recentlast_archived_timeon a server with traffic.- A base backup exists that is older than the moment you want to recover to, and
pg_verifybackuppasses on it. - The archive holds every segment from that backup onward, with no missing number between the first and the last.
- The restore drill passes against the newest base backup with a target a few minutes old, and the checks include one that proves the data is fresh.
- The drill runs on a schedule (cron on Linux, Task Scheduler on Windows) and a failure reaches a person.
Where to go next
- Incremental backups. PostgreSQL 18 supports incrementals: the documentation says it “can take a full or incremental base backup of the database”, and
pg_combinebackupreassembles a chain, verifying that “the backups you specify form a legal backup chain from which a correct full backup can be reconstructed.” I did not run incremental backups for this article. - Retention. Decide how many base backups to keep, delete only the WAL older than the oldest backup you keep, and then test that a restore from that oldest backup still works.
- Production tools. pgBackRest, Barman and WAL-G add parallel backup, compression, encryption, retention and remote storage. I did not test them here.
- Related tutorials on this site. Zero-downtime schema migrations on PostgreSQL 18 (create a restore point before a risky migration), finding slow queries in PostgreSQL 18, SQLite write-ahead logging for the same idea in a smaller database, changed block tracking for incremental backups, and the Git equivalent of this article, recovering lost commits with reflog and fsck.
Sources
- PostgreSQL 18 documentation: Continuous Archiving and Point-in-Time Recovery (PITR)
- PostgreSQL 18 documentation: Write Ahead Log settings (archiving and recovery targets)
- PostgreSQL 18 documentation: pg_basebackup
- PostgreSQL 18 documentation: pg_waldump
- PostgreSQL 18 documentation: System Administration Functions
- PostgreSQL 18 documentation: pg_combinebackup
- PostgreSQL source,
sequence.con the REL_18_STABLE branch








No Comment! Be the first one.