--- title: "How to Configure PgBouncer for PostgreSQL on Ubuntu 24.04" description: "Set up PgBouncer on Ubuntu 24.04 with transaction pooling, SCRAM authentication, prepared statement support and TLS, then monitor pools from the admin console." category: "Database Operations" language: en source: https://cubepath.com/docs/database-operations/pgbouncer-advanced-configuration-postgresql updated: 2026-09-25 --- # How to Configure PgBouncer for PostgreSQL on Ubuntu 24.04 PgBouncer is a lightweight connection pooler that sits between your applications and PostgreSQL. Each PostgreSQL connection is a separate backend process that costs memory, so applications that open hundreds of short-lived connections waste resources; PgBouncer keeps a small set of server connections open and hands them out to thousands of clients. In this tutorial you will install PgBouncer on Ubuntu 24.04, configure transaction pooling with SCRAM authentication, enable protocol-level prepared statements, add TLS for remote clients, and monitor the pools from the admin console. ## Prerequisites To follow this guide you need: - A server running Ubuntu 24.04 LTS with PostgreSQL 16 installed from the Ubuntu repositories (`sudo apt install postgresql`), for example a CubePath VPS. - A non-root user with `sudo` privileges. - Basic familiarity with `psql`. This guide runs PgBouncer on the same server as PostgreSQL, which is the most common layout. Ubuntu 24.04 ships PgBouncer 1.22, which includes every feature used here. ## Step 1 - Installing PgBouncer Install the package from the Ubuntu repositories: ```bash sudo apt update sudo apt install -y pgbouncer ``` The package runs PgBouncer as the `postgres` system user, reads its configuration from `/etc/pgbouncer/pgbouncer.ini` and listens on `127.0.0.1:6432` by default. Check the version and service: ```bash pgbouncer --version systemctl status pgbouncer --no-pager ``` ```output PgBouncer 1.22.0 ... Active: active (running) since Thu 2026-09-25 10:02:11 UTC; 8s ago ``` Keep a copy of the original configuration file, which documents every option in its comments: ```bash sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.orig ``` ## Step 2 - Creating a database and roles Create an application role and database to pool connections for, plus a role for the PgBouncer admin console. Replace both passwords with strong values of your own: ```bash sudo -u postgres psql ``` ```sql CREATE ROLE appuser LOGIN PASSWORD 'your_app_password'; CREATE DATABASE appdb OWNER appuser; CREATE ROLE pgbouncer_admin LOGIN PASSWORD 'your_admin_password'; \q ``` PostgreSQL 16 stores these passwords as SCRAM-SHA-256 hashes (`password_encryption = scram-sha-256` is the default), and the default `pg_hba.conf` on Ubuntu requires `scram-sha-256` for TCP connections from `127.0.0.1`. ## Step 3 - Configuring SCRAM authentication PgBouncer authenticates clients itself before handing them a server connection. The simplest secure method is to copy the SCRAM secrets from PostgreSQL into PgBouncer's `auth_file`. When a client logs in with SCRAM and PgBouncer holds the SCRAM secret, PgBouncer can also use it to log in to PostgreSQL, so no plain-text password is stored anywhere. Export the secrets in the `"user" "secret"` format that `userlist.txt` expects: ```bash sudo -u postgres psql -Atc \ "SELECT format('\"%s\" \"%s\"', rolname, rolpassword) FROM pg_authid WHERE rolname IN ('appuser','pgbouncer_admin');" \ | sudo tee /etc/pgbouncer/userlist.txt > /dev/null ``` Restrict the file to the `postgres` user and check its content: ```bash sudo chown postgres:postgres /etc/pgbouncer/userlist.txt sudo chmod 640 /etc/pgbouncer/userlist.txt sudo cat /etc/pgbouncer/userlist.txt ``` ```output "appuser" "SCRAM-SHA-256$4096:mRd...==$Lq...=:x9...=" "pgbouncer_admin" "SCRAM-SHA-256$4096:Yc2...==$Hk...=:Qp...=" ``` Whenever you change a role's password in PostgreSQL, run the export again and reload PgBouncer. > Note: For many roles, PgBouncer can instead look passwords up at login time with `auth_user` and `auth_query`. That requires a privileged lookup function in each database; the static file above is easier to audit for a small number of roles. ## Step 4 - Choosing a pool mode The pool mode decides when a server connection returns to the pool: | Mode | Server connection released | Use it when | |------|---------------------------|-------------| | `session` | When the client disconnects | The application relies on session state (`SET`, `LISTEN`, temporary tables, session advisory locks) | | `transaction` | When each transaction ends | Web applications and APIs with many short transactions; the usual choice | | `statement` | After every statement | Only autocommit workloads; multi-statement transactions are rejected | Transaction mode gives the best connection reuse, but consecutive transactions from one client can run on different server connections. Anything that must survive between transactions breaks: session-level `SET` (use `SET LOCAL` inside the transaction), `LISTEN`/`NOTIFY` listeners, `WITH HOLD` cursors, session advisory locks and temporary tables used across transactions. If part of your application needs these, point it at a separate PgBouncer database entry with `pool_mode=session`. ## Step 5 - Writing the PgBouncer configuration Open the configuration file: ```bash sudo nano /etc/pgbouncer/pgbouncer.ini ``` Replace its content with the following: ```ini [databases] appdb = host=127.0.0.1 port=5432 dbname=appdb [pgbouncer] listen_addr = 127.0.0.1 listen_port = 6432 unix_socket_dir = /var/run/postgresql logfile = /var/log/postgresql/pgbouncer.log pidfile = /var/run/postgresql/pgbouncer.pid ; Authentication auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt admin_users = pgbouncer_admin stats_users = pgbouncer_admin ; Pooling pool_mode = transaction max_client_conn = 500 default_pool_size = 20 min_pool_size = 5 reserve_pool_size = 5 reserve_pool_timeout = 3 max_db_connections = 50 ; Timeouts (seconds) server_idle_timeout = 300 server_lifetime = 3600 query_wait_timeout = 120 ; Prepared statements in transaction mode max_prepared_statements = 200 ; Parameters some drivers send that PgBouncer cannot track ignore_startup_parameters = extra_float_digits ``` The key settings are: - `default_pool_size`: server connections per database and user pair. Start around two to four times the number of CPU cores of the database server. - `max_db_connections`: a hard cap on server connections to one database across all users. Keep the sum of your pools below PostgreSQL's `max_connections` (100 by default), leaving room for superuser and maintenance sessions. - `max_client_conn`: how many clients PgBouncer accepts. Clients above the pool size wait in a queue instead of failing. - `reserve_pool_size` and `reserve_pool_timeout`: extra connections added when a client has waited more than 3 seconds, to absorb short bursts. - `query_wait_timeout`: a waiting client gets an error after 120 seconds rather than hanging forever. - `max_prepared_statements`: see the next step. Restart PgBouncer to apply the new configuration: ```bash sudo systemctl restart pgbouncer sudo systemctl status pgbouncer --no-pager ``` Connect through PgBouncer on port 6432 and enter the application password: ```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) ``` The query reports port 5432 because it runs on the PostgreSQL backend that PgBouncer assigned. If the connection fails, check `sudo tail -n 20 /var/log/postgresql/pgbouncer.log`. ## Step 6 - Enabling prepared statements in transaction mode Drivers such as JDBC, asyncpg, pgx and psycopg 3 use protocol-level prepared statements. Before PgBouncer 1.21, those broke in transaction mode because a statement prepared on one server connection was missing on the next one. With `max_prepared_statements` above zero, PgBouncer tracks the prepared statements of each client and prepares them transparently on whichever server connection it uses. The value is the number of statements cached per server connection. Test it with `pgbench` in prepared mode. First create the test tables directly on PostgreSQL: ```bash pgbench -h 127.0.0.1 -p 5432 -U appuser -i -s 10 appdb ``` Then run a 30-second benchmark with 50 clients through PgBouncer, using prepared statements: ```bash pgbench -h 127.0.0.1 -p 6432 -U appuser -c 50 -j 2 -T 30 -M prepared appdb ``` ```output ... number of failed transactions: 0 (0.000%) latency average = 12.874 ms tps = 3882.310448 (without initial connection time) ``` Fifty clients ran with no errors while PgBouncer used at most 20 server connections. With `max_prepared_statements = 0` the same run fails with `prepared statement "P_1" does not exist`. > Note: Only protocol-level prepared statements are tracked. SQL-level `PREPARE` and `EXECUTE` commands still require session pooling. ## Step 7 - Adding TLS for remote clients If application servers connect over the network, encrypt client traffic with TLS. The example uses Ubuntu's self-signed "snakeoil" certificate, which the `postgres` user can already read through the `ssl-cert` group. For production, use a certificate from your internal CA or a public CA and point the same options at it. Edit the configuration: ```bash sudo nano /etc/pgbouncer/pgbouncer.ini ``` Change `listen_addr` and add the TLS options in the `[pgbouncer]` section: ```ini listen_addr = 0.0.0.0 client_tls_sslmode = require client_tls_cert_file = /etc/ssl/certs/ssl-cert-snakeoil.pem client_tls_key_file = /etc/ssl/private/ssl-cert-snakeoil.key ``` With `client_tls_sslmode = require`, PgBouncer refuses unencrypted client connections. Restart the service and open the port only to your application servers, replacing `app_server_ip`: ```bash sudo systemctl restart pgbouncer sudo ufw allow from app_server_ip to any port 6432 proto tcp ``` From the application server, connect with TLS and check the connection details: ```bash psql "host=your_server_ip port=6432 dbname=appdb user=appuser sslmode=require" -c "\conninfo" ``` ```output You are connected to database "appdb" as user "appuser" on host "your_server_ip" at port "6432". SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) ``` The connection between PgBouncer and PostgreSQL stays on `127.0.0.1`, so it does not leave the server. PostgreSQL itself does not need to listen on a public interface. ## Step 8 - Monitoring pools from the admin console PgBouncer exposes a virtual database named `pgbouncer` for monitoring and control. Connect as the admin user: ```bash psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer ``` `SHOW POOLS` is the most useful command. Run it while the `pgbench` test from Step 6 is running: ```sql SHOW POOLS; ``` ```output database | user | cl_active | cl_waiting | sv_active | sv_idle | sv_used | maxwait | pool_mode ----------+-----------------+-----------+------------+-----------+---------+---------+---------+------------- appdb | appuser | 50 | 0 | 20 | 0 | 0 | 0 | transaction pgbouncer| pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | statement ``` (Some columns are omitted here.) Read it this way: - `cl_active`: clients linked to a server connection or idle without waiting. - `cl_waiting`: clients queued for a server connection. Occasional values are normal; a sustained number means the pool is too small or queries are too slow. - `sv_active` and `sv_idle`: server connections in use and ready in the pool. - `maxwait`: how long, in seconds, the oldest waiting client has waited. Other useful commands: ```sql SHOW STATS; SHOW CLIENTS; SHOW SERVERS; SHOW CONFIG; ``` `SHOW STATS` reports totals and averages per database, such as transactions per second and average query time. The console also controls the running process: `RELOAD;` rereads the configuration and `userlist.txt` without dropping connections, `PAUSE appdb;` waits for current transactions to finish and holds new ones (useful during a PostgreSQL restart), and `RESUME appdb;` releases them. From the shell, `sudo systemctl reload pgbouncer` has the same effect as `RELOAD`. ## Troubleshooting **`password authentication failed` through PgBouncer but not directly**: the secret in `userlist.txt` is outdated or the file has a formatting error. Export it again as in Step 3 and reload PgBouncer. **`no such database: appdb`**: the client asked for a database that is not listed in `[databases]`. Add it, or add a fallback line `* = host=127.0.0.1 port=5432` to route any database name. **`unsupported startup parameter: ...`**: a driver sends a startup parameter PgBouncer does not track. Add its name to `ignore_startup_parameters`. **Clients wait and time out with `query_wait_timeout`**: the pool is saturated. Look for long-running transactions with `SELECT pid, state, now() - xact_start AS age, query FROM pg_stat_activity ORDER BY age DESC NULLS LAST;` on PostgreSQL before raising `default_pool_size`; idle-in-transaction sessions keep server connections busy in transaction mode. **`sorry, too many clients already` from PostgreSQL**: the sum of pools exceeds `max_connections`. Lower `default_pool_size` or `max_db_connections`. ## Conclusion PgBouncer now pools connections to PostgreSQL in transaction mode, authenticates clients with SCRAM without storing plain-text passwords, supports prepared statements from modern drivers, encrypts remote client traffic and reports its state through the admin console. As next steps, feed the `SHOW POOLS` and `SHOW STATS` numbers into your monitoring system, size the pools from real load tests, and consider running PgBouncer on each application server or behind a load balancer if a single pooler becomes a point of failure.