How to Set Up PgBouncer Connection Pooling on Ubuntu 24.04

Every PostgreSQL connection is a separate server process that uses several megabytes of RAM, so hundreds of short-lived connections from web workers or serverless functions can exhaust max_connections and memory long before the CPU is busy. PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL: clients open as many connections as they like to PgBouncer, and it multiplexes them onto a small, fixed pool of real server connections. In this tutorial you will install PgBouncer on Ubuntu 24.04 next to PostgreSQL 16, authenticate clients with SCRAM-SHA-256, configure transaction pooling, and monitor the pools.

Prerequisites

To follow this guide you need:

• A server running Ubuntu 24.04 LTS, such as a CubePath VPS, with a non-root user with sudo privileges.
• PostgreSQL installed and running on the same server. On a fresh server, install it with sudo apt install postgresql, which provides PostgreSQL 16.
• Basic familiarity with psql.

Step 1 - Creating a database and roles

Create an application role and a database it owns. Since PostgreSQL 14, passwords are stored as SCRAM-SHA-256 secrets by default, which PgBouncer can reuse without ever seeing the plain password. Open psql as the postgres superuser:

sudo -u postgres psql

Replace the passwords with strong values of your own:

CREATE ROLE appuser LOGIN PASSWORD 'your_app_password';
CREATE DATABASE appdb OWNER appuser;
CREATE ROLE pgbouncer_admin NOLOGIN PASSWORD 'your_admin_password';

pgbouncer_admin will only be used to log into PgBouncer's admin console. It is NOLOGIN, so it cannot connect to PostgreSQL itself. Confirm that the passwords are stored as SCRAM secrets and quit:

SELECT rolname, left(rolpassword, 14) FROM pg_authid WHERE rolname IN ('appuser', 'pgbouncer_admin');
\q

     rolname     |      left
-----------------+----------------
 appuser         | SCRAM-SHA-256$
 pgbouncer_admin | SCRAM-SHA-256$
(2 rows)

Step 2 - Installing PgBouncer

PgBouncer is in the Ubuntu repositories:

sudo apt update
sudo apt install pgbouncer

Check the version:

pgbouncer --version

PgBouncer 1.22.x

The package runs PgBouncer as the postgres system user, reads /etc/pgbouncer/pgbouncer.ini and writes its log to /var/log/postgresql/pgbouncer.log.

Step 3 - Creating the authentication file

PgBouncer checks client passwords against /etc/pgbouncer/userlist.txt, a file with one "user" "secret" line per role. Generate it directly from PostgreSQL so it contains the SCRAM secrets instead of plain passwords:

sudo -u postgres psql -At <<'SQL' | sudo tee /etc/pgbouncer/userlist.txt
SELECT format('"%s" "%s"', rolname, rolpassword) FROM pg_authid WHERE rolname IN ('appuser', 'pgbouncer_admin');
SQL

"appuser" "SCRAM-SHA-256$4096:...$...:..."
"pgbouncer_admin" "SCRAM-SHA-256$4096:...$...:..."

Restrict access to the file, since the secrets can be used to authenticate against PgBouncer:

sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

Note

If you change a role's password in PostgreSQL later, regenerate this file and reload PgBouncer, otherwise clients will fail to log in.

Step 4 - Configuring PgBouncer

Keep a copy of the packaged configuration for reference:

sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.orig

Open the file:

sudo nano /etc/pgbouncer/pgbouncer.ini

Replace its contents with the following:

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

admin_users = pgbouncer_admin
stats_users = pgbouncer_admin

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
max_prepared_statements = 100

server_idle_timeout = 600
ignore_startup_parameters = extra_float_digits

The important settings:

• [databases] maps the name clients ask for (appdb) to a real PostgreSQL server. PgBouncer connects over TCP to 127.0.0.1, which the default Ubuntu pg_hba.conf already allows with scram-sha-256.
• auth_type = scram-sha-256 makes clients authenticate with SCRAM. Because the client proves its password with SCRAM, PgBouncer can reuse that exchange to log into PostgreSQL with the stored secret.
• max_client_conn is how many client connections PgBouncer accepts. default_pool_size is how many server connections it opens per user and database pair. Here 1,000 clients share at most 20 (plus 5 reserve) PostgreSQL connections.
• reserve_pool_size adds extra server connections when a client has waited more than reserve_pool_timeout seconds, to absorb short bursts.
• max_prepared_statements lets PgBouncer track protocol-level prepared statements in transaction mode, which most modern drivers use. It is available from PgBouncer 1.21.
• ignore_startup_parameters = extra_float_digits avoids a connection error with drivers such as JDBC that send that parameter at login.

