What is it? #
Point-in-time recovery answers a specific and very uncomfortable question: someone deleted important data at 14:35, and we only have last night's backup — can we get back to 14:34?
With nightly backups alone, the answer is no. You restore last night and lose everything since.
With PITR, the answer is yes. You restore the base backup, then replay archived WAL forward and stop at the moment you choose.
The pieces are already covered: a physical base backup, and WAL archiving. PITR is what they are for.
One expectation to set clearly: this is a recovery procedure, not a routine operation. It takes as long as restoring the backup plus replaying the WAL, it produces a new database that you switch to deliberately, and the exact commands depend on your PostgreSQL version and how your backups are taken.
Think of it like this #
A photograph of the shop taken on Monday, plus the till notebook recording every sale since.
On Wednesday someone empties a shelf by mistake at 14:35.
You cannot undo that directly. But you can set up a copy of the shop from Monday's photograph, then work through the notebook, replaying every sale in order — and simply stop reading at 14:34, just before the mistake. The result is the shop exactly as it was one minute before the error.
The photograph alone would only give you Monday. The notebook alone has nothing to apply to. Together they give you any moment you like.
Simple example #
Monday 02:00, a base backup is taken. WAL is archived continuously afterwards.
Wednesday 14:35, a bad deployment runs DELETE FROM orders without a WHERE clause.
Recovery: restore Monday's base backup into a new directory, replay WAL up to Wednesday 14:34:50, verify the orders are present, and switch over. Roughly two days of legitimate work is preserved.
Code #
---------- What PITR needs, set up BEFORE you need it ----------
1. wal_level = replica (or logical)
2. archive_mode = on
3. archive_command that reliably stores every segment
4. A BASE BACKUP taken while archiving is running
5. Archived WAL kept from the base backup forward, without gaps
If ANY WAL segment between the base backup and your target time is
missing, recovery stops at the gap. The chain must be unbroken.# ---------- STEP 0 (done in advance): take a base backup ----------
pg_basebackup \
-h localhost -U replicator \
-D /backup/base_2026-09-21 \
-Ft -z -Xs -P -c fast
# -Ft -z tar format, compressed
# -Xs stream the WAL generated DURING the backup (important)
# -c fast force an immediate checkpoint so the backup starts promptly
# Record WHEN this was taken. You will need it.
date -Is > /backup/base_2026-09-21/TAKEN_AT---------- THE INCIDENT ----------
Wednesday 14:35:12 — a deployment runs:
DELETE FROM orders; -- no WHERE clause
14:41 — someone notices.
!! FIRST ACTION: STOP WRITES TO THE DATABASE.
Every new transaction makes the situation harder to reason about and
risks overwriting what you are trying to recover.
Stop the application, or revoke its connection rights.# ---------- STEP 1: preserve the current state. DO NOT SKIP THIS ----------
# !! You may need the current data later, and a recovery attempt can
# !! destroy it. Copy before you change anything.
sudo systemctl stop postgresql
sudo cp -a /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken.$(date +%F_%H%M)
# Also copy any WAL not yet archived — it may contain the most recent
# transactions you want to recover:
sudo cp -a /var/lib/postgresql/16/main/pg_wal /backup/pg_wal_rescue/
# Check you have the disk space for this before starting:
df -h# ---------- STEP 2: restore the base backup into a NEW directory ----------
# Recovering into a separate directory means the broken original is
# untouched and you can try again if the target time was wrong.
sudo mkdir -p /var/lib/postgresql/16/recovery
sudo chown postgres:postgres /var/lib/postgresql/16/recovery
sudo chmod 700 /var/lib/postgresql/16/recovery
sudo -u postgres tar -xzf /backup/base_2026-09-21/base.tar.gz \
-C /var/lib/postgresql/16/recovery
# If the WAL was streamed separately during the base backup:
sudo -u postgres tar -xzf /backup/base_2026-09-21/pg_wal.tar.gz \
-C /var/lib/postgresql/16/recovery/pg_wal# ---------- STEP 3: tell PostgreSQL how to fetch WAL, and where to stop ----------
# PostgreSQL 12 and later: these go in postgresql.conf (or a conf.d file)
sudo -u postgres tee -a /var/lib/postgresql/16/recovery/postgresql.conf > /dev/null <<'EOF'
# --- point-in-time recovery settings ---
restore_command = 'cp /archive/%f %p'
# ^ the REVERSE of archive_command: fetch a segment by name
recovery_target_time = '2026-09-23 14:34:50+05:30'
# ^ stop JUST BEFORE the mistake. Include the TIME ZONE.
recovery_target_action = 'pause'
# pause stop and WAIT so you can inspect before committing to it <- SAFEST
# promote finish recovery and start accepting writes immediately
# shutdown stop the server when the target is reached
EOF
# This file is what tells PostgreSQL to start in RECOVERY mode:
sudo -u postgres touch /var/lib/postgresql/16/recovery/recovery.signal
# NOTE: PostgreSQL 11 and earlier used a separate recovery.conf file.
# The settings are similar but the mechanism differs — check the
# documentation for YOUR major version.---------- Other ways to specify the stopping point ----------
recovery_target_time = '2026-09-23 14:34:50+05:30'
The usual choice. Requires knowing roughly when it happened.
recovery_target_xid = '48573922'
Stop just before a specific transaction id. Precise, if you can
identify the transaction from the logs.
recovery_target_lsn = '0/23A4F120'
Stop at an exact WAL position.
recovery_target_name = 'before_migration'
Stop at a named restore point you created IN ADVANCE with:
SELECT pg_create_restore_point('before_migration');
Worth doing before any risky migration — it gives you an exact,
unambiguous point to return to.
recovery_target_inclusive = false
Stop BEFORE the target rather than after it.# ---------- STEP 4: start recovery ----------
sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl \
-D /var/lib/postgresql/16/recovery \
-o "-p 5433" \
-l /tmp/recovery.log start
# -p 5433 a DIFFERENT PORT, so this does not collide with the live server
# and nothing connects to it by accident
# Watch it work:
tail -f /tmp/recovery.log
# Expected output:
# LOG: starting point-in-time recovery to 2026-09-23 14:34:50+05:30
# LOG: restored log file "000000010000000000000023" from archive
# LOG: restored log file "000000010000000000000024" from archive
# ...
# LOG: recovery stopping before commit of transaction 48573922
# LOG: recovery has paused
# HINT: Execute pg_wal_replay_resume() to promote.-- ---------- STEP 5: VERIFY before committing to it ----------
-- Recovery is paused. The database is READ-ONLY. Look before you leap.
psql -p 5433 -U postgres -d shop
SELECT count(*) FROM orders; -- are the rows back?
SELECT max(placed_at) FROM orders; -- how recent is the data?
SELECT * FROM orders ORDER BY id DESC LIMIT 10; -- does it look right?
-- Check that legitimate work done BEFORE the incident is still present:
SELECT count(*) FROM orders WHERE placed_at::date = '2026-09-23';
-- TOO FAR BACK? Stop the server, raise recovery_target_time, start again.
-- TOO FAR FORWARD (the delete is included)? Lower it and start again.
-- This is why we recovered into a SEPARATE directory: you can retry.-- ---------- STEP 6: promote, once you are satisfied ----------
SELECT pg_wal_replay_resume(); -- finish recovery, become writable
-- PostgreSQL now switches to a NEW TIMELINE (e.g. 00000002...).
-- This prevents the recovered database's future WAL from being confused
-- with the original server's WAL.
SELECT pg_is_in_recovery(); -- false = fully promoted and writable# ---------- STEP 7: switch over ----------
# 1. Stop the recovered instance and the old one.
# 2. Move the recovered data directory into place (keep the broken one).
# 3. Start PostgreSQL on the normal port.
# 4. RUN ANALYZE — statistics do not survive this.
psql -U postgres -d shop -c "ANALYZE;"
# 5. TAKE A NEW BASE BACKUP IMMEDIATELY.
# You are on a new timeline; your previous base backup plus WAL no
# longer describes this database's future.
pg_basebackup -D /backup/base_$(date +%F) -Ft -z -Xs -P
# 6. Only then point the application back at it.
# 7. Keep main.broken.* for a few days before deleting.How it works #
PITR works because the base backup and the WAL fit together exactly. The base backup is a physical copy of the data directory at one moment. The archived WAL is every change since. Replaying the WAL against the backup reconstructs the database at any point covered by the archive.
restore_command is the mirror image of archive_command: where archiving copies a finished segment out, restoring fetches a named segment back in. PostgreSQL calls it repeatedly, in order, asking for each segment it needs.
The recovery.signal file is what puts PostgreSQL into recovery mode on startup. Its presence is the switch; the settings in postgresql.conf supply the details. PostgreSQL removes it automatically once recovery completes, which is why an interrupted attempt needs it recreated.
recovery_target_action = 'pause' is the setting that makes this safe to get wrong. Recovery stops at the target and waits, with the database readable but not writable, so you can inspect the result before committing to it. If you overshot or undershot, you stop the instance, change the target and start again — which is only possible because the recovery was done in a separate directory, leaving the original untouched.
When recovery finishes and the server is promoted, PostgreSQL switches to a new timeline. This matters: the original server's WAL continued past the point you recovered to, and without timelines those two futures would share segment names and become impossible to tell apart. The timeline id increments so the histories stay distinct.
This is also why a new base backup immediately after promotion is not optional. Your old base backup belongs to the old timeline; combined with the new WAL it no longer describes a coherent database. Until a fresh base backup exists, you have no working recovery chain at all — a genuinely dangerous gap right after an incident.
Two limits are worth knowing. You cannot recover to a point before the base backup, and you cannot recover past the end of the archived WAL. And a single missing segment stops replay at that gap, which is why monitoring archiving matters so much.
Real-world use #
The first action in any data-loss incident is to stop writes, and it is the one most often skipped in the rush to fix things. Every transaction that runs afterwards makes the picture harder to reason about and can overwrite what you are trying to recover.
The second is to preserve the current state before attempting anything. Copy the data directory and any unarchived WAL. A recovery attempt can destroy the evidence, and the current broken state may still contain data you need — including transactions committed after the incident that are worth extracting later.
Knowing the exact time of the incident is the hard part in practice. Application logs, the PostgreSQL log with log_min_duration_statement enabled, deployment timestamps and audit tables all help. If you cannot pin it down, recover to a time you are confident is before it, check, and move forward — the pause-and-inspect loop exists for exactly this.
pg_create_restore_point() is underused and worth adopting. Creating a named restore point immediately before any risky migration gives you an exact, unambiguous target instead of guessing at timestamps afterwards. It costs nothing.
Rehearse this. A PITR performed for the first time during a real incident, under pressure, from documentation you have never followed, will take hours and may not succeed. Practising it once on a test server — and writing down the version-specific commands for your setup — turns it into a procedure rather than an experiment. This is the single most valuable exercise in the whole track.
For production systems, use pgBackRest, Barman or WAL-G rather than assembling this by hand. They manage base backups, archive integrity, retention and the recovery process itself, with verification the manual approach lacks. Understanding the mechanism as described here is what lets you use those tools confidently and debug them when something is wrong.
Common mistakes #
- Not stopping application writes first, which complicates recovery and can overwrite recoverable data.
- Recovering over the original data directory instead of a copy, leaving no way to retry a wrong target.
- Omitting the time zone from recovery_target_time and landing at the wrong moment.
- Promoting immediately instead of pausing to verify the recovered data first.
- Forgetting to take a new base backup after promotion, leaving no valid recovery chain on the new timeline.
Practice #
Set up archiving on a test server and take a base backup. Insert recognisable data, note the exact time, then delete it. Now perform a full PITR into a separate directory on a different port: restore the base backup, set recovery_target_time to just before the deletion, start with recovery_target_action = 'pause', and verify the deleted rows are present before resuming. Deliberately pick a target that is too late, see the deletion included, and repeat with an earlier target — that retry loop is the skill worth having.