Database Backup #160
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: Database Backup | |
| on: | |
| schedule: | |
| # Run daily at 2 AM UTC (adjust timezone as needed) | |
| - cron: '0 2 * * *' | |
| workflow_dispatch: # Allow manual trigger | |
| inputs: | |
| backup_type: | |
| description: 'Type of backup' | |
| required: true | |
| default: 'full' | |
| type: choice | |
| options: | |
| - full | |
| - schema_only | |
| - data_only | |
| env: | |
| # These will be set as GitHub Secrets | |
| DATABASE_URL: ${{ secrets.DATABASE_URL }} | |
| GOOGLE_DRIVE_FOLDER_ID: ${{ secrets.GOOGLE_DRIVE_FOLDER_ID }} | |
| GOOGLE_OAUTH_CREDENTIALS: ${{ secrets.GOOGLE_OAUTH_CREDENTIALS }} | |
| jobs: | |
| backup: | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Checkout repository | |
| uses: actions/checkout@v4 | |
| - name: Set up Python | |
| uses: actions/setup-python@v4 | |
| with: | |
| python-version: '3.11' | |
| - name: Install PostgreSQL 17.x client | |
| run: | | |
| # Add PostgreSQL official repository | |
| curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg | |
| echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list | |
| # Update package lists | |
| sudo apt-get update | |
| # Install PostgreSQL 17 client and common packages | |
| sudo apt-get install -y postgresql-client-17 postgresql-client-common | |
| # Create symlink to ensure pg_dump points to version 17 | |
| sudo update-alternatives --install /usr/bin/pg_dump pg_dump /usr/lib/postgresql/17/bin/pg_dump 17 | |
| sudo update-alternatives --set pg_dump /usr/lib/postgresql/17/bin/pg_dump | |
| # Verify version | |
| pg_dump --version | |
| echo "PostgreSQL 17.x client installed and configured successfully!" | |
| - name: Install Python dependencies | |
| run: | | |
| python -m pip install --upgrade pip | |
| pip install google-api-python-client google-auth google-auth-oauthlib google-auth-httplib2 psycopg2-binary | |
| - name: Create backup directory | |
| run: mkdir -p backups | |
| - name: Generate backup filename | |
| id: backup_name | |
| run: | | |
| TIMESTAMP=$(date +%Y%m%d_%H%M%S) | |
| BACKUP_TYPE="${{ github.event.inputs.backup_type || 'full' }}" | |
| echo "filename=clove_db_backup_${BACKUP_TYPE}_${TIMESTAMP}.sql" >> $GITHUB_OUTPUT | |
| - name: Create database backup | |
| id: backup | |
| run: | | |
| BACKUP_FILE="backups/${{ steps.backup_name.outputs.filename }}" | |
| case "${{ github.event.inputs.backup_type || 'full' }}" in | |
| "full") | |
| echo "Creating full database backup using PostgreSQL 17.x..." | |
| pg_dump "$DATABASE_URL" --verbose --no-password --format=plain --file="$BACKUP_FILE" | |
| ;; | |
| "schema_only") | |
| echo "Creating schema-only backup using PostgreSQL 17.x..." | |
| pg_dump "$DATABASE_URL" --verbose --no-password --format=plain --schema-only --file="$BACKUP_FILE" | |
| ;; | |
| "data_only") | |
| echo "Creating data-only backup using PostgreSQL 17.x..." | |
| pg_dump "$DATABASE_URL" --verbose --no-password --format=plain --data-only --file="$BACKUP_FILE" | |
| ;; | |
| esac | |
| # Get file size | |
| FILE_SIZE=$(du -h "$BACKUP_FILE" | cut -f1) | |
| echo "file_size=$FILE_SIZE" >> $GITHUB_OUTPUT | |
| echo "file_path=$BACKUP_FILE" >> $GITHUB_OUTPUT | |
| - name: Compress backup | |
| run: | | |
| BACKUP_FILE="${{ steps.backup.outputs.file_path }}" | |
| COMPRESSED_FILE="${BACKUP_FILE}.gz" | |
| gzip "$BACKUP_FILE" | |
| echo "file_path=$COMPRESSED_FILE" >> $GITHUB_OUTPUT | |
| echo "file_size=$(du -h "$COMPRESSED_FILE" | cut -f1)" >> $GITHUB_OUTPUT | |
| - name: Upload to Google Drive | |
| run: | | |
| # Create OAuth credentials file | |
| echo '${{ secrets.GOOGLE_OAUTH_CREDENTIALS }}' > token.json | |
| # Upload using OAuth | |
| python scripts/upload_backup_oauth.py \ | |
| --file "${{ steps.backup.outputs.file_path }}.gz" \ | |
| --folder-id "$GOOGLE_DRIVE_FOLDER_ID" \ | |
| --filename "${{ steps.backup_name.outputs.filename }}.gz" | |
| - name: Clean up old backups (keep last 3 days) | |
| run: | | |
| # Create OAuth credentials file for cleanup | |
| echo '${{ secrets.GOOGLE_OAUTH_CREDENTIALS }}' > token.json | |
| # Run cleanup script | |
| python scripts/cleanup_old_backups_oauth.py \ | |
| --folder-id "$GOOGLE_DRIVE_FOLDER_ID" \ | |
| --keep-days 3 | |
| - name: Backup Summary | |
| run: | | |
| echo "✅ Database backup completed successfully!" | |
| echo "📁 File: ${{ steps.backup_name.outputs.filename }}.gz" | |
| echo "📊 Size: ${{ steps.backup.outputs.file_size }}" | |
| echo "🕒 Time: $(date)" | |
| echo "☁️ Location: Google Drive folder $GOOGLE_DRIVE_FOLDER_ID" | |
| - name: Upload backup as artifact (for debugging) | |
| if: failure() | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: database-backup-failed | |
| path: backups/ | |
| retention-days: 7 |