Per-Tenant Backup and Restore Across Isolation Tiers

"Can you restore just our data?" is a question every enterprise customer asks, and the honest answer depends entirely on which isolation tier they are on. This page is a focused part of Hybrid & Tiered Isolation Models: what each tier makes possible, how to build a per-tenant restore where the tier does not give you one, and how to prove the recovery time you promise.

Problem Framing

Physical backups are cluster-shaped. A point-in-time recovery restores an entire database to an instant, which is exactly right for a siloed tenant and useless for a pooled one — restoring the shared cluster to yesterday would roll back every other tenant along with the one that needs it.

So the achievable restore granularity is a property of the tier. Siloed: restore the database, done. Bridged: restore a schema from a logical dump, or restore everything into a staging instance and copy one schema back. Pooled: there is no physical path at all — the only option is a logical per-tenant export taken on a schedule, restored into a staging schema and copied back row by row, in referential order.

That last path is slow, and its cost is what should be stated in the contract. A pooled tenant with a four-hour recovery objective is a promise the architecture cannot keep; the same tenant on a dedicated database can be restored in minutes. Recovery granularity is one of the strongest arguments for tier promotion, and it is the one customers understand immediately.

The tier decides the answer before any tooling is written. Promising a pooled tenant point-in-time recovery is promising something the storage layer cannot do.

Step-by-Step Guide

1. Give every tenant a recovery objective, and record which tier can meet it

CREATE TABLE tenant_recovery_objective (
  tenant_id      uuid PRIMARY KEY,
  rpo_minutes    integer NOT NULL,          -- how much data may be lost
  rto_minutes    integer NOT NULL,          -- how long a restore may take
  source         text NOT NULL,             -- 'default' | 'contract'
  CHECK (rpo_minutes > 0 AND rto_minutes > 0)
);

-- Objectives the current placement cannot meet.
SELECT o.tenant_id, p.tier, o.rto_minutes
FROM   tenant_recovery_objective o
JOIN   tenant_placement p USING (tenant_id)
WHERE  (p.tier = 'pooled'  AND o.rto_minutes < 240)
   OR  (p.tier = 'bridged' AND o.rto_minutes < 30);
-- every row is either a promotion candidate or a contract to renegotiate

2. Take logical per-tenant exports for pooled tenants

# Nightly, per tenant, in dependency order. Binary format for speed and fidelity.
for TABLE in tenant organisation membership document document_version invoice; do
  psql "$SRC" -c "\copy (SELECT * FROM $TABLE WHERE tenant_id = '$TENANT') \
    TO PROGRAM 'zstd -q -o /backup/$TENANT/$(date +%F)/$TABLE.bin.zst' (FORMAT binary)"
done

Export from one repeatable-read snapshot across all tables, exactly as in a tier promotion, so the export is internally consistent. A per-table export taken over twenty minutes produces child rows whose parents were deleted midway, and the restore will fail on a foreign key at the least convenient moment.

3. Restore into staging first, never directly over live data

-- Restore lands in an isolated schema so nothing can overwrite production by accident.
CREATE SCHEMA restore_acme_20260730;
SET search_path TO restore_acme_20260730, public;
-- \copy each table back into schema-local tables created from the same migration set

// verify-restore.ts — check before anything is copied into production
export async function verifyStagedRestore(schema: string, tenantId: string) {
  const checks = await Promise.all(TABLES.map(async (t) => ({
    table: t,
    staged: await count(`${schema}.${t}`, tenantId),
    manifest: await manifestCount(tenantId, t),
  })));
  const bad = checks.filter((c) => c.staged !== c.manifest);
  if (bad.length) throw new Error(`row count mismatch: ${JSON.stringify(bad)}`);
  await assertNoForeignTenantRows(schema, tenantId);      // the restore must contain one tenant only
  return checks;
}

assertNoForeignTenantRows is the check that turns a restore into a safe operation: a staged dataset containing a second tenant's rows would inject them into production the moment it is copied across.

4. Copy back in referential order, inside one transaction

BEGIN;
SET LOCAL app.current_tenant_id = '…acme…';

-- Remove the tenant's current rows, children first.
DELETE FROM document_version WHERE tenant_id = current_setting('app.current_tenant_id')::uuid;
DELETE FROM document         WHERE tenant_id = current_setting('app.current_tenant_id')::uuid;
DELETE FROM membership       WHERE tenant_id = current_setting('app.current_tenant_id')::uuid;

-- Insert the restored rows, parents first.
INSERT INTO membership       SELECT * FROM restore_acme_20260730.membership;
INSERT INTO document         SELECT * FROM restore_acme_20260730.document;
INSERT INTO document_version SELECT * FROM restore_acme_20260730.document_version;
COMMIT;

