Postgres Point in Time Recovery, Explained and Set Up

At 14:02 someone runs an UPDATE without a WHERE clause. By 14:07 the support inbox is filling up. Your last backup finished at 03:00.

You can restore that backup and lose eleven hours of orders, sign-ups and edits. Or you can restore to 14:01 and lose about a minute. The second option needs a different kind of backup, and it needs to be in place before the mistake.

Postgres point in time recovery is that second option. It is built into Postgres, it is free, and the moving parts are fewer than people expect. The catch is that it fails quietly if you set it up once and never test it.

Think of two things kept side by side. The first is a photograph of the whole database at one moment. Postgres calls it a base backup. The second is a diary of every change made afterwards, page by page, in order. Postgres calls it the write-ahead log, or WAL.

Every change is written to the WAL before it reaches the data files. That is how Postgres survives a crash. Continuous archiving simply copies each finished WAL segment somewhere safe as well. To recover, you unpack the photograph, then replay the diary forward until you reach the moment you want and stop.

• Base backup: a file-level copy of the whole cluster, taken at a known moment.
• WAL archive: every completed WAL segment since, copied off the machine.
• Recovery target: the stopping point. A time, a transaction ID, a named restore point or the end of the log.

The PostgreSQL manual states the one hard rule plainly: you need a continuous sequence of archived WAL files reaching back at least as far as the start of your backup. One missing segment ends the replay at that gap.

A pg_dump is a logical export: SQL or a custom archive that recreates your tables and rows. It is portable, small and easy to inspect. It also records exactly one moment, which is the moment the dump started. Our guide to backing up and restoring a PostgreSQL database covers dumps properly, and they are still worth having.

The two are not interchangeable, and a dump cannot feed postgres point in time recovery. The manual says why:

A dump cannot be the photograph, because WAL replay needs the physical files. So keep both if you can. Dumps are for moving data between versions or machines. A base backup plus WAL is for getting back to a moment.

Recovery point objective, RPO, is how much recent data you can afford to lose. Recovery time objective, RTO, is how long you can afford to be down while you restore. Neither is a setting. Both come from your choices, so say them out loud before you pick tools.

With postgres point in time recovery, the loss window is the time since the last WAL segment was archived. A segment is 16MB by default and is archived when full, so a quiet database can go a long while between segments. The archive_timeout setting forces a switch after a set number of seconds. Set it to 60 and the archive is at most about a minute behind. The cost is more, smaller files.

Worked example, so you can check the arithmetic. A 200GB database restored over a disk that reads at 200MB per second needs 1,000 seconds to copy, which is about 17 minutes. Replay is extra, and it depends on how much WAL has built up since the base backup. Measure it on your own data, not on numbers from a blog.

Three settings in postgresql.conf, and a restart because two of them need one. wal_level must be replica or higher, archive_mode must be on, and archive_command must copy a finished segment somewhere that is not the same disk.

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/archive/%f && cp %p /mnt/archive/%f'
archive_timeout = 60

The detail that catches people is the exit status. The archive command must return zero if and only if the file was really archived, because Postgres then removes or recycles the segment. A command that returns zero after a failed upload throws away history you needed.

Then take the photograph. pg_basebackup is the tool the manual calls the easiest way to do it.

pg_basebackup -h db.internal -U replicator -D /backups/base-2026-09-24 -Ft -z -P

Check that segments are reaching the archive. The pg_stat_archiver view shows the last archived file and the last failure. If failed_count is climbing, the disk that holds pg_wal will fill up, because Postgres keeps every segment it cannot archive.

Do this on a spare machine first, never on the only copy of your data. The steps follow the PostgreSQL manual, and the target time below is only an example.

1. 1Stop the server and keep what is leftShut down Postgres on the machine you are restoring to. If the old data directory is damaged but readable, move it aside rather than deleting it. You may want its pg_wal for the last few seconds.
2. 2Unpack the base backupExtract the base backup into an empty data directory. Give it mode 0700 and the right owner, or Postgres will refuse to start. Empty the pg_wal directory if the backup left files there.
3. 3Tell Postgres where the archive isIn postgresql.conf set restore_command, for example cp /mnt/archive/%f %p. It will be asked for files that do not exist, and it must return a nonzero status when they are missing. That is normal, not an error.
4. 4Set the recovery targetSet recovery_target_time to just before the mistake, with an explicit offset: '2026-09-24 14:01:00+00'. Leave recovery_target_action at its default of pause, so you can look before you commit. The target must be later than the moment the base backup finished.
5. 5Create recovery.signal and startCreate an empty file called recovery.signal in the data directory and start the server. Postgres replays WAL from the archive and stops at your target. The log says which time it reached.
6. 6Check the data, then end recoveryConnect and query the tables you care about. If you stopped too early or too late, shut down, change the target and start again. If it is right, run SELECT pg_wal_replay_resume(). Postgres promotes itself, removes recovery.signal and opens for writes on a new timeline.
7. 7Extract what you need, or cut overUsually the restored server is a source. Dump the damaged rows and load them into production. Replace production wholesale only if the damage is that wide.

The retention window in postgres point in time recovery is how far back you can aim a recovery target. It is set by the oldest base backup you keep, plus every WAL segment from that backup forward. Delete a segment in the middle and everything after it is unreachable from that backup.

For postgres point in time recovery, pick the window by asking how long a mistake stays hidden. A bad deploy is found in an hour. A slow data-corruption bug can take a fortnight. Seven days covers most teams. Storage is cheap next to finding a problem on day eight.

Keep at least two base backups, so one bad one does not empty the window. Prune WAL only up to the start of the oldest base backup you still hold.

An archive that has never been replayed is a guess. Put a restore of your postgres point in time recovery archive on a schedule: fetch the latest base backup into a scratch instance, replay to a recent time, run a few queries you know the answer to, and record how long the whole thing took.

That last number is your real RTO, and it grows with the database. Write it down each time, so drift shows up on a calm Tuesday, not during the incident.

• Restore the latest base backup and replay to a time an hour ago.
• Compare a row count and the newest timestamp in a busy table against production.
• Check the archive for gaps: every segment name should follow the last with no jump.

Everything above is work a managed host can take on, and postgres point in time recovery is a common paid feature. Supabase documents that its recovery option combines daily physical snapshots with continuous WAL archiving, and that in the worst case its recovery point is two minutes. It is a paid add-on: $100 a month for seven days of retention, per its pricing page when this was checked.

On Helm7, Builder keeps daily backups for 7 days, and point-in-time recovery is included from Pro, at $29 a month. Restores are an API and MCP operation today rather than a CLI command. The pricing page lists exactly what each plan includes. If you run Postgres yourself, the settings above are all there is, and our piece on self-hosting Supabase shows what its stack leaves for you to back up.