Run tests against multiple PostgreSQL versions

[simonw]

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 #138 (comment)

[ryancheley]

Looks like this might help as a starting point

https://justatheory.com/2020/06/test-extensions-with-github-actions/

[simonw]

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

[simonw]

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)

[simonw]

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)

[simonw]

sudo apt-get -y install postgresql-10 worked but sudo apt-get -y install postgresql-9 did not.

[simonw]

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.

[simonw]

        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:

django-sql-dashboard/pytest_use_postgresql.py

Lines 6 to 8 in 65e7d51

	from testing.postgresql import Postgresql
	_POSTGRESQL = Postgresql()

[simonw]

I'm going to test on 10, 11, 12 and 13 - using a matrix.

[simonw]

If this passes I'll revert the change from #138 on this branch to confirm that it fails, then merge the new tests.

[simonw]

Error:

  File "/opt/hostedtoolcache/Python/3.6.13/x64/lib/python3.6/site-packages/testing/common/database.py", line 171, in wait_booting
    self.read_bootlog())
RuntimeError: *** failed to launch Postgresql ***
2021-07-06 00:20:45.509 UTC [4414] FATAL:  database files are incompatible with server
2021-07-06 00:20:45.509 UTC [4414] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 11.12 (Ubuntu 11.12-1.pgdg20.04+1).

[simonw]

I think this is the problem:

    def initialize(self):
        self.initdb = self.settings.pop('initdb')
        if self.initdb is None:
            self.initdb = find_program('initdb', ['bin'])

        self.postgres = self.settings.pop('postgres')
        if self.postgres is None:
            self.postgres = find_program('postgres', ['bin'])

So I need to pass in a custom path to initdb too.

[simonw]

That passed against all PostgreSQL versions!

[simonw]

I expect this to break in PostgreSQL 10.

[simonw]

It failed against PostgreSQL 10 as expected.

[simonw]

Wrote this up in a TIL: https://til.simonwillison.net/github-actions/different-postgresql-versions