How to Configure Database Connection Pooling with PgBouncer on RHEL 7

How to Configure Database Connection Pooling with PgBouncer on RHEL 7

Every PostgreSQL client connection spawns a backend process on the server, consuming approximately 5–10 MB of RAM for its working memory and adding overhead to the process scheduler. Applications that open hundreds of short-lived connections — such as PHP scripts, Node.js workers, or microservices — can quickly exhaust the server’s max_connections limit, causing new connections to fail with the dreaded “FATAL: sorry, too many clients already” error. PgBouncer solves this by acting as a lightweight connection pooler that sits between the application and PostgreSQL. Applications connect to PgBouncer (typically on port 6432), and PgBouncer maintains a small pool of real PostgreSQL connections, multiplexing thousands of application connections over them. This guide covers installing PgBouncer from EPEL on RHEL 7, configuring all three pool modes, setting up the user authentication file, testing with psql, and monitoring pool health.

Prerequisites

• RHEL 7 server with PostgreSQL installed and running
• Root or sudo access
• EPEL repository enabled (required for PgBouncer on RHEL 7)
• A PostgreSQL database and user already created for testing
• Basic familiarity with PostgreSQL and the psql client

Step 1: Enable the EPEL Repository

PgBouncer is not available in the default RHEL 7 base repositories. It is provided by the Extra Packages for Enterprise Linux (EPEL) repository. Install EPEL if it is not already enabled:

sudo yum install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

Verify the repository is active:

yum repolist | grep epel

Step 2: Install PgBouncer

With EPEL enabled, install PgBouncer with a single yum command:

sudo yum install -y pgbouncer

The installation creates the pgbouncer system user, installs the binary at /usr/bin/pgbouncer, places the main configuration file at /etc/pgbouncer/pgbouncer.ini, and installs a systemd unit file. Check the installed version:

pgbouncer --version

Step 3: Understand PgBouncer Pool Modes

PgBouncer supports three pool modes, each with different performance and compatibility trade-offs. Choose the mode that fits your application:

• session — A server connection is assigned to the client for the entire session. This is the safest mode and is compatible with all PostgreSQL features (prepared statements, advisory locks, session variables). It provides less multiplexing benefit because the server connection is held for as long as the client is connected.
• transaction — A server connection is held only for the duration of a transaction. This is the most commonly used mode for web applications because it allows much higher multiplexing. Caution: prepared statements and SET commands that persist beyond a transaction do not work reliably in this mode without additional configuration.
• statement — A server connection is returned to the pool after every single SQL statement. This is the most aggressive mode and is incompatible with multi-statement transactions. It is rarely used in practice.

Step 4: Configure pgbouncer.ini

The main configuration file is /etc/pgbouncer/pgbouncer.ini. Open it for editing:

sudo vi /etc/pgbouncer/pgbouncer.ini

Replace the default content with the following configuration. Adjust the database name, PostgreSQL host, and pool sizes to match your environment:

[databases]
; Format: alias = host=PGHOST port=PGPORT dbname=DBNAME
; This maps the alias "myappdb" that clients connect to, to the real database
myappdb = host=127.0.0.1 port=5432 dbname=myappdb

; You can also define a wildcard entry to proxy all databases
; * = host=127.0.0.1 port=5432

[pgbouncer]
; Network settings
listen_addr = 127.0.0.1
listen_port = 6432

; Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Pool mode: session, transaction, or statement
pool_mode = transaction

; Maximum number of client connections PgBouncer accepts
max_client_conn = 200

; Default pool size per database/user pair
; This is the number of real PostgreSQL connections maintained
default_pool_size = 20

; Minimum number of server connections kept open
min_pool_size = 5

; Reserve pool for emergencies - extra connections when pool is full
reserve_pool_size = 5
reserve_pool_timeout = 3

; Maximum connections PgBouncer keeps to PostgreSQL across all pools
; Should be less than PostgreSQL's max_connections
server_pool_size = 100

; Timeouts
server_connect_timeout = 10
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 0

; Logging
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

; Administrative interface
admin_users = pgbounceradmin
stats_users = pgbounceradmin, readonly_stats

; Ignore startup parameters that confuse some clients
ignore_startup_parameters = extra_float_digits

Key settings explained:

• listen_addr — Set to 127.0.0.1 for local-only access, or 0.0.0.0 to accept connections from other hosts on the network.
• max_client_conn — The maximum number of clients that can connect to PgBouncer simultaneously. This can be set much higher than PostgreSQL’s max_connections.
• default_pool_size — The number of real PostgreSQL connections maintained per database/user pair. This is the key tuning parameter.
• ignore_startup_parameters — Prevents errors when clients send parameters that PgBouncer does not understand; extra_float_digits is commonly sent by modern psql and libpq versions.

Step 5: Create the Log Directory