Choosing a pool mode

In transaction mode, anything that lasts beyond a transaction breaks, because the next transaction may run on a different server connection. Replace session-level SET with SET LOCAL inside the transaction, and move LISTEN or session advisory locks to a direct connection to port 5432.

Sizing the pool

The total number of server connections PgBouncer can open must stay below PostgreSQL's max_connections (100 by default), leaving room for superuser and maintenance sessions. Check the current limit:

sudo -u postgres psql -Atc "SHOW max_connections;"

A pool of 20 to 30 per database is plenty for most servers; more active connections than a few times the number of CPU cores usually increases latency instead of throughput.

Step 5 - Restarting and testing PgBouncer

Restart the service to load the new configuration and make sure it is enabled at boot:

sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer
sudo systemctl status pgbouncer

Check that it listens on port 6432:

sudo ss -ltnp | grep 6432

LISTEN 0      128        127.0.0.1:6432       0.0.0.0:*    users:(("pgbouncer",pid=4121,fd=7))

Connect through PgBouncer as the application user. Note the port, 6432 instead of 5432:

psql -h 127.0.0.1 -p 6432 -U appuser appdb -c "SELECT current_user, inet_server_port();"

 current_user | inet_server_port
--------------+------------------
 appuser      |             5432
(1 row)

inet_server_port() returns 5432 because the query ran on a real PostgreSQL connection that PgBouncer opened on your behalf.

Point your application at PgBouncer by changing only the port in its connection string:

postgresql://appuser:[email protected]:6432/appdb

Step 6 - Monitoring pools from the admin console

PgBouncer exposes a virtual database called pgbouncer that accepts SHOW commands. Connect with the admin role:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer

Show the state of each pool:

SHOW POOLS;

 database |   user    | cl_active | cl_waiting | ... | sv_active | sv_idle | sv_used | ... | maxwait | pool_mode
----------+-----------+-----------+------------+-----+-----------+---------+---------+-----+---------+-------------
 appdb    | appuser   |         0 |          0 | ... |         0 |       5 |       0 | ... |       0 | transaction
 pgbouncer| pgbouncer |         1 |          0 | ... |         0 |       0 |       0 | ... |       0 | statement

The columns to watch:

• cl_active / cl_waiting: clients linked to a server connection, and clients queued waiting for one. A cl_waiting that stays above zero, together with a growing maxwait (seconds), means the pool is too small or transactions are too long.
• sv_active / sv_idle: server connections in use and ready for reuse.

Other useful commands:

SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW CONFIG;

SHOW STATS gives per-database totals and averages such as avg_xact_time and avg_wait_time (in microseconds). To apply changes to pgbouncer.ini or userlist.txt without dropping connections, run RELOAD; here or sudo systemctl reload pgbouncer from the shell.

Step 7 - Allowing remote application servers (optional)

If your application runs on another server, make PgBouncer listen on the private interface. In /etc/pgbouncer/pgbouncer.ini, set listen_addr to the server's private IP, for example 10.0.0.5:

listen_addr = 10.0.0.5

Allow only the application server (here 10.0.0.20) through the firewall and restart PgBouncer, since a listen address change needs a restart rather than a reload:

sudo ufw allow from 10.0.0.20 to any port 6432 proto tcp
sudo systemctl restart pgbouncer

PostgreSQL itself can stay bound to localhost: only PgBouncer talks to it.

Troubleshooting

FATAL: password authentication failed: the secret in userlist.txt no longer matches PostgreSQL, or the user is missing from the file. Regenerate it as in Step 3 and reload. Check /var/log/postgresql/pgbouncer.log for the exact reason.

no such database: appdb: the name the client asked for is not listed under [databases].

prepared statement "..." does not exist: your driver uses prepared statements in transaction mode. Make sure max_prepared_statements is set and greater than zero, then reload.

Clients stuck waiting (cl_waiting high): long transactions hold server connections. Find them with SELECT pid, now() - xact_start, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY 2 DESC; in PostgreSQL before increasing default_pool_size.

Conclusion

PgBouncer now accepts up to 1,000 client connections and serves them with a small pool of PostgreSQL connections, using SCRAM authentication without storing any plain passwords. As next steps, tune default_pool_size using the SHOW POOLS and SHOW STATS numbers under real load, look at the auth_query option to avoid maintaining userlist.txt by hand, and collect PgBouncer metrics with a Prometheus exporter.