Setup Docker Compose for Postgres and PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL. It sits between your application and the PostgreSQL server, reusing existing database connections to reduce the overhead of establishing new ones — which is particularly beneficial for high-concurrency applications or microservices architectures.

🔧 What PgBouncer Does:

• Manages a pool of PostgreSQL connections.
• Reuses active connections instead of opening/closing new ones frequently.
• Reduces resource consumption on the PostgreSQL server.

✅ Pros of PgBouncer:

⚠️ Cons of PgBouncer:

🧠 Pooling Modes in PgBouncer:

lets, start…..

first, create file postgres-docker-compose.yml (or using filename anything you want)

services:
  postgres-db:
    image: postgres:17.4-alpine3.21
    container_name: pg
    restart: unless-stopped
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: "myuser"
      POSTGRES_PASSWORD: "mypass"
      POSTGRES_DB: "mydb"
    volumes:
      - /home/docker/postgres/data:/var/lib/postgresql/data
      - /home/docker/postgres/postgresql.conf:/etc/postgresql/postgresql.conf
      - /home/docker/postgres/pg_hba.conf:/etc/postgresql/pg_hba.conf
    command: postgres -c config_file=/etc/postgresql/postgresql.conf

  pgbouncer:
    image: edoburu/pgbouncer
    container_name: pgbouncer
    restart: always
    depends_on:
      - postgres-db
    ports:
      - "6432:6432"
    environment:
      DB_USER: "myuser"
      DB_PASSWORD: "mypass"
    volumes:
      - /home/docker/pgbouncer/pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini
      - /home/docker/pgbouncer/userlist.txt:/etc/pgbouncer/userlist.txt

for example, structure for host directory:

--/home/docker
    --/postgres
        --/data
        --postgresql.conf
        --pg_hba.conf
    --/pgbouncer
        --pgbouncer.ini
        --userlist.txt

i assummed have 5Gb RAM for VPS, so my configuration is :

my /postgres/postgresql.conf`

#listen_addresses = '*'
listen_addresses = '192.168.0.3' 
# Memory
shared_buffers = 1GB
effective_cache_size = 4GB
work_mem = 16MB
maintenance_work_mem = 512MB

# WAL
wal_buffers = 16MB
checkpoint_completion_target = 0.9

# Query planner
random_page_cost = 1.1

# Logging
log_min_duration_statement = 500
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '

# Performance
synchronous_commit = off

my postgres/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               md5

my /pgbouncer/pgbouncer.ini

[databases]
* = host=pg port=5432
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = session
max_client_conn = 200
default_pool_size = 50
reserve_pool_size = 10
reserve_pool_timeout = 5.0

log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

server_reset_query = DISCARD ALL
ignore_startup_parameters = extra_float_digits

connect to postgres 5432, then type SQL

SELECT usename,passwd FROM pg_shadow;

you’ll see :

username    | password
myuser      | SCRAM-SHA-256$4096:rixxxxxxxxxxxxxxxxxxxxxxxxxx

add in pgbouncer/userlist.txt

"myuser" "SCRAM-SHA-256$4096:rixxxxxxxxxxxxxxxxxxxxxxxxxx"

then, just running

docker-compose -f postgres-docker-compose.yml up -d

voila, you just success to running setup docker compose for postgres and pgbouncer, you can connect postgres via pgbouncer on port 6432

references :
https://dntffm.medium.com/instalasi-dan-konfigurasi-pgbouncer-untuk-postgresql-mu-b2ac08e42385
chatgpt