Weekly Database Dump #4
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: Weekly Database Dump | |
| on: | |
| schedule: | |
| # Run every Monday at 2 AM UTC | |
| - cron: '0 2 * * 1' | |
| workflow_dispatch: # Allow manual trigger | |
| jobs: | |
| dump-database: | |
| runs-on: ubuntu-latest | |
| permissions: | |
| contents: read | |
| actions: write | |
| steps: | |
| - name: Set up PostgreSQL 17 client | |
| run: | | |
| # Add PostgreSQL official APT repository | |
| 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 --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - | |
| sudo apt-get update | |
| # Install PostgreSQL 17 client specifically | |
| sudo apt-get install -y postgresql-client-17 | |
| - name: Create dump directory | |
| run: mkdir -p database_dumps | |
| - name: Dump database (excluding users table) | |
| env: | |
| DATABASE_URL: ${{ secrets.DATABASE_URL }} | |
| run: | | |
| # Parse DATABASE_URL to extract connection details | |
| export PGHOST=$(echo $DATABASE_URL | sed -E 's/.*@([^:\/]+).*/\1/') | |
| export PGPORT=$(echo $DATABASE_URL | sed -E 's/.*:([0-9]+)\/.*/\1/') | |
| export PGDATABASE=$(echo $DATABASE_URL | sed -E 's/.*\/([^?]*).*/\1/') | |
| export PGUSER=$(echo $DATABASE_URL | sed -E 's/postgres:\/\/([^:]+):.*/\1/') | |
| export PGPASSWORD=$(echo $DATABASE_URL | sed -E 's/postgres:\/\/[^:]+:([^@]+)@.*/\1/') | |
| # Create dump excluding users table in custom archive format | |
| DUMP_FILE="database_dumps/db_dump_$(date +%Y%m%d_%H%M%S).dump" | |
| # Dump schema and data in custom format, excluding the users table | |
| pg_dump --no-owner --no-privileges \ | |
| --format=custom \ | |
| --exclude-table=users \ | |
| --exclude-table=schema_migrations \ | |
| --exclude-table=ar_internal_metadata \ | |
| -f "$DUMP_FILE" | |
| echo "DUMP_FILE=${DUMP_FILE}" >> $GITHUB_ENV | |
| echo "Dump created: ${DUMP_FILE}" | |
| - name: Upload dump as artifact | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: database-dump-${{ github.run_number }}-${{ github.run_attempt }} | |
| path: ${{ env.DUMP_FILE }} | |
| retention-days: 30 | |
| - name: Clean up old artifacts | |
| env: | |
| GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }} | |
| run: | | |
| # Keep only the last 4 weeks of dumps (delete artifacts older than 28 days) | |
| echo "Cleaning up old database dump artifacts..." | |
| # Get all artifacts with name starting with "database-dump-" | |
| ARTIFACTS=$(curl -L \ | |
| -H "Accept: application/vnd.github+json" \ | |
| -H "Authorization: Bearer $GITHUB_TOKEN" \ | |
| -H "X-GitHub-Api-Version: 2022-11-28" \ | |
| "https://api.github.com/repos/${{ github.repository }}/actions/artifacts?per_page=100" | \ | |
| jq -r '.artifacts[] | select(.name | startswith("database-dump-")) | "\(.id)|\(.created_at)"') | |
| # Delete artifacts older than 28 days | |
| CUTOFF_DATE=$(date -d '28 days ago' +%s) | |
| echo "$ARTIFACTS" | while IFS='|' read -r artifact_id created_at; do | |
| if [ -n "$artifact_id" ]; then | |
| ARTIFACT_DATE=$(date -d "$created_at" +%s) | |
| if [ "$ARTIFACT_DATE" -lt "$CUTOFF_DATE" ]; then | |
| echo "Deleting old artifact ID: $artifact_id (created: $created_at)" | |
| curl -L \ | |
| -X DELETE \ | |
| -H "Accept: application/vnd.github+json" \ | |
| -H "Authorization: Bearer $GITHUB_TOKEN" \ | |
| -H "X-GitHub-Api-Version: 2022-11-28" \ | |
| "https://api.github.com/repos/${{ github.repository }}/actions/artifacts/$artifact_id" | |
| fi | |
| fi | |
| done |