Backup & Restore for Partitioned Databases

Backing up one database is a solved problem. Backing up a fleet of shards, each carrying hundreds of partitions, with a recovery time that someone has actually measured, is not. This topic sits under Shard Migration & Rebalancing Operations and covers the two layers a partitioned fleet needs — physical backups per shard and logical dumps per partition — plus the consistency and timing questions that only appear at this scale.

Problem Framing

A platform runs eight shards, each about 1.2 TB, each partitioned monthly with 25 months of retention. Nightly pg_dump was configured when there was one database of 200 GB. It now takes eleven hours per shard, runs them in sequence, and finishes some time the following afternoon.

Nobody has restored from it. When a support request asks to recover one customer’s data from March, the only available path is to restore an entire 1.2 TB logical dump into a scratch server — a process that takes most of a working day and consumes the same storage again.

The design has two independent problems. The backup mechanism is wrong for the size, and the restore granularity is wrong for the request. Partitioning makes both fixable, because a partition is a natural unit of both backup and restore.

Two Layers, Two Purposes

The physical layer is the primary recovery mechanism: a base backup plus continuously archived WAL, per shard, restoring the whole shard to an arbitrary point in time. Tools such as pgBackRest or WAL-G handle this and should be configured identically on every shard so the runbook is the same everywhere.

The logical layer falls out of the retention job described in partition lifecycle and retention management. Each partition, as it ages out of the hot window, is dumped once and never changes again — which makes it the cheapest possible unit of long-term recoverability.

Cross-Shard Consistency

Eight shards produce eight independent backups, each internally consistent as of a slightly different instant. Whether that matters depends on one question: can a single business operation leave a durable trace on two shards?

If the answer is no — every transaction is shard-local, cross-shard effects go through an outbox with at-least-once delivery — then independent backups are fine, and recovery is a per-shard operation.

If the answer is yes, restoring all shards to “the same” moment is impossible without a coordinated snapshot, and the recovery plan must include a reconciliation pass:

-- after a fleet-wide restore: find operations whose halves disagree
SELECT o.saga_id, o.state, o.updated_at
FROM   saga_instance o
WHERE  o.state IN ('running','compensating')
  AND  o.updated_at < now() - interval '1 hour';

Naming this explicitly in the disaster recovery document is worth more than any tooling, because the alternative is discovering it during the recovery.

Restore Time Is the Real Objective

Backups are measured by how fast they can be undone, and that number is almost always worse than the document claims. Measure it rather than estimating it:

The last two rows are the same data with a different runbook. Fleet recovery must be parallel by design — one restore worker per shard, started simultaneously — and that only works if the runbook, the credentials and the target capacity all exist ahead of time.

What to Verify, and How Often

A backup that has never been restored is a hypothesis. Three checks, at three frequencies, turn it into a fact:

# daily: does the backup exist, and is its metadata sane?
pgbackrest --stanza=shard0 info --output=json | jq '.[0].backup[-1] | {label, timestamp: .timestamp.stop, size}'

# weekly: restore one partition dump into a scratch database and checksum it
pg_restore --dbname=verify_scratch /archive/events_2025_06.dump

# quarterly: restore a full shard to a point in time and run the application's smoke tests
pgbackrest --stanza=shard0 --type=time --target='2026-08-01 12:00:00+00' restore

The quarterly test is the one that finds real problems — an expired credential, a WAL archive gap, a base backup taken from a replica that had fallen behind, a restore target with too little disk. None of these appear in the daily check.

Sizing the Backup Window Against the Write Rate

A backup schedule is usually inherited from a smaller system and never revisited, which is how an eleven-hour nightly job appears in a fleet that expected a two-hour one. Three numbers, measured rather than estimated, determine whether a schedule is still viable.

Write volume per shard per day. This drives WAL archive size and, more importantly, replay time during recovery. It is available directly from the archiver:

SELECT archived_count,
       pg_size_pretty(archived_count::bigint * 16 * 1024 * 1024) AS wal_archived,
       stats_reset
FROM   pg_stat_archiver;

Base backup duration and its effect on the primary. A full backup is a sustained sequential read of the entire data directory. Whether it can run during business hours is a property of the storage, not of the tool, and the honest test is to run one during peak and watch p99 latency.

