Skip to content

PR 2: backup DB hooks for postgres, mariadb, mongodb, couchdb, sqlite

jedmund requested to merge jedmund/backup-db-hooks into main

Created by: jedmund

Summary

Second PR in the backup series. PR 1 set up borgmatic running file-level snapshots; this one adds database consistency. Without it, restoring a snapshot of an actively-written DB recovers whatever crash-recoverable state happened to be on disk at 02:00, which is good enough for Postgres but not safe to rely on for SQLite or anything mid-transaction.

What gets backed up

Native borgmatic hooks (streamed dump, no temp files)

Engine Count Databases
PostgreSQL 9 Immich, Mastodon, Synapse, MAS, n8n, Miniflux, Forgejo, Blinko, Draftboard
MariaDB 1 RomM (dumped as root)
MongoDB 1 Komodo admin DB

Custom before_backup scripts (write to /var/backup/dumps/, then borgmatic snapshots that path)

  • dump-couchdb.sh: CouchDB on obsidian_livesync. Pulls _all_docs?include_docs=true&attachments=true per non-system database to JSON.
  • dump-sqlite.sh: SQLite .backup snapshots for PocketID auth, Papra, Homebox, album-sort, the *arrs (Sonarr/Radarr/Lidarr/Prowlarr), qui, Pinchflat, Kavita, Stash, Tunarr.

What's not in this PR

  • Plex library database (deep path with spaces; hot backup needs more careful handling).
  • Named-volume state that isn't a database (e.g. Synapse media store).
  • borg-ui (PR 3) and monitoring (PR 4).

Plumbing changes

  • compose.yaml.j2: borgmatic now joins backend-internal, shared-network, and proxy-network so it can reach every DB container by hostname.
  • New /var/backup/dumps mount for the custom-hook output.
  • Dockerfile: adds sqlite3, jq, and curl on top of the upstream borgmatic-collective image.
  • env/borgmatic.env.j2: passes every DB password through as a separate env var; borgmatic.yaml references them via the {credential env VAR} syntax.

Vault keys consumed

All pre-existing in their respective group_vars/<stack>/vault.yml files. nuc-mini is a member of every relevant host group, so all values resolve at template time. No new vault entries required.

Documented in roles/backup/defaults/main.yml:

  • Postgres: immich_db_*, mastodon_db_*, synapse_db_*, mas_db_*, n8n_db_*, miniflux_db_*, forgejo_db_*, blinko_db_*, draftboard_db_*
  • MariaDB: romm_db_root_password
  • MongoDB: komodo_db_username, komodo_db_password
  • CouchDB: couchdb_user, couchdb_password

Commits

  1. backup: add database dump hooks across all stacks
  2. backup: document DB hook coverage in README

Test plan

  • make deploy-backup builds the extended image and starts borgmatic
  • docker inspect borgmatic -f '{{ range $k, $v := .NetworkSettings.Networks }}{{ $k }} {{ end }}' shows all three networks
  • Inside the container: nc -zv immich_postgres 5432, nc -zv komodo_mongo 27017, nc -zv obsidian_livesync 5984 all succeed
  • Trigger a manual run: docker exec borgmatic borgmatic --verbosity 1
  • Logs show successful dumps for every Postgres, MariaDB, MongoDB hook
  • ls /var/backup/dumps/couchdb/ and ls /var/backup/dumps/sqlite/ show files after a run
  • docker exec borgmatic borgmatic list --archive latest shows DB dumps in the archive (look for borgmatic/postgresql_databases/... paths and the /dumps/... paths)
  • Pick one database, restore a dump to a test path, confirm it loads (pg_restore, mongorestore, mariadb, or sqlite3 depending on engine)

Merge request reports