pgBouncer

pgBouncer Connection Pooling Example

Crunchy pgBouncer is a lightweight connection pooler for PostgreSQL databases.

The following examples create the following containers:

pgBouncer Primary
pgBouncer Replica
PostgreSQL Primary
PostgreSQL Replica

In Kubernetes and OpenShift, this example will also create:

pgBouncer Primary Service
pgBouncer Replica Service
Primary Service
Replica Service
PostgreSQL Secrets
pgBouncer Secrets

To cleanup the objects created by this example, run the following in the pgbouncer example directory:

./cleanup.sh

For more information on pgBouncer, see the official website.

This example uses a custom configuration to create the pgbouncer user and an auth function in the primary for the pgbouncer containers to authenticate against. It takes advantage of the post-startup-hook and a custom sql file mounted in the /pgconf directory.

Docker

Run the pgbouncer example:

cd $CCPROOT/examples/docker/pgbouncer
./run.sh

Once all containers have deployed and are ready for use, psql to the target databases through pgBouncer:

psql -d userdb -h 0.0.0.0 -p 6432 -U testuser
psql -d userdb -h 0.0.0.0 -p 6433 -U testuser

To connect to the administration database within pgbouncer, connect using psql:

psql -d pgbouncer -h 0.0.0.0 -p 6432 -U pgbouncer
psql -d pgbouncer -h 0.0.0.0 -p 6433 -U pgbouncer

Kubernetes and OpenShift

OpenShift: If custom configurations aren’t being mounted, an emptydir volume is required to be mounted at /pgconf.

Run the pgbouncer example:

cd $CCPROOT/examples/kube/pgbouncer
./run.sh

Once all containers have deployed and are ready for use, psql to the target databases through pgBouncer:

psql -d userdb -h pgbouncer-primary -p 6432 -U testuser
psql -d userdb -h pgbouncer-replica -p 6432 -U testuser

To connect to the administration database within pgbouncer, connect using psql:

psql -d pgbouncer -h pgbouncer-primary -p 6432 -U pgbouncer -c "SHOW SERVERS"
psql -d pgbouncer -h pgbouncer-replica -p 6432 -U pgbouncer -c "SHOW SERVERS"