PostgreSQL

The Official PostgreSQL Docker Image

We base all our containers and Docker Compose definitions on the official postgres Docker image.

The "hello world" test for this container described in their README is:

$ docker run -it --rm --network some-network postgres psql -h some-postgres -U postgres
psql (14.3)
Type "help" for help.

postgres=# SELECT 1;
 ?column? 
----------
        1
(1 row)

If that works, then the following sections should work too.

Configuration for kwild

postgres must be running with a few configuration changes to support kwild:

• wal_level = logical (enabled logical replication)
• max_wal_senders = 10
• max_replication_slots = 10
• max_prepared_transactions = 2 (enable prepared transactions / two phase commit)

There also must be a postgres "role" (a user) with the name that corresponds to the app.pg_db_user setting. That user must have "superuser" privileges, and be the owner of a database that corresponds to the app.pg_db_name setting. The default for both is "kwild".

All of the above prerequisites are handled by one of our custom Docker containers in the following sections.

Our Extended postgres Docker Image

deployments/compose/postgres/Dockerfile defines a new Docker image that extends the official postgres:16.1 image to start with the required setting. It does not create the kwild user or database, as that is left to the user or a Docker Compose service definition (see the next section). This image is pushed to Docker Hub at https://hub.docker.com/r/jchappelow/kwil-postgres, and it is used by the Github Action to start a postgres service for use by the unit tests until they no longer rely on a postgres host to run.

Our Basic postgres Docker Compose Service

deployments/compose/postgres/docker-compose.yml defines a pg service (container name postgres) with a local db volume using the official postgres:16.1 Docker image. It starts the container with all of the above settings, and on creation it runs an initialization script, init.sql to create the "kwild" user and database.

You can start this service by running docker compose up from the deployments/compose/postgres folder.

NOTE: This service will try to bind on your host machine's port 5432, so if you have installed PostgreSQL on your system already, you will receive a bind: address already in use error. You may either use your system's postgres instance or change the port in docker-compose.yml.

This docker-compose.yml defines a named volume as follows:

volumes:
  db:
    driver: local

Because of this, it will persist it's data in a postgres_db volume that you can see in Docker Desktop. You will need to delete this if you want to start fresh. This is to facilitate a postgres instance for a node, not just tests.

Single kwild + postgres Node Container

deployments/compose/kwil/single/docker-compose.yml start containers for both kwild and postgres. The configuration for kwild will use the postgres instance in the container.

This is primarily for quick manual testing, as it uses kwild's --autogen switch to bring up a fresh network.

Acceptance Test Service

test/acceptance/docker-compose.yml is updated with a "pg" container that is used by the kwild container.

This one listens on the host port 5439 to avoid conflict with a system install or one of the above containers listening on 5432. Note that from within the kwil-act-testnet network created by this Compose file, the connection is on the normal port 5432.

Integration Test Service

test/integration/docker-compose.yml is updated with several new pgX containers used by the separate kwild node containers. These listen on ports 5433 to 5438. Their definitions are otherwise the same as the one used by the acceptance tests.