Configuration¶

This page shows where PgBouncer's configuration lives and how to make the common changes: pointing it at your database, users and passwords, pool sizes, backend certificate checks and network access. Edit the files as root.

Where things live¶

Path	What it is
`/etc/pgbouncer/pgbouncer.ini`	Main configuration. Created on first boot and not overwritten afterwards.
`/etc/pgbouncer/userlist.txt`	User names and passwords that clients authenticate with.
`/var/lib/pgbouncer/credentials/app.password`	Password generated for the `app` user.
`/var/lib/pgbouncer/credentials/admin.password`	Password generated for the `pgbouncer` admin user.
`/var/lib/pgbouncer/tls/`	CA and server certificates and keys.
`/usr/local/share/sushi/pgbouncer/ca.pem`	Public copy of the CA certificate, for clients.

Apply a change¶

Most changes take effect on a reload, which keeps client connections open:

sudo systemctl reload pgbouncer

After you change the listener (listen_addr or listen_port), restart instead. A restart drops open client connections:

sudo systemctl restart pgbouncer

Point PgBouncer at your database¶

The [databases] section ships with two entries that point at the walkthrough's temporary backend:

[databases]
app = host=127.0.0.1 port=15432 dbname=app
session = host=127.0.0.1 port=15432 dbname=app pool_mode=session

Replace the host, port and database name with your own PostgreSQL or Amazon RDS endpoint, for example:

[databases]
app = host=<db-endpoint> port=<db-port> dbname=<db-name>
session = host=<db-endpoint> port=<db-port> dbname=<db-name> pool_mode=session

The name on the left is the database name your clients connect to. You can add more entries in the same form. Then reload PgBouncer.

Make sure the instance's security group can reach the database port, and that the database's security group allows the instance.

Users and passwords¶

Clients authenticate against /etc/pgbouncer/userlist.txt, which has one line per user:

"app" "<password>"
"pgbouncer" "<password>"

PgBouncer logs in to your database with the same user name and password the client used. So each user in this file must exist on your database with the same password.

Use the generated app password: create a role named app on your database with the password in /var/lib/pgbouncer/credentials/app.password.
Use an existing role: add a line for it to userlist.txt with its password, and connect your application as that user.

After any change to userlist.txt, reload PgBouncer. Keep the file's ownership and mode as they are (root:pgbouncer, 0640), and never paste passwords into tickets or chat.

The pgbouncer user is the admin and stats user for the admin console, see Operations.

Pool modes and sizes¶

The defaults are deliberately small:

pool_mode = transaction
max_client_conn = 50
default_pool_size = 2

pool_mode sets the default for all databases. A database entry can override it with pool_mode=session, as the session entry does.
max_client_conn is how many clients can connect to PgBouncer at once.
default_pool_size is how many server connections PgBouncer opens per database and user pair.

Raise them to fit your workload and your database's connection limit, then reload.

Verify the backend's certificate¶

server_tls_sslmode = require encrypts the connection to your database but does not check its certificate or host name. To verify it, set server_tls_sslmode = verify-full and point PgBouncer at your database's CA certificate with the server_tls_ca_file setting. Test a good and a bad endpoint before you rely on it.

Reach PgBouncer from your VPC¶

By default PgBouncer listens on 127.0.0.1 only. To accept clients from other hosts:

Set listen_addr to the instance's private address, after you review the security group.
In the security group, allow TCP 6432 only from your application's security group.
Issue a server certificate that includes that address. The certificate created on first boot covers only localhost and 127.0.0.1, so clients using sslmode=verify-full would reject it.
Restart PgBouncer and give your clients /usr/local/share/sushi/pgbouncer/ca.pem.

Keep it private

Do not set listen_addr to * or 0.0.0.0 with an open security group, and never set client_tls_sslmode to disable.