How to Perform Point-in-Time Recovery in PostgreSQL

There’s one incident every database engineer eventually experiences: someone runs an UPDATE or DELETE without a WHERE clause against production, or a migration script has a bug that corrupts data, and the mistake gets committed before anyone notices. A plain nightly backup only gets you back to last night — everything since then, including the good data, is gone if you restore from it. Point-in-time recovery (PITR) is what saves you in that exact situation, letting you restore a database to the precise moment just before the mistake happened. I want to walk through how PITR actually works in PostgreSQL, how to set it up properly before you ever need it, and how to actually perform a recovery when the day comes.

How PITR Works, Conceptually

PostgreSQL writes every change to the database into the Write-Ahead Log (WAL) before it’s applied to the actual data files — this is what makes crash recovery and replication possible in the first place. PITR builds on this: if you have a base backup (a full copy of the database at some point in time) plus every WAL segment generated since that backup, you can replay those WAL records forward to reconstruct the database’s state at any point after the base backup — not just the moment the backup was taken, but any arbitrary timestamp, transaction ID, or named restore point in between.

This is fundamentally different from a simple pg_dump backup, which only captures a single snapshot. PITR requires continuous WAL archiving, which means it needs to be set up in advance — you cannot retroactively enable PITR after the incident has already happened.

Setting Up WAL Archiving

The first requirement is enabling WAL archiving in postgresql.conf.

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

wal_level = replica (or logical, if you also need logical replication) ensures enough information is written to WAL to support archiving and recovery. archive_command is run by PostgreSQL for every completed WAL segment, and its job is to copy that segment somewhere durable — outside the primary database’s own storage, ideally on a completely separate system or cloud storage.

The cp example above is intentionally the simplest possible illustration. In any real production setup, I use something more robust — copying to S3 (or equivalent object storage) via a tool like wal-g or pgBackRest, which handle compression, encryption, retry logic, and parallel uploads, none of which a bare cp command gives you.

archive_command = 'wal-g wal-push %p'

After changing these settings, PostgreSQL needs a restart (wal_level and archive_mode both require one).

Taking a Base Backup

With archiving running, you need a base backup to start from. PostgreSQL provides pg_basebackup:

pg_basebackup -h localhost -U replication_user -D /var/lib/postgresql/base_backup -Fp -Xs -P

• -Fp outputs plain format (a directory structure matching the data directory).
• -Xs streams the WAL needed to make the backup immediately consistent, alongside the base backup itself.
• -P shows progress, which I always want for anything that might take a while on a large database.

For any real production workload, I use pgBackRest or wal-g instead of raw pg_basebackup, because they handle incremental backups, retention policies, parallel compression, and integrity verification — things that matter a lot once your database is large enough that a full base backup takes hours.

A minimal pgBackRest example, once configured:

pgbackrest --stanza=main backup

I schedule base backups on a recurring basis (nightly or weekly, depending on database size and how much WAL replay time is acceptable during a recovery), because the recovery process has to replay every WAL segment since the base backup — the older the base backup, the longer recovery takes.

Performing the Actual Recovery

This is the part that only happens under pressure, which is exactly why I always practice it before I actually need it. Here’s the general shape of the process using pg_basebackup and manual recovery configuration (the exact commands differ slightly with pgBackRest or wal-g, but the concepts are identical).

Step 1: Stop PostgreSQL and Preserve the Current State

If the database is still running, stop it. If possible, I also make a copy of the current (post-mistake) data directory before touching anything, in case I need to go back to it or extract something from it later.

sudo systemctl stop postgresql

Step 2: Restore the Base Backup

Replace the data directory with a fresh copy of the base backup.