The EPEL package may not create the log directory automatically. Create it and set the correct ownership:

sudo mkdir -p /var/log/pgbouncer
sudo chown pgbouncer:pgbouncer /var/log/pgbouncer

Step 6: Create the userlist.txt Authentication File

PgBouncer maintains its own list of users and password hashes in /etc/pgbouncer/userlist.txt. The file format is one user per line, with the username and MD5 password hash enclosed in double quotes:

"username" "md5hash"

To generate the MD5 hash for a user, use this formula: md5(password + username). The easiest way on RHEL 7 is with a shell one-liner. Replace mypassword and appuser with the actual password and username:

echo -n "mypasswordappuser" | md5sum

This prints the raw MD5 hash (e.g., 5f4dcc3b5aa765d61d8327deb882cf99). Prepend md5 to it in the userlist file. Alternatively, query the hash directly from PostgreSQL if you already have a user created:

sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename = 'appuser';"

The passwd column contains the hash in the correct md5... format. Create the userlist file:

sudo vi /etc/pgbouncer/userlist.txt

Add entries for each user that will connect through PgBouncer:

"appuser" "md5HASH_FROM_ABOVE"
"pgbounceradmin" "md5ADMIN_HASH"

Secure the file permissions:

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

Step 7: Enable and Start PgBouncer

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer

Check the log for any startup errors:

sudo tail -20 /var/log/pgbouncer/pgbouncer.log

Step 8: Test the Connection with psql

Connect to PgBouncer on port 6432 instead of the standard PostgreSQL port 5432. PgBouncer will transparently route the connection to PostgreSQL:

psql -h 127.0.0.1 -p 6432 -U appuser -d myappdb

Once connected, run a test query to verify the connection is routing correctly:

SELECT current_database(), current_user, version();
SELECT pg_postmaster_start_time();

To confirm you are connected through PgBouncer and not directly to PostgreSQL, note the port number in the connection output. You should also be able to see the connection in PgBouncer’s pool statistics (see Step 9).

Step 9: Monitor with SHOW POOLS

PgBouncer provides a virtual administration database called pgbouncer. Connect to it using the admin user defined in the configuration:

psql -h 127.0.0.1 -p 6432 -U pgbounceradmin pgbouncer

Once connected, run the following monitoring commands:

-- Show all pools with connection counts and states
SHOW POOLS;

-- Show connected clients
SHOW CLIENTS;

-- Show server connections held in the pool
SHOW SERVERS;

-- Show aggregate statistics per database
SHOW STATS;

-- Show configuration summary
SHOW CONFIG;

-- Show PgBouncer version and status
SHOW VERSION;
SHOW STATUS;

The SHOW POOLS output includes these key columns:

• cl_active — Client connections currently executing a query
• cl_waiting — Client connections waiting for a server connection to become available
• sv_active — Server connections currently in use
• sv_idle — Server connections waiting in the pool (ready to be used)
• sv_used — Server connections that have been used but are waiting for client reuse timeout
• maxwait — Seconds the oldest waiting client has been waiting (should normally be 0)

If cl_waiting is persistently non-zero or maxwait is growing, your default_pool_size is too small for the current load and should be increased.

Step 10: Reload Configuration Without Downtime

PgBouncer supports online configuration reload without dropping existing connections. After editing pgbouncer.ini, reload from the admin console or via systemctl:

-- From the pgbouncer admin console:
RELOAD;

-- Or from the shell:
sudo systemctl reload pgbouncer

Step 11: Application Configuration

Update your application’s database connection string to point to PgBouncer instead of PostgreSQL directly. For example, in a PHP application using PDO:

$dsn = 'pgsql:host=127.0.0.1;port=6432;dbname=myappdb';
$pdo = new PDO($dsn, 'appuser', 'mypassword');

In a Python application using psycopg2:

import psycopg2
conn = psycopg2.connect(
    host="127.0.0.1",
    port=6432,
    database="myappdb",
    user="appuser",
    password="mypassword"
)

The application requires no other changes — PgBouncer is fully transparent to the PostgreSQL protocol.

Conclusion

You have installed PgBouncer from EPEL on RHEL 7, configured it in transaction pooling mode with appropriate pool sizes, set up MD5 password authentication via the userlist file, verified connectivity through psql on port 6432, and learned to monitor pool health using the SHOW POOLS command. PgBouncer is one of the most impactful and lowest-cost performance improvements you can make to a PostgreSQL deployment used by web applications. By keeping only 20–50 real PostgreSQL connections while serving hundreds or thousands of application connections, PgBouncer dramatically reduces per-connection memory overhead and eliminates connection exhaustion errors under load. For further hardening, consider enabling TLS on the PgBouncer listener, restricting listen_addr to specific interfaces, and setting up Prometheus monitoring using the pgbouncer_exporter to track pool saturation over time.