PostgreSQL point-in-time recovery, two ways

Why nightly backups aren't enough

A nightly dump means a bad deploy at 6 PM can cost you a full day of writes. Point-in-time recovery (PITR) shrinks that window — your recovery point objective — from 'last night' to minutes, or to the exact second before the mistake.

Tier 1 — minute-level snapshots, zero setup

Schedule a backup every 5–15 minutes. That sounds expensive, but xbackupman hashes the plaintext of every dump during streaming: when nothing changed since the previous run, it stores a pointer instead of a duplicate artifact. An idle database at a 5-minute cadence costs one object per change, not 288 copies a day. Restoring is a picker: choose the moment, get the newest snapshot at or before it.

Tier 2 — to the exact second with WAL archiving

For PostgreSQL, the xbm server agent can archive every WAL segment, sealed with your organization's key before it leaves the machine. Configure postgres once:

wal_level = replica
archive_mode = on
archive_command = 'xbm pitr wal-push %p'
archive_timeout = 60   # a quiet database still ships a segment every minute

-- then take a weekly base anchor:
xbm pitr base --conn "host=127.0.0.1 user=postgres"

Restoring to a moment

One command materializes everything postgres needs — the newest base anchor before your target, every WAL segment since, a recovery.signal and the exact configuration to add. Recovery replays to your target time and stops right before the transaction you're escaping, then promotes to a new timeline.

xbm pitr restore --at "2026-07-23T15:04:05Z" --into /restore
# ...then start postgres on /restore/data — it replays and promotes.

The details that bite

Two things people learn the hard way: a transaction only reaches the archive when a later segment ships (set archive_timeout so quiet periods stay bounded), and recovery_target_time needs at least one archived commit past your target to prove it got there. xbackupman's tooling bakes both lessons in — the enable instructions set archive_timeout, and restore over-fetches segments so postgres always has what it needs.