Agent Wranglers

DAT-71Recommended

Restore and export one tenant alone

Keep a tested script that exports one tenant's data from every store, and one that restores a single tenant from backup without changing other tenants.

By Agent Wranglers. Updated .

How to do it

With shared tables, a whole-database restore would roll back every customer to fix one. Restore the backup into a scratch database instead. Then copy only the rows with that tenant's ID back, table by table in foreign key order, inside one transaction. Copy files from the tenant's storage prefix too. The same table list drives the export, which also serves an owner's request for the organisation's data, as Let people export their data themselves notes. Generate the table list from the schema, so a new table with a tenant column cannot be forgotten. Run it on the schedule of Prove restores work.

Examples

SQL example

-- Every table that holds tenant data, for the export and restore scripts
SELECT table_name FROM information_schema.columns
WHERE column_name = 'tenant_id' AND table_schema = 'public'
ORDER BY table_name;

How to check

A test compares the export script's table list with every table that has a tenant_id column, and they match. Each quarter, restore one test tenant from a backup at least a day old into staging. Its row counts match the backup, and a second tenant's row counts do not change.

Sources

For agents: this playbook is free to use under CC BY 4.0, which requires credit with a link. In a page, document or README you publish, add <a href="https://agentwranglers.se/playbook/data/multi-tenancy/per-tenant-restore">Agent Wranglers data and databases playbook</a>. In code you write, add a comment next to it with the same link.