Point-in-time recovery for Postgres: what it actually buys you

Your app corrupted a table at 2:47 in the afternoon. Your last backup ran at midnight. So restoring it throws away everything your users did in those 15 hours. That gap is the difference between a daily snapshot and point-in-time recovery, and most people don’t learn it until the bad day.

Why this happens

A daily snapshot is a photo of your database taken once a night. It’s useful, but it only knows one moment: whenever the snapshot ran. If a bad deploy at 2:47pm deletes half your orders table, your choices are ugly. Restore last midnight and lose the whole day, or keep the broken data and clean it up by hand.

Point-in-time recovery, or PITR, works differently. It doesn’t just save photos. It saves the running commentary of every change your database makes, then lets you replay that commentary up to any second you name.

The commentary is called the write-ahead log, or WAL. Here’s the part worth understanding. Before Postgres changes an actual table on disk, it first writes a small record of that change to the WAL. “Row 4,812 in orders is about to be deleted.” Only after that record is safely written does the real change happen. That ordering is why it’s called write-ahead. The log is written ahead of the data.

Postgres does this for its own crash safety. If the server loses power mid-write, it reads the WAL on restart and replays anything that didn’t finish. PITR takes that same mechanism and points it at recovery. You keep one base backup, plus every WAL record since. To recover, Postgres loads the base backup and replays the WAL forward, record by record, and stops exactly where you tell it to stop. Want the state at 2:46:59pm, one second before the bad deploy? You can have it.

So PITR buys you two things a snapshot can’t. A recovery point measured in seconds instead of hours. And a much longer history, because WAL is small and cheap to keep.

How to fix it

If you run your own Postgres, pgBackRest is the standard tool. There are two halves: capture the WAL continuously, and take a base backup on a schedule.

Step 1: tell Postgres to archive its WAL

In postgresql.conf, turn on archiving and hand each finished WAL file to pgBackRest:

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica

%p is the path to the WAL file Postgres just finished. The archive_command runs once per file. If it fails, Postgres holds the file and retries, so you never silently lose a segment. Restart Postgres after changing these.

Step 2: create the stanza and a base backup

# One-time: initialize the backup repository
pgbackrest --stanza=main --repo1-path=/var/backups/pgbackrest stanza-create

# Weekly: full base backup
pgbackrest --stanza=main --type=full backup

# Nightly: incremental (only what changed since the last backup)
pgbackrest --stanza=main --type=incr backup

The base backup is your starting photo. The archived WAL is everything that happened after it. Together they let you land on any second between the base backup and now.

Step 3: restore to an exact time

This is the whole point. You pass --type=time and a target timestamp. Always restore into a fresh, separate directory. Never restore on top of your live database.

# Recover the state as of one second before the bad deploy
pgbackrest --stanza=main --type=time \
  "--target=2026-07-16 14:46:59" \
  --target-action=promote \
  --pg1-path=/var/lib/postgresql/restore-test \
  restore

Here’s what happens under the hood. pgBackRest lays down the most recent base backup taken before your target time. Then Postgres starts in recovery mode, opens the archived WAL, and replays each record in order. As it replays, it checks the commit timestamp of every transaction. The moment it reaches a commit later than 14:46:59, it stops, throws away anything past that line, and promotes the database to normal running. --target-action=promote is what tells it to open for writes once it lands.

Start Postgres against /var/lib/postgresql/restore-test, run a query for a row you know existed at 2:46pm, and confirm the bad deploy’s damage isn’t there. That copy is now a clean source you can pull the lost rows from, without touching production.

Step 4: confirm you can actually reach a given time

WAL only helps if it’s all there. One missing segment and replay stops early. Check your archive is complete and gap-free:

pgbackrest --stanza=main check
info --stanza=main

The info output shows your oldest and newest recoverable point. That range is your real recovery window. If it says 3 days but you thought you had 30, better to learn it now.

For the full backup setup including off-server storage and restore drills, see the backup and restore tutorial. PITR is the recovery model; that post covers getting the copy off the machine and testing it end to end.

Or, we do it for you

Every Stackbastion plan runs PITR by default, with a real restore drill so the recovery window is a tested number, not a hope. Want to know what your current setup can actually recover to? Get a free audit.

FAQ

How much extra storage does WAL archiving need?

Less than people expect. WAL volume tracks how much your data changes, not how big the database is. A small app often generates a few hundred megabytes a day, and it compresses well. pgBackRest can expire old WAL automatically once it’s past your retention window.

What’s the smallest gap PITR can restore to?

One second, using --type=time. If you need to stop at an exact transaction instead, Postgres also supports recovering to a named transaction ID or a restore point you set yourself. For most recoveries, a timestamp one second before the incident is plenty.

Does Lovable or Supabase give me PITR out of the box?

Supabase offers PITR on paid plans, but you have to turn it on and it isn’t the default. Lovable Cloud’s built-in backups are daily snapshots with a fixed retention window, not second-level recovery. If your data matters, don’t assume it’s on. Check.

Can I test recovery without a spare server?

Yes. Restore into a separate data directory on the same machine, or into a throwaway container. The restore doesn’t touch your live data directory as long as --pg1-path points somewhere else. Run it once a month and you’ll never be surprised by a broken backup.