Database backups that actually restore
Most backup setups have never been tested. What to back up, how often, where to keep it, and the restore drill that tells you whether any of it works.
Almost every business we look at has backups. Very few have restores.
The distinction is not pedantic. A backup is a file you hope is good. A restore is a demonstrated ability to get your business running again. Organisations discover which one they have on the worst possible day, and the discovery is not usually a happy one.
The failures we actually find
These are not hypothetical. In roughly this order of frequency:
The backup runs but contains nothing useful. A dump of an empty schema, or a container that was rebuilt so the job now points at a fresh database. The job exits zero every night, which is why nobody noticed.
Backups exist only where the data does. Same server, same account, same region. That covers disk failure and nothing else. It does not cover a deleted account, a bad migration replicated everywhere, or ransomware.
Nobody knows how to restore. The person who set it up left. The runbook is “ask Dave.” Dave works somewhere else now.
The restore takes longer than the business can survive. A twelve hour restore is a real backup and an unacceptable answer if you take orders online.
Retention is too short to matter. Seven days of backups does not help with corruption noticed after three weeks, which is the common case, because slow corruption is quiet.
What to back up
More than the database, though the database is the part people remember.
- The data itself. A logical dump plus, for anything meaningful, continuous archiving so you can restore to a point in time rather than to last midnight.
- The schema, separately. You want to be able to inspect structure without restoring a full copy.
- Uploaded files. Documents, images, attachments. These usually live outside the database and get forgotten, and they are frequently the part that cannot be recreated.
- Configuration and secrets, safely. Not in the same place as the data, but somewhere. A restored database with no idea how the application connects to it is a slower outage, not a shorter one.
Two numbers that decide everything
Before choosing tooling, write down two numbers. They convert an abstract argument into a budget.
How much data can you afford to lose? If the answer is a day, nightly dumps are fine. If the answer is five minutes, you need continuous archiving, and that is a different cost.
How long can you be down? If the answer is a day, a dump in object storage is fine. If the answer is an hour, you need a restore that has been practised and probably a standby copy.
Most small businesses, asked honestly, land on “a day of data, half a day of downtime.” That is a comfortable and inexpensive target. Some land on “an hour and an hour,” and they should know that costs meaningfully more, and decide deliberately rather than by default.
The rule worth keeping
Three copies of the data, on two kinds of storage, with one copy somewhere else entirely. It is an old rule from photography and it survives because the failure modes it covers have not changed.
For a small business running managed Postgres, that is realistically: the live database, the provider’s automated backups, and your own dump pushed to object storage in a different account. The third one is the one people skip, and it is the only one that survives your cloud account being closed or compromised.
Add immutability if you can. Object storage that refuses deletion for thirty days turns “the attacker deleted the backups” into a non-event, and it costs nothing extra.
The drill
This is the part that separates the two groups. Once a quarter, someone who is not the person who built the system restores the most recent backup into a scratch environment and answers three questions:
- Did it restore without errors?
- Is the newest record in it as recent as you expected?
- Can the application start against it and log someone in?
Write down how long it took. That number is your real recovery time, and it is usually two to three times what people guess.
A quarterly drill costs an hour and it is the only evidence that any of the rest is real. If it has never been done, you do not have backups, you have hope with a cron schedule.
Cheap wins
- Alert on the absence of a backup, not just on failure. A job that stops running silently is the most common failure and the least alerted.
- Record the size of every backup. A sudden drop is corruption or a misconfiguration, visible weeks before you would otherwise notice.
- Encrypt before it leaves the machine, so an exposed bucket is an embarrassment rather than a breach.
- Keep one restore document that a competent stranger could follow. Written when things are calm, not during an outage.
Where this connects to performance
Backups and performance sound unrelated and are not. A database with no maintenance, bloated tables and no index discipline takes far longer to dump and far longer to restore. Keeping the database in good shape shortens your worst day, which is a benefit nobody puts in the business case. The signs your database is the bottleneck are worth reading with that in mind.
If you are not certain your backups would restore, that uncertainty is the finding. Ask us to run the drill once and you will at least know which of the two groups you are in.
Related: database indexes explained, cleaning up a neglected database, and our database work.
Further reading
Topics in this article
Get new articles by email
One email when something new goes up, roughly twice a month. Plain writing on what software costs and what is worth building. No sequences, no sales calls, and one click to leave.
Keep reading on the same subject
-
Database indexes explained without the jargon
Why one missing index can make a page time out, why more indexes is not better, and what to ask your developer about it.
Understand indexes -
How to clean up a database nobody has maintained
Inherited a database full of duplicates, unused columns and mystery tables? A safe order of operations for cleaning it up without breaking production.
Follow the cleanup order -
Five signs your database is the bottleneck
How to tell whether your database is what is slowing your business down, and the cheapest fixes to try before paying for a bigger server.
Spot the warning signs