--- title: "How to Set Up PgBouncer Connection Pooling on Ubuntu 24.04" description: "Install PgBouncer on Ubuntu 24.04, connect it to PostgreSQL with SCRAM auth, pick a pool mode and size, and monitor the pools from the admin console." category: "Database Advanced" language: en source: https://cubepath.com/docs/database-advanced/database-connection-pooling-with-pgbouncer updated: 2026-09-25 --- # 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: ```bash sudo -u postgres psql ``` Replace the passwords with strong values of your own: ```sql 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: ```sql SELECT rolname, left(rolpassword, 14) FROM pg_authid WHERE rolname IN ('appuser', 'pgbouncer_admin'); \q ``` ```output rolname | left -----------------+---------------- appuser | SCRAM-SHA-256$ pgbouncer_admin | SCRAM-SHA-256$ (2 rows) ``` ## Step 2 - Installing PgBouncer PgBouncer is in the Ubuntu repositories: ```bash sudo apt update sudo apt install pgbouncer ``` Check the version: ```bash pgbouncer --version ``` ```output 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: ```bash 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 ``` ```output "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: ```bash 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: ```bash sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.orig ``` Open the file: ```bash sudo nano /etc/pgbouncer/pgbouncer.ini ``` Replace its contents with the following: ```ini [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 | Mode | Server connection returned to the pool | Use it when | |------|----------------------------------------|-------------| | `session` | When the client disconnects | The app relies on session state (`SET`, advisory locks, `LISTEN/NOTIFY`, temporary tables). Only helps with connection churn, not with the number of connections. | | `transaction` | At the end of each transaction | Web apps and workers with short transactions. This is the mode that actually reduces server connections. | | `statement` | After each statement | Only for autocommit workloads; multi-statement transactions are rejected. | 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: ```bash 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: ```bash sudo systemctl restart pgbouncer sudo systemctl enable pgbouncer sudo systemctl status pgbouncer ``` Check that it listens on port 6432: ```bash sudo ss -ltnp | grep 6432 ``` ```output 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: ```bash psql -h 127.0.0.1 -p 6432 -U appuser appdb -c "SELECT current_user, inet_server_port();" ``` ```output 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: ```text postgresql://appuser:your_app_password@127.0.0.1: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: ```bash psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer ``` Show the state of each pool: ```sql SHOW POOLS; ``` ```output 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: ```sql 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`: ```ini 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: ```bash 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.