Point in Time Recovery for Postgres: How It Works

Point in time recovery (PITR) in Postgres restores a database to any chosen moment, not just to the time of the last backup. It works by combining a base backup (a physical copy of the data directory) with the write-ahead log (WAL): Postgres restores the base backup, then replays logged changes up to the target time and stops. If someone runs a bad delete at 14:05:30, you can restore to 14:05:29.

This guide explains how PITR works under the hood, shows the self-hosted setup, and then covers what managed PITR looks like and how to use it without making things worse.

#Why nightly dumps are not enough

A nightly pg_dump gives you up to 24 hours of data loss. If the bad write happened at 17:00 and your dump ran at 02:00, everything between 02:00 and 17:00 is gone or has to be rebuilt by hand.

PITR reduces that to seconds. Every committed change is recorded in WAL anyway (it is how Postgres survives crashes). Archive that WAL somewhere safe and you can rebuild the database to any point it covers.

#How point in time recovery works

1. Base backup. A physical copy of the cluster's data files, taken with pg_basebackup or a tool like pgBackRest. It does not need to be perfectly consistent on its own; WAL replay makes it consistent.
2. WAL archive. Postgres writes changes into 16 MB WAL segments. With archive_mode = on, each finished segment is handed to an archive_command that copies it to durable storage.
3. Recovery. You restore the base backup into a new data directory, tell Postgres where the archive is and what the target is, and start it. Postgres replays WAL until it reaches the target, then pauses or promotes.

The time it takes is roughly: copy the base backup + replay all WAL since that backup. That is why tools take frequent base backups. Replaying a week of WAL is slow.

#A self-hosted example

On the primary, in postgresql.conf:

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

Take a base backup:

pg_basebackup -D /backups/base-2026-11-02 -Ft -z -X stream -P

To recover to a point in time (Postgres 12 and newer), restore the base backup into a fresh data directory, then add to its postgresql.conf:

restore_command = 'cp /mnt/wal-archive/%f %p'
recovery_target_time = '2026-11-02 14:05:29+00'
recovery_target_action = 'pause'

Create an empty recovery.signal file in the data directory and start Postgres. With pause, the server stops at the target and lets you inspect the data read-only before you call select pg_wal_replay_resume(); to promote it. See the PostgreSQL docs on continuous archiving and PITR for every option.

In real deployments, use a maintained tool (pgBackRest, Barman, WAL-G) rather than cp. They handle compression, encryption, retention, parallel restore and verifying that the archive has no gaps. A gap in the WAL archive means you cannot recover past it.

#Things that trip people up

• Time zones. recovery_target_time is interpreted in the server's time zone unless you include an offset. Always include +00 or your offset.
• Finding the right moment. You rarely know the exact second. Look in application logs, the Postgres log, or audit tables for the bad statement, and pick a time just before it.
• Recovering in place. Restoring over production discards everything after the target, including good writes made since. Often it is better to recover into a separate instance and copy back only what you lost.
• Files outside the database. PITR covers the database. Object storage, caches and search indexes are not rewound with it.
• Untested archives. If you have never restored, you do not have backups. Schedule a restore drill.

#Managed PITR

Managed Postgres platforms run the archive and base backups for you and expose PITR as a button. What differs between them is the retention window, whether you can restore into a new instance, and whether you can recover a single table.

On StitchDB, every project has PITR, including Free. The window depends on the plan:

Every change is streamed off the project's server to a different machine as it happens, so a lost server loses at most the last few seconds. Backups are encrypted with AES-256. Details are on the Backups page.

#Three ways to restore

Restore to a point in time (in place). Rewinds the project's database to the second you pick. A backup of the current state is taken first and listed as Before a restore, so the restore itself can be undone. Changes after the chosen moment are lost.

Restore into a new branch. On Pro and up, you can choose Restore into: A new branch (see Preview branches). A new branch gets the database as it was at that moment, with its own URL and keys, and production is not touched. This is the safe default for investigations: look at the old data, copy back what you need. The branch counts toward your plan's branch limit.

Restore one table. Brings back a single table as it was at a moment in your window, without rewinding anything else. The copy lands in the _stitch_restore schema as <table>_<date>_<time>. Then you choose:

• Only restore the copy, and compare or copy rows yourself:

  SQLCopy

  insert into public.orders
  select * from _stitch_restore.orders_20261025_134501
  where id = 42;

• Put old rows back (needs a primary key): deleted rows come back and changed rows get their old values, while rows added since stay. You can limit it with a condition such as customer_id = 42.

• Replace all rows: the table gets exactly the rows of that moment; its rows from before are kept in _stitch_restore.

Putting rows back runs in one transaction as the database owner, so triggers and foreign keys apply and a failure changes nothing.

#For deletes: the recycle bin

For the most common accident, a bad delete, StitchDB also has a per-table recycle bin. Turn it on for a table and every deleted row is kept for your plan's retention (1 day on Free up to 30 on Enterprise), with who deleted it and when. Restoring is a click or one SQL call:

select id, deleted_at, deleted_by from _stitch_bin.rows
where tbl = 'public.orders'::regclass and restored_at is null
order by id desc limit 20;

select _stitch_bin.restore(array[41, 42]);

truncate and drop table are not captured by the bin; use Restore one table for those.

#What PITR does not rewind

Point-in-time recovery on StitchDB rewinds the database, not stored files. File records in the storage schema are rewound, so files uploaded after the target moment are no longer listed. Plan for that if your app stores uploads.

#A recovery runbook

When something goes wrong, the order of steps matters more than speed.

1. Stop the damage. Disable the job, revert the deploy, or revoke the key that is writing bad data. Restoring while the bug is still running wastes the restore.
2. Find the moment. Use logs, audit trails or updated_at columns to find when the bad change started.
3. Pick the narrowest tool. A few rows: recycle bin. One table: restore one table. Many tables: restore into a new branch and copy back. Whole database corrupted: in-place restore.
4. Restore into a copy first when you can. Check the data there.
5. Copy back only what you lost, inside a transaction.
6. Write it down. What happened, the timestamps, what you restored. Then fix the root cause.

On StitchDB, only administrators and owners can restore, run PITR or restore a table; developers can take backups and export. See Backups for the role table. For more on layering backups, read Postgres Backup Best Practices.

#FAQ

#How far back can I recover?

As far as your WAL archive and base backups go. On StitchDB that is 24 hours on Free and up to 30 days on Enterprise.

#Does PITR replace pg_dump?

No. PITR is for fast recovery on the same platform. A logical dump is a portable copy you control. Keep both.

#How long does a restore take?

It grows with database size and the amount of WAL to replay. StitchDB shows an estimate before you start; its large-database docs give a guide of a few minutes for a few GB and roughly an hour per 100 to 200 GB.

#Can I restore to a time before a table was dropped?

Yes, if the moment is inside your window. Restoring one table to just before the drop brings it back with its rows and indexes.

Every StitchDB project, including Free, gets point-in-time recovery. Create one or compare retention on the pricing page.