PITR for One Tenant: Restoring One Database

The simplest example of single-database PITR is a multi-tenant system where every tenant has its own database.

text

tenant_acme
tenant_globex
tenant_initech

At 10:43, a bad job damaged data only in tenant_acme.

The other tenants are fine.

There is no reason to roll back the whole server.

We want to roll back only one database:

text

tenant_acme -> state from 10:42

Operation goal

The operation should be small and concrete.

text

restore a server copy to 10:42
take tenant_acme from it
replace the production database for that tenant
resume tenant traffic

This is not full disaster recovery.

This is recovery for one customer.

1. Freeze the tenant

First, we stop writes only for tenant_acme.

text

put tenant_acme in maintenance mode
stop tenant_acme workers
leave other tenants online

The point is to stop the damaged database from accepting new writes during recovery.

If we do not do this, we will restore a state from the past while production keeps adding new data to the old database.

2. Restore beside production

We do not restore directly on production.

We create a helper instance from backup and WAL up to the selected time.

text

target time: 10:42

After restore, we have a helper server with the state from before the bug.

On that server, we check whether tenant_acme looks good.

text

bad data is gone
the application can read the database
basic queries work

3. Dump one database

From the helper server, we take only the damaged tenant database.

bash

pg_dump -Fc tenant_acme > tenant_acme_1042.dump

We do not care about other tenant databases.

We do not move the whole server.

We extract only the part we want to restore.

4. Import as a new database

In production, we create a new database, for example:

text

tenant_acme_restored

And import the dump:

bash

createdb tenant_acme_restored
pg_restore -d tenant_acme_restored tenant_acme_1042.dump

The old tenant_acme database still exists.

That matters because we can still compare data or return to it if the restore turns out wrong.

5. Switch the tenant

The application must know that tenant_acme now uses the new database.

In the simplest model, we have a table or configuration:

text

tenant: acme
database: tenant_acme

We change it to:

text

tenant: acme
database: tenant_acme_restored

Then we restart or refresh processes that hold old connections.

text

API
workers
scheduled jobs

6. Check

After the switch, we do a short sanity check.

text

tenant_acme can log in
basic reads work
basic writes work
workers write to the new database
metrics do not show errors

If everything looks good, we disable maintenance mode for tenant_acme.

We do not delete the old database immediately.

It stays as material for comparison and postmortem.

What about data after 10:42

If the tenant was frozen only at 11:15, everything written between 10:42 and 11:15 will not appear in the restored database.

That is the cost of the operation.

We need to name it directly:

text

what we lose
what can be copied manually
what can be restored from logs
what needs to be communicated to the customer

PITR is not turning back time without consequences.

It is choosing the point we return to.

Minimal procedure

The whole operation fits into a short checklist.

text

confirm the problem affects only tenant_acme
freeze tenant_acme
restore a helper server to 10:42
dump the tenant_acme database
import the dump as tenant_acme_restored
switch tenant_acme to the new database
check reads, writes, and workers
unfreeze tenant_acme
keep the old database for analysis

That is the point of single-database PITR in this example.

We are not saving the whole platform.

We are not touching other tenants.

We restore one database because the boundary of the problem is clear.