One transaction means the tenant is never half-restored. On a large tenant this transaction can be long, which is the honest cost of pooled recovery and another reason the recovery objective belongs in the tier decision.

5. Rehearse, and publish the measured time rather than the hoped one

// drill.ts — scheduled monthly against a real tenant-sized dataset
export async function restoreDrill(tier: Tier) {
  const t0 = Date.now();
  const tenant = await pickRepresentativeTenant(tier);
  const staged = await restoreToStaging(tenant, latestBackup(tenant));
  await verifyStagedRestore(staged.schema, tenant.id);
  const elapsedMin = (Date.now() - t0) / 60_000;

  await recordDrill({ tier, tenantId: tenant.id, elapsedMin, bytes: staged.bytes });
  if (elapsedMin > objectiveFor(tier)) await alert('restore_drill_exceeded_rto', { tier, elapsedMin });
}

An untested restore path is a plan, not a capability. The drill also produces the number you are allowed to put in a contract, which is otherwise an estimate someone made once.

Staging is not an extra step for tidiness. It is the only place where a bad backup can be discovered before it becomes the customer's live data.

Verification

-- 1. Every tenant has a recent backup appropriate to its objective.
SELECT t.tenant_id, o.rpo_minutes,
       extract(epoch FROM (now() - max(b.taken_at))) / 60 AS minutes_since_backup
FROM   tenant_recovery_objective o
JOIN   tenant_placement t USING (tenant_id)
LEFT   JOIN tenant_backup b USING (tenant_id)
GROUP  BY t.tenant_id, o.rpo_minutes
HAVING max(b.taken_at) IS NULL
    OR extract(epoch FROM (now() - max(b.taken_at))) / 60 > o.rpo_minutes;
-- expected: no rows

-- 2. Drill results are within the promised recovery time.
SELECT tier, max(elapsed_min) AS worst, count(*) AS drills
FROM   restore_drill
WHERE  ran_at > now() - interval '90 days'
GROUP  BY tier;

-- 3. No staged restore schema has been left behind.
SELECT nspname FROM pg_namespace WHERE nspname LIKE 'restore\_%'
  AND  nspname NOT IN (SELECT schema_name FROM active_restore);

The third query matters more than it looks: an abandoned staging schema holds a full copy of a tenant's data outside every access control built for the live tables, which is a quiet data-exposure problem sitting in plain sight.

Two tiers meet their promise; the pooled row does not. That gap is a commercial decision — promote the tenant, or change the objective in the contract.

Failure Modes & Gotchas

Symptom: a restore fails on a foreign key. Cause: tables were exported at different times, so children reference parents that were later deleted. Fix: export every table from one exported snapshot.
Symptom: a restore overwrites another tenant's rows. Cause: the staged data contained more than one tenant, or the copy-back lacked a tenant predicate. Fix: assert tenant purity in staging, and scope every delete and insert by tenant.
Symptom: the customer's recovery expectation cannot be met. Cause: the objective was written before the tier was chosen. Fix: derive achievable objectives from the tier and reconcile the two at contract time; use the gap as the promotion argument.
Symptom: old staging schemas accumulate. Cause: the restore workflow has no teardown step. Fix: drop the schema on completion and sweep for orphans nightly — they are unguarded copies of customer data.

FAQ

How often should per-tenant logical exports run for pooled tenants? Match the recovery-point objective you have actually promised: nightly for a twenty-four-hour objective, every few hours for something tighter. The cost is real, since each export is a scan over that tenant's rows, so run them off-peak and stagger tenants across the window. If a pooled tenant needs an objective measured in minutes, no export cadence will get you there and the answer is promotion.

Can a single tenant be restored from a physical cluster backup? Indirectly. Restore that whole snapshot into a temporary instance, extract that tenant's rows from it, and copy them back — which works but costs a full-cluster restore in time and money, and needs enough capacity to stand up a second copy of the whole database. It is the right emergency path for a pooled tenant with no recent export, and a poor routine one.

Does restoring one tenant risk the others? It should not, and the controls that guarantee it are the same ones used everywhere else: stage in an isolated schema, verify the staged data contains exactly one tenant, scope every statement in the copy-back, and run the whole cutover in one transaction. The genuine residual risk is duration — a long transaction on a shared cluster holds locks and delays vacuum — which is why large pooled restores are scheduled rather than performed on demand.

Should customers be able to trigger their own restore? Not directly, and the reason is blast radius rather than trust. A restore overwrites live data, and a customer who selects the wrong backup has destroyed work with no way back — so the operation needs a confirmation path, a staged verification and a human who has done it before. What customers can reasonably self-serve is the read-only half: browsing available restore points, seeing what a backup contains, and requesting a restore that a human then executes with the safeguards in place.