PgBouncer

Lightweight connection pooler for PostgreSQL

Notes

This template assumes you have a database named Postgres in your project before deploying this template, if this isn't the case please adjust the reference variables so they reference the correct namespace.

pgbouncer is a PostgreSQL connection pooler. Any target application can be connected to pgbouncer as if it were a PostgreSQL server, and pgbouncer will create a connection to the actual server, or it will reuse one of its existing connections.

The aim of pgbouncer is to lower the performance impact of opening new connections to PostgreSQL.

In order not to compromise transaction semantics for connection pooling, pgbouncer supports several types of pooling when rotating connections.

Extra configurations can be done with the environment variables found on bitnami's pgbouncer image overview page

How to add a Userlist to PgBouncer

If you have already created a new user, skip to step 2.

Connect to the base Postgres database and create a new user -

CREATE USER 'new_user' WITH PASSWORD 'my_password';
ALTER USER new_user WITH LOGIN;
GRANT ALL PRIVILEGES ON DATABASE railway TO new_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO new_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO new_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO new_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO new_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO new_user;

Replace new_user and my_password with your desired username and a secure password.

Add or remove any privileges as you see fit.

Grab the rolpassword for the newly created user

SELECT rolpassword FROM pg_authid WHERE rolname = 'new_user';

Replace new_user with the username created in the first step.

Copy down the returned value from the rolpassword column.

Set the PGBOUNCER_USERLIST service variable.

Notes -

This must be done using the Raw Editor option to avoid unnecessary string sanitations.
The value for this variable should be encapsulated in quotes as shown.

PGBOUNCER_USERLIST=""new_user" "rolpassword""

Replace new_user with the username created in the first step, and replace rolpassword with the returned value from the previous step.

Deploy your changes.
Done! - Connect to PgBouncer using the username and password of the user created in the first step!