#!/bin/sh
# Nightly database backup — runs ON THE DATABASE SERVER, as postgres:
#
#   sudo install -m 755 backup-db.sh /usr/local/bin/flowfabric-backup-db
#   echo '15 2 * * * postgres /usr/local/bin/flowfabric-backup-db' | sudo tee /etc/cron.d/flowfabric-backup-db
#
# Dumps every database on the server — FlowFabric's own and each Workspace's
# (they get their own database) — plus the roles and their passwords
# (globals.sql). Keeps KEEP_DAYS days. Copy BACKUP_DIR off this server too
# (rsync, rclone, ...): a backup on the same disk isn't one.
#
# Restore one database:   pg_restore --clean --if-exists --no-owner --role=<owner> -d <db> <db>.dump
# (roles first, on a new server: psql -f globals.sql)
set -eu

BACKUP_DIR=${BACKUP_DIR:-/var/backups/flowfabric/db}
KEEP_DAYS=${KEEP_DAYS:-14}
STAMP=$(date +%Y-%m-%d_%H%M)
DEST="$BACKUP_DIR/$STAMP"

umask 077
mkdir -p "$DEST"

pg_dumpall --globals-only > "$DEST/globals.sql"

for db in $(psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres' ORDER BY 1"); do
  pg_dump -Fc -d "$db" -f "$DEST/$db.dump"
done

find "$BACKUP_DIR" -mindepth 1 -maxdepth 1 -type d -mtime +"$KEEP_DAYS" -exec rm -rf {} +
echo "FlowFabric database backup written to $DEST"