test-pg-upgrade.yml

name: Test pg_upgrade

on:
  push:
    branches:
      - develop
      - pcnc/pg_upgrade-test-extensions
  workflow_dispatch:

permissions:
  id-token: write

jobs:
  test:
    strategy:
      matrix:
        base_pg_version:
          - 15.1.1.60
          - 15.1.1.70
    runs-on: arm-runner
    timeout-minutes: 30
    defaults:
      run:
        working-directory: ./tests/pg_upgrade
    env:
      PGPORT: 5478
      PGPASSWORD: postgres
      PGDATABASE: postgres
      PGUSER: supabase_admin
      PGHOST: localhost
      PG_MAJOR_VERSION: 15
      IS_CI: true
      container: pg_upgrade_test
    steps:
      - uses: actions/checkout@v3
      
      - name: Grab release version
        id: process_release_version
        working-directory: ./
        run: |
          VERSION=$(sed -e 's/postgres-version = "\(.*\)"/\1/g' common.vars.pkr.hcl)
          echo "version=$VERSION" >> "$GITHUB_OUTPUT"

      - name: configure aws credentials - staging
        uses: aws-actions/configure-aws-credentials@v1
        with:
          role-to-assume: ${{ secrets.DEV_AWS_ROLE }}
          aws-region: "us-east-1"

      - name: Download pg_upgrade_scripts and binaries
        run: |
          aws s3 cp s3://${{ secrets.ARTIFACTS_BUCKET }}/upgrades/postgres/supabase-postgres-${{ steps.process_release_version.outputs.version }}/pg_upgrade_scripts.tar.gz scripts/pg_upgrade_scripts.tar.gz
          aws s3 cp s3://${{ secrets.ARTIFACTS_BUCKET }}/upgrades/postgres/supabase-postgres-${{ steps.process_release_version.outputs.version }}/20.04.tar.gz scripts/pg_upgrade_bin.tar.gz
      
      - run: docker context create builders
      - uses: docker/setup-buildx-action@v2
        with:
          endpoint: builders
          driver-opts: image=moby/buildkit:v0.11.6
          buildkitd-flags: --debug

      - name: Start Postgres
        run: |
          docker rm -f "$container" || true
          docker run --name "$container" --env-file .env \
          -v "$(pwd)/scripts:/tmp/upgrade" \
          --entrypoint "/tmp/upgrade/entrypoint.sh" -d \
          -p ${PGPORT}:5432 \
          "supabase/postgres:${{ matrix.base_pg_version }}"

      - name: Install psql
        run: |
          sudo apt update
          sudo apt install -y --no-install-recommends postgresql-client

      - name: Install pg_prove
        run: |
          sudo apt-get update
          sudo apt-get install -y --no-install-recommends perl cpanminus
          sudo cpanm -n App::cpanminus
          sudo cpanm -n TAP::Parser::SourceHandler::pgTAP
        env:
          SHELL: /bin/bash
          PERL_MM_USE_DEFAULT: 1
          PERL_MM_NONINTERACTIVE: 1

      - name: Wait for healthy database
        run: |
          count=0
          while ! docker exec "$container" bash -c "pg_isready"; do
            count=$((count + 1))
            if [ $count -ge "$retries" ]; then
                echo "Retry $count/$retries exited $exit, no more retries left."
                docker logs "$container"
                docker rm -f "$container"
                exit 1
            fi
          done
        env:
          retries: 20

      - name: Run migrations
        run: |
          docker cp ../../migrations/db/migrations "$container:/docker-entrypoint-initdb.d/"
          docker exec "$container" bash -c "/docker-entrypoint-initdb.d/migrate.sh > /tmp/migrate.log 2>&1"

      - name: Run initial tests
        run: pg_prove "../../migrations/tests/test.sql"
        env:
          PERL5LIB: /usr/local/lib/perl5

      - name: Apply pre-upgrade fixtures
        run: |
          psql -f "./tests/97-enable-extensions.sql"
          psql -f "./tests/98-data-fixtures.sql"
          psql -f "./tests/99-fixtures.sql"
          
      - name: Initiate upgrade
        run: docker exec "$container" bash -c '/tmp/upgrade/pg_upgrade_scripts/initiate.sh "$PG_MAJOR_VERSION"; exit $?'

      - name: Complete pg_upgrade
        run: docker exec pg_upgrade_test bash -c '/tmp/upgrade/pg_upgrade_scripts/complete.sh; exit $?'

      - name: Run post-upgrade tests
        run: |
          pg_prove tests/01-schema.sql
          pg_prove tests/02-data.sql
          pg_prove tests/03-settings.sql

      - name: Clean up container
        if: ${{ always() }}
        continue-on-error: true
        run: docker rm -f "$container" || true