immich_postgres healthcheck not working

[PotatoPTT]

The bug

In the latest dockercompose was added a healthcheck but in immich_postgres
SELECT SUM(checksum_failures) FROM pg_stat_database returns NULL not 0, so the healthcheck fails every time
For a temporary solution i just changed to SELECT COALESCE(SUM(checksum_failures), 0) FROM pg_stat_database

The OS that Immich Server is running on

Docker/Non Relatable

Version of Immich Server

v1.106.1

Version of Immich Mobile App

v1.106.1

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

database:
    container_name: immich_postgres
    image: ${IMMICH_POSTGRES}
    env_file:
      - .env
    networks:
      - non_exposed
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_INITDB_ARGS: '--data-checksums'
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: pg_isready --dbname='${DB_DATABASE_NAME}' || exit 1; Chksum="$$(psql --dbname='${DB_DATABASE_NAME}' --username='${DB_USERNAME}' --tuples-only --no-align --command='SELECT SUM(checksum_failures) FROM pg_stat_database')"; echo "checksum failure count is $$Chksum"; [ "$$Chksum" = '0' ] || exit 1
      interval: 5m
      start_interval: 30s
      start_period: 5m
    command: ["postgres", "-c" ,"shared_preload_libraries=vectors.so", "-c", 'search_path="$$user", public, vectors', "-c", "logging_collector=on", "-c", "max_wal_size=2GB", "-c", "shared_buffers=512MB", "-c", "wal_compression=on"]
    restart: always
    labels:
      - 'wud.watch=false'

Your .env content

NON RELATABLE

Reproduction steps

1. Run the container
2. Wait 5 minutes
3. Healthcheck fails

Relevant log output

No response

Additional information

No response

[AngelaDMerkel]

I am also experiencing this issue

[dsquare86]

Same issue on micro services and postgres

[PotatoPTT]

Same issue on micro services and postgres

microservices it's no longer required release notes

[zaggash]

This is not a bug.
You are using running the DB without checksum.
So do not use the new test.

You can however, backup the DB, then create a new one with the new env POSTGRES_INITDB_ARGS: '--data-checksums'.
Then restore the DB.

You database will be recreated with checksum enabled and the new healthcheck will work.

[lo97]

This is not a bug. You are using running the DB without checksum. So do not use the new test.

You can however, backup the DB, then create a new one with the new env POSTGRES_INITDB_ARGS: '--data-checksums'. Then restore the DB.

You database will be recreated with checksum enabled and the new healthcheck will work.

How should we backup the DB, create a new one and restore it? Does anyone have a step by step guide?

[Tractor777]

This seems to work (backed up and restore) and the health check doesn't fail https://immich.app/docs/administration/backup-and-restore/#:~:text=The%20recommended%20way%20to%20backup,to%20use%20the%20pg_dumpall%20command.

[PotatoPTT]

This is not a bug. You are using running the DB without checksum. So do not use the new test.

You can however, backup the DB, then create a new one with the new env POSTGRES_INITDB_ARGS: '--data-checksums'. Then restore the DB.

You database will be recreated with checksum enabled and the new healthcheck will work.

[mertalev]

I think this should be fixed. The health check shouldn’t report Postgres as unhealthy unless it has a reason to think it is. Adding a COALESCE would make it more elegant for existing databases (and generally for users who disable checksums).

[zaggash]

It is written in the documentation FAQ
I agree it deserves more visibility

https://immich.app/docs/FAQ#how-can-i-verify-the-integrity-of-my-database