pgcmp.yml

name: Database Migrations Check PR

on:
  pull_request:
    paths:
      - "deployment/hasura/migrations/**"
      - "deployment/postgres-init-db/sql/**"
      - ".github/workflows/pgcmp.yml"
      - ".github/workflows/scripts/compareDatabases*"
  push:
    paths:
      - "deployment/hasura/migrations/**"
      - "deployment/postgres-init-db/sql/**"
      - ".github/workflows/pgcmp.yml"
      - ".github/workflows/scripts/compareDatabases*"
    branches:
      - develop
      - dev-[0-9]+.[0-9]+.[0-9]+
    tags:
      - v*
  workflow_dispatch:

env:
  AERIE_USERNAME: "${{secrets.AERIE_USERNAME}}"
  AERIE_PASSWORD: "${{secrets.AERIE_PASSWORD}}"
  HASURA_GRAPHQL_ADMIN_SECRET: "${{secrets.HASURA_GRAPHQL_ADMIN_SECRET}}"
  HASURA_GRAPHQL_JWT_SECRET: "${{secrets.HASURA_GRAPHQL_JWT_SECRET}}"
  POSTGRES_USER: "${{secrets.POSTGRES_USER}}"
  POSTGRES_PASSWORD: "${{secrets.POSTGRES_PASSWORD}}"
  GATEWAY_USERNAME: "${{secrets.GATEWAY_USERNAME}}"
  GATEWAY_PASSWORD: "${{secrets.GATEWAY_PASSWORD}}"
  MERLIN_USERNAME: "${{secrets.MERLIN_USERNAME}}"
  MERLIN_PASSWORD: "${{secrets.MERLIN_PASSWORD}}"
  SCHEDULER_USERNAME: "${{secrets.SCHEDULER_USERNAME}}"
  SCHEDULER_PASSWORD: "${{secrets.SCHEDULER_PASSWORD}}"
  SEQUENCING_USERNAME: "${{secrets.SEQUENCING_USERNAME}}"
  SEQUENCING_PASSWORD: "${{secrets.SEQUENCING_PASSWORD}}"

