Configure PostgreSQL 17 connection pooling with PgBouncer for high availability

Install PostgreSQL 17 client libraries and PgBouncer

PgBouncer needs the PostgreSQL client libraries to connect upstream. Install both PgBouncer and the PostgreSQL 17 client tools before configuring anything.

sudo apt update
sudo apt install -y curl ca-certificates gnupg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql.gpg
echo "deb [signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install -y postgresql-client-17 pgbouncer

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql17 pgbouncer

Confirm PostgreSQL 17 is reachable

Assume PostgreSQL 17 is already running on a primary server at 203.0.113.10. Verify connectivity before wiring up the pooler, otherwise you will debug PgBouncer for a problem that lives elsewhere.

psql -h 203.0.113.10 -U postgres -d appdb -c "SELECT version();"

If you have not yet configured the primary, see install and configure PostgreSQL 17 with performance tuning and security hardening first.

Choose a pooling mode in pgbouncer.ini

PgBouncer supports three pooling modes. Session pooling assigns one server connection per client for the whole session, the safest but least efficient. Transaction pooling releases the server connection after each transaction, the best default for most web apps. Statement pooling releases it after every statement, but breaks multi-statement transactions and most ORMs, so use it only for stateless read-only workloads.

[databases]
appdb = host=203.0.113.10 port=5432 dbname=appdb
appdb_session = host=203.0.113.10 port=5432 dbname=appdb pool_mode=session

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats

pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 10
reserve_pool_timeout = 3
max_db_connections = 100
max_user_connections = 100

server_idle_timeout = 300
server_lifetime = 3600
query_wait_timeout = 30
client_idle_timeout = 0
client_login_timeout = 60

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

Generate SCRAM-SHA-256 credentials in userlist.txt

PostgreSQL 17 defaults to scram-sha-256 password hashing. PgBouncer must use the exact same hash format in userlist.txt, plaintext or md5 entries will fail authentication against a SCRAM-only server.

psql -h 203.0.113.10 -U postgres -d appdb -t -A -c "SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'app_user';"

Copy the output into userlist.txt, keeping the SCRAM-SHA-256 hash exactly as returned. Never write a real password here in plaintext.

"app_user" "SCRAM-SHA-256$4096:base64salt$base64storedkey:base64serverkey"
"pgbouncer_admin" "SCRAM-SHA-256$4096:base64salt$base64storedkey:base64serverkey"

sudo chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt /etc/pgbouncer/pgbouncer.ini
sudo chmod 600 /etc/pgbouncer/userlist.txt

Enable and start PgBouncer

Start the service and confirm it is listening on port 6432, the standard PgBouncer port distinct from PostgreSQL's 5432.

sudo mkdir -p /var/log/pgbouncer /var/run/pgbouncer
sudo chown pgbouncer:pgbouncer /var/log/pgbouncer /var/run/pgbouncer
sudo systemctl enable --now pgbouncer
sudo systemctl status pgbouncer

Deploy PgBouncer on a second node for redundancy

A single PgBouncer instance is a single point of failure. Repeat the installation and configuration steps above on a second host (203.0.113.11), pointing to the same PostgreSQL primary and using an identical userlist.txt and pgbouncer.ini.

scp /etc/pgbouncer/userlist.txt root@203.0.113.11:/etc/pgbouncer/userlist.txt
scp /etc/pgbouncer/pgbouncer.ini root@203.0.113.11:/etc/pgbouncer/pgbouncer.ini

Front both PgBouncer nodes with HAProxy

HAProxy load balances client connections across both PgBouncer nodes and removes a failed node from rotation automatically using TCP health checks.

sudo apt install -y haproxy

sudo dnf install -y haproxy

frontend pgbouncer_front
    bind 203.0.113.20:6432
    mode tcp
    default_backend pgbouncer_nodes

backend pgbouncer_nodes
    mode tcp
    balance roundrobin
    option tcp-check
    tcp-check connect port 6432
    server pgb1 203.0.113.10:6432 check inter 3s fall 3 rise 2
    server pgb2 203.0.113.11:6432 check inter 3s fall 3 rise 2

sudo systemctl enable --now haproxy

