Module 58: Database Administration
Backups
🏠The everyday picture
Important documents are photocopied and the copies kept in a different building. A copy in the same drawer burns in the same fire.
📘What it really is
-
Things that destroy data: a bad
DELETEwithoutWHERE(Module 55), a buggy migration, a disk failure, a hacked server, ransomware, a cloud account closed by mistake. -
Backup types:
- logical backups: SQL or archive files that recreate the data (
pg_dumpfor PostgreSQL;.backuporVACUUM INTOfor SQLite). Portable and easy to check; - physical backups: copies of the database's files, often combined with continuous logs for point-in-time recovery (restore to 10:41 this morning, just before the mistake). Managed services provide this.
-
The 3-2-1 rule: at least 3 copies, on 2 different kinds of storage, with 1 off-site (another region or provider).
-
Automate backups on a schedule (daily at least, more often for busy systems); manual backups get forgotten.
-
Protect backups: encrypt them (they contain all your customers' data), restrict access, and keep some that can't be deleted by the same account that runs the app (so an attacker can't delete them too).
-
Decide your targets:
- RPO (recovery point objective): how much data you can afford to lose (one day? five minutes?);
- RTO (recovery time objective): how long you can afford to be down while restoring.
🖱️Try it
Back up crm.db with sqlite3 crm.db ".backup crm-backup.db" (or DB Browser's Export → Database to SQL file). Write in
admin.md your backup plan for the CRM: what, how often, where (3-2-1), who can access backups, and your RPO and RTO.
✅Quick check
Backups are kept on the same server as the database. What's the problem?
Show the answer
If the server fails or is hacked, the backups are lost with it; keep copies off-site.
What's an RPO?
Show the answer
Recovery point objective: the maximum amount of recent data you can afford to lose.