test_postgresql_OS.yml

name: Setup PostgreSQL on Multiple OS

on: [push]

jobs:
  postgresql-setup:
    runs-on: ${{ matrix.os }}
    strategy:
      matrix:
        os: [macos-latest, ubuntu-latest, windows-latest]
      fail-fast: false
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Download PostgreSQL Portable for macOS
        if: matrix.os == 'macos-latest'
        run: wget https://get.enterprisedb.com/postgresql/postgresql-16.4-1-osx-binaries.zip -O postgresql.zip

      - name: Download PostgreSQL source for Ubuntu
        if: matrix.os == 'ubuntu-latest'
        run: wget https://ftp.postgresql.org/pub/source/v14.1/postgresql-14.1.tar.gz -O postgresql.tar.gz

      - name: Build PostgreSQL from source on Ubuntu
        if: matrix.os == 'ubuntu-latest'
        run: |
          WORKSPACE_ABS_PATH=$(realpath "$GITHUB_WORKSPACE")
          tar -xzf postgresql.tar.gz -C $WORKSPACE_ABS_PATH
          cd $WORKSPACE_ABS_PATH/postgresql-14.1
          ./configure --prefix=$WORKSPACE_ABS_PATH/postgresql
          make
          make install

      - name: Download PostgreSQL Portable for Windows
        if: matrix.os == 'windows-latest'
        run: |
          Invoke-WebRequest -Uri https://get.enterprisedb.com/postgresql/postgresql-16.4-1-windows-x64-binaries.zip -OutFile postgresql.zip
        shell: pwsh

      - name: Unpack PostgreSQL archive on macOS or Windows
        if: matrix.os == 'macos-latest' || matrix.os == 'windows-latest'
        run: |
          unzip postgresql.zip -d .
          mv ./pgsql ./postgresql

      - name: Setup PostgreSQL environment variables
        run: |
          echo 'POSTGRESQL_HOME=./postgresql' >> $GITHUB_ENV
          echo 'PATH=$POSTGRESQL_HOME/bin:$PATH' >> $GITHUB_ENV
          source $GITHUB_ENV
        shell: bash

      - name: Initialize PostgreSQL data directory
        run: |
          ./postgresql/bin/initdb -D ./postgresql_data
        shell: bash

      - name: Start PostgreSQL server
        run: |
          ./postgresql/bin/pg_ctl -D ./postgresql_data -l logfile start
        shell: bash

      - name: Wait for PostgreSQL to start
        run: |
          for i in {30..0}; do
            if ./postgresql/bin/pg_isready &>/dev/null; then
              break
            fi
            echo 'Waiting for PostgreSQL to start...'
            sleep 1
          done
        shell: bash

      - name: Set up PostgreSQL user and database on MacOS
        if: matrix.os == 'macos-latest'
        run: |
          ./postgresql/bin/createdb -U runner "runner"
          ./postgresql/bin/psql -U runner -c "CREATE USER msnoise WITH PASSWORD 'msnoise';"
          ./postgresql/bin/psql -U runner -c "ALTER USER msnoise WITH SUPERUSER;"
        shell: bash

      - name: Set up PostgreSQL user and database on Windows
        if: matrix.os == 'windows-latest'
        run: |
          ./postgresql/bin/createdb -U runneradmin "runneradmin"
          ./postgresql/bin/psql -U runneradmin -c "CREATE USER msnoise WITH PASSWORD 'msnoise';"
          ./postgresql/bin/psql -U runneradmin -c "ALTER USER msnoise WITH SUPERUSER;"
        shell: bash

      - name: Verify PostgreSQL setup on MacOS
        if: matrix.os == 'macos-latest'
        run: |
          ./postgresql/bin/psql -U runner -c "\l"
        shell: bash

      - name: Verify PostgreSQL setup on Windows
        if: matrix.os == 'windows-latest'
        run: |
          ./postgresql/bin/psql -U runneradmin -c "\l"
        shell: bash