postgresql docker: FATAL: role "root" does not exist

The Problem

When you use docker to run postgresql (ex, postgres:14.5-bullseye) and enabled healthcheck, you might get health status from docker compose ps, but you might see error messages in your docker log.

Sample docker-compose.yml:

version: '3.7'

services:
  postgresql:
    image: postgres:14.5-bullseye
    container_name: postgresql
    hostname: postgresql
    ports:
      - "127.0.0.1:5432:5432"
    env_file:.env
    volumes:
      - ./db-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: always

Sample error message in docker log:

postgresql  | 2023-05-27 20:30:56.759 UTC [39] FATAL:  role "root" does not exist

The Cause

The test command is run by root user, which is not what postgres expected.

The Solution

To support customized user (specified in your env file, as in POSTGRES_USER variable), two things must be done:

1. create a file pg_check.sh, set executable permission with chmod +x pg_check.sh, and mount it into your container.
2. force your healthcheck test command to run with the system user postgres.

Content of pg_check.sh:

#!/bin/bash
pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}

Sample docker-compose.yml:

version: '3.7'

services:
  postgresql:
    image: postgres:14.5-bullseye
    ports:
      - "127.0.0.1:5432:5432"
    env_file:
      - .env
    volumes:
      - ./db-data:/var/lib/postgresql/data
      - ./pg_check.sh:/pg_check.sh
    healthcheck:
      test: ["CMD", "su", "-s", "/bin/sh", "-c", "/pg_check.sh", "postgres"]
      interval: 10s
      timeout: 5s
    restart: always