Database Backup Automation

Problem

When I moved into the System Officer role, database backups across all three engines the organisation runs — Oracle, PostgreSQL and MySQL — were a manual routine. Somebody had to remember, and somebody had to do it, taking up out of hours work for a colleague.

Manual backups fail in a specific way. They do not stop working loudly; they get skipped on a busy week, then a busier one, and the gap is only found at the point where it matters most. For a national digital archive, the backup is not a convenience.

Approach

Scripted the lot with python, scheduled with cron.

Dumps are compressed and written to NFS storage, into a directory structure the scripts build themselves — year and month folders created automatically as they are needed. A scheme that organises itself has no annual maintenance step to forget, and nothing accumulates in one flat directory until it is unmanageable.

Every job reports to Slack. Each has its own webhook and its own bot, so a result is attributable at a glance rather than arriving as an anonymous alert, and everything is written to a log file as well. This is the part that makes the rest worth having: an unmonitored backup job is a manual backup with extra steps, because nobody finds out it stopped.

Backups land in more than one place. Alongside the primary NFS copy there is an offsite NFS server in another country and a copy on the university campus, with tape behind that. Past 3 months, the automated backups are the only remaining copy.

Retention currently prunes beyond 12 months, which is due a review against the formal retention policy rather than left at a number that seemed reasonable.

Outcome

No manual step remains. Backups happen because the schedule says so, and if one fails, Slack says so.

A restore has been performed from these backups and completed cleanly.