Restoring an Archived Partition into a Live Table

Archiving is only half a policy; the other half is the ability to bring data back. This guide restores an archived partition into a running system — into a staging table first, with the bounds constraint pre-declared so the attach does not scan, and verified against the archive log written when it left. It closes the loop opened in Partition Lifecycle & Retention Management, part of Partitioning Implementation Patterns & Routing.

Prerequisites

Step 1 — Confirm what you are restoring

Start from the log row, not from the object listing. It carries the expected row count and checksum:

SELECT table_name, row_count, checksum, archive_uri, archived_at, dropped_at
FROM   partition_archive_log
WHERE  table_name = 'events_2025_06';
   table_name   | row_count  |     checksum      |                archive_uri                |      archived_at
----------------+------------+-------------------+-------------------------------------------+------------------------
 events_2025_06 |  418223104 | -8871220394112889 | s3://archive/events/events_2025_06.dump   | 2026-07-02 03:41:12+00

Verify the object’s integrity before spending an hour restoring it:

aws s3 cp s3://archive/events/events_2025_06.dump /var/tmp/
aws s3 cp s3://archive/events/events_2025_06.dump.sha256 /var/tmp/
(cd /var/tmp && sha256sum -c events_2025_06.dump.sha256)

Operational note: If the checksum file is missing, the archive is not necessarily bad — but it is unverifiable, which is a finding worth recording. Regenerate one after this restore so the next person is not in the same position.

DBA tip: pg_restore --list on the dump shows its table of contents and the server version that produced it, without restoring anything. It is the cheapest possible sanity check.

Step 2 — Restore into a staging table, never directly into the parent

Restore to a differently named table so nothing can touch the live parent until you are ready:

pg_restore \
  --dbname=app \
  --no-owner --no-privileges \
  --jobs=4 \
  /var/tmp/events_2025_06.dump

The dump restores the table under its original name, events_2025_06, as a standalone table — which is exactly what it was when it was dumped. It is not attached to anything and takes no traffic.

Operational note: --jobs=4 parallelises data load and index builds and requires the custom or directory format. On a 148 GB compressed dump it is the difference between forty minutes and three hours.

SRE tip: Restore onto the replica-free path if possible — a bulk restore generates WAL proportional to the data and will push replication lag on every downstream node while it runs.

The restore path, and where it can be abandoned safely The archive object is downloaded and checksum-verified, restored into a standalone staging table, checked against the archive log's row count and checksum, given a bounds constraint, and only then attached to the live parent. Every step before the attach can be abandoned by dropping the staging table, with no effect on production. downloadsha256 verified pg_restorestandalone table verify countsagainst archive log add CHECKmatching the bounds ATTACHinstant everything here is abandonable — DROP TABLE and production never knew visible Restoring straight into the parent inverts this property: a partial or wrong restore is immediately queryable, and removing it again means detaching under time pressure while reports are already reading it. The staging step costs nothing — the dump restores under its original standalone name anyway.

Step 3 — Verify before it becomes visible

Compute the same two numbers the archive log holds:

SELECT count(*)                       AS rows,
       sum(hashtext(t::text)::bigint) AS checksum
FROM   events_2025_06 t;
   rows     |     checksum
------------+-------------------
  418223104 | -8871220394112889

Both must match the log row exactly. A row-count match with a checksum mismatch means the data changed after archiving — usually because the archive was taken from a replica that had not caught up, which is worth investigating before the rows re-enter the live table.

Operational note: If the archive predates a schema change, the restored table will not match the parent’s current column list, and the attach will fail with a clear error. Add the missing columns with the same defaults before attaching.

DBA tip: Compare pg_get_tabledef-style output between the parent and the restored table when versions differ. Attach requires identical column names, types and order — not merely compatible ones.

Step 4 — Add the bounds constraint so ATTACH does not scan

This is the step that turns a two-hour attach into an instant one:

-- declare what the data already satisfies
ALTER TABLE events_2025_06
  ADD CONSTRAINT events_2025_06_bounds
  CHECK (occurred_at >= '2025-06-01' AND occurred_at < '2025-07-01') NOT VALID;

-- validate it once, with a weak lock, before attaching
ALTER TABLE events_2025_06 VALIDATE CONSTRAINT events_2025_06_bounds;

NOT VALID followed by VALIDATE splits the work: the second statement scans the table but takes only a SHARE UPDATE EXCLUSIVE lock, and it does so on a table nothing is querying yet.

