Repository: simonw/django-sql-dashboard (language: python)

Issue:
Run tests against multiple PostgreSQL versions
> It would be great to have the GitHub Actions workflow run tests against multiple PostgreSQL versions to catch this kind of thing in the future https://github.com/simonw/django-sql-dashboard/blob/main/.github/workflows/test.yml

_Originally posted by @simonw in https://github.com/simonw/django-sql-dashboard/issues/138#issuecomment-873649061_


Discussion / hints:
Looks like this might help as a starting point

https://justatheory.com/2020/06/test-extensions-with-github-actions/
https://www.postgresql.org/download/linux/ubuntu/ has relevant instructions too.

The list of currently supported PostgreSQL versions is at https://www.postgresql.org/support/versioning/ - currently that's:

- 9.6
- 10
- 11
- 12
- 13
The GitHub Actions `ubuntu-latest` worker has PostgreSQL 13 by default - checked with `tmate`:
```
runner@fv-az206-57:~/work/datasette/datasette$ /usr/lib/postgresql/13/bin/postgres --version
postgres (PostgreSQL) 13.3 (Ubuntu 13.3-1.pgdg20.04+1)
```
I tried running this:
```
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
sudo apt-get -y install postgresql-12
```
And now this works too:
```
runner@fv-az206-57:~/work/datasette/datasette$ /usr/lib/postgresql/12/bin/postgres --version
postgres (PostgreSQL) 12.7 (Ubuntu 12.7-1.pgdg20.04+1)
```
`sudo apt-get -y install postgresql-10` worked but `sudo apt-get -y install postgresql-9` did not.
Since this installs multiple PostgreSQL versions I need to figure out how to tell `testing.postgresql` which one to launch. https://github.com/tk0miya/testing.postgresql/blob/master/src/testing/postgresql.py is relevant code.
```
        self.postgres = self.settings.pop('postgres')
        if self.postgres is None:
            self.postgres = find_program('postgres', ['bin'])
```
So I need to use the `settings["postgres"]` mechanism, which is a parameter passed to this constructor:

https://github.com/simonw/django-sql-dashboard/blob/65e7d51bd827a3f065c833378480488fe2db08e5/pytest_use_postgresql.py#L6-L8
I'm going to test on 10, 11, 12 and 13 - using a matrix.

Fix patch:
diff --git a/.github/workflows/publish.yml b/.github/workflows/publish.yml
index ee5ce9d..837524f 100644
--- a/.github/workflows/publish.yml
+++ b/.github/workflows/publish.yml
@@ -10,6 +10,7 @@ jobs:
     strategy:
       matrix:
         python-version: [3.6, 3.7, 3.8, 3.9]
+        postgresql-version: [10, 11, 12, 13]
     steps:
     - uses: actions/checkout@v2
     - name: Set up Python ${{ matrix.python-version }}
@@ -23,11 +24,23 @@ jobs:
         key: ${{ runner.os }}-pip-${{ hashFiles('**/setup.py') }}
         restore-keys: |
           ${{ runner.os }}-pip-
+    - name: Install PostgreSQL
+      env:
+        POSTGRESQL_VERSION: ${{ matrix.postgresql-version }}
+      run: |
+        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
+        sudo apt-get -y install "postgresql-$POSTGRESQL_VERSION"
     - name: Install dependencies
       run: |
         pip install -e '.[test]'
     - name: Run tests
+      env:
+        POSTGRESQL_VERSION: ${{ matrix.postgresql-version }}
       run: |
+        export POSTGRESQL_PATH="/usr/lib/postgresql/$POSTGRESQL_VERSION/bin/postgres"
+        export INITDB_PATH="/usr/lib/postgresql/$POSTGRESQL_VERSION/bin/initdb"
         pytest
   deploy:
     runs-on: ubuntu-latest


Test patch:
diff --git a/.github/workflows/test.yml b/.github/workflows/test.yml
index 4ee7d52..2e3c1df 100644
--- a/.github/workflows/test.yml
+++ b/.github/workflows/test.yml
@@ -8,6 +8,7 @@ jobs:
     strategy:
       matrix:
         python-version: [3.6, 3.7, 3.8, 3.9]
+        postgresql-version: [10, 11, 12, 13]
     steps:
     - uses: actions/checkout@v2
     - name: Set up Python ${{ matrix.python-version }}
@@ -21,11 +22,23 @@ jobs:
         key: ${{ runner.os }}-pip-${{ hashFiles('**/setup.py') }}
         restore-keys: |
           ${{ runner.os }}-pip-
+    - name: Install PostgreSQL
+      env:
+        POSTGRESQL_VERSION: ${{ matrix.postgresql-version }}
+      run: |
+        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
+        sudo apt-get -y install "postgresql-$POSTGRESQL_VERSION"
     - name: Install dependencies
       run: |
         pip install -e '.[test]'
     - name: Run tests
+      env:
+        POSTGRESQL_VERSION: ${{ matrix.postgresql-version }}
       run: |
+        export POSTGRESQL_PATH="/usr/lib/postgresql/$POSTGRESQL_VERSION/bin/postgres"
+        export INITDB_PATH="/usr/lib/postgresql/$POSTGRESQL_VERSION/bin/initdb"
         pytest
     - name: Check formatting
       run: black . --check
diff --git a/pytest_use_postgresql.py b/pytest_use_postgresql.py
index b04196f..42b5ac0 100644
--- a/pytest_use_postgresql.py
+++ b/pytest_use_postgresql.py
@@ -5,7 +5,9 @@
 from django.conf import settings
 from testing.postgresql import Postgresql
 
-_POSTGRESQL = Postgresql()
+postgres = os.environ.get("POSTGRESQL_PATH")
+initdb = os.environ.get("INITDB_PATH")
+_POSTGRESQL = Postgresql(postgres=postgres, initdb=initdb)
 
 
 @pytest.hookimpl(tryfirst=True)