PgBouncer Connection Pooling Setup

Intermediate15 minTrending

Deploy PgBouncer as a connection pooler to reduce PostgreSQL connection overhead and support thousands of concurrent clients.

Prerequisites

-PostgreSQL running
-PgBouncer installed (apt install pgbouncer or equivalent)

Steps

Configure PgBouncer databases

Edit pgbouncer.ini to define which databases to pool and the upstream PostgreSQL connection.

$ cat > /etc/pgbouncer/pgbouncer.ini << 'INI' [databases] mydb = host=127.0.0.1 port=5432 dbname=mydb [pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction max_client_conn = 1000 default_pool_size = 20 min_pool_size = 5 reserve_pool_size = 5 server_idle_timeout = 300 INI

Transaction pooling mode (pool_mode = transaction) gives the best connection reuse for most applications.

Create the authentication file

Add user credentials that PgBouncer uses to authenticate clients.

$ echo '"app_user" "scram-sha-256$4096:salt$stored_key:server_key"' > /etc/pgbouncer/userlist.txt

Generate the SCRAM hash from PostgreSQL: SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'app_user';

Start PgBouncer

Launch PgBouncer and verify it is listening for connections.

$ sudo systemctl start pgbouncer && sudo systemctl status pgbouncer

Connect through PgBouncer

Connect to PostgreSQL through the PgBouncer proxy port.

$ psql -h 127.0.0.1 -p 6432 -U app_user -d mydb -c 'SELECT 1;'

Monitor PgBouncer statistics

Connect to the PgBouncer admin console to view pool statistics.

$ psql -h 127.0.0.1 -p 6432 -U pgbouncer -d pgbouncer -c 'SHOW POOLS;'

Full Script

full-script.sh

#!/bin/bash
# PgBouncer Monitoring Script
set -euo pipefail

PGBOUNCER_HOST="127.0.0.1"
PGBOUNCER_PORT="6432"

echo "=== PgBouncer Status ==="
sudo systemctl status pgbouncer --no-pager | head -5

echo ""
echo "=== Connection Pools ==="
psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW POOLS;"

echo ""
echo "=== Active Clients ==="
psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW CLIENTS;" | head -20

echo ""
echo "=== Server Connections ==="
psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW SERVERS;" | head -20

echo ""
echo "=== Statistics ==="
psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW STATS;"

echo ""
echo "=== Configuration ==="
psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW CONFIG;" | grep -E "pool_mode|max_client_conn|default_pool_size"

FAQ

Discussion

Loading comments...

#!/bin/bash # PgBouncer Monitoring Script set -euo pipefail PGBOUNCER_HOST="127.0.0.1" PGBOUNCER_PORT="6432" echo "=== PgBouncer Status ===" sudo systemctl status pgbouncer --no-pager | head -5 echo "" echo "=== Connection Pools ===" psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW POOLS;" echo "" echo "=== Active Clients ===" psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW CLIENTS;" | head -20 echo "" echo "=== Server Connections ===" psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW SERVERS;" | head -20 echo "" echo "=== Statistics ===" psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW STATS;" echo "" echo "=== Configuration ===" psql -h "$PGBOUNCER_HOST" -p "$PGBOUNCER_PORT" -U pgbouncer -d pgbouncer -c "SHOW CONFIG;" | grep -E "pool_mode|max_client_conn|default_pool_size"