PgBouncer Docker image

This is a minimal PgBouncer image, based on Alpine Linux.

Features:

• Very small, quick to pull (just around 18MB)
• Configurable using environment variables
• Uses standard Postgres port 5432, to work transparently for applications.
• Includes PostgreSQL client tools such as psql, pg_isready
• MD5 authentication by default.
• /etc/pgbouncer/pgbouncer.ini and /etc/pbbouncer/userlist.txt are auto-created if they don't exist.

Why using PgBouncer

PostgreSQL connections take up a lot of memory (about 10MB per connection). There is also a significant startup cost to establish a connection with TLS, hence web applications gain performance by using persistent connections.

By placing PgBouncer in between the web application and the actual PostgreSQL database, the memory and start-up costs are reduced. The web application can keep persistent connections to PgBouncer, while PgBouncer only keeps a few connections to the actual PostgreSQL server. It can reuse the same connection for multiple clients.

Available tags

Base images:

• latest
• 1.17.0
• 1.21.0

Usage

docker run --rm \
    -e DATABASE_URL="postgres://user:pass@postgres-host/database" \
    -p 5432:5432 \
    docker-registry.services.sabio.de/serviceware-ops/pgbouncer:latest

Or using separate variables:

docker run --rm \
    -e DATABASES_USER=user \
    -e DATABASES_PASSWORD=pass \
    -e DATABASES_HOST=postgres-host \
    -e DATABASES_NAME=database \
    -p 5432:5432 \
    docker-registry.services.sabio.de/serviceware-ops/pgbouncer:latest

Connecting should work as expected:

psql 'postgresql://user:pass@localhost/dbname'

Configuration

Almost all settings found in the pgbouncer.ini can be defined as environment variables, except a few that make little sense in a Docker environment (like port numbers, syslog and pid settings). See the entrypoint script for details. For example:

docker run --rm \
    -e DATABASE_URL="postgres://user:pass@postgres-host/database" \
    -e DATABASES_POOL_MODE=session \
    -e PGBOUNCER_SERVER_RESET_QUERY="DISCARD ALL" \
    -e PGBOUNCER_MAX_CLIENT_CONN=100 \
    -p 5432:5432
    docker-registry.services.sabio.de/serviceware-ops/pgbouncer:latest

PostgreSQL configuration

Make sure PostgreSQL at least accepts connections from the machine where PgBouncer runs! Update listen_addresses in postgresql.conf and accept incoming connections from your IP range (e.g. 10.0.0.0/8) in pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             10.0.0.0/8              md5

Using a custom configuration

When the default pgbouncer.ini is not sufficient, or you'd like to let multiple users connect through a single PgBouncer instance, mount an updated configuration:

docker run --rm \
    -e DATABASES_USER=user \
    -e DATABASES_PASSWORD=pass \
    -e DATABASES_HOST=postgres-host \
    -e DATABASES_NAME=database \
    -v pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro
    -p 5432:5432
    docker-registry.services.sabio.de/serviceware-ops/pgbouncer:latest

Or extend the Dockerfile:

FROM docker-registry.services.sabio.de/serviceware-ops/pgbouncer:1.17.0
COPY pgbouncer.ini userlist.txt /etc/pgbouncer/

When the pgbouncer.ini file exists, the startup script will not override it. An extra entry will be written to userlist.txt when DATABASE_URL contains credentials, or DATABASES_USER and DATABASES_PASSWORD are defined.

The userlist.txt file uses the following format:

"username" "plaintext-password"

or:

"username" "md5<md5 of password + username>"

Use examples/generate-userlist to generate this file:

examples/generate-userlist >> userlist.txt

You can also connect with a single user to PgBouncer, and from there retrieve the actual database password by setting PGBOUNCER_AUTH_USER. See the example from: https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/

Connecting to the admin console

When an admin user is defined, and it has a password in the userlist.txt, it can connect to the special pgbouncer database:

psql postgres://postgres@hostname-of-container/pgbouncer  # outside container
psql postgres://127.0.0.1/pgbouncer                       # inside container

Hence this requires a custom configuration, or a mount of a custom userlist.txt in the docker file. Various admin console commands can be executed, for example:

SHOW STATS;
SHOW SERVERS;
SHOW CLIENTS;
SHOW POOLS;

And it allows temporary disconnecting the backend database (e.g. for restarts) while the web applications keep a connection to PgBouncer:

PAUSE;
RESUME;