Database Backup Strategy Plan

This plan details how we will implement automated backups for the PostgreSQL database running on your ASUS server.

User Review Required

IMPORTANT

The backups will be saved on the host machine. You will need to ensure that the directory where the backups are stored is periodically synced or backed up externally (e.g., using Immich, a cloud provider, or an external hard drive) if you want off-site disaster recovery.

Open Questions

1. Backup Retention: The default setup will keep daily backups for the last 7 days, weekly backups for the last 4 weeks, and monthly backups for 6 months. Does this retention policy sound good to you?
2. Backup Schedule: The backups will run automatically every day at midnight (@daily). Is this acceptable, or do you prefer a different time?

Proposed Changes

Add Automated Backup Service

We will add a dedicated background service to docker-compose.yaml using the popular prodrigestivill/postgres-backup-local image. This service runs completely transparently, meaning you won’t need to configure cron jobs manually on your ASUS server.

[MODIFY] docker-compose.yaml

• Add pgbackups service linked to the db service.
• Configure environment variables to connect to PostgreSQL (reusing the existing credentials from .env).
• Map a local volume ./backups:/backups so the .sql.gz dump files are directly accessible from the ASUS server filesystem.

[MODIFY] .gitignore

• Add /backups to .gitignore so that database dumps are never accidentally committed to GitHub.

Verification Plan

Manual Verification

• We will manually trigger a backup to ensure the image works correctly, authenticates with Postgres, and drops the .sql.gz file into the local ./backups folder.