Docker Compose Setup for PostgreSQL with PgBouncer and Adminer

docker-compose.yml

# Docker Compose file for setting up PostgreSQL with PgBouncer and Adminer

version: '3.8'  # Specify the Docker Compose version

services:
  # PostgreSQL database service
  postgredb:
    image: postgres:latest  # Use the latest PostgreSQL image
    environment:
      # Environment variables are loaded from the .env file
      POSTGRES_USER: ${POSTGRES_USER}  # Database user (from .env)
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}  # Database password (from .env)
      POSTGRES_DB: ${POSTGRES_DB}  # Database name (from .env)
      POSTGRES_HOST_AUTH_METHOD: md5  # Authentication method
      POSTGRES_INITDB_ARGS: --auth=md5  # Initialize DB with MD5 auth
    volumes:
      - ./postgres-data:/var/lib/postgresql/data  # Persist data in a local folder

  # PgBouncer service for connection pooling
  pgbouncer:
    image: edoburu/pgbouncer:latest  # Use the latest PgBouncer image
    ports:
      - "5434:5432"  # Expose PgBouncer on port 5434
    environment:
      # Use environment variables from the .env file
      DB_USER: ${POSTGRES_USER}  # Reuse the PostgreSQL user
      DB_PASSWORD: ${POSTGRES_PASSWORD}  # Reuse the PostgreSQL password
      DB_HOST: postgredb  # Link to the PostgreSQL service
      DB_NAME: ${POSTGRES_DB}  # Reuse the PostgreSQL database name
      ADMIN_USERS: postgres,admin  # Admin users for PgBouncer

  # Adminer service for database management
  adminer:
    image: adminer:latest  # Use the latest Adminer image
    restart: always  # Always restart Adminer if it stops
    ports:
      - 8080:8080  # Expose Adminer on port 8080
    environment:
      ADMINER_DEFAULT_SERVER: pgbouncer  # Set PgBouncer as the default server
      ADMINER_DEFAULT_SYSTEM: pgsql  # Set PostgreSQL as the default system
    depends_on:
      - pgbouncer  # Ensure PgBouncer starts before Adminer

# .env file structure:
# --------------------
# POSTGRES_USER=myuser
# POSTGRES_PASSWORD=mypassword
# POSTGRES_DB=mydatabase

Important Note on Authentication

Starting with PostgreSQL version 14, the default authentication method has changed to scram-sha-256. However, PgBouncer expects md5 authentication by default. To avoid authentication errors such as Npgsql.PostgresException: '08P01: server login failed: wrong password type', make sure to configure PostgreSQL to use md5 authentication. This can be done by setting the POSTGRES_HOST_AUTH_METHOD and POSTGRES_INITDB_ARGS environment variables in your Docker Compose file.

If you prefer to use scram-sha-256 with PgBouncer, you will need to use a compatible PgBouncer image, such as rmccaffrey/pgbouncer:latest, and adjust your configuration accordingly.

See: https://stackoverflow.com/questions/76046768/configure-pgbouncer-and-postgresql-in-docker-compose