Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Supabase CLI

Automated backups using GitHub Actions

Backup your database on a regular basis.

Backup your database

You can use the Supabase CLI to backup your Postgres database. The steps involve running a series of commands to dump roles, schema, and data separately. Inside your repository, create a new file inside the .github/workflows folder called backup.yml. Copy the following snippet inside the file, and the action will run whenever a new PR is created.

Backup action


_19
name: 'backup-database'
_19
on:
_19
pull_request:
_19
jobs:
_19
build:
_19
runs-on: ubuntu-latest
_19
env:
_19
supabase_db_url: ${{ secrets.SUPABASE_DB_URL }} # For example: postgresql://postgres:[YOUR-PASSWORD]@db.<ref>.supabase.co:5432/postgres
_19
steps:
_19
- uses: actions/checkout@v2
_19
- uses: supabase/setup-cli@v1
_19
with:
_19
version: latest
_19
- name: Backup roles
_19
run: supabase db dump --db-url "$supabase_db_url" -f roles.sql --role-only
_19
- name: Backup schema
_19
run: supabase db dump --db-url "$supabase_db_url" -f schema.sql
_19
- name: Backup data
_19
run: supabase db dump --db-url "$supabase_db_url" -f data.sql --data-only --use-copy

Periodic Backups Workflow

You can use the GitHub Action to run periodic backups of your database. In this example, the Action workflow is triggered by push and pull_request events on the main branch, manually via workflow_dispatch, and automatically at midnight every day due to the schedule event with a cron expression. The workflow runs on the latest Ubuntu runner and requires write permissions to the repository's contents. It uses the Supabase CLI to dump the roles, schema, and data from your Supabase database, utilizing the SUPABASE_DB_URL environment variable that is securely stored in the GitHub secrets. After the backup is complete, it auto-commits the changes to the repository using the git-auto-commit-action. This ensures that the latest backup is always available in your repository. The commit message for these automated commits is "Supabase backup". This workflow provides an automated solution for maintaining regular backups of your Supabase database. It helps keep your data safe and enables easy restoration in case of any accidental data loss or corruption.


_34
name: Supa-backup
_34
_34
on:
_34
push:
_34
branches: [ main ]
_34
pull_request:
_34
branches: [ main ]
_34
workflow_dispatch:
_34
schedule:
_34
- cron: '0 0 * * *' # Runs every day at midnight
_34
jobs:
_34
run_db_backup:
_34
runs-on: ubuntu-latest
_34
permissions:
_34
contents: write
_34
env:
_34
supabase_db_url: ${{ secrets.SUPABASE_DB_URL }} # For example: postgresql://postgres:[YOUR-PASSWORD]@db.<ref>.supabase.co:5432/postgres
_34
steps:
_34
- uses: actions/checkout@v3
_34
with:
_34
ref: ${{ github.head_ref }}
_34
- uses: supabase/setup-cli@v1
_34
with:
_34
version: latest
_34
- name: Backup roles
_34
run: supabase db dump --db-url "$supabase_db_url" -f roles.sql --role-only
_34
- name: Backup schema
_34
run: supabase db dump --db-url "$supabase_db_url" -f schema.sql
_34
- name: Backup data
_34
run: supabase db dump --db-url "$supabase_db_url" -f data.sql --data-only --use-copy
_34
_34
- uses: stefanzweifel/git-auto-commit-action@v4
_34
with:
_34
commit_message: Supabase backup

More resources