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

Instructions for Setting Up

1. Create a .env file in the same directory as the docker-compose.yml:

   • This file should contain the following environment variables:

   POSTGRES_USER=myuser
   POSTGRES_PASSWORD=mypassword
   POSTGRES_DB=mydatabase

   • Replace myuser, mypassword, and mydatabase with your desired values.

2. Directory Structure:

   • Ensure your directory looks something like this:

     your-project-directory/
     ├── docker-compose.yml
     ├── .env
     └── postgres-data/  # This will be created automatically to store PostgreSQL data
     

3. Starting the Services:

   • Run the following command in your terminal:

     docker-compose up -d

   • This will start the PostgreSQL, PgBouncer, and Adminer services in the background.

4. Accessing Adminer:

   • Open your browser and navigate to http://localhost:8080 to access Adminer.
   • Use the following credentials:
     • Server: pgbouncer
     • Username: myuser (or your chosen POSTGRES_USER)
     • Password: mypassword (or your chosen POSTGRES_PASSWORD)
     • Database: mydatabase (or your chosen POSTGRES_DB)

5. Stopping the Services:

   • Run the following command to stop the services:

     docker-compose down

This setup will allow you to manage your PostgreSQL database using PgBouncer and Adminer, with configurations conveniently stored in a .env file.

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