jobs:
  dump-db-base:
    runs-on: ubuntu-latest
    environment: e2e-test
    steps:
      - name: Checkout v2.8.0
        uses: actions/checkout@v4
        with:
          ref: "v2.8.0"
      - name: Clone PGCMP
        uses: actions/checkout@v4
        with:
          repository: NASA-AMMOS/pgcmp
          path: pgcmp
      - name: Setup Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.10"
      - name: Setup Postgres Client (psql)
        # ubuntu-latest does not currently have psql-16 in its apt library, so we cannot just run
        # sudo apt update && sudo apt-get install --yes postgresql-client-16
        # Current instructions are from https://www.postgresql.org/download/linux/ubuntu/
        run: |
          sudo apt update
          sudo apt install curl ca-certificates
          sudo install -d /usr/share/postgresql-common/pgdg
          sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
          sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          sudo apt update
          sudo apt -y install postgresql-client-16
      - name: Setup Hasura CLI
        run: sudo curl -L https://github.com/hasura/graphql-engine/raw/stable/cli/get.sh | bash
      - name: Start Postgres
        run: |
          sed -i 's/postgres:14.8/postgres:16.4/' docker-compose.yml
          docker compose up -d postgres hasura
          docker images
          docker ps -a
      - name: Sleep for 1 Minute
        run: sleep 60s
        shell: bash
      - name: Dump v2.8.0 Database
        run: |
          mkdir pgdumpV2_8_0
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie \
          PGCMPOUTPUT=./pgdumpV2_8_0/AerieV2_8_0 \
          PGCLABEL=AerieV2_8_0 \
          PGBINDIR=/usr/bin \
          ./pgcmp/pgcmp-dump
        shell: bash
      - name: Share Database Dump
        uses: actions/upload-artifact@v4
        with:
          name: v2_8_0-db-dump
          path: pgdumpV2_8_0
          retention-days: 1
      - name: Checkout Latest
        uses: actions/checkout@v4
      - name: Restart Hasura
        run: |
          docker compose down
          docker compose up -d postgres hasura
          docker images
          docker ps -a
      - name: Sleep for 30 seconds
        run: sleep 30s
        shell: bash
      - name: Migrate Base to Latest
        run: |
          cd deployment
          cat << EOF > .env
          AERIE_USERNAME=${AERIE_USERNAME}
          AERIE_PASSWORD=${AERIE_PASSWORD}
          EOF
          python -m pip install -r requirements.txt
          python aerie_db_migration.py --apply --all
          cd ..
      - name: Clone PGCMP
        uses: actions/checkout@v4
        with:
          repository: NASA-AMMOS/pgcmp
          path: pgcmp
      - name: Dump Migrated Database
        run: |
          mkdir pgdumpmigrated
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie \
          PGCMPOUTPUT=./pgdumpmigrated/AerieMigratedUp \
          PGCLABEL=AerieMigratedUp \
          PGBINDIR=/usr/bin \
          ./pgcmp/pgcmp-dump
        shell: bash
      - name: Share Database Dump
        uses: actions/upload-artifact@v4
        with:
          name: migrated-db-dump
          path: pgdumpmigrated
          retention-days: 1
      - name: Print Logs for Services
        if: success() || failure()
        run: docker compose logs -t
      - name: Stop Postgres and Hasura
        run: |
          docker ps -a
          docker compose down
          docker ps -a
      - name: Prune Volumes
        run: docker volume prune --force

  dump-current:
    runs-on: ubuntu-latest
    environment: e2e-test
    steps:
      - name: Checkout Repo
        uses: actions/checkout@v4
      - name: Setup Postgres Client (psql)
        # ubuntu-latest does not currently have psql-16 in its apt library, so we cannot just run
        # sudo apt update && sudo apt-get install --yes postgresql-client-16
        # Current instructions are from https://www.postgresql.org/download/linux/ubuntu/
        run: |
          sudo apt update
          sudo apt install curl ca-certificates
          sudo install -d /usr/share/postgresql-common/pgdg
          sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
          sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          sudo apt update
          sudo apt -y install postgresql-client-16
      - name: Setup Python
        uses: actions/setup-python@v5
        with:
          python-version: "3.10"
      - name: Setup Hasura CLI
        run: sudo curl -L https://github.com/hasura/graphql-engine/raw/stable/cli/get.sh | bash
      - name: Start Postgres and Hasura
        run: |
          docker compose up -d postgres hasura
          docker images
          docker ps -a
      - name: Sleep for 1 Minute
        run: sleep 60s
        shell: bash
      - name: Clone PGCMP
        uses: actions/checkout@v4
        with:
          repository: NASA-AMMOS/pgcmp
          path: pgcmp
      - name: Dump Current Database
        run: |
          mkdir pgdumpcurrent
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie \
          PGCMPOUTPUT=./pgdumpcurrent/AerieCurrent \
          PGCLABEL=AerieCurrent \
          PGBINDIR=/usr/bin \
          ./pgcmp/pgcmp-dump
        shell: bash
      - name: Share Database Dump
        uses: actions/upload-artifact@v4
        with:
          name: current-sql-db-dump
          path: pgdumpcurrent
          retention-days: 1
      - name: Migrate Latest to Base
        run: |
          cd deployment
          cat << EOF > .env
          AERIE_USERNAME=${AERIE_USERNAME}
          AERIE_PASSWORD=${AERIE_PASSWORD}
          EOF
          python -m pip install -r requirements.txt
          python aerie_db_migration.py --revert --all
          cd ..
      - name: Dump Migrated Database
        run: |
          mkdir pgdumpmigrateddown
          PGURI=postgres://"${AERIE_USERNAME}":"${AERIE_PASSWORD}"@localhost:5432/aerie \
          PGCMPOUTPUT=./pgdumpmigrateddown/AerieMigratedDown \
          PGCLABEL=AerieMigratedDown \
          PGBINDIR=/usr/bin \
          ./pgcmp/pgcmp-dump
        shell: bash
      - name: Share Database Dump
        uses: actions/upload-artifact@v4
        with:
          name: migrated-down-db-dump
          path: pgdumpmigrateddown
          retention-days: 1
      - name: Print Logs for Services
        run: docker compose logs -t
      - name: Stop Postgres and Hasura
        run: |
          docker ps -a
          docker compose down
          docker ps -a
      - name: Prune Volumes
        run: docker volume prune --force

  pgcmp-up:
    needs: [dump-db-base, dump-current]
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Repo
        uses: actions/checkout@v4
      - name: Clone PGCMP
        uses: actions/checkout@v4
        with:
          repository: NASA-AMMOS/pgcmp
          path: pgcmp
      - name: Setup Postgres Client (psql)
        # ubuntu-latest does not currently have psql-16 in its apt library, so we cannot just run
        # sudo apt update && sudo apt-get install --yes postgresql-client-16
        # Current instructions are from https://www.postgresql.org/download/linux/ubuntu/
        run: |
          sudo apt update
          sudo apt install curl ca-certificates
          sudo install -d /usr/share/postgresql-common/pgdg
          sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
          sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          sudo apt update
          sudo apt -y install postgresql-client-16
      - name: Start Postgres
        run: docker run -d -p 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust --name=postgres postgres:16.4
      - name: Sleep for 5 Seconds
        run: sleep 5s
        shell: bash
      - name: Download Shared Dumps
        uses: actions/download-artifact@v4
        with:
          name: migrated-db-dump
          path: pgcmp/pgdumpmigrated
      - uses: actions/download-artifact@v4
        with:
          name: current-sql-db-dump
          path: pgcmp/pgdumpcurrent
      - name: Compare Databases
        id: dbcmp
        run: |
          cp ./.github/scripts/explanations pgcmp
          cp ./.github/scripts/compareDatabasesUp.sh pgcmp/compareDatabases.sh
          cd pgcmp
          ./compareDatabases.sh
        shell: bash
      - name: Upload Invalid
        if: ${{ failure() && steps.dbcmp.conclusion == 'failure' }}
        uses: actions/upload-artifact@v4
        with:
          name: pgcmpresultsup
          path: "**/results/"
      - name: Print Logs for Services
        if: always()
        run: docker logs -t postgres
      - name: Stop Postgres
        run: |
          docker ps -a
          docker stop postgres
          docker ps -a
      - name: Prune Volumes
        run: docker volume prune --force

  pgcmp-down:
    needs: [dump-db-base, dump-current]
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Repo
        uses: actions/checkout@v4
      - name: Clone PGCMP
        uses: actions/checkout@v4
        with:
          repository: NASA-AMMOS/pgcmp
          path: pgcmp
      - name: Setup Postgres Client (psql)
        # ubuntu-latest does not currently have psql-16 in its apt library, so we cannot just run
        # sudo apt update && sudo apt-get install --yes postgresql-client-16
        # Current instructions are from https://www.postgresql.org/download/linux/ubuntu/
        run: |
          sudo apt update
          sudo apt install curl ca-certificates
          sudo install -d /usr/share/postgresql-common/pgdg
          sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
          sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          sudo apt update
          sudo apt -y install postgresql-client-16
      - name: Start Postgres
        run: docker run -d -p 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust --name=postgres postgres:16.4
      - name: Sleep for 5 Seconds
        run: sleep 5s
        shell: bash
      - name: Download Shared Dumps
        uses: actions/download-artifact@v4
        with:
          name: v2_8_0-db-dump
          path: pgcmp/pgdumpV2_8_0
      - uses: actions/download-artifact@v4
        with:
          name: migrated-down-db-dump
          path: pgcmp/pgdumpmigrateddown
      - name: Compare Databases
        id: dbcmp
        run: |
          cp ./.github/scripts/explanations pgcmp
          cp ./.github/scripts/compareDatabasesDown.sh pgcmp/compareDatabases.sh
          cd pgcmp
          ./compareDatabases.sh
        shell: bash
      - name: Upload Invalid
        if: ${{ failure() && steps.dbcmp.conclusion == 'failure' }}
        uses: actions/upload-artifact@v4
        with:
          name: pgcmpresultsdown
          path: "**/results/"
      - name: Print Logs for Services
        if: always()
        run: docker logs -t postgres
      - name: Stop Postgres
        run: |
          docker ps -a
          docker stop postgres
          docker ps -a
      - name: Prune Volumes
        run: docker volume prune --force