lomi · backup · supabase · db #282
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: lomi · backup · supabase · db | |
| on: | |
| workflow_dispatch: | |
| schedule: | |
| # Off-platform encrypted DB copy every 6 hours. This is the disaster-recovery | |
| # copy in our own S3; Supabase's own backups/PITR cover lower-RPO needs. | |
| - cron: "17 */6 * * *" | |
| concurrency: | |
| group: lomi-backup-supabase-db | |
| cancel-in-progress: false | |
| permissions: | |
| contents: read | |
| jobs: | |
| backup: | |
| name: Back up database | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 60 | |
| env: | |
| SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }} | |
| BACKUP_S3_BUCKET: ${{ secrets.BACKUP_S3_BUCKET }} | |
| BACKUP_S3_ENDPOINT: ${{ secrets.BACKUP_S3_ENDPOINT }} | |
| BACKUP_S3_ACCESS_KEY_ID: ${{ secrets.BACKUP_S3_ACCESS_KEY_ID }} | |
| BACKUP_S3_SECRET_ACCESS_KEY: ${{ secrets.BACKUP_S3_SECRET_ACCESS_KEY }} | |
| BACKUP_S3_PREFIX: ${{ vars.BACKUP_S3_PREFIX }} | |
| BACKUP_S3_REGION: ${{ vars.BACKUP_S3_REGION }} | |
| AGE_PUBLIC_KEY: ${{ secrets.BACKUP_AGE_PUBLIC_KEY }} | |
| steps: | |
| - name: Validate configuration | |
| run: | | |
| set -euo pipefail | |
| missing=0 | |
| normalize_env_value() { | |
| local name="$1" | |
| local required="${2:-required}" | |
| local raw="${!name-}" | |
| local normalized | |
| normalized="$(printf '%s' "$raw" | tr -d '\r\n' | sed 's/^[[:space:]]*//; s/[[:space:]]*$//')" | |
| if [ -z "$normalized" ]; then | |
| if [ "$required" = "required" ]; then | |
| echo "::error::Missing required secret: ${name}" | |
| missing=1 | |
| fi | |
| return | |
| fi | |
| if [ "$raw" != "$normalized" ]; then | |
| echo "::warning::${name} contained leading/trailing whitespace or line breaks; using a trimmed single-line value." | |
| fi | |
| export "$name=$normalized" | |
| printf '%s=%s\n' "$name" "$normalized" >> "$GITHUB_ENV" | |
| } | |
| for name in \ | |
| SUPABASE_DB_URL \ | |
| BACKUP_S3_BUCKET \ | |
| BACKUP_S3_ENDPOINT \ | |
| BACKUP_S3_ACCESS_KEY_ID \ | |
| BACKUP_S3_SECRET_ACCESS_KEY \ | |
| AGE_PUBLIC_KEY | |
| do | |
| normalize_env_value "$name" | |
| done | |
| normalize_env_value BACKUP_S3_PREFIX optional | |
| normalize_env_value BACKUP_S3_REGION optional | |
| if [ "$missing" -ne 0 ]; then | |
| exit 1 | |
| fi | |
| if printf '%s' "$SUPABASE_DB_URL" | grep -q '[[:space:]]'; then | |
| echo "::error::SUPABASE_DB_URL must be a single-line Postgres connection string with no literal whitespace." | |
| exit 1 | |
| fi | |
| case "$SUPABASE_DB_URL" in | |
| postgres://*|postgresql://*) ;; | |
| *) | |
| echo "::error::SUPABASE_DB_URL must start with postgres:// or postgresql://." | |
| exit 1 | |
| ;; | |
| esac | |
| - name: Checkout | |
| uses: actions/checkout@v5 | |
| # GitHub-hosted runners are IPv4-only; the direct Supabase host is IPv6-only. | |
| - name: Set up IPv6 egress | |
| uses: ./.github/actions/setup-ipv6-warp | |
| - name: Install backup tools | |
| run: | | |
| set -euo pipefail | |
| sudo apt-get update | |
| sudo apt-get install -y age jq curl gnupg lsb-release | |
| # Install the PostgreSQL 17 client. pg_dump/pg_dumpall must be >= the server | |
| # major version (Supabase runs Postgres 17), which is newer than Ubuntu's default. | |
| curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \ | |
| | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/pgdg.gpg | |
| echo "deb [signed-by=/etc/apt/trusted.gpg.d/pgdg.gpg] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \ | |
| | sudo tee /etc/apt/sources.list.d/pgdg.list >/dev/null | |
| sudo apt-get update | |
| sudo apt-get install -y postgresql-client-17 | |
| # The ubuntu-latest runner ships a running PostgreSQL 16 cluster, so the | |
| # pg_dump/pg_dumpall on PATH are the postgresql-common "pg_wrapper" shim, which | |
| # resolves to the default local cluster's version (16) even after client 17 is | |
| # installed. That caused "server version mismatch: server 17.x, pg_dumpall 16.x". | |
| # Put the v17 binaries at the front of PATH (for all later steps) so the real | |
| # v17 tools are used and the wrapper is bypassed. | |
| echo "/usr/lib/postgresql/17/bin" >> "$GITHUB_PATH" | |
| pg_dumpall_bin="/usr/lib/postgresql/17/bin/pg_dumpall" | |
| "$pg_dumpall_bin" --version | |
| case "$("$pg_dumpall_bin" --version)" in | |
| *" 17."*) echo "pg_dumpall 17 confirmed." ;; | |
| *) echo "::error::Expected pg_dumpall 17 at ${pg_dumpall_bin}."; exit 1 ;; | |
| esac | |
| # Derive the direct (IPv6) connection from SUPABASE_DB_URL. If the secret still | |
| # points at the shared pooler, rewrite it to the direct host; otherwise use it | |
| # as-is. The result is exported as BACKUP_DB_URL for the steps below. | |
| - name: Resolve database connection | |
| run: | | |
| set -euo pipefail | |
| url="$SUPABASE_DB_URL" | |
| rest="${url#*://}" | |
| userinfo="${rest%%@*}" | |
| hostpath="${rest#*@}" | |
| user="${userinfo%%:*}" | |
| pass="${userinfo#*:}" | |
| hostport="${hostpath%%/*}" | |
| dbname="${hostpath#*/}" | |
| dbname="${dbname%%\?*}" | |
| host="${hostport%%:*}" | |
| [ -n "$dbname" ] || dbname="postgres" | |
| case "$host" in | |
| *.pooler.supabase.com) | |
| ref="${user#*.}" | |
| backup_db_url="postgresql://postgres:${pass}@db.${ref}.supabase.co:5432/${dbname}" | |
| echo "::notice::Pooler host detected; using direct IPv6 connection db.${ref}.supabase.co:5432 over Cloudflare WARP." | |
| ;; | |
| *) | |
| backup_db_url="$url" | |
| echo "::notice::Using SUPABASE_DB_URL host ${host} as-is." | |
| ;; | |
| esac | |
| echo "::add-mask::$pass" | |
| echo "::add-mask::$backup_db_url" | |
| printf 'BACKUP_DB_URL=%s\n' "$backup_db_url" >> "$GITHUB_ENV" | |
| - name: Preflight database connection | |
| run: | | |
| set -euo pipefail | |
| echo "Checking database reachability before backup..." | |
| db_host="$(printf '%s' "$BACKUP_DB_URL" | sed -E 's#^[a-z]+://[^@]+@([^:/]+).*#\1#')" | |
| db_port="$(printf '%s' "$BACKUP_DB_URL" | sed -nE 's#^[a-z]+://[^@]+@[^:/]+:([0-9]+)/.*#\1#p')" | |
| echo "Target: ${db_host}:${db_port:-5432}" | |
| stderr_file="$(mktemp)" | |
| trap 'rm -f "$stderr_file"' EXIT | |
| if PGCONNECT_TIMEOUT=20 psql "$BACKUP_DB_URL" -v ON_ERROR_STOP=1 -tAc "SELECT 1" >/dev/null 2>"$stderr_file"; then | |
| echo "Database connection OK." | |
| exit 0 | |
| fi | |
| err="$(tr '\n' ' ' <"$stderr_file" | sed 's/ */ /g')" | |
| if printf '%s' "$err" | grep -qiE 'connection to database not available|EAUTHQUERY|ECIRCUITBREAKER'; then | |
| echo "::error::Pooler reported it cannot reach Postgres (EAUTHQUERY/ECIRCUITBREAKER). This backup targets the direct host, so SUPABASE_DB_URL likely still points at the pooler and was not rewritten. Verify the secret is a valid Supabase connection string." | |
| exit 1 | |
| fi | |
| if printf '%s' "$err" | grep -qiE 'Network is unreachable|No route to host|could not connect to server'; then | |
| echo "::error::Cannot reach ${db_host}. The direct Supabase host is IPv6-only; confirm the Cloudflare WARP step established IPv6 egress (see the 'Verify IPv6 connectivity' step)." | |
| exit 1 | |
| fi | |
| if printf '%s' "$err" | grep -qi 'password authentication failed'; then | |
| echo "::error::Authentication failed for the direct connection (user 'postgres'). Update the password in the SUPABASE_DB_URL secret from Supabase Dashboard → Project Settings → Database." | |
| exit 1 | |
| fi | |
| if printf '%s' "$err" | grep -qiE 'could not translate host name|Name or service not known|nodename nor servname'; then | |
| echo "::error::Cannot resolve ${db_host}. Check the project ref encoded in SUPABASE_DB_URL." | |
| exit 1 | |
| fi | |
| if printf '%s' "$err" | grep -qiE 'timeout expired|timed out'; then | |
| echo "::error::Connection to ${db_host} timed out. Likely an IPv6/WARP issue; re-run in a few minutes." | |
| exit 1 | |
| fi | |
| echo "::error::Database preflight failed: ${err}" | |
| exit 1 | |
| - name: Create database dump | |
| id: db-dump | |
| run: | | |
| set -euo pipefail | |
| export AWS_ACCESS_KEY_ID="$BACKUP_S3_ACCESS_KEY_ID" | |
| export AWS_SECRET_ACCESS_KEY="$BACKUP_S3_SECRET_ACCESS_KEY" | |
| export AWS_DEFAULT_REGION="${BACKUP_S3_REGION:-auto}" | |
| export AWS_EC2_METADATA_DISABLED="true" | |
| timestamp="$(date -u +'%Y%m%dT%H%M%SZ')" | |
| backup_dir="supabase-db-${timestamp}" | |
| archive="${backup_dir}.tar.gz" | |
| encrypted_archive="${archive}.age" | |
| checksum_file="${encrypted_archive}.sha256" | |
| manifest_file="${backup_dir}.manifest.json" | |
| prefix="${BACKUP_S3_PREFIX:-supabase}" | |
| latest_manifest="latest-db-manifest.json" | |
| endpoint_args=() | |
| if [ -n "${BACKUP_S3_ENDPOINT:-}" ]; then | |
| endpoint_args=(--endpoint-url "$BACKUP_S3_ENDPOINT") | |
| fi | |
| mkdir -p "$backup_dir" | |
| # The three dumps below replicate `supabase db dump` (--role-only, schema, | |
| # --data-only --use-copy) using the native PG17 client, so we can run them | |
| # over the direct IPv6 connection without Docker. The flags and sed filters | |
| # are taken verbatim from `supabase db dump --dry-run` to preserve Supabase's | |
| # filtering (excluded internal schemas, stripped reserved roles, idempotent | |
| # IF NOT EXISTS clauses). Do not "simplify" these into a plain pg_dump: that | |
| # would include Supabase internals and break restores. | |
| # Roles (equivalent of: supabase db dump --role-only) | |
| pg_dumpall -d "$BACKUP_DB_URL" \ | |
| --roles-only \ | |
| --role "postgres" \ | |
| --quote-all-identifiers \ | |
| --no-role-passwords \ | |
| --no-comments \ | |
| | sed -E 's/^\\(un)?restrict .*$/-- &/' \ | |
| | sed -E "s/^CREATE ROLE \"(anon|authenticated|authenticator|cli_login_.*|dashboard_user|pgbouncer|postgres|service_role|supabase_.*|pgsodium_keyholder|pgsodium_keyiduser|pgsodium_keymaker|pgtle_admin)\"/-- &/" \ | |
| | sed -E "s/^ALTER ROLE \"(anon|authenticated|authenticator|cli_login_.*|dashboard_user|pgbouncer|postgres|service_role|supabase_.*|pgsodium_keyholder|pgsodium_keyiduser|pgsodium_keymaker|pgtle_admin)\"/-- &/" \ | |
| | sed -E "s/ (NOSUPERUSER|NOREPLICATION)//g" \ | |
| | sed -E "s/^-- (.* SET \"(pgaudit.*|pgrst.*|session_replication_role|statement_timeout|track_io_timing)\" .*)/\1/" \ | |
| | sed -E "s/GRANT \".*\" TO \"(anon|authenticated|authenticator|cli_login_.*|dashboard_user|pgbouncer|postgres|service_role|supabase_.*|pgsodium_keyholder|pgsodium_keyiduser|pgsodium_keymaker|pgtle_admin)\"/-- &/" \ | |
| | sed -E "/^--/d" \ | |
| | uniq > "$backup_dir/roles.sql" | |
| echo "RESET ALL;" >> "$backup_dir/roles.sql" | |
| # Schema (equivalent of: supabase db dump) | |
| pg_dump -d "$BACKUP_DB_URL" \ | |
| --schema-only \ | |
| --quote-all-identifiers \ | |
| --role "postgres" \ | |
| --exclude-schema "information_schema|pg_*|_analytics|_realtime|_supavisor|auth|etl|extensions|pgbouncer|realtime|storage|supabase_functions|supabase_migrations|cron|dbdev|graphql|graphql_public|net|pgmq|pgsodium|pgsodium_masks|pgtle|repack|tiger|tiger_data|timescaledb_*|_timescaledb_*|topology|vault" \ | |
| | sed -E 's/^\\(un)?restrict .*$/-- &/' \ | |
| | sed -E 's/^CREATE SCHEMA "/CREATE SCHEMA IF NOT EXISTS "/' \ | |
| | sed -E 's/^CREATE TABLE "/CREATE TABLE IF NOT EXISTS "/' \ | |
| | sed -E 's/^CREATE SEQUENCE "/CREATE SEQUENCE IF NOT EXISTS "/' \ | |
| | sed -E 's/^CREATE VIEW "/CREATE OR REPLACE VIEW "/' \ | |
| | sed -E 's/^CREATE FUNCTION "/CREATE OR REPLACE FUNCTION "/' \ | |
| | sed -E 's/^CREATE TRIGGER "/CREATE OR REPLACE TRIGGER "/' \ | |
| | sed -E 's/^CREATE PUBLICATION "supabase_realtime/-- &/' \ | |
| | sed -E 's/^CREATE EVENT TRIGGER /-- &/' \ | |
| | sed -E 's/^ WHEN TAG IN /-- &/' \ | |
| | sed -E 's/^ EXECUTE FUNCTION /-- &/' \ | |
| | sed -E 's/^ALTER EVENT TRIGGER /-- &/' \ | |
| | sed -E 's/^ALTER PUBLICATION "supabase_realtime_/-- &/' \ | |
| | sed -E 's/^ALTER FOREIGN DATA WRAPPER (.+) OWNER TO /-- &/' \ | |
| | sed -E 's/^ALTER DEFAULT PRIVILEGES FOR ROLE "supabase_admin"/-- &/' \ | |
| | sed -E 's/^GRANT ALL ON FOREIGN DATA WRAPPER (.+) TO "postgres" WITH GRANT OPTION/-- &/' \ | |
| | sed -E "s/^GRANT (.+) ON (.+) \"(information_schema|pg_*|_analytics|_realtime|_supavisor|auth|etl|extensions|pgbouncer|realtime|storage|supabase_functions|supabase_migrations|cron|dbdev|graphql|graphql_public|net|pgmq|pgsodium|pgsodium_masks|pgtle|repack|tiger|tiger_data|timescaledb_*|_timescaledb_*|topology|vault)\"/-- &/" \ | |
| | sed -E "s/^REVOKE (.+) ON (.+) \"(information_schema|pg_*|_analytics|_realtime|_supavisor|auth|etl|extensions|pgbouncer|realtime|storage|supabase_functions|supabase_migrations|cron|dbdev|graphql|graphql_public|net|pgmq|pgsodium|pgsodium_masks|pgtle|repack|tiger|tiger_data|timescaledb_*|_timescaledb_*|topology|vault)\"/-- &/" \ | |
| | sed -E 's/^(CREATE EXTENSION IF NOT EXISTS "pg_tle").+/\1;/' \ | |
| | sed -E 's/^(CREATE EXTENSION IF NOT EXISTS "pgsodium").+/\1;/' \ | |
| | sed -E 's/^(CREATE EXTENSION IF NOT EXISTS "pgmq").+/\1;/' \ | |
| | sed -E 's/^COMMENT ON EXTENSION (.+)/-- &/' \ | |
| | sed -E 's/^CREATE POLICY "cron_job_/-- &/' \ | |
| | sed -E 's/^ALTER TABLE "cron"/-- &/' \ | |
| | sed -E 's/^SET transaction_timeout = 0;/-- &/' \ | |
| | sed -E "/^--/d" > "$backup_dir/schema.sql" | |
| # Data (equivalent of: supabase db dump --data-only --use-copy -x storage.buckets_vectors -x storage.vector_indexes) | |
| { | |
| echo "SET session_replication_role = replica;" | |
| echo | |
| pg_dump -d "$BACKUP_DB_URL" \ | |
| --data-only \ | |
| --quote-all-identifiers \ | |
| --role "postgres" \ | |
| --exclude-schema "information_schema|pg_*|graphql|graphql_public|pgsodium|pgsodium_masks|pgtle|repack|tiger|tiger_data|timescaledb_*|_timescaledb_*|topology|vault|etl|extensions|pgbouncer|realtime|supabase_migrations|_analytics|_realtime|_supavisor" \ | |
| --exclude-table "auth.schema_migrations" \ | |
| --exclude-table "storage.migrations" \ | |
| --exclude-table "supabase_functions.migrations" \ | |
| --schema "*" \ | |
| --exclude-table "storage.buckets_vectors" \ | |
| --exclude-table "storage.vector_indexes" \ | |
| | sed -E 's/^\\(un)?restrict .*$/-- &/' | |
| echo "RESET ALL;" | |
| } > "$backup_dir/data.sql" | |
| db_content_sha="$( | |
| sha256sum "$backup_dir/roles.sql" "$backup_dir/schema.sql" "$backup_dir/data.sql" \ | |
| | sha256sum \ | |
| | awk '{print $1}' | |
| )" | |
| previous_db_content_sha="" | |
| if aws s3 cp "s3://${BACKUP_S3_BUCKET}/${prefix}/db/latest-manifest.json" "$latest_manifest" "${endpoint_args[@]}" --only-show-errors; then | |
| previous_db_content_sha="$(jq -r '.db_content_sha // empty' "$latest_manifest")" | |
| fi | |
| if [ "$db_content_sha" = "$previous_db_content_sha" ]; then | |
| echo "DB_CHANGED=false" >> "$GITHUB_ENV" | |
| echo "Database dump content matches the latest uploaded backup; skipping DB archive upload." | |
| exit 0 | |
| fi | |
| { | |
| echo "Backup created at ${timestamp}" | |
| echo | |
| echo "Restore order:" | |
| echo "1. roles.sql" | |
| echo "2. schema.sql" | |
| echo "3. data.sql" | |
| } > "$backup_dir/RESTORE.txt" | |
| tar -czf "$archive" "$backup_dir" | |
| age --recipient "$AGE_PUBLIC_KEY" --output "$encrypted_archive" "$archive" | |
| sha256sum "$encrypted_archive" > "$checksum_file" | |
| jq -n \ | |
| --arg created_at "$timestamp" \ | |
| --arg backup_file "$encrypted_archive" \ | |
| --arg checksum_file "$checksum_file" \ | |
| --arg db_content_sha "$db_content_sha" \ | |
| '{ | |
| created_at: $created_at, | |
| backup_file: $backup_file, | |
| checksum_file: $checksum_file, | |
| db_content_sha: $db_content_sha | |
| }' > "$manifest_file" | |
| echo "DB_CHANGED=true" >> "$GITHUB_ENV" | |
| echo "DB_BACKUP_FILE=$encrypted_archive" >> "$GITHUB_ENV" | |
| echo "DB_CHECKSUM_FILE=$checksum_file" >> "$GITHUB_ENV" | |
| echo "DB_MANIFEST_FILE=$manifest_file" >> "$GITHUB_ENV" | |
| - name: Upload database backup to object storage | |
| if: env.DB_CHANGED == 'true' | |
| run: | | |
| set -euo pipefail | |
| export AWS_ACCESS_KEY_ID="$BACKUP_S3_ACCESS_KEY_ID" | |
| export AWS_SECRET_ACCESS_KEY="$BACKUP_S3_SECRET_ACCESS_KEY" | |
| export AWS_DEFAULT_REGION="${BACKUP_S3_REGION:-auto}" | |
| export AWS_EC2_METADATA_DISABLED="true" | |
| prefix="${BACKUP_S3_PREFIX:-supabase}" | |
| destination="s3://${BACKUP_S3_BUCKET}/${prefix}/db" | |
| endpoint_args=() | |
| if [ -n "${BACKUP_S3_ENDPOINT:-}" ]; then | |
| endpoint_args=(--endpoint-url "$BACKUP_S3_ENDPOINT") | |
| fi | |
| aws s3 cp "$DB_BACKUP_FILE" "$destination/$DB_BACKUP_FILE" "${endpoint_args[@]}" --only-show-errors | |
| aws s3 cp "$DB_CHECKSUM_FILE" "$destination/$DB_CHECKSUM_FILE" "${endpoint_args[@]}" --only-show-errors | |
| aws s3 cp "$DB_MANIFEST_FILE" "$destination/latest-manifest.json" "${endpoint_args[@]}" --only-show-errors | |
| echo "Uploaded encrypted database backup to $destination/$DB_BACKUP_FILE" |