rm -rf /var/lib/postgresql/16/main/*
cp -R /var/lib/postgresql/base_backup/* /var/lib/postgresql/16/main/

Step 3: Configure Recovery Target

Create a postgresql.auto.conf (or, on older versions, a recovery.conf) specifying where to find archived WAL and, critically, the recovery target — the exact point you want to stop replaying at.

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-08-14 09:14:00'
recovery_target_action = 'promote'

There are several ways to specify the target, depending on what you actually know about the incident:

• recovery_target_time — restore to a specific timestamp. Useful when you know roughly when the mistake happened (e.g., from an application log or a Slack message timestamp).
• recovery_target_xid — restore to just before a specific transaction ID. Useful if you can identify the exact transaction that caused the problem.
• recovery_target_name — restore to a named restore point, created in advance with pg_create_restore_point('before_migration'). I create one of these manually before running any risky production migration specifically so I have a precise, unambiguous recovery target if something goes wrong.
• recovery_target_lsn — restore to a specific Log Sequence Number, useful when working from WAL inspection tools that report LSNs directly.

I almost always prefer recovery_target_time when working from an incident report, since “the bad query ran around 9:14 AM” is usually the information I actually have.

Step 4: Create the Recovery Signal File

PostgreSQL needs to know it should enter recovery mode rather than start up normally. In modern PostgreSQL (12+), this is done with a signal file rather than a recovery.conf file:

touch /var/lib/postgresql/16/main/recovery.signal

Step 5: Start PostgreSQL and Let It Replay

sudo systemctl start postgresql

PostgreSQL will start in recovery mode, apply the base backup, then replay WAL segments one by one from the archive, stopping exactly at the recovery target. With recovery_target_action = 'promote', it automatically promotes itself to a normal, writable primary once it reaches that point.

Step 6: Verify Before Trusting It

Before pointing the application back at this database, I always verify the data is actually in the state I expect — checking the specific rows or tables affected by the original incident, confirming the “bad” change isn’t present, and confirming the data I expect to still exist actually does.

SELECT * FROM orders WHERE id = <known_affected_row> ;

Only after that verification do I actually cut over traffic to the recovered instance.

Restoring to a Separate Instance First

One habit I’ve become strict about: never perform a PITR recovery directly onto what will become production without first restoring to a separate, isolated instance to verify the target point is correct. Recovery targets are easy to get subtly wrong — a timezone mismatch on recovery_target_time is a classic mistake, and discovering it only after promoting a live primary is a bad way to find out. I restore to a scratch instance first, verify the data looks right, and only then either promote that instance into service or replay the same recovery process against the real target.

Testing Your PITR Setup Before You Need It

The single biggest mistake I see teams make with PITR isn’t a misconfiguration — it’s never actually testing the restore process until the day they desperately need it, at which point they discover the archive command has been silently failing for three weeks, or the base backups have been corrupt, or nobody remembers the exact recovery procedure under pressure.

SELECT * FROM pg_stat_archiver;

I check this regularly — archived_count should be climbing steadily, and failed_count should stay at (or very near) zero. A climbing failed_count means WAL segments aren’t making it to the archive, which quietly breaks your entire PITR capability without any obvious symptom until recovery day.

Beyond monitoring, I schedule an actual recovery drill — restoring a real backup to a scratch environment and verifying it — on a recurring basis (monthly is reasonable for most teams), not just once when the backup system was first set up.

Common Use Cases

• Recovering from an accidental bad UPDATE/DELETE without a WHERE clause — the classic scenario.
• Rolling back a buggy migration that corrupted or destroyed data before anyone noticed.
• Forensic investigation — spinning up a copy of the database as it existed at a specific past moment to understand what happened, without touching production.
• Compliance and audit requirements that mandate the ability to reconstruct historical database state.
• Recovering from a bad deploy that included destructive schema or data changes.

Troubleshooting Tips

Recovery seems stuck / never reaches the target. Check that restore_command can actually find and read the archived WAL segments — a wrong path or missing permissions is the most common cause. Check the PostgreSQL log for restore command failures.

Recovery completes but the data isn’t what I expected. Double-check timezone handling on recovery_target_time — PostgreSQL interprets it based on the server’s configured timezone unless you specify one explicitly in the timestamp itself. I now always include an explicit timezone offset to avoid ambiguity.

pg_stat_archiver shows a climbing failed_count. Something is wrong with your archive destination — disk space, permissions, network connectivity to object storage, or a broken archive script. This needs to be fixed immediately, since every failure represents a WAL segment that isn’t safely archived, directly threatening your recovery capability.

Base backup takes too long, making recovery time unacceptable. Consider more frequent base backups to reduce the amount of WAL that needs replaying, or use a tool like pgBackRest that supports incremental and differential backups to reduce backup (and sometimes restore) time.

Best Practices

1. Set up WAL archiving and take your first base backup before you need PITR, not after an incident. It cannot be retroactively enabled to cover a mistake that already happened.
2. Use a proper backup tool (pgBackRest or wal-g) rather than hand-rolled scripts for anything beyond a toy environment.
3. Create named restore points before risky operations (pg_create_restore_point) so you have an unambiguous recovery target if something goes wrong.
4. Monitor pg_stat_archiver continuously, and alert on any nonzero or climbing failed_count.
5. Always restore to a scratch instance first to verify the recovery target before promoting anything into production.
6. Practice the full recovery process on a schedule, not just once during initial setup.
7. Store WAL archives and base backups off the primary server entirely, ideally in a separate region or provider, so a failure of the primary’s storage doesn’t take your backups down with it.
8. Be explicit about timezones in recovery target timestamps to avoid a subtle but critical mistake.

A Real-World Example: Recovering from a Bad Migration

Let me walk through an actual recovery I performed, since the theory only goes so far without seeing the real sequence of decisions. A migration script intended to backfill a new column instead ran an unqualified UPDATE against a related table due to a bug in a conditional, overwriting a status column across roughly two million rows around 2:47 PM. It was noticed about twenty minutes later when downstream reports started showing obviously wrong numbers.

Here’s what I actually did, in order:

First, I confirmed the exact scope of the damage before touching anything, using the application’s own logs and the migration’s own execution timestamp, so I had a precise target rather than a rough guess. I also immediately created a named restore point for future reference, in case I needed a “post-damage but pre-any-further-changes” marker:

SELECT pg_create_restore_point('post_incident_investigation');

Second, rather than recovering the primary database directly, I spun up a completely separate scratch instance from the most recent base backup plus archived WAL, since I wasn’t yet fully certain of the exact moment to target.

restore_command = 'wal-g wal-fetch %f %p'
recovery_target_time = '2026-08-14 14:46:30-05'
recovery_target_action = 'promote'

I intentionally set the target a few seconds before the earliest plausible time the bad UPDATE could have started, since it’s always safer to recover slightly too early and lose a few seconds of legitimate writes than to recover even one second too late and re-include the damage.

Third, once that scratch instance came up, I queried the affected rows directly to confirm the status values matched what they should have been before the incident:

SELECT id, status FROM subscriptions WHERE id IN (<sample of known affected ids>);

Fourth, with the target confirmed correct, rather than cutting the whole production database over to this recovered instance (which would also have discarded roughly twenty minutes of otherwise legitimate writes from unrelated tables and unrelated users), I instead exported just the affected table’s correct data from the recovered instance and used it to selectively repair the production table, inside a transaction, with a full backup of the pre-repair state taken first:

-- on the recovered scratch instance
COPY (SELECT id, status FROM subscriptions) TO '/tmp/correct_status.csv' CSV;

-- on production, after copying the file over
CREATE TEMP TABLE status_fix (id INT, status TEXT);
COPY status_fix FROM '/tmp/correct_status.csv' CSV;

BEGIN;
UPDATE subscriptions s
SET status = f.status
FROM status_fix f
WHERE s.id = f.id AND s.status IS DISTINCT FROM f.status;
COMMIT;

This selective-repair approach — recovering to a scratch instance, extracting just the correct data for the affected rows, and merging it back into a production database that otherwise kept running — avoided both extremes: a full, disruptive cutover that would have lost unrelated legitimate activity, and doing nothing while incorrect data lingered. It’s not always the right approach (sometimes a full cutover genuinely is correct, especially for a database-wide corruption event), but for a scoped, well-understood mistake affecting a specific table, it minimized collateral damage considerably.

Frequently Asked Questions

How much WAL storage should I budget for? Enough to cover the time between base backups, plus a safety margin. If I take a base backup nightly and generate roughly 20GB of WAL per day, I keep at least a few days’ worth of margin in archive storage in case a base backup fails and I need to fall back to an older one, which means replaying more WAL than usual.

Can I do PITR without a base backup, using only WAL? No — WAL records describe changes relative to a starting state; without a base backup to apply them on top of, there’s nothing for the WAL to reconstruct from. A base backup is a hard requirement.

Does PITR work the same way with managed database providers (RDS, Cloud SQL, etc.)? The underlying WAL-based mechanism is similar, but managed providers usually wrap it in their own console/API rather than exposing raw restore_command configuration — check your provider’s specific PITR documentation, since the exact steps and retention limits vary significantly between providers.

How far back can I recover? However far back your retained WAL archive and base backups go — this is a retention policy decision you set deliberately, balancing storage cost against how far back you might realistically need to recover from.

Wrapping Up

Point-in-time recovery is the feature that turns “someone ran a destructive query in production” from a company-ending event into an unpleasant but survivable afternoon. The setup — WAL archiving, regular base backups, a tested restore procedure — takes real, deliberate effort well before you ever need it, and it’s exactly the kind of infrastructure work that’s easy to deprioritize until the day it’s the only thing standing between you and permanent data loss. Set it up early, monitor it continuously, and actually practice a recovery before you’re forced to do one for real.