edoburu/docker-pgbouncer — 599 Stars

Topics

#docker #kubernetes #pgbouncer #postgresql

Language breakdown

Shell 82.6%

Dockerfile 11.7%

Makefile 5.6%

▸ Files click to expand

README

PgBouncer Docker image ======================

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

Features:

Very small, quick to pull (just 15MB)
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/pgbouncer/userlist.txt are auto-created if they don't exist.


Why use 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.
Usage 

<pre><code class="lang-sh">docker run --rm \     -e DATABASE_URL=&quot;postgres://user:pass@postgres-host/database&quot; \     -p 5432:5432 \     edoburu/pgbouncer</code></pre>
Or using separate variables:
<pre><code class="lang-sh">docker run --rm \     -e DB_USER=user \     -e DB_PASSWORD=pass \     -e DB_HOST=postgres-host \     -e DB_NAME=database \     -p 5432:5432 \     edoburu/pgbouncer</code></pre>

During startup we'll generate the necessary userlist.txt & pgbouncer.ini entries to match this information. Connecting should work as expected:


<pre><code class="lang-sh">psql &#39;postgresql://user:pass@localhost/dbname&#39;</code></pre>

> [!NOTE] > If you need quick multi-user setup you can use DATABASEURLS (instead of DATABASEURL) with a comma (,) separated list of URLs. > > <pre><code class="lang-sh">> docker run --rm \ > -e DATABASE_URLS="postgres://foo:123@postgres-host/foo,postgres://bar:456@postgres-host/bar" \ > -p 5432:5432 \ > edoburu/pgbouncer ></code></pre>


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:
<pre><code class="lang-sh">docker run --rm \     -e DATABASE_URL=&quot;postgres://user:pass@postgres-host/database&quot; \     -e POOL_MODE=session \     -e SERVERRESETQUERY=&quot;DISCARD ALL&quot; \     -e MAXCLIENTCONN=100 \     -p 5432:5432     edoburu/pgbouncer</code></pre>
Kubernetes integration 

For example in Kubernetes, see the examples/kubernetes folder.
Docker Compose 

For example in Docker Compose, see the examples/docker-compose folder.
PostgreSQL configuration

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


<pre><code class="lang-conf"># TYPE  DATABASE        USER            ADDRESS                 METHOD host    all             all             10.0.0.0/8              md5</code></pre>
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:


<pre><code class="lang-sh">docker run --rm \     -e DB_USER=user \     -e DB_PASSWORD=pass \     -e DB_HOST=postgres-host \     -e DB_NAME=database \     -v pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro     -p 5432:5432     edoburu/pgbouncer</code></pre>

Or extend the Dockerfile:


<pre><code class="lang-Dockerfile">FROM edoburu/pgbouncer:1.11.0 COPY pgbouncer.ini userlist.txt /etc/pgbouncer/</code></pre>

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

The userlist.txt file uses the following format:


<pre><code class="lang-txt">&quot;username&quot; &quot;plaintext-password&quot;</code></pre>
or:
<pre><code class="lang-txt">&quot;username&quot; &quot;md5&lt;md5 of password + username&gt;&quot;</code></pre>
Use examples/generate-userlist to generate this file:
<pre><code class="lang-sh">examples/generate-userlist &gt;&gt; userlist.txt</code></pre>

You can also connect with a single user to PgBouncer, and from there retrieve the actual database password by setting 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:


<pre><code class="lang-sh">psql postgres://postgres@hostname-of-container/pgbouncer  # outside container psql postgres://127.0.0.1/pgbouncer                       # inside container</code></pre>

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;

🔗 More in this category