Restore throughput from the object store. This is the number nobody measures and the one that sets the recovery objective. Download bandwidth from object storage to a fresh host is frequently the binding constraint, and it varies by an order of magnitude between providers, regions and instance types.

With those three, the schedule follows mechanically. Base-backup frequency is chosen so that worst-case replay stays inside the recovery objective; incremental or differential cadence fills the gap between full backups; and retention is chosen so the oldest recoverable point is at least as old as the oldest incident you would want to recover from — commonly two weeks, occasionally much longer for regulated data.

Backup retention is not data retention

These two windows are routinely conflated, and the consequence is a partition that was dropped by the retention job and is no longer inside any backup. The retention job deleted it deliberately; the backup window simply moved past it. Nothing is wrong, and the data is gone.

Keeping them explicitly separate makes the gap visible:

The archive layer described elsewhere in this section exists precisely to cover the space between the first two rows. A fleet that has physical backups and no per-partition archives can answer questions about last week and nothing about last year — which is the opposite of what most compliance requests actually ask.

Rehearse the request you actually receive

Most recovery requests are not “the whole fleet is gone”. They are “customer X says their March data is wrong”, “a bad deploy corrupted a day of rows”, or “legal needs the state of these accounts as of a date”. Each is answered by a different layer, and a team that has only rehearsed whole-fleet recovery will improvise the other three under time pressure. Write down the three most likely requests, rehearse each once, and record how long it took — that record is worth more than any document describing the theory.

Backups of the shard map are not optional

One small table decides where every row lives, and it is usually stored somewhere different from the data — a coordinator database, an etcd cluster, or a configuration repository. It is also the one artefact whose loss makes every other backup useless, because a restored shard whose identity nobody knows is a terabyte of unattributable rows.

Include it in the same schedule as the data, with the same verification, and keep more history than the data warrants: a shard map from six months ago is small, and it is the only way to interpret an archive taken at that time. Version each change with the timestamp it took effect, so a restore to a past moment can be paired with the routing that was live then.

The same argument applies to the partition policy table, the archive log and any directory that maps tenants to shards. Together they are a few megabytes, and without them a fleet-wide recovery becomes a forensic exercise rather than a procedure.

Who runs the recovery

The last piece of a backup design is the least technical: recovery is performed by whoever is on call, at an hour they did not choose, under pressure, against a system they may not have built. That constraint should shape the tooling more than it usually does.

A recovery runbook that assumes familiarity with the backup tool’s flags will be executed slowly and wrongly. The version that works is a single script per scenario — restore a partition, restore a shard to a time, rebuild the fleet in staging — each taking the smallest possible number of arguments and printing what it is about to do before doing it. The drills described in this section exist as much to keep those scripts working as to prove the backups are valid.

Two habits make the difference in practice. First, the person who runs the quarterly drill should be someone who did not write the tooling, rotating through the team; every round of that rotation finds an assumption the author did not know they had made. Second, the drill’s output — commands run, timings, surprises — belongs in the runbook itself, so the document describes what actually happens rather than what was intended.

Failure Modes

Common Mistakes

• Treating pg_dump as a backup strategy at terabyte scale. It is a logical export with no point-in-time capability and a restore time measured in hours.
• Backing up the coordinator and forgetting the shards. The shard map is tiny and critical; the data is large and equally critical. Both need to be in the plan.
• Running the fleet restore sequentially because that is how the script was written. The backups support parallelism; the runbook has to as well.
• Never testing a restore because production is healthy. Restore paths only run during incidents, which means untested restore paths only fail during incidents.
• Letting backup retention be shorter than data retention without noticing. A partition dropped after thirteen months and a backup window of thirty days means anything older than thirty days exists only in the archive — if the archive step ran.

FAQ

Related

• Per-Partition Backup with pg_dump and Parallel Restore — the logical layer, sized and parallelised properly
• Point-in-Time Recovery for a Single Shard — base backups, WAL archiving and recovery targets
• Verifying Backup Integrity Across Shards — the checks that turn a backup from a hypothesis into a fact
• Restoring a Sharded Database into a Staging Environment — parallel fleet restore, shard-map rewriting and data masking
• Partition Lifecycle & Retention Management — where the per-partition archives come from