db-backup.yml

# .github/workflows/db-backup.yml
# Runs nightly. Dumps production Postgres and rotates to keep 7 days.
name: Database backup

on:
  schedule:
    - cron: "0 3 * * *"  # 03:00 UTC daily
  workflow_dispatch:

jobs:
  backup:
    name: pg_dump to storage
    runs-on: ubuntu-latest
    timeout-minutes: 10

    steps:
      - name: Dump and rotate via SSH
        uses: appleboy/ssh-action@0ff4204d59e8e51228ff73bce53f80d53301dee2 # v1
        with:
          host: ${{ secrets.DEPLOY_HOST }}
          username: ${{ secrets.DEPLOY_USER }}
          key: ${{ secrets.DEPLOY_SSH_KEY }}
          script: |
            set -e
            BACKUP_DIR=/opt/simplerms/backups
            mkdir -p "$BACKUP_DIR"
            FILENAME="simplerms-$(date +%Y%m%d-%H%M%S).sql.gz"
            docker compose -f /opt/simplerms/docker-compose.yml \
              exec -T db pg_dump -U simplerms simplerms \
              | gzip > "$BACKUP_DIR/$FILENAME"
            # Keep only the 7 most recent backups.
            ls -t "$BACKUP_DIR"/*.sql.gz | tail -n +8 | xargs -r rm --
            echo "Backup complete: $FILENAME"