The platform ran 103 Postgres databases — one per tenant, plus a handful of shared ones for admin, licensing and the demo account. When we decided to consolidate it into a newer product, the shape of the problem was set by that decision years earlier: we were not migrating a database, we were migrating a hundred of them, each with its own drift.
Database-per-tenant is unfashionable and, for this, it was a gift. Every tenant could move independently. A failed migration blast radius was one customer, not all of them. We could rehearse on the smallest tenant and keep rehearsing until the script was boring.
Dump first, think second
Before touching anything we took a full custom-format dump of every database, plus globals, plus a manifest recording database name, byte count and SHA-256 for each file. Not because we expected to need it — because the cost of taking it was an afternoon and the cost of not having it was the company.
pg_dump -Fc --no-owner --no-privileges -d "$db" -f "$OUT/$db.dump"
sha256sum "$OUT/$db.dump" >> "$OUT/_MANIFEST.tsv"The manifest mattered more than the dumps. Restoring is easy; knowing that what you restored is byte-identical to what you dumped is what lets you sign off. Every restore we did during the migration was verified against that file before anyone looked at the data.
The schema is not the hard part
Mapping tables across is mechanical. What breaks migrations is semantics that look identical and are not. Two examples cost us real time, and both were invisible until we reconciled totals.
The first was accounts receivable. The old system recorded a customer who owed money as a "Credit" entry. The new one uses a signed ledger where owing money is a debit. Copying the field across verbatim produced a set of books that balanced perfectly and had the sign inverted on every open balance. Nothing errored. The totals were simply wrong, in a way that looked plausible.
The second was inventory. The old schema had both a quantity column and an available-quantity column on the same stock table, because the table was a movement ledger rather than a stock level: quantity recorded what arrived in a lot, available quantity was the live balance. Migrating quantity gives you the sum of everything ever received, which for an established store is a beautiful and completely fictional number.
A migration that errors is a migration you will fix. A migration that silently produces plausible numbers is one you ship.
Reconcile, do not trust
The rule we settled on: every migrated tenant gets a reconciliation report before anyone logs in, comparing source and destination on the handful of numbers a business owner would notice within a day.
- Total on-hand stock value, and unit count for the top 50 SKUs
- Open accounts receivable, as a total and as a count of customers with a balance
- Sales totals for the trailing 30, 90 and 365 days
- Customer count, and how many have a non-zero balance
- Row counts on every table that was mapped rather than copied
Any line that disagrees stops the cutover for that tenant. Not for the whole migration — that is the point of per-tenant isolation. One customer waits a day while we chase a mapping bug; the other ninety-nine go live.
What we would do the same
Rehearse on production data, in a scratch database, repeatedly. Synthetic fixtures do not contain the tenant who has been running since 2019 with three duplicate customer records and a negative stock line nobody ever fixed. Real data is the only test data that finds real bugs.
Keep the old system readable for longer than feels necessary. We kept the full archive — databases, vector store, secrets, source — long after shutdown. Twice in the following months a question came up that only the old data could answer.
And write the reconciliation report before writing the migration. It forces you to state, in numbers, what "this worked" means. Most migration pain comes from teams that never agreed on that definition and discovered the disagreement in production.