Weekly Database Dump #13
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: Cache PostgreSQL client | |
| id: cache-postgresql | |
| uses: actions/cache@v3 | |
| with: | |
| path: | | |
| /usr/lib/postgresql/17 | |
| /usr/share/postgresql/17 | |
| /usr/bin/pg_dump | |
| /usr/bin/pg_restore | |
| /usr/bin/psql | |
| key: ${{ runner.os }}-postgresql-client-17-${{ hashFiles('.github/workflows/database-dump.yml') }} | |
| - name: Set up PostgreSQL 17 client | |
| if: steps.cache-postgresql.outputs.cache-hit != 'true' | |
| run: | | |
| # Add PostgreSQL official APT repository using the new method | |
| sudo apt-get update | |
| sudo apt-get install -y wget ca-certificates gnupg | |
| # Create keyrings directory if it doesn't exist | |
| sudo mkdir -p /usr/share/keyrings | |
| # Download and add the signing key with proper conversion | |
| wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \ | |
| gpg --dearmor | \ | |
| sudo tee /usr/share/keyrings/postgresql-archive-keyring.gpg > /dev/null | |
| # Add the repository with signed-by option | |
| echo "deb [signed-by=/usr/share/keyrings/postgresql-archive-keyring.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | \ | |
| sudo tee /etc/apt/sources.list.d/pgdg.list | |
| # Update and install PostgreSQL 17 client | |
| sudo apt-get update | |
| 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 |