PostgreSQL testing

The procedures and libraries we use for testing the PostgreSQL backend for pepys-import have tripped a few people up - so this page attempts to document the details of how - and why - we test PostgreSQL.

We use the testing.postgresql library for testing our PostgreSQL interfaces. This library actually starts and stops new Postgres servers as part of each test. The benefits of this are that:

• We don't have to have a Postgres server set up on a specific port and with specific users for the tests to work
• We can test the creation and destruction of the database
• Anything we do for the tests will be deleted straight after the test - and we can't affect any 'real' data

However, doing things this way means that the tests look a little strange in places, and that we have to have a Postgres server set up on the machine that we're running the tests on, and have the various executables (principally postgres and initdb) available on the PATH. This means we cannot run the tests against a remote Postgres server.

In the tests, we create a testing.postgresql.Postgresql instance in the setUp method of a test class, as follows:

self.postgres = Postgresql(
                database="test",
                host="localhost",
                user="postgres",
                password="postgres",
                port=55527,
            )

It should be remembered that this is not anything from within pepys, and is not setting up a connection to a server. This is a call to a testing.postgresql function that will, when run, create a Postgres server which can be accessed using the credentials, host and port provided.

Once we've created the server to connect to, we then use pepys functions to connect to and initialise a DataStore:

self.store = DataStore(
                db_name="test",
                db_host="localhost",
                db_username="postgres",
                db_password="postgres",
                db_port=55527,
            )
self.store.initialise()

In the tearDown method of the test class, we stop the Postgres server:

self.postgres.stop()

Windows specific issues

testing.postgresql has a few issues on Windows. To work at all on Windows, the latest master version from the testing.postgresql Github repo must be installed, as this has a fix to stop the server correctly on Windows. (Note: requirements_dev.txt installs the master version).

In some circumstances on Windows the server can be closed in a slightly strange way, which leads to an error from psycopg2 (the library we use to access Postgres). As you will see from the example output below, all tests pass - but there is an error displaying - from what I can tell, this is not a problem at all and can be ignored.

(venv) c:\IanMayo\pepys-import>python -m pytest tests\test_data_store_api_postgis.py
================================================= test session starts =================================================
platform win32 -- Python 3.7.6, pytest-5.3.5, py-1.8.1, pluggy-0.13.1
rootdir: c:\IanMayo\pepys-import
collected 36 items

tests\test_data_store_api_postgis.py ..............xxss.......xs.x.sss...                                        [100%]

================================ 26 passed, 6 skipped, 4 xfailed in 733.26s (0:12:13) ================================= Exception during reset or similar
Traceback (most recent call last):
  File "c:\IanMayo\pepys-import\venv\lib\site-packages\sqlalchemy\pool\base.py", line 680, in _finalize_fairy
    fairy._reset(pool)
  File "c:\IanMayo\pepys-import\venv\lib\site-packages\sqlalchemy\pool\base.py", line 867, in _reset
    pool._dialect.do_rollback(self)
  File "c:\IanMayo\pepys-import\venv\lib\site-packages\sqlalchemy\engine\default.py", line 530, in do_rollback
    dbapi_connection.rollback()
psycopg2.OperationalError: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.