This episode builds an automated backup system for the expense-management app: pg_dump every night, media synced to S3 or MinIO, and regular restore drills — so when something breaks, you recover in minutes instead of losing data. Following episode 14 on secure file uploads, this one protects the very files and data those uploads created — because data without a backup is just data waiting to be lost.

1. Automating PostgreSQL Backups with pg_dump

pg_dump produces a single SQL file containing the database’s entire schema and data. Run it with the –no-owner and –no-acl flags so the file restores cleanly into a different database without getting stuck on ownership. Run the backup at 2 a.m., compress it with gzip, name the file by date, keep the 7 most recent copies, and delete older ones with find -mtime +7. Never back up onto the same disk as the database: copy the file to S3, MinIO, or another machine right after the dump finishes. Pass the password through the PGPASSWORD environment variable — never write it into the script.

#!/bin/bash
set -euo pipefail
DATE=$(date +%Y%m%d)
pg_dump --no-owner --no-acl -h "$DB_HOST" -U "$DB_USER" "$DB_NAME" \
  | gzip > "/backup/db-$DATE.sql.gz"
find /backup -name "db-*.sql.gz" -mtime +7 -delete
aws s3 cp "/backup/db-$DATE.sql.gz" "s3://my-backups/db/"
0 2 * * * /opt/app/backup.sh >> /var/log/backup.log 2>&1

2. Backing Up Media and Uploaded Files

Media — attached images, user-uploaded files — is not inside pg_dump, so it needs a separate backup. Use rclone sync or aws s3 sync from the media directory to a dedicated bucket, and enable versioning so you can recover a file that was overwritten or deleted by mistake. If you self-host MinIO, snapshot the volume holding its data as well. Check the bucket size every week, because media grows faster than you think.

3. Practicing Restores Regularly

A backup that has never been restored is not a backup yet. Once a month, create an empty database, restore the newest backup file into it, run migrate –check, then open a few key pages and count the important records. Measure the restore time and write it down: if a restore takes two hours, a 30-minute recovery target is just wishful thinking. Keep the restore procedure as a runbook in the repo so anyone on call can follow it at 3 a.m.

gunzip -c /backup/db-20261110.sql.gz | psql -h localhost -U app restore_test
python manage.py migrate --check --database restore_test

Apply It to Your Project

Start tonight: write a backup script for the expense-management database, schedule it with cron for 2 a.m., sync media to S3, and set a reminder to practice a restore on the first day of next month. Once the system has run a week without errors, try deleting a test transaction and recovering it from a backup, so you can be confident everything really works.

Related Posts

Frequently Asked Questions

How often should I back up the database?

For an expense-management app, a full backup every night is enough. If data changes constantly during the day, add WAL archiving to recover to any point in time.

Does pg_dump lock the database?

No. pg_dump runs inside a consistent read transaction, so the app keeps writing normally — it only costs extra CPU and I/O while the dump runs.

Do media backups need encryption?

Yes, if the media contains sensitive data. Turn on the bucket’s server-side encryption (SSE-S3 or SSE-KMS) and give the backup account write-only permission, never delete permission.

What should I watch for when restoring to another machine?

Check that the target PostgreSQL version is equal to or newer than the source, create the right user and extensions (for example pg_trgm), and only then run the SQL file.

References