What Is Point in Time Recovery and When You Need It

A nightly dump loses everything since the dump. If someone runs a bad DELETE at 14:03, the last good state is 14:02, and a restore from 02:00 throws away twelve hours of legitimate work. Point-in-time recovery restores to 14:02. It is the standard answer for production databases, and its cost is setup and storage. It builds on logical versus physical backups, and the commands assume PostgreSQL 17.

How it works

   base backup (Sunday 02:00) ----+
                                  +--> replay archived WAL --> stop at 14:02 on Wednesday
   WAL archive: continuous -------+

The database writes every change to a write-ahead log before applying it. Archive each finished log segment somewhere safe, take a physical base backup now and then, and you can restore the base backup and replay the logs forward until the moment you choose. The base backup is the starting state; the logs are the history. Without the logs you can only restore to the instant of the base backup.

Set up continuous archiving

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal-archive/%f && cp %p /mnt/wal-archive/%f'
archive_timeout = 300

sudo systemctl restart postgresql
psql -c "SELECT pg_switch_wal()"
ls /mnt/wal-archive | head -3
pg_basebackup -h localhost -U replicator -D /backups/base-$(date +%F) -Ft -z -X stream -P

The archive command copies each completed WAL segment, and must return success only when the file is safely stored; the test ! -f guard avoids overwriting. A local directory is only a demonstration: the archive belongs on separate storage or an object store, and tools such as pgBackRest, WAL-G and Barman handle compression, encryption, retention and parallelism, and are what most production setups use. archive_timeout forces a segment switch every five minutes on a quiet server so the data-loss window is bounded. The base backup shown uses pg_basebackup.

Restore to a moment

sudo systemctl stop postgresql
mv /var/lib/postgresql/17/main /var/lib/postgresql/17/main.broken
mkdir /var/lib/postgresql/17/main && tar -xzf /backups/base-2026-11-01/base.tar.gz -C /var/lib/postgresql/17/main
tar -xzf /backups/base-2026-11-01/pg_wal.tar.gz -C /var/lib/postgresql/17/main/pg_wal
cat >> /var/lib/postgresql/17/main/postgresql.auto.conf <<'EOF'
restore_command = 'cp /mnt/wal-archive/%f %p'
recovery_target_time = '2026-11-03 14:02:00+00'
recovery_target_action = 'promote'
EOF
touch /var/lib/postgresql/17/main/recovery.signal
sudo chown -R postgres:postgres /var/lib/postgresql/17/main
sudo systemctl start postgresql

LOG:  starting point-in-time recovery to 2026-11-03 14:02:00+00
LOG:  restored log file "000000010000000000000042" from archive
LOG:  recovery stopping before commit of transaction 8123, time 2026-11-03 14:03:11.5
LOG:  database system is ready to accept read-only connections

Keep the damaged data directory until the recovered one is verified. The server replays archived logs until the target and then promotes. Choose the target carefully: use the time just before the mistake, and inspect the data in a read-only state first (recovery_target_action = 'pause') so you can move the target if you are off. Recovery time depends on how much log has to be replayed, which is why base backups should be taken at least weekly. The log lines shown are illustrative of the messages.

MySQL's version

mysqlbinlog --start-datetime="2026-11-01 02:00:00" --stop-datetime="2026-11-03 14:02:00" /var/lib/mysql/binlog.0000* | mysql -u root -p

MySQL restores a physical or logical backup and then replays binary logs with mysqlbinlog up to a time or position. The principle is the same; enable binary logging with a retention period and copy the logs off the host. A --stop-position is more exact than a time when you know the offending statement's position.

When you need it

You need it when the business cannot accept losing up to a day of data (orders, payments, user content), when mistakes like a bad migration or deletion are plausible and need an undo, or when compliance demands a fine-grained recovery. You may not need it for a blog database that changes rarely, a read-mostly store that can be rebuilt, or when a nightly dump meets the recovery point objective. Managed databases usually provide it as a checkbox with a retention window; verify the window and test a restore, as in a restore drill.

The pieces you need to keep safe

Point-in-time recovery depends on an unbroken chain: a base backup, followed by every log segment generated since. Lose one segment in the middle and replay stops there, so recovery to any later time becomes impossible from that base. This makes the archive as important as the base backup, and it deserves the same care: store it away from the database server, on storage with its own redundancy, encrypted if the data requires it, and with monitoring for gaps. A failing archive command is a silent threat, because the database keeps running while segments pile up on its own disk until it fills and stops.

Monitor the archiver. PostgreSQL exposes counters of archived and failed segments in a statistics view, and an alert on any failure or on a growing backlog catches problems the same day. Alert on the age of the newest archived segment as well; on a busy database it should be a few minutes old at most, and an hour-old newest segment means the chain has stopped growing.

Choosing the recovery target

A target can be a time, a transaction identifier, a named restore point or a log position, and the choice depends on what you know. A time is easy but imprecise: the clock of the database server decides, and the mistake may have happened within a few seconds of your guess. A named restore point, created before risky operations with a function call, gives an exact target and costs nothing. Taking the habit of creating one before every migration turns a hard recovery into a trivial one.

After the replay stops, inspect the data before promoting the server. Recovery with a pause option leaves the server read-only at the target, so you can check that the deleted rows are back and that nothing newer is needed. If you stopped too early or too late, restart recovery with another target; promotion is the point of no return for that data directory. Extracting the missing rows from the recovered copy and loading them into the live system is often better than switching to the recovered database wholesale, since it keeps the legitimate changes made after the mistake.

Timelines and what happens after recovery

When a recovered server is promoted, PostgreSQL starts a new timeline so that its history does not conflict with the old one. Later recoveries must be told which timeline to follow, and archived segments from different timelines coexist in the archive. This is normal, and it explains the history files you will see there. Keep it in mind when testing, because repeated drills create several timelines, and the recovery target setting for the timeline decides which history is replayed.

Practising and sizing

Run a recovery to a point in time on a spare machine every quarter, with a chosen target, and measure the time. Replay speed depends on the volume of logs since the base backup, so frequent base backups shorten recovery. Retention is a trade-off: keeping a week of logs and two base backups allows recovery anywhere in the last week and costs the storage of both. Match the window to the longest time a mistake could go unnoticed, and make sure the archive's size growth is in the capacity plan.

Where this does not apply

PITR does not protect against losing the archive itself or the credentials to it; keep the archive on separate infrastructure. It also replays corruption: if a bad write happened at 14:02, you must pick an earlier target. Long histories take time to replay. It is a physical technique and so tied to the same major version. See the PostgreSQL continuous archiving documentation and pgBackRest.

Two further references are worth keeping at hand: the MySQL point-in-time recovery chapter and the guide to checking that a backup restores. A small team can combine them with scheduled database backups so that base backups and archive cleanup run without attention.