Docker Database Backup Script

Note: Runs on bnet006:/usr/local/bin/backup_postgres_db.sh (daily 23:00 via cron).

Overview

Backs up every stateful database container on bnet006. Containers are discovered dynamically by image (not hardcoded names), dumped, compressed, and logged:

• Postgres (image supabase/postgres:*) via pg_dumpall — e.g. supabase-db, supabase-stage-db, billing-postgres-prod-billing-db-1, billing-postgres-stage-billing-db-1
• MariaDB (image killbill/mariadb*) via mariadb-dump — e.g. killbill-prod-db-1, killbill-stage-db-1

Why dynamic?

Killbill was migrated Postgres → MariaDB and its container renamed (Aug 2025). The old script hardcoded the previous Postgres name and used pg_dumpall, so killbill had no backup for ~11 months while cron still reported success. Discovering by image means renamed/new DBs on the same images are picked up automatically. If discovery finds zero DBs for an engine, the run is treated as a failure (exit non-zero) so an image change can never silently back up nothing.

Backups

Written to /var/backups/postgres/<container>/<container>.sql.gz (only the latest is kept per container). MariaDB credentials are read from the container's own MYSQL_ROOT_PASSWORD (via MYSQL_PWD) — no secret is stored in this script.

Logs

/var/log/postgres_bkp/postgres_backup.log (single appended file). The script exits non-zero if any DB fails or if zero DBs are discovered.

Scheduling

0 23 * * * /usr/local/bin/backup_postgres_db.sh

Restoring

Postgres (Supabase / billing):

gunzip -c /var/backups/postgres/supabase-db/supabase-db.sql.gz \
  | docker exec -i supabase-db psql -U supabase_admin --disable-triggers -d postgres

--disable-triggers handles circular foreign-key dependencies; triggers are re-enabled automatically after restore.

MariaDB (Killbill):

gunzip -c /var/backups/postgres/killbill-prod-db-1/killbill-prod-db-1.sql.gz \
  | docker exec -i killbill-prod-db-1 sh -c 'MYSQL_PWD="$MYSQL_ROOT_PASSWORD" mariadb -uroot'

Requirements

• Docker
• Postgres containers backed up as supabase_admin
• MariaDB containers expose MYSQL_ROOT_PASSWORD
• Sufficient disk space

Troubleshooting

If backups fail, check the log for [ERROR]/[WARNING], confirm the DB containers are running, that supabase_admin (PG) / MYSQL_ROOT_PASSWORD (MariaDB) are valid, and that there is free disk space.

Development

Developed by b.net - service@bamsenet.de