docker-compose health check should be aware of restart during install of postgis extension

[devrimbaris]

Feature description

Hi,
Currently the pg_isready check seems to return true when initial container starts. However after postgis extensions are installed the database is shut down. The second service may get the healthy trigger and try to connect to a shutdown postgresql.

A check like following which actually checks postgis extension installation may help overcome this issue:

test: [ "CMD-SHELL","psql -U docker -d fsdb -c \"SELECT ST_Buffer( ST_SetSRID('POINT(0 0)'::GEOMETRY, 4326), 1) AS geom ;\""]

Additional context

No response

[NyakudyaA]

Doesn't the health check occur after the last restart of the DB?

[devrimbaris]

That health check is done via docker engine. It doesnt know if it is up or not.

[NyakudyaA]

Revisiting this again, how do we check for the health check if

  - POSTGRES_MULTIPLE_EXTENSIONS=hstore

Assuming a user hasn't installed the postgis extension?

[Cellule]

Just chiming here, I've just faced the same issue where the postgres container with postgis would look healthy from pg_isready check only to reboot momentarily.
My issue was that another service was waiting on the database to be healthy to start and perform various setup on the database.
Because of that reboot, the 2nd service would sometimes try to perform the setup while the database was down and fail.
All of that happening in the span of less than 1 second.

I ended up using @devrimbaris solution for the health check, I had to add POSTGRES_DB: <db_name> to create the initial database (used to be performed later by another service)

Ideally, pg_isready would not be ready until the extension is installed and database is rebooted. I understand that might not simple to do.
Another option would be to at least document why pg_isready is not a good healthcheck for startup and provide an alternative

Here's my full service definition for reference

services:
  postgres:
    container_name: postgres
    image: postgis/postgis:16-3.4-alpine
    platform: linux/amd64
    restart: unless-stopped
    environment:
      PGDATA: /var/lib/postgresql/data/pgdata
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGUSER: some_user
      POSTGRES_DB: some_db
    ports:
      - "5433:5432"
    healthcheck:
      test:
        [
          "CMD-SHELL",
          'psql -U some_user -d some_db -c "SELECT ST_Buffer( ST_SetSRID(''POINT(0 0)''::GEOMETRY, 4326), 1) AS geom ;"',
        ]
      interval: 2s
      timeout: 1s
      retries: 5

[NyakudyaA]

@Cellule The main question hasn't been answered. What if a user doesn't install PostGIS i.e

POSTGRES_MULTIPLE_EXTENSIONS=hstore

[Cellule]

I'm not sure I understand, you mean what should the healthcheck be if postgis extension is not installed?
Is that a real use case when using the postgis docker image?

[NyakudyaA]

I'm not sure I understand, you mean what should the healthcheck be if postgis extension is not installed? Is that a real use case when using the postgis docker image?

Yes, Even though this is mainly geared toward Postgis, there could be rare situations where the users could run without the Postgis extension. In any case, I am not against this but maybe raise a PR and also add some notes in the README indicating that this is only valid if the Postgis extension is installed