Task
Several PostgreSQL databases had to be backed up without SSH access to the server and without running pg_dump by hand. A full copy of a large database is expensive, so small backups with only the changes were needed between full ones.
Solution
Built a web application on Django. Every backup saves a fingerprint of all rows, and the next one compares the current state with it and writes only the difference: new, changed and deleted rows. The difference goes into a single SQL file applied with plain psql on top of the restored full backup. Before a run the app checks the connection and explains any error in plain words.
What was done
- Built full backups with pg_dump and incremental and differential ones on top of them
- Captured every change, deleted rows included, without updated_at columns
- Tested restores end to end against a real PostgreSQL
- Moved backups to a background worker, so the site never waits for a dump
- Offered a choice of storage: the server disk or S3
- Kept database passwords out of storage: encrypted in the queue and erased after the backup
- Separated access: every user sees only their own backups
Result
A database backup takes a couple of clicks in the browser, and only the changes are stored between full backups. The code is open on GitHub, and the service starts with a single docker compose up.
Technologies
PythonDjangoPostgreSQLS3Docker
