Home / Restore a single tenant from Postgres
How to restore a single tenant from a pooled Postgres database
One customer deleted the wrong records, or a bad migration corrupted their rows. Your backups are healthy — but restoring them rolls back every tenant in the shared database. Here is how to recover just the one.
In a pooled multi-tenant architecture, hundreds or thousands of customers share the same Postgres tables, separated only by a tenant_id column (or something looser). Point-in-time recovery and pg_dump operate on the whole database, so the moment you need to fix one customer without disturbing the others, the standard backup toolchain stops helping. Single-tenant restore is an application-boundary problem, not a database-snapshot problem.
Why a simple WHERE tenant_id = ? is not enough
The instinct is to filter every table by tenant and copy those rows back. In practice, a tenant is not one table and rarely one clean column:
- Rows spread across foreign keys, composite keys, and join tables that never mention
tenant_iddirectly. - Some tables use UUID tenant keys; others denormalize the tenant onto child rows; others infer ownership only through the application.
- Shared reference tables (plans, feature flags, country codes) are touched by the tenant but must never be restored or deleted.
- Files — invoices, uploads, exports — live in S3-compatible object storage the database cannot see.
Miss one relationship and you either leave orphaned rows behind or, worse, overwrite another customer's data during merge-back.
The safe sequence
1. Map the tenant graph
Introspect the schema with a read-only role and classify every table as root, tenant-owned, shared, ambiguous, or excluded. The goal is a complete tenant closure: the full set of rows and object keys that belong to one customer. Ambiguous relationships — tables with no foreign key, or an inferred link — must be resolved with explicit rules before anything unsafe runs.
2. Restore your snapshot to a scratch database
Bring your PITR snapshot or backup up in an isolated instance. You never restore over production. The scratch copy is where you carve out the single tenant.
3. Extract the tenant bundle
From the scratch database, extract the tenant closure — every owned row plus linked object-storage keys — into a portable bundle with a manifest and deterministic hashes. That bundle is now replayable and verifiable, independent of the live system.
4. Diff against live data
Before touching production, compare the bundle against current live data per table: which rows are missing, changed, or extra. This is where you catch primary-key collisions, sequence drift, and rows the customer has legitimately changed since the incident.
5. Plan, approve, then merge
Compute a merge plan, pin it to a hash, and require a second authorized person to approve that exact hash. The write happens inside a transaction that verifies non-target tenants' checksums are unchanged — if the blast radius exceeds the plan, it rolls back.
The rule that keeps you safe: no write to production until a hash-pinned plan exists and a second human has approved that exact hash. Reads are cheap; merge-backs are irreversible.
Don't forget the files
A restore that recovers database rows but not the customer's uploaded documents is only half a recovery. Track blob columns and object-storage keys alongside rows so the bundle — and the restore — covers files too. See how TenantUndo handles object storage linkage and the tenant graph resolver.
Rehearse before you need it
The worst time to discover your schema has three ambiguous relationships is at 3am during a real incident. Run a recovery drill against a representative tenant now: map the graph, extract a bundle, and read the readiness report. If you can't cleanly recover one customer in a rehearsal, you can't do it under pressure.
Find out if you can recover one customer
Send a schema, a backup description, and one representative tenant. TenantUndo returns a recovery map, the failure points, and a signed drill report.
Run a recovery drill