-- now the attach uses the constraint instead of scanning
ALTER TABLE events ATTACH PARTITION events_2025_06
  FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');
Pre-declaring the bounds turns the attach into a catalog change Without a constraint, ATTACH scans all 418 million rows to prove they fall inside the bounds, taking about 96 minutes while holding a lock on the parent. With a validated CHECK constraint that implies the bounds, PostgreSQL skips the scan and the attach completes in well under a second. ATTACH without a bounds constraint ATTACH with a validated CHECK constraint ≈ 96 min < 1 s scans 418M rows to prove the bounds hold, holding a lock on the parent for the whole scan reads the constraint from the catalog and concludes the bounds hold without touching a row The scan is not wasted work in principle — it is the same proof either way. Doing it as a separate VALIDATE, on a table nothing is querying, moves that cost out of the window where it blocks the live parent.

Operational note: The constraint must imply the partition bounds, not merely resemble them. A CHECK using <= on the upper bound does not imply a half-open range and will not save the scan.

SRE tip: Keep the constraint after attaching. It is redundant with the partition bounds but harmless, and it makes a future detach-and-reattach equally fast.

Step 5 — Record the restore

Close the loop in the same log the archive used:

One log row, updated at each stage of an archive's life When a partition is archived, a row records its name, row count, checksum and object URI. When the archive is verified, the verification timestamp is added. If it is ever restored, the restore timestamp and operator are added. That single row is what makes a restore two years later a lookup rather than an investigation. archived table_name, row_count checksum, archive_uri archived_at, dropped_at verified verified_at verified_by (job or person) restore duration, for planning restored (rare) restored_at, restored_by incident reference whether it reattached cleanly Keep these rows forever. They are a few hundred bytes each and they are the only record that a given month ever existed once the table is gone — including for the archives nobody has ever needed to read. The quarterly rehearsal picks an archive with no restored_at, which is how untested archives get tested rather than the familiar ones. 
UPDATE partition_archive_log
   SET restored_at = now(),
       restored_by = current_user
 WHERE table_name = 'events_2025_06';

An archive that has been restored once is a different risk category from one that never has. Recording it means the quarterly rehearsal can deliberately pick an archive nobody has ever read.

Verification

Confirm the partition is live and pruning includes it:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM events
WHERE  occurred_at >= '2025-06-10' AND occurred_at < '2025-06-11';
 Aggregate
   ->  Index Only Scan using events_2025_06_occurred_at_idx on events_2025_06 events
         Index Cond: ((occurred_at >= '2025-06-10 00:00:00+00') AND (occurred_at < '2025-06-11 00:00:00+00'))

The restored month appears in the plan and nothing else does. Also confirm the parent’s child count increased by exactly one and that no bound gap or overlap was introduced.

Failure mode table

Failure mode Root cause SRE mitigation
ATTACH runs for hours and blocks DDL no bounds CHECK constraint, so PostgreSQL validated by scanning every row while holding a lock on the parent add and VALIDATE the constraint on the standalone table first; the attach then reads the catalog
pg_restore fails on an unknown format the dump was written by a newer server than the one restoring it restore with a pg_restore binary of the newer version, or keep archives in a format pinned to a supported version range
Restored rows overlap an existing partition the bounds in the archive log do not match the parent’s current layout after a granularity change restore to a staging table, split it into the current bound shape with INSERT ... SELECT, then attach the pieces

FAQ

Why does ATTACH PARTITION scan the whole table?

Because PostgreSQL must prove every row falls inside the declared bounds. If the table already carries a CHECK constraint that implies those bounds, the planner uses the constraint instead of scanning, and the attach returns in milliseconds. Adding the constraint first is the difference between a two-hour attach and an instant one.

Can I attach a restored partition while the parent is serving traffic?

Yes. ATTACH PARTITION takes a SHARE UPDATE EXCLUSIVE lock on the parent in current versions, which does not block reads or writes to other partitions. The window where the parent is briefly locked is short as long as the constraint check is satisfied from the catalog rather than by a scan.

How often should a restore be rehearsed?

At least quarterly, against a randomly chosen archive rather than the most recent one. Archives fail in ways that only a restore reveals: a truncated upload, a format written by a server version you no longer run, or credentials that expired. A rehearsal that picks the newest archive tests the happy path and misses all three.

Back to Partition Lifecycle Retention Management