~/sajjad $ cat projects/database-backups
← cd ../projects
Operations & compliance
Database backup system
Working9 databases · 4 organisations
Everything I build runs on one provider’s servers. Nine production databases across four organisations, and no copy of any of them anywhere else. One command now dumps every schema, every row and every role to plain SQL on my own machine, into a dated folder.
The decisions
- Plain SQL over the provider’s own backup format. The dashboard offers downloadable daily backups, but they restore into that provider. A
.sqlfile restores into any PostgreSQL database anywhere — and it’s readable, so I can open it and check what’s actually in it. - Scriptable over manual. Nine databases clicked through by hand is twenty minutes, and a backup you have to remember to do is one you eventually stop doing.
- Correctness over lightness. Calling
pg_dumpdirectly would have been a 50MB install rather than 1.5GB of Docker — but then I’d have to name the schemas to include myself, and the provider keeps its own internal schemas alongside mine. Get that list wrong and the backup looks complete and isn’t. The CLI already knows the right set, so I took the heavier install. - Three files, restored in order — roles, then schema, then data. You can’t grant a permission to a role that doesn’t exist, or insert a row into a table that doesn’t exist.
Written to survive its own failures
- One project failing doesn’t stop the other eight. Each step checks its exit code and moves on rather than aborting the run.
- Failures are collected and summarised at the end, not left in the scroll. Across 27 dump operations producing hundreds of lines of output, a single error in the middle is invisible; the same error alone at the bottom is not.
- Undefined variables are fatal by design, so a typo in a path can’t quietly become an empty string in a command that deletes things.
- Every run writes to a folder dated that day. Nothing overwrites anything — because a backup that overwrites the previous one only protects you against problems you notice immediately.
- The run ends by printing total size on disk, as a sanity check that catches the case where nothing errored and nothing was written either.
First run: seven of nine succeeded, and the summary named the two that didn’t and the stage each failed at. Both were paused free-tier databases with nothing listening — correct behaviour, reported clearly, no edits needed to pick them up once resumed.
The side effect worth more than the backup
A dated series of plain-text schemas is a version history of the database design, obtained for free. Diffing two weeks shows exactly which columns, constraints and row-level security policies changed between them — on databases that were never under version control in the first place.
It also makes a schema greppable:
CREATE TABLE tells you what a backup contains, and counting CREATE POLICY tells you how much of it is actually protected — without connecting to anything live.Honestly, what’s still open
- The copies are on one laptop. That covers a provider-side problem and does nothing about a dead or stolen machine. Off-machine sync is the next step and until it’s done the job is half finished.
- The restore path has been written down but not exercised. An untested backup is a hope, not a backup — the test belongs in a scratch database, not in an emergency.
- It runs on demand rather than on a schedule, and it covers the databases but not uploaded files.
Listed here for the same reason the security audit’s residual exposure is listed: the gap you’ve written down is the one that gets closed.