Per-Partition Backup with pg_dump and Parallel Restore

A partition is an ideal backup unit: it stops changing, it has a natural name, and it is small enough to restore in minutes. This guide dumps partitions individually, stores them with a manifest that makes them findable years later, and restores one in a fraction of the time a whole-cluster recovery would take. It implements the logical layer described in Backup & Restore for Partitioned Databases, part of Shard Migration & Rebalancing Operations.

Prerequisites

Step 1 — Dump one partition, not the table

pg_dump --table accepts a child partition directly:

#!/usr/bin/env bash
set -euo pipefail

PART=events_2025_06
OUT=/var/tmp/${PART}.dump

pg_dump \
  --host="${PGREPLICA}" --dbname=app \
  --table="public.${PART}" \
  --format=custom \
  --compress=9 \
  --no-owner --no-privileges \
  --file="${OUT}"

ls -lh "${OUT}"
-rw-r--r-- 1 postgres postgres 12G Aug  3 02:14 /var/tmp/events_2025_06.dump

Operational note: --table on a partition dumps the child as a standalone table, without the PARTITION OF clause. That is what makes it restorable into a scratch database that has no parent — and it is why reattaching later needs the bounds constraint described in the restore guide.

DBA tip: Do not use --table events expecting to get one partition. It dumps the parent and every child, which is the whole table and defeats the purpose.

Step 2 — Size compression and parallelism deliberately

Two knobs, pulling in opposite directions:

# directory format allows a parallel dump; custom format does not
pg_dump --format=directory --jobs=4 --compress=6 \
        --table="public.${PART}" --file=/var/tmp/${PART}.dir --dbname=app
Setting Effect Cost
--compress=9 smallest object, cheapest storage single-threaded, CPU bound; 2–3× slower than level 6
--compress=6 ~5% larger, noticeably faster the usual default choice
--format=directory --jobs=N parallel dump across N connections needs a directory rather than a file; N connections on the source
--jobs on restore parallel data load and index builds needs custom or directory format; N CPUs on the target
Where the time actually goes in dump and restore Dumping a 391 gigabyte partition at compression level nine takes 74 minutes and produces a 12 gigabyte object; at level six it takes 31 minutes for 12.6 gigabytes. Restoring single-threaded takes 96 minutes, of which 64 is index rebuilding; restoring with four jobs takes 34 minutes because the index builds run in parallel. dump --compress=9 dump --compress=6 restore --jobs=1 restore --jobs=4 74 min → 12.0 GB 31 min → 12.6 GB data load index rebuild — 64 min, single-threaded load indexes in parallel — 22 min 96 min total 34 min Index rebuilding dominates restore, which is why `--jobs` matters more on the way back than on the way out. Compression level barely changes the object size above level six and doubles the dump time, so level six is the sensible default. Raise maintenance_work_mem on the restore session as well — index builds that spill to disk erase most of the parallel gain.

Operational note: --jobs on restore requires the custom or directory format. A plain SQL dump restores as a single serial stream no matter how many cores the target has.

SRE tip: Match --jobs to available CPUs on the target, not the source. Restore is where the parallelism pays, and over-subscribing the target slows the index builds it is meant to accelerate.

Step 3 — Write a manifest alongside the object

An archive nobody can find is not an archive:

Every manifest field exists to answer a question later The partition name and bounds answer which data this is. Row count and checksum answer whether a restore is complete. Server version answers whether current tooling can read it. Dump format and size answer how long a restore will take. Created-at and sha256 answer whether the object is the one that was written. FieldQuestion it answers later partition, parent, bounds rows, checksum server_version dump_format, dump_bytes sha256, created_at which data is in this object, and where it belonged is a restore complete and identical? can the tooling we still run read it? how long will the restore take, and where will it fit? is this the object that was written, unmodified? Store the manifest as its own small object beside the dump. Listing manifests to answer "what do we still have from 2025?" is then a cheap operation rather than a download of every archive in the bucket. 
{
  "partition": "events_2025_06",
  "parent": "events",
  "shard": "shard_3",
  "bounds": {"from": "2025-06-01T00:00:00Z", "to": "2025-07-01T00:00:00Z"},
  "rows": 418223104,
  "checksum": "-8871220394112889",
  "server_version": "16.3",
  "dump_format": "custom",
  "dump_bytes": 12884901888,
  "sha256": "3f0b7a…",
  "created_at": "2026-08-03T02:14:51Z"
}
aws s3 cp "${OUT}"                 "s3://archive/events/${PART}.dump"
aws s3 cp "${OUT}.manifest.json"   "s3://archive/events/${PART}.manifest.json"

Operational note: Record server_version. A dump written by PostgreSQL 16 cannot be restored by a 15 client, and in five years the version that produced it will not be obvious from anything else.

DBA tip: Keep the manifest as a separate small object rather than embedding it in the dump. Listing manifests to answer “what do we still have from 2025?” is then a cheap operation instead of a download of every archive.

Step 4 — Restore one partition in minutes

The restore path is short because the unit is small:

createdb events_recovery

pg_restore \
  --dbname=events_recovery \
  --no-owner --no-privileges \
  --jobs=4 \
  /var/tmp/events_2025_06.dump
-- confirm against the manifest before anyone uses it
SELECT count(*)                       AS rows,
       sum(hashtext(t::text)::bigint) AS checksum
FROM   events_2025_06 t;
Answering "recover March for one customer" two ways Restoring the March partition into a scratch database moves twelve gigabytes and takes about half an hour end to end, including the query that extracts the customer's rows. Restoring the whole shard to a point in time moves 1.2 terabytes and takes two to three hours, plus the provisioning of a machine large enough to hold it. Partition restore — 34 min, 12 GB moved Full shard PITR — 2 h 40 m, 1.2 TB moved fetch archive4 min pg_restore --jobs=428 min extract the rows2 min runs on any spare machine with 40 GB of disk; nothing in production is touched provision host25 min download base70 min replay WAL55 min extract the rows2 min needs a machine sized like production, and the WAL replay is single-threaded regardless of how many cores it has Both paths answer the same question. The first exists only because the data was partitioned and each partition was archived on its own.

Operational note: Restore into a database named for the incident, not into anything that looks production-adjacent. events_recovery_INC1482 is unambiguous six hours later when someone asks whether it can be dropped.

SRE tip: Automate the extraction query alongside the restore. Most requests are “give me these rows as CSV”, and scripting that end to end turns a 40-minute engineer task into a 40-minute unattended one.

Verification

Run the whole path unattended once a week against a random archive:

#!/usr/bin/env bash
# weekly archive verification — picks one archive at random
PART=$(aws s3 ls s3://archive/events/ | grep '.dump$' | shuf -n1 | awk '{print $4}')
aws s3 cp "s3://archive/events/${PART}" /var/tmp/
createdb verify_scratch
pg_restore --dbname=verify_scratch --jobs=4 "/var/tmp/${PART}"
psql -d verify_scratch -tAc \
  "SELECT count(*), sum(hashtext(t::text)::bigint) FROM ${PART%.dump} t"
dropdb verify_scratch
418223104|-8871220394112889

Compare both values against the manifest. A mismatch means the archive is corrupt or the manifest is wrong, and both are findings worth an alert rather than a log line.

Failure mode table

Failure mode Root cause SRE mitigation
Restore takes hours instead of minutes the dump was written in plain SQL format, so --jobs has no effect and index builds run serially always dump in custom or directory format; verify by checking pg_restore --list succeeds on the object
Archive cannot be restored on the current fleet the dump was produced by a server newer than the available client tools record server_version in the manifest, and keep client tools for every version still represented in the archive
Nobody can find the archive for a given month objects were named by job run rather than by partition, and no manifest was written name objects after the partition; write a manifest per object; make listing manifests the discovery path

FAQ

Custom format or directory format?

Directory format if you want a parallel dump, custom format if a single file is easier to store and you only need parallel restore. Both support selective and parallel restore; only directory format supports pg_dump --jobs. For per-partition archives, custom format is usually the better fit because one partition is one object in the store.

Why is restore so much slower than the dump?

Because restoring rebuilds every index, and index builds are CPU and memory bound rather than I/O bound. On a partition with three indexes the rebuild is usually the majority of the restore time. Raising maintenance_work_mem for the restore session and using --jobs to build indexes in parallel are the two changes that matter.

Should the dump run against the primary or a replica?

A replica, whenever one exists. A dump is a long sequential read that competes with live traffic for cache and I/O, and on a replica it costs nothing except replication lag if the dump holds a long snapshot. Set hot_standby_feedback thoughtfully — it prevents query cancellation at the cost of bloat on the primary.

Back to Backup Restore For Partitioned Databases