For a deeper walkthrough of HAProxy tuning and ACL-based routing, see configure HAProxy load balancing with multiple backend servers.

Add keepalived for a floating virtual IP

HAProxy itself needs redundancy. Run keepalived on both HAProxy hosts so a virtual IP fails over automatically if the active node goes down.

sudo apt install -y keepalived

sudo dnf install -y keepalived

vrrp_script chk_haproxy {
    script "/usr/bin/killall -0 haproxy"
    interval 2
    weight 2
}

vrrp_instance VI_PGBOUNCER {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass Ch4ngeThisVrrpSecret
    }
    virtual_ipaddress {
        203.0.113.20/24
    }
    track_script {
        chk_haproxy
    }
}

sudo systemctl enable --now keepalived

Set state BACKUP and priority 100 on the second node. For the complete active/passive pattern including firewall rules, see configure keepalived with HAProxy backend health monitoring.

Tune pool sizes and timeouts for production load

default_pool_size controls how many server connections PgBouncer opens per database/user pair in transaction mode. Set it based on PostgreSQL's max_connections divided by the number of PgBouncer instances and databases sharing the primary, leaving headroom for replication and admin connections.

Verify PostgreSQL's own limit accommodates all pooled connections plus replication slots.

psql -h 203.0.113.10 -U postgres -c "SHOW max_connections;"

Query live pool and stats via the admin console

PgBouncer exposes an administrative pseudo-database called pgbouncer. Connect as an admin_users entry to inspect pool state in real time.

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW STATS;"
psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW CLIENTS;"

SHOW POOLS reports cl_active, cl_waiting and sv_active columns per database/user pair. A consistently non-zero cl_waiting means default_pool_size is too small for the current load.

Deploy the Prometheus exporter

prometheus-pgbouncer-exporter scrapes SHOW STATS and SHOW POOLS and exposes them as Prometheus metrics for Grafana dashboards and alerting.

sudo useradd --no-create-home --shell /usr/sbin/nologin pgbouncer_exporter
curl -L -o /tmp/pgbouncer_exporter.tar.gz https://github.com/prometheus-community/pgbouncer_exporter/releases/download/v0.10.2/pgbouncer_exporter-0.10.2.linux-amd64.tar.gz
tar -xzf /tmp/pgbouncer_exporter.tar.gz -C /tmp
sudo mv /tmp/pgbouncer_exporter-0.10.2.linux-amd64/pgbouncer_exporter /usr/local/bin/
sudo chown pgbouncer_exporter:pgbouncer_exporter /usr/local/bin/pgbouncer_exporter

[Unit]
Description=PgBouncer Prometheus Exporter
After=network.target

[Service]
User=pgbouncer_exporter
Group=pgbouncer_exporter
Environment=DATA_SOURCE_NAME="postgres://pgbouncer_stats:StrongExporterPass9!@127.0.0.1:6432/pgbouncer?sslmode=disable"
ExecStart=/usr/local/bin/pgbouncer_exporter --web.listen-address=127.0.0.1:9127
Restart=on-failure

[Install]
WantedBy=multi-user.target

sudo systemctl daemon-reload
sudo systemctl enable --now pgbouncer_exporter
curl http://127.0.0.1:9127/metrics | grep pgbouncer_pools

Wire this into an existing Prometheus and Grafana stack as described in set up Prometheus and Grafana monitoring stack with Docker Compose, then build alerts on pgbouncer_pools_cl_waiting and pgbouncer_pools_sv_used.

Simulate a primary failure

If PostgreSQL streaming replication is already in place, test that PgBouncer's target can be repointed to a promoted replica quickly. This is the scenario your HA setup exists for.

sudo systemctl stop postgresql

On the standby, promote it to become the new primary.

sudo -u postgres /usr/lib/postgresql/17/bin/pg_ctl promote -D /var/lib/postgresql/17/main

Repoint PgBouncer to the new primary

Update the host in pgbouncer.ini on both PgBouncer nodes, then reload without restarting, PgBouncer reload keeps existing pooled connections draining gracefully.

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

sudo systemctl reload pgbouncer

For the full replication topology, promotion tooling, and automated failover scripts, follow set up PostgreSQL 17 streaming replication with PgBouncer connection pooling and load balancing.