Point-in-Time Recovery (PITR)

• Two ingredients – PITR requires a physical base backup and an unbroken archive of WAL (or binlog) segments from that base onward.
• Recovery target – you set a target timestamp, transaction id, LSN, or named restore point, and the engine replays WAL stopping exactly there.
• Granularity – it rewinds the database to the exact moment before a bad event, not just to the last full backup.
• Critical for accidental deletes – full backups alone lose all work since their capture time, and replication faithfully copies mistakes.
• Side-instance recovery – for logical deletes, PITR into a separate instance, extract the lost rows, and merge them back.

What is Point-in-Time Recovery?

PITR restores a physical base backup and then replays the archived write-ahead log (or MySQL binlog) forward only up to a chosen target. It lets you rewind the database to the exact moment just before a bad event rather than only to the last full backup.

Nightly backup          WAL segments          Disaster
(midnight)              (continuous)          (2:05 PM)
    │                       │                     │
    ▼                       ▼                     ▼
  Base ────── WAL 1 ─ WAL 2 ─ WAL 3 ─ ... ─ WAL N
    │                                         │
    └── Restore base, replay WAL to 2:04 PM ──┘

Why is PITR critical for accidental deletes?

Full backups alone only restore to their capture time, so any work since then is lost. Replication faithfully copies the mistake to every replica. PITR targets the moment just before the bad transaction.

-- The accidental delete at National Bank of India
DELETE FROM transactions WHERE status = 'PENDING';
-- Oops! This deleted ALL pending transactions, not just the intended batch.
-- Replication copied the DELETE to every replica within seconds.
-- Only PITR can recover to the moment before this statement.

How do you set up continuous WAL archiving?

In PostgreSQL, configure archive_mode and archive_command to copy every WAL segment to durable storage.

-- postgresql.conf settings for PITR readiness
-- archive_mode = on
-- archive_command = 'aws s3 cp %p s3://nbi-wal-archive/%f'
-- wal_level = replica

# Take a base backup
pg_basebackup -D /backup/base -Ft -z -P

# Upload to off-site storage
aws s3 sync /backup/base s3://nbi-backups/daily/

How do you perform the actual recovery?

Restore the base into a fresh data directory, then configure a recovery target.

# Step 1: restore the base backup
mkdir /data/recovery
tar -xzf /backup/base/base.tar.gz -C /data/recovery

-- Step 2: set recovery target in postgresql.conf or recovery.conf
-- restore_command = 'aws s3 cp s3://nbi-wal-archive/%f %p'
-- recovery_target_time = '2026-10-25 14:04:00+05:30'
-- recovery_target_action = 'promote'

# Step 3: start the instance; it replays WAL and stops at the target
pg_ctl -D /data/recovery start
# The engine replays WAL segments in order, stopping at 14:04:00 IST

What recovery targets are available?

You can target by timestamp, transaction id, LSN, or named restore point.

-- Target by timestamp
recovery_target_time = '2026-10-25 14:04:00+05:30'

-- Target by transaction ID
recovery_target_xid = '12345678'

-- Target by named restore point (created before a risky operation)
SELECT pg_create_restore_point('before_migration_v42');
-- Later:
recovery_target_name = 'before_migration_v42'

Pankaj creates a named restore point before every risky migration at National Bank of India.

How do you recover specific rows without rewinding all of production?

For a targeted logical delete, PITR into a side instance, extract the lost rows, and merge them back.

-- On the side recovery instance (recovered to 14:04:00)
COPY (SELECT * FROM transactions WHERE status = 'PENDING')
    TO '/tmp/recovered_pending.csv' WITH CSV HEADER;

-- On the production instance
COPY transactions FROM '/tmp/recovered_pending.csv' WITH CSV HEADER;

This avoids rewinding all of production, which would lose every legitimate change after the target time.

What is the Recovery Point Objective and how does WAL affect it?

The RPO is bounded by how frequently and reliably WAL is archived. Any gap in the WAL archive breaks the replay chain.

package com.bank.monitoring;

public class WalArchiveMonitor {
    /**
     * Alert if the latest archived WAL segment is older than
     * the acceptable RPO threshold (e.g., 5 minutes).
     */
    public void checkWalArchiveLag(int maxLagMinutes) {
        // Query pg_stat_archiver for last_archived_time
        // Alert if now() - last_archived_time > maxLagMinutes
    }
}

What are the common mistakes with PITR?

• Not testing the recovery process: an untested backup is a hope, not a plan.
• Not monitoring WAL archive continuity: a gap breaks the chain.
• Not storing backups off-site: a disk failure can destroy both data and backups.
• Not creating named restore points before risky operations.

-- Monitor WAL archiving health
SELECT archived_count, failed_count, last_archived_wal,
       last_archived_time
FROM pg_stat_archiver;

What should a senior engineer know?

A senior engineer rehearses PITR regularly and measures the Recovery Time Objective.

• Keep base backups and WAL in durable off-site storage with encryption.
• Use named restore points before risky operations.
• Monitor archive continuity and alert on gaps.
• Rehearse PITR into a scratch environment quarterly.
• For MySQL, the equivalent uses mysqlbinlog with --stop-datetime to replay binlogs to a target.