supabase_backup.yml

name: Database Backup Workflow

on:
  schedule:
    # Runs at 00:00 UTC every Sunday
    - cron: '0 0 * * 0'
  workflow_dispatch:
    # This allows the workflow to be manually triggered
  push:
    branches:
    - master

jobs:
  backup-database:
    runs-on: ubuntu-latest

    steps:
    - name: Checkout code
      uses: actions/checkout@v2

    - name: Install AWS CLI
      run: sudo apt-get install awscli

#     Postgres SQL Client 15 is required to dump the database -- Supabase's Postgres version is 15
    - name: Install postgresql-client-15
      run: |
        sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
        wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc &>/dev/null
        sudo apt update
        sudo apt install postgresql-client-15 -y

      # Reference for pg_dump code (partially modified from source): https://github.com/mansueli/Supa-Migrate/
    - name: Dump PostgreSQL database
      env:
        PRODUCTION_DB_PASSWORD: ${{ secrets.PRODUCTION_DB_PASSWORD }}
        PRODUCTION_DB_URL: ${{ secrets.PRODUCTION_DB_URL }}
        PRODUCTION_DB_USER: ${{ secrets.PROD_DB_USER }}
      run: |
        /usr/lib/postgresql/15/bin/pg_dump "postgres://${PRODUCTION_DB_USER}:${PRODUCTION_DB_PASSWORD}@${PRODUCTION_DB_URL}/postgres" \
          --quote-all-identifiers \
          --exclude-table-data 'storage.objects' \
          --exclude-schema 'extensions|graphql|graphql_public|net|tiger|pgbouncer|vault|_realtime|supabase_functions|storage|pg*|schema_migrations|information_schema' \
          > full_db.sql
        ls 

    - name: Upload to AWS S3
      env:
        AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
        AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
        AWS_DEFAULT_REGION: 'us-east-2'
      run: aws s3 cp full_db.sql s3://cpp-lrr-database-backup --debug