Point-in-Time Recovery for a Single Shard

Point-in-time recovery turns “we lost data at 14:05” into “restore to 14:04”. This guide sets up the archiving that makes it possible, performs a recovery to an exact target, and rejoins the shard to a fleet that has moved on in the meantime. It implements the physical layer from Backup & Restore for Partitioned Databases, part of Shard Migration & Rebalancing Operations.

Prerequisites

Step 1 — Archive WAL so that a gap is impossible

The single property that makes PITR real is an unbroken WAL chain:

# postgresql.conf on every shard
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=shard3 archive-push %p'
archive_timeout = 60          # close a segment at least once a minute on quiet shards
max_wal_size = 4GB
-- the archiver's own health, which is not the same as the database's
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count,  last_failed_wal,   last_failed_time
FROM   pg_stat_archiver;
 archived_count | last_archived_wal        | last_archived_time     | failed_count
----------------+--------------------------+------------------------+--------------
        1284391 | 0000000100000A2C000000FE | 2026-08-03 09:41:02+00 |            0

Operational note: archive_command failing does not stop the database. PostgreSQL retries and keeps the segment, so the disk fills slowly while the backup silently becomes unusable. failed_count rising is an emergency, not a warning.

DBA tip: archive_timeout matters on low-traffic shards. Without it, a shard writing little WAL may leave the current segment unarchived for hours, and that period is unrecoverable.

The recoverable window, and what a WAL gap does to it Weekly base backups with continuously archived write-ahead log segments make every moment between the oldest retained base backup and now recoverable. A gap in the archived segments truncates that window: everything after the gap is unreachable from the earlier base backup, so the recoverable range restarts only at the next successful base backup. basebasebasebase week 1week 2week 3week 4 continuous WAL — every second between the first base backup and now is a valid recovery target gap recoverable from base 2 unreachable from base 2 — the window restarts at base 3 A gap does not announce itself: the database is healthy, backups keep being taken, and the loss is discovered only when someone tries to recover across it — which is why archiver failures must page immediately.

Step 2 — Take base backups on a cadence matched to replay time

Base backup frequency is a recovery-time decision, not a storage one. Every hour of WAL between the base backup and the target is an hour of single-threaded replay:

# weekly full, daily differential — a common shape for a 1.2 TB shard
pgbackrest --stanza=shard3 --type=full backup      # Sundays
pgbackrest --stanza=shard3 --type=diff backup      # Monday–Saturday

pgbackrest --stanza=shard3 info
stanza: shard3
    status: ok
    db (current)
        full backup: 20260726-020103F
            timestamp start/stop: 2026-07-26 02:01:03 / 2026-07-26 03:14:52
            database size: 1.2TB, backup size: 1.2TB
        diff backup: 20260802-020104F_20260802-020104D
            database size: 1.2TB, backup size: 88GB

Operational note: WAL replay is single-threaded and roughly linear in volume. A shard writing 40 GB of WAL a day needs a daily differential if the recovery objective is under two hours; a weekly-only cadence puts six days of replay in the worst case.

DBA tip: Take base backups from a replica where the tool supports it. It removes the I/O cost from the primary entirely, at the price of a slightly older starting point.

Step 3 — Recover to an exact target

Route traffic away from the shard first, then recover:

Four ways to say where recovery should stop A time target is approximate and suits "before the deploy at 14:05". A transaction id target is exact and suits a known offending transaction. A named restore point is exact and requires having created it beforehand, which makes it ideal ahead of a risky migration. The latest target replays everything and suits hardware loss with no corruption to avoid. TargetPrecisionRequiresSuits recovery_target_time±1 transactiona clock you trust"before the 14:05 deploy" recovery_target_xidexactthe offending xida known bad transaction recovery_target_nameexactcreating it in advancebefore a risky migration latesteverythingnothinghardware loss, no corruption The third row costs one function call before any risky change and converts a stressful guess about timestamps into an exact target — which is why `pg_create_restore_point` belongs in every migration runbook. 
# 1. stop the damaged instance and preserve its data directory
systemctl stop postgresql
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken

# 2. restore with an explicit target
pgbackrest --stanza=shard3 \
  --type=time --target='2026-08-03 14:04:00+00' \
  --target-action=promote \
  restore

systemctl start postgresql
-- watch replay progress; is_in_recovery flips to false at the target
SELECT pg_is_in_recovery(),
       pg_last_wal_replay_lsn(),
       pg_last_xact_replay_timestamp();
 pg_is_in_recovery | pg_last_wal_replay_lsn | pg_last_xact_replay_timestamp
-------------------+------------------------+-------------------------------
 t                 | A2C/FE118820           | 2026-08-03 13:47:11.204+00
Target type Setting Use when
time --type=time --target='…' “before the deploy at 14:05”
transaction id --type=xid --target='918442' the offending transaction is known exactly
named restore point --type=name --target='pre-migration' created deliberately with pg_create_restore_point before a risky change
latest --type=default hardware loss, no data corruption to avoid

Operational note: --target-action=promote ends recovery at the target and opens the database for writes on a new timeline. pause stops and waits, which is the safer choice when you want to inspect before committing to the point.

SRE tip: Create a named restore point before every migration and every risky bulk operation. It costs one function call and turns a timestamp guess into an exact target.

Recovery creates a new timeline; the original is abandoned but retained The original timeline continues past the recovery target with the damaging transactions. Recovery replays up to the target and then promotes onto a new timeline, so subsequent writes diverge from the original. The original timeline's WAL is retained, which is what makes it possible to recover to a different target later if the first choice was wrong. timeline 1 — normal operation recovery target: 14:04:00 timeline 2 — recovered, now serving timeline 1 continues in the archive only contains the damaging transactions; retained, never replayed 14:05 — the DELETE that started this Keep the old timeline's WAL. If the target turns out to be a minute too early, recovering again is possible only while it exists.

Step 4 — Reconcile and rejoin the fleet

The recovered shard is now behind its siblings. Two things must happen before traffic returns:

-- 1. cross-shard operations that were in flight at the recovery target
SELECT saga_id, state, updated_at
FROM   saga_instance
WHERE  updated_at > '2026-08-03 13:00:00+00'
  AND  state IN ('running','compensating');

-- 2. events that were written to the outbox and lost by the rewind
SELECT count(*) FROM outbox WHERE created_at > '2026-08-03 14:04:00+00';

Anything the shard delivered before the recovery point but no longer remembers delivering will be delivered again — which is safe if consumers are idempotent, and is exactly why they must be.

Operational note: Update the shard map only after reconciliation. A shard that rejoins early serves stale data to reads that route to it, and those reads look successful.

SRE tip: Compare the recovered shard’s newest partition bound against its siblings. A rewind past a partition-creation job means the shard is missing future partitions its neighbours have, and inserts will fail at midnight rather than immediately.

Verification

Prove the target was hit and the data is what you expect:

SELECT pg_is_in_recovery() AS still_recovering,
       (SELECT max(occurred_at) FROM events) AS newest_row,
       (SELECT count(*) FROM events WHERE occurred_at > '2026-08-03 14:04:00+00') AS after_target;
 still_recovering |        newest_row         | after_target
------------------+---------------------------+--------------
 f                | 2026-08-03 14:03:58.91+00 |            0

No rows after the target, recovery finished, and the application’s smoke tests pass against the recovered instance before any traffic is routed to it.

Failure mode table

Failure mode Root cause SRE mitigation
Recovery stops short of the target a WAL segment between the base backup and the target was never archived alert on pg_stat_archiver.failed_count and on last_failed_time > last_archived_time; treat archiving failure as a page
Recovery takes far longer than expected too much WAL between the base backup and the target, replayed single-threaded shorten base-backup cadence; measure replay throughput and derive the cadence from the recovery objective
Recovered shard serves stale data to the fleet traffic was routed back before reconciliation, or the shard map was updated automatically on startup keep the shard out of the map until reconciliation completes; make rejoining an explicit operator action

FAQ

How do I choose a recovery target?

Prefer a transaction id or a named restore point over a timestamp when you know the offending transaction, because timestamps are ambiguous under clock skew and concurrent commits. recovery_target_time is fine for “before the deploy at 14:05”, recovery_target_xid for “just before transaction 918442”, and recovery_target_name for a point you created deliberately with pg_create_restore_point.

What happens to the other shards during a single-shard recovery?

They keep serving, which is the advantage of sharding and also the complication. The recovered shard rejoins the fleet at an earlier moment than its siblings, so any cross-shard operation in flight at that moment needs reconciliation. Route traffic away from the shard while it recovers, and run the reconciliation before routing traffic back.

Why did recovery stop before reaching my target?

Almost always a WAL gap: a segment between the base backup and the target was never archived, so replay cannot continue past it. The archiver failing while the database keeps running is the usual cause, and it is silent unless pg_stat_archiver is monitored. Once a gap exists, the recoverable window ends at the gap regardless of how much WAL was archived afterwards.

Back to Backup Restore For Partitioned Databases