supabase-backup #184
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: supabase-backup | |
| on: | |
| push: | |
| branches: [main, dev] | |
| workflow_dispatch: # Can be triggered manually or via external schedulers | |
| inputs: | |
| trigger_source: | |
| description: "Source of the trigger (e.g., external-scheduler, manual, etc.)" | |
| required: false | |
| default: "manual" | |
| type: string | |
| schedule: | |
| - cron: "0 22 * * *" # Daily at 22:00 UTC (1:00 AM EAT) | |
| env: | |
| BACKUP_ENABLED: true | |
| BACKUP_DIR: backups | |
| jobs: | |
| run_db_backup: | |
| runs-on: ubuntu-latest | |
| timeout-minutes: 30 # Fail if backup takes longer than 30 minutes | |
| permissions: | |
| contents: write | |
| env: | |
| SUPABASE_DB_URL: ${{ secrets.SUPABASE_DB_URL }} | |
| BACKUP_ENABLED: ${{ vars.BACKUP_ENABLED || 'true' }} | |
| steps: | |
| - name: Diagnostic - Workflow trigger information | |
| run: | | |
| echo "=== Workflow Trigger Diagnostics ===" | |
| echo "Event name: ${{ github.event_name }}" | |
| echo "Workflow: ${{ github.workflow }}" | |
| echo "Actor: ${{ github.actor }}" | |
| echo "Repository: ${{ github.repository }}" | |
| echo "Ref: ${{ github.ref }}" | |
| echo "SHA: ${{ github.sha }}" | |
| echo "Scheduled time: ${{ github.event.schedule }}" | |
| echo "Trigger source: ${{ github.event.inputs.trigger_source || 'not-specified' }}" | |
| echo "====================================" | |
| - name: Check if backups are enabled | |
| run: | | |
| echo "BACKUP_ENABLED value: '$BACKUP_ENABLED'" | |
| echo "BACKUP_ENABLED type: $(echo "$BACKUP_ENABLED" | od -c | head -1)" | |
| if [ "$BACKUP_ENABLED" != "true" ]; then | |
| echo "Backups are disabled. Exiting workflow." | |
| exit 0 | |
| fi | |
| echo "Backups are enabled. Proceeding with backup..." | |
| - name: Checkout repository | |
| if: env.BACKUP_ENABLED == 'true' | |
| uses: actions/checkout@v3 | |
| with: | |
| ref: ${{ github.head_ref || github.ref_name }} | |
| fetch-depth: 0 | |
| - name: Setup Supabase CLI | |
| if: env.BACKUP_ENABLED == 'true' | |
| uses: supabase/setup-cli@v1 | |
| with: | |
| version: latest | |
| - name: Setup PostgreSQL client | |
| if: env.BACKUP_ENABLED == 'true' | |
| run: | | |
| sudo apt-get update | |
| sudo apt-get remove -y postgresql-client 2>/dev/null || true | |
| sudo apt-get install -y wget ca-certificates | |
| wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - | |
| echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list | |
| sudo apt-get update | |
| sudo apt-get install -y postgresql-client-17 | |
| - name: Setup backup environment | |
| if: env.BACKUP_ENABLED == 'true' | |
| run: | | |
| chmod +x .github/scripts/*.sh | |
| mkdir -p "$BACKUP_DIR/latest" "$BACKUP_DIR/archive" | |
| - name: Generate backup timestamp | |
| if: env.BACKUP_ENABLED == 'true' | |
| id: timestamp | |
| run: | | |
| BACKUP_TIMESTAMP=$(date -u +"%Y-%m-%dT%H-%M-%SZ") | |
| echo "timestamp=$BACKUP_TIMESTAMP" >> $GITHUB_OUTPUT | |
| echo "Generated backup timestamp: $BACKUP_TIMESTAMP" | |
| - name: Detect schemas | |
| if: env.BACKUP_ENABLED == 'true' | |
| id: detect-schemas | |
| run: | | |
| set +e | |
| # Source common.sh first for utility functions | |
| SOURCE_ERROR=$(mktemp) | |
| if ! source .github/scripts/common.sh 2> "$SOURCE_ERROR"; then | |
| echo "Error: Failed to source common.sh" >&2 | |
| cat "$SOURCE_ERROR" >&2 | |
| rm -f "$SOURCE_ERROR" | |
| exit 1 | |
| fi | |
| rm -f "$SOURCE_ERROR" | |
| # Source the script and capture any sourcing errors | |
| if ! source .github/scripts/detect.sh 2> "$SOURCE_ERROR"; then | |
| echo "Error: Failed to source detect.sh" >&2 | |
| cat "$SOURCE_ERROR" >&2 | |
| rm -f "$SOURCE_ERROR" | |
| exit 1 | |
| fi | |
| rm -f "$SOURCE_ERROR" | |
| SCHEMA_OUTPUT=$(mktemp) | |
| # Note: We don't capture stderr to a file - let errors show in logs immediately | |
| echo "Debug: Calling detect_schemas function..." >&2 | |
| echo "Debug: Using PostgreSQL binary: $(get_pg_binary psql)" >&2 | |
| echo "Debug: PostgreSQL binary exists: $([ -f "$(get_pg_binary psql)" ] && echo 'yes' || echo 'no')" >&2 | |
| echo "Debug: Testing PostgreSQL connection..." >&2 | |
| # Run detect_schemas - errors will go to stderr (visible in logs) | |
| # Capture stdout for processing | |
| detect_schemas "$SUPABASE_DB_URL" > "$SCHEMA_OUTPUT" || { | |
| EXIT_CODE=$? | |
| set -e | |
| echo "" >&2 | |
| echo "=== Schema Detection Failed (Exit code: $EXIT_CODE) ===" >&2 | |
| echo "" >&2 | |
| echo "=== Captured Standard Output ===" >&2 | |
| if [ -s "$SCHEMA_OUTPUT" ]; then | |
| cat "$SCHEMA_OUTPUT" >&2 | |
| else | |
| echo "(Output file is empty)" >&2 | |
| fi | |
| echo "" >&2 | |
| echo "=== Debug Information ===" >&2 | |
| echo "PostgreSQL client location: $(get_pg_binary psql)" >&2 | |
| echo "PostgreSQL client exists: $([ -f "$(get_pg_binary psql)" ] && echo 'yes' || echo 'no')" >&2 | |
| echo "PostgreSQL client version: $($(get_pg_binary psql) --version 2>&1 || echo 'failed to get version')" >&2 | |
| rm -f "$SCHEMA_OUTPUT" | |
| exit $EXIT_CODE | |
| } | |
| EXIT_CODE=0 | |
| set -e | |
| echo "Debug: detect_schemas completed successfully" >&2 | |
| SCHEMAS=$(cat "$SCHEMA_OUTPUT" | grep -vE "^(Resolved|Warning)" | grep -E "^[a-zA-Z_][a-zA-Z0-9_]*$") | |
| rm -f "$SCHEMA_OUTPUT" | |
| if [ -z "$SCHEMAS" ]; then | |
| echo "Warning: No schemas detected" >&2 | |
| fi | |
| echo "schemas<<EOF" >> $GITHUB_OUTPUT | |
| echo "$SCHEMAS" >> $GITHUB_OUTPUT | |
| echo "EOF" >> $GITHUB_OUTPUT | |
| echo "Detected schemas:" | |
| echo "$SCHEMAS" | while read schema; do | |
| [ -n "$schema" ] && echo " - $schema" | |
| done | |
| - name: Archive previous latest and create new latest | |
| if: env.BACKUP_ENABLED == 'true' | |
| id: latest-setup | |
| run: | | |
| source .github/scripts/backup.sh | |
| BACKUP_TIMESTAMP="${{ steps.timestamp.outputs.timestamp }}" | |
| SOURCE_REPO="${{ github.repository }}" | |
| TRIGGER_EVENT="${{ github.event_name }}" | |
| COMMIT_SHA="${{ github.sha }}" | |
| # Ensure latest directory exists | |
| mkdir -p "$BACKUP_DIR/latest" | |
| # Find existing latest_{timestamp} folder inside latest/ to archive | |
| OLD_LATEST="" | |
| if [ -d "$BACKUP_DIR/latest" ]; then | |
| OLD_LATEST=$(find "$BACKUP_DIR/latest" -maxdepth 1 -type d -name "latest_*" | sort -r | head -1) | |
| fi | |
| # If old latest exists, move it to archive | |
| if [ -n "$OLD_LATEST" ] && [ -d "$OLD_LATEST" ] && [ -n "$(ls -A "$OLD_LATEST" 2>/dev/null)" ]; then | |
| OLD_LATEST_NAME=$(basename "$OLD_LATEST") | |
| # Extract timestamp from old latest folder name (format: latest_{timestamp}) | |
| OLD_TIMESTAMP=$(echo "$OLD_LATEST_NAME" | sed 's/^latest_//') | |
| # Create archive name for the old latest backup | |
| ARCHIVE_NAME=$(generate_archive_name "$OLD_TIMESTAMP" "$SOURCE_REPO" "$TRIGGER_EVENT" "${COMMIT_SHA:0:7}") | |
| ARCHIVE_PATH="$BACKUP_DIR/archive/$ARCHIVE_NAME" | |
| # Handle duplicates by appending sequence number | |
| COUNTER=2 | |
| while [ -d "$ARCHIVE_PATH" ] && [ -n "$(ls -A "$ARCHIVE_PATH" 2>/dev/null)" ]; do | |
| SANITIZED_REPO=$(echo "$SOURCE_REPO" | tr '/' '-') | |
| ARCHIVE_NAME="${OLD_TIMESTAMP}--${SANITIZED_REPO}--${TRIGGER_EVENT}--${COMMIT_SHA:0:7}--${COUNTER}/" | |
| ARCHIVE_PATH="$BACKUP_DIR/archive/$ARCHIVE_NAME" | |
| COUNTER=$((COUNTER + 1)) | |
| [ $COUNTER -gt 100 ] && break | |
| done | |
| # Move old latest to archive | |
| mkdir -p "$(dirname "$ARCHIVE_PATH")" | |
| mv "$OLD_LATEST" "$ARCHIVE_PATH" | |
| echo "Archived previous latest: $OLD_LATEST_NAME → archive/$ARCHIVE_NAME" | |
| fi | |
| # Create new timestamped latest folder inside latest/ for this backup | |
| LATEST_NAME="latest_${BACKUP_TIMESTAMP}" | |
| LATEST_PATH="$BACKUP_DIR/latest/$LATEST_NAME" | |
| mkdir -p "$LATEST_PATH" | |
| # Remove any remaining old latest folders (cleanup) | |
| if [ -d "$BACKUP_DIR/latest" ]; then | |
| for old_latest in "$BACKUP_DIR/latest"/latest_*; do | |
| [ -d "$old_latest" ] && [ "$old_latest" != "$LATEST_PATH" ] && rm -rf "$old_latest" | |
| done | |
| fi | |
| echo "latest_path=$LATEST_PATH" >> $GITHUB_OUTPUT | |
| echo "latest_name=$LATEST_NAME" >> $GITHUB_OUTPUT | |
| echo "Created new latest folder: $LATEST_NAME" | |
| - name: Backup roles | |
| if: env.BACKUP_ENABLED == 'true' | |
| run: | | |
| source .github/scripts/backup.sh | |
| LATEST_PATH="${{ steps.latest-setup.outputs.latest_path }}" | |
| backup_roles "$SUPABASE_DB_URL" "$LATEST_PATH/roles.sql" | |
| - name: Backup full database | |
| if: env.BACKUP_ENABLED == 'true' | |
| run: | | |
| source .github/scripts/common.sh | |
| source .github/scripts/backup.sh | |
| BACKUP_TIMESTAMP="${{ steps.timestamp.outputs.timestamp }}" | |
| LATEST_PATH="${{ steps.latest-setup.outputs.latest_path }}" | |
| # Initialize connection semaphore for full database backup | |
| export GLOBAL_SEMAPHORE_DIR="/tmp/backup_semaphore_$$" | |
| export GLOBAL_MAX_CONNECTIONS="${GLOBAL_MAX_CONNECTIONS:-10}" | |
| init_global_semaphore | |
| trap "cleanup_global_semaphore" EXIT | |
| backup_full_database "$SUPABASE_DB_URL" "$BACKUP_TIMESTAMP" "$LATEST_PATH" | |
| cleanup_global_semaphore | |
| trap - EXIT | |
| - name: Backup schemas and tables | |
| if: env.BACKUP_ENABLED == 'true' | |
| id: backup-schemas | |
| env: | |
| BACKUP_MAX_PARALLEL: 8 # Number of parallel table backups per schema | |
| GLOBAL_MAX_CONNECTIONS: 10 # Total concurrent database connections across all schemas | |
| # Note: This includes connections for table detection queries, so 10 allows for better parallelism | |
| run: | | |
| set +e # Disable exit on error for parallel execution | |
| START_TIME=$(date +%s) | |
| source .github/scripts/common.sh | |
| source .github/scripts/backup.sh | |
| set +e # Re-disable exit on error after sourcing (backup.sh sets -e) | |
| # Initialize global connection semaphore to limit total concurrent connections | |
| # Use a fixed semaphore directory that all background jobs can access | |
| export GLOBAL_SEMAPHORE_DIR="/tmp/backup_semaphore_$$" | |
| export GLOBAL_MAX_CONNECTIONS="${GLOBAL_MAX_CONNECTIONS:-10}" | |
| init_global_semaphore | |
| trap "cleanup_global_semaphore" EXIT | |
| SCHEMAS="${{ steps.detect-schemas.outputs.schemas }}" | |
| BACKUP_TIMESTAMP="${{ steps.timestamp.outputs.timestamp }}" | |
| if [ -z "$SCHEMAS" ]; then | |
| echo "No schemas detected. Exiting." | |
| cleanup_global_semaphore | |
| exit 0 | |
| fi | |
| # Get latest path from previous step | |
| LATEST_PATH="${{ steps.latest-setup.outputs.latest_path }}" | |
| # Step 1: Create schema-level .dump files (dual backup structure) | |
| echo "Creating schema-level .dump files..." | |
| SCHEMA_DUMP_PIDS=() | |
| for schema in $SCHEMAS; do | |
| [ -z "$schema" ] && continue | |
| # Create schema dump in background | |
| ( | |
| set +e | |
| backup_schema_dump "$SUPABASE_DB_URL" "$schema" "$BACKUP_TIMESTAMP" "$LATEST_PATH" || { | |
| echo "Warning: Failed to create schema dump for $schema" >&2 | |
| } | |
| ) & | |
| SCHEMA_DUMP_PIDS+=($!) | |
| done | |
| # Wait for all schema dumps to complete | |
| for pid in "${SCHEMA_DUMP_PIDS[@]}"; do | |
| wait "$pid" 2>/dev/null || true | |
| done | |
| # Step 2: Backup schemas and tables (per-table structure) | |
| echo "Backing up tables in each schema..." | |
| SCHEMA_COUNT=0 | |
| SCHEMA_PIDS=() | |
| # Start all schema backups in parallel | |
| for schema in $SCHEMAS; do | |
| [ -z "$schema" ] && continue | |
| # Start schema backup in background (all at once) | |
| ( | |
| set +e | |
| backup_schema "$SUPABASE_DB_URL" "$schema" "$LATEST_PATH" || { | |
| echo "Warning: Failed to backup schema $schema" >&2 | |
| } | |
| ) & | |
| SCHEMA_PIDS+=($!) | |
| ((SCHEMA_COUNT++)) | |
| done | |
| # Wait for all schema backups to complete | |
| for pid in "${SCHEMA_PIDS[@]}"; do | |
| wait "$pid" 2>/dev/null || true | |
| done | |
| echo "Completed backup for $SCHEMA_COUNT schema(s) (with dual structure: .dump files + per-table files)" | |
| # Cleanup semaphore | |
| cleanup_global_semaphore | |
| trap - EXIT | |
| END_TIME=$(date +%s) | |
| echo "start_time=$START_TIME" >> $GITHUB_OUTPUT | |
| echo "end_time=$END_TIME" >> $GITHUB_OUTPUT | |
| set -e # Re-enable exit on error (after outputs are set) | |
| - name: Generate backup summary | |
| if: env.BACKUP_ENABLED == 'true' | |
| run: | | |
| source .github/scripts/backup.sh | |
| START_TIME="${{ steps.backup-schemas.outputs.start_time }}" | |
| END_TIME="${{ steps.backup-schemas.outputs.end_time }}" | |
| BACKUP_TIMESTAMP="${{ steps.timestamp.outputs.timestamp }}" | |
| LATEST_NAME="${{ steps.latest-setup.outputs.latest_name }}" | |
| print_backup_summary "$BACKUP_DIR" "$SUPABASE_DB_URL" "$START_TIME" "$END_TIME" "$BACKUP_TIMESTAMP" "" "$LATEST_NAME" | |
| - name: Commit backups | |
| if: env.BACKUP_ENABLED == 'true' | |
| uses: stefanzweifel/git-auto-commit-action@v4 | |
| with: | |
| commit_message: "Backup: Supabase database backup - $(date -u +'%Y-%m-%d %H:%M:%S UTC')" | |
| file_pattern: "backups/**" | |
| branch: ${{ github.head_ref || github.ref_name }} |