Introduction

If you’re running PostgreSQL in production, you’ve likely encountered connection scalability challenges. Each PostgreSQL connection consumes memory and CPU resources, and managing hundreds or thousands of concurrent connections can bring your database server to its knees. This is where PgBouncer comes to the rescue.

In this comprehensive guide, you’ll learn how to install and configure PgBouncer on Rocky Linux 10, from basic setup to production-ready deployment with security hardening, multi-user authentication, and monitoring.

What is PgBouncer?

PgBouncer is a lightweight connection pooler for PostgreSQL. It sits between your application servers and PostgreSQL database, managing a pool of reusable database connections.

The Problem PgBouncer Solves

Modern web applications can have hundreds or thousands of concurrent users. Without connection pooling:

Each application connection = One PostgreSQL backend process
PostgreSQL backend processes consume 5-10 MB of memory each
Connection setup/teardown has overhead (authentication, SSL handshake, etc.)
PostgreSQL has practical limits on concurrent connections

Example scenario without PgBouncer:

1
2
3
1,000 concurrent users
× 10 MB per connection
= 10 GB of memory just for connections!

How PgBouncer Works

PgBouncer maintains a pool of persistent connections to PostgreSQL and multiplexes client connections over them:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
┌─────────────────────────────────────────────────────┐
│                 Application Servers                 │
│                                                     │
│  ┌─────┐  ┌─────┐  ┌─────┐  ┌─────┐  ┌─────┐     │
│  │ 200 │  │ 300 │  │ 150 │  │ 400 │  │ 250 │     │
│  │conn │  │conn │  │conn │  │conn │  │conn │     │
│  └──┬──┘  └──┬──┘  └──┬──┘  └──┬──┘  └──┬──┘     │
│     │        │        │        │        │          │
│     └────────┴────────┴────────┴────────┘          │
│                      │                              │
│              1,300 client connections               │
└──────────────────────┼──────────────────────────────┘
                       │
                       ▼
         ┌─────────────────────────┐
         │      PgBouncer          │
         │   Connection Pooler     │
         │                         │
         │  Pool size: 25 conns    │
         └────────┬────────────────┘
                  │
          Only 25 connections!
                  │
                  ▼
         ┌─────────────────────────┐
         │     PostgreSQL          │
         │      Database           │
         └─────────────────────────┘

Result: 1,300 client connections are multiplexed over just 25 PostgreSQL connections!

Key Features and Benefits

1. Reduced Memory Footprint

1
2
3
4
5
Without PgBouncer:
1,000 connections × 10 MB = 10 GB

With PgBouncer:
25 connections × 10 MB = 250 MB (40x reduction!)

2. Connection Reuse

Instead of opening a new PostgreSQL connection for each request:

PgBouncer reuses existing connections
No authentication overhead for each request
Faster response times for clients

3. Connection Limits

Protect your database from connection exhaustion:

Set maximum client connections
Queue requests when pool is full
Prevent database overload

4. Multiple Pool Modes

Session mode: Connection held until client disconnects (no pooling)
Transaction mode: Connection returned after transaction ends (recommended)
Statement mode: Connection returned after each statement (aggressive)

5. Zero Downtime Reloads

Change configuration without dropping connections:

1
systemctl reload pgbouncer  # No connection interruption!

When to Use PgBouncer

Use PgBouncer when:

You have 100+ concurrent database connections
Your application opens/closes connections frequently
You’re hitting PostgreSQL connection limits
You want to reduce database memory usage
You need to support thousands of concurrent users
You’re running microservices with many instances

You might not need PgBouncer if:

You have < 50 concurrent connections
Your application uses long-lived persistent connections
You need advanced connection pooling features (consider Pgpool-II)

Prerequisites

Before we begin, ensure you have:

System Requirements

OS: Rocky Linux 10 (fresh install recommended)
Access: Root or sudo privileges
PostgreSQL: Version 12+ (local or remote)
Memory: At least 512MB RAM for PgBouncer
Network: Firewall configuration access

Update Your System

1
2
3
4
5
6
7
8
9
# Update system packages
sudo dnf update -y

# Install essential tools
sudo dnf install -y vim wget curl net-tools

# Verify Rocky Linux version
cat /etc/rocky-release
# Output: Rocky Linux release 9.x

Step 1: Database User Creation

CRITICAL FIRST STEP: Before installing PgBouncer, you must create database users in PostgreSQL.

PgBouncer is a connection pooler that requires users to exist in two places:

PostgreSQL - Where actual authentication happens
PgBouncer userlist - So PgBouncer can pass credentials through

Understanding the Workflow

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
┌─────────────────────────────────────────────────────────────┐
│  Step 1: Create Users in PostgreSQL                        │
│  ↓                                                           │
│  Step 2: Grant Permissions                                  │
│  ↓                                                           │
│  Step 3: Test Direct PostgreSQL Connection                  │
│  ↓                                                           │
│  Step 4: Add Users to PgBouncer userlist.txt               │
│  ↓                                                           │
│  Step 5: Applications Connect Through PgBouncer            │
└─────────────────────────────────────────────────────────────┘

Connect to PostgreSQL

First, connect to your PostgreSQL server:

1
2
3
4
5
6
7
8
# Option 1: Local PostgreSQL as postgres user
sudo -u postgres psql

# Option 2: Remote PostgreSQL with TCP
psql -h db.example.com -p 5432 -U postgres -d postgres

# Option 3: Local with password authentication
psql -h localhost -p 5432 -U postgres -d postgres

You should see:

1
2
3
4
psql (17.7)
Type "help" for help.

postgres=#

Create Application Database

1
2
3
4
5
6
7
8
-- Create your production database
CREATE DATABASE myapp_production;

-- Verify
\l

-- Connect to it
\c myapp_production

Create Database Users

A. PgBouncer Admin User

This user accesses the PgBouncer admin console:

1
2
3
4
5
6
7
-- Create admin user (use a strong password!)
CREATE USER pgbouncer_admin WITH PASSWORD 'YourSecureAdminPassword123!';

-- Grant connection to PostgreSQL (optional)
GRANT CONNECT ON DATABASE postgres TO pgbouncer_admin;

-- This user's main purpose is PgBouncer admin console access

B. Application Users

These are the users your applications will use:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- Web application user
CREATE USER webapp_user WITH PASSWORD 'SecureWebAppPass456!';

-- API service user
CREATE USER api_user WITH PASSWORD 'SecureApiPass789!';

-- Background worker user
CREATE USER worker_user WITH PASSWORD 'SecureWorkerPass012!';

-- Verify users were created
\du

C. Read-Only User

For reporting and analytics:

1
CREATE USER readonly_user WITH PASSWORD 'SecureReadOnlyPass345!';

Grant Permissions

Connect to your application database first:

1
2
-- Connect to your application database
\c myapp_production

For Application Users (Full Read/Write)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Grant connection to database
GRANT CONNECT ON DATABASE myapp_production
  TO webapp_user, api_user, worker_user;

-- Grant schema usage (required to see tables)
GRANT USAGE ON SCHEMA public
  TO webapp_user, api_user, worker_user;

-- Grant table permissions (SELECT, INSERT, UPDATE, DELETE)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public
  TO webapp_user, api_user, worker_user;

-- Grant permissions on future tables (important!)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES
  TO webapp_user, api_user, worker_user;

-- Grant sequence permissions (for SERIAL/auto-increment)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public
  TO webapp_user, api_user, worker_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES
  TO webapp_user, api_user, worker_user;

For Read-Only User

1
2
3
4
5
6
7
8
9
-- Grant connection and schema usage
GRANT CONNECT ON DATABASE myapp_production TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;

-- Grant SELECT only
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

Test PostgreSQL Connections

CRITICAL: Test direct PostgreSQL connections BEFORE setting up PgBouncer!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# Test webapp_user
psql -h localhost -p 5432 -U webapp_user -d myapp_production \
  -c "SELECT current_user, current_database();"

# Expected output:
#  current_user | current_database
# --------------+------------------
#  webapp_user  | myapp_production

# Test api_user
psql -h localhost -p 5432 -U api_user -d myapp_production -c "SELECT 1;"

# Test readonly_user
psql -h localhost -p 5432 -U readonly_user -d myapp_production -c "SELECT 1;"

If these tests fail, fix PostgreSQL permissions before proceeding.

Configure pg_hba.conf (If Needed)

If connections fail with “no pg_hba.conf entry”:

1
2
# Edit pg_hba.conf (adjust path based on PostgreSQL version)
sudo vim /var/lib/pgsql/16/data/pg_hba.conf

Add entries for your users:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# TYPE  DATABASE              USER            ADDRESS         METHOD
# Local connections
local   myapp_production      webapp_user                     md5
local   myapp_production      api_user                        md5
local   myapp_production      readonly_user                   md5

# TCP connections from localhost
host    myapp_production      webapp_user     127.0.0.1/32    md5
host    myapp_production      api_user        127.0.0.1/32    md5
host    myapp_production      readonly_user   127.0.0.1/32    md5

# If PgBouncer is on a different server, add its IP:
host    myapp_production      webapp_user     192.168.1.10/32 md5

Reload PostgreSQL:

1
sudo systemctl reload postgresql-17

Step 2: Installing PgBouncer

Method 1: Install from PostgreSQL Repository (Recommended)

This method provides the latest version:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Install PostgreSQL repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable built-in PostgreSQL module (Rocky Linux 10 specific)
sudo dnf -qy module disable postgresql

# Install PgBouncer
sudo dnf install -y pgbouncer

# Verify installation
pgbouncer --version

Expected output:

1
2
3
4
5
PgBouncer 1.21.0
libevent 2.1.12-stable
adns: c-ares 1.19.1
tls: OpenSSL 3.0.7 1 Nov 2022
systemd: yes

Method 2: Install from EPEL

Alternative method using EPEL repository:

1
2
3
4
5
6
7
8
# Enable EPEL repository
sudo dnf install -y epel-release

# Install PgBouncer
sudo dnf install -y pgbouncer

# Verify installation
pgbouncer --version

Step 3: Basic Configuration

Create Directory Structure

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# Create PgBouncer directories
sudo mkdir -p /etc/pgbouncer
sudo mkdir -p /var/log/pgbouncer
sudo mkdir -p /var/run/pgbouncer

# Create pgbouncer user (if not exists)
sudo useradd -r -s /bin/false pgbouncer

# Set ownership
sudo chown -R pgbouncer:pgbouncer /etc/pgbouncer
sudo chown -R pgbouncer:pgbouncer /var/log/pgbouncer
sudo chown -R pgbouncer:pgbouncer /var/run/pgbouncer

# Set secure permissions
sudo chmod 700 /etc/pgbouncer
sudo chmod 755 /var/log/pgbouncer
sudo chmod 755 /var/run/pgbouncer

Create Main Configuration File

Create /etc/pgbouncer/pgbouncer.ini:

1
sudo vim /etc/pgbouncer/pgbouncer.ini

Complete production-ready configuration:

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
; ==============================================================================
; PgBouncer Configuration for Production
; ==============================================================================

[databases]
; Database connection format: dbname = host=hostname port=port dbname=database
; You can define multiple databases here

; Production database
myapp_production = host=localhost port=5432 dbname=myapp_production

; Reports database (optional)
reports = host=localhost port=5432 dbname=reports pool_size=10

; Analytics database on separate server (optional)
analytics = host=192.168.1.100 port=5432 dbname=analytics

; Fallback for any database not explicitly listed (optional)
;* = host=localhost port=5432

[pgbouncer]
; ==============================================================================
; Administrative Settings
; ==============================================================================

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

; ==============================================================================
; Network Configuration
; ==============================================================================

; Listen on all interfaces (use specific IP in production)
listen_addr = 0.0.0.0
listen_port = 6432

; Unix socket location (optional, for local connections)
unix_socket_dir = /var/run/pgbouncer
unix_socket_mode = 0777

; ==============================================================================
; Authentication Settings
; ==============================================================================

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

; Admin users who can access admin console
admin_users = pgbouncer_admin

; Users who can query statistics
stats_users = pgbouncer_admin, readonly_user

; ==============================================================================
; Pool Configuration
; ==============================================================================

; Pool mode:
;   session      - Connection held until client disconnects (no pooling)
;   transaction  - Connection returned after transaction ends (RECOMMENDED)
;   statement    - Connection returned after each statement (aggressive)
pool_mode = transaction

; How many server connections to allow per user/database pair
default_pool_size = 25

; Minimum number of server connections to keep in pool
min_pool_size = 10

; Reserve connections for priority user/database pairs
reserve_pool_size = 5

; How long to wait before giving up on reserve pool (seconds)
reserve_pool_timeout = 5

; ==============================================================================
; Connection Limits
; ==============================================================================

; Total maximum client connections allowed
max_client_conn = 1000

; Maximum server connections per database
max_db_connections = 100

; Maximum server connections per user
max_user_connections = 100

; ==============================================================================
; Timeouts
; ==============================================================================

; Close server connection if idle for this long (seconds)
server_idle_timeout = 600

; Close server connection after this long (seconds)
server_lifetime = 3600

; Abort connection attempt if takes longer than this (seconds)
server_connect_timeout = 15

; If client has been in "idle in transaction" state longer, disconnect
; 0 = disabled
query_timeout = 0

; Client must connect in this time (seconds)
client_login_timeout = 60

; ==============================================================================
; Logging
; ==============================================================================

; What to log
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

; Log level: DEBUG, INFO, WARNING, ERROR, FATAL
;log_level = INFO

; ==============================================================================
; Connection Sanity Checks
; ==============================================================================

; Server is assumed dead after this many seconds
server_check_delay = 30

; Run this query to check if server is alive
server_check_query = SELECT 1

; Reset connection after transaction if needed
server_reset_query = DISCARD ALL

; ==============================================================================
; Startup Parameters
; ==============================================================================

; Startup parameters that PgBouncer should ignore
ignore_startup_parameters = extra_float_digits,application_name

; Add hostname to application name
application_name_add_host = 1

Set Configuration File Permissions

1
2
sudo chmod 640 /etc/pgbouncer/pgbouncer.ini
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/pgbouncer.ini

Step 4: Configure User Authentication

Now we add the PostgreSQL users to PgBouncer’s userlist.

Generate MD5 Hashes

Important: Use the EXACT same passwords you used when creating users in PostgreSQL!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
# Generate hash for pgbouncer_admin
# Password: YourSecureAdminPassword123!
echo -n "YourSecureAdminPassword123!pgbouncer_admin" | md5sum
# Output: a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6  -

# Generate hash for webapp_user
# Password: SecureWebAppPass456!
echo -n "SecureWebAppPass456!webapp_user" | md5sum

# Generate hash for api_user
# Password: SecureApiPass789!
echo -n "SecureApiPass789!api_user" | md5sum

# Generate hash for worker_user
# Password: SecureWorkerPass012!
echo -n "SecureWorkerPass012!worker_user" | md5sum

# Generate hash for readonly_user
# Password: SecureReadOnlyPass345!
echo -n "SecureReadOnlyPass345!readonly_user" | md5sum

Important Notes:

Format is: echo -n "PASSWORD+USERNAME" | md5sum
NO space between password and username
Use -n flag (no newline)
Password must match what you set in PostgreSQL

Create userlist.txt

Create /etc/pgbouncer/userlist.txt:

1
sudo vim /etc/pgbouncer/userlist.txt

Add all users with their MD5 hashes:

1
2
3
4
5
"pgbouncer_admin" "md5a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6"
"webapp_user" "md5b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6q7"
"api_user" "md5c3d4e5f6g7h8i9j0k1l2m3n4o5p6q7r8"
"worker_user" "md5d4e5f6g7h8i9j0k1l2m3n4o5p6q7r8s9"
"readonly_user" "md5e5f6g7h8i9j0k1l2m3n4o5p6q7r8s9t0"

Set Permissions

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

Step 5: Create Systemd Service

Create /etc/systemd/system/pgbouncer.service:

1
sudo vim /etc/systemd/system/pgbouncer.service

Add the following configuration:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
[Unit]
Description=PgBouncer PostgreSQL Connection Pooler
Documentation=man:pgbouncer(1)
After=network.target
Wants=network-online.target

[Service]
Type=forking

User=pgbouncer
Group=pgbouncer

# Path to PgBouncer executable
ExecStart=/usr/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini

# Reload configuration
ExecReload=/bin/kill -HUP $MAINPID

# Stop gracefully
KillSignal=SIGTERM
KillMode=mixed

# Restart policy
Restart=on-failure
RestartSec=5s

# Resource limits
LimitNOFILE=65536
LimitNPROC=65536

# Security hardening
PrivateTmp=yes
NoNewPrivileges=yes
ProtectSystem=strict
ProtectHome=yes
ReadWritePaths=/var/log/pgbouncer /var/run/pgbouncer

# Logging
StandardOutput=journal
StandardError=journal
SyslogIdentifier=pgbouncer

[Install]
WantedBy=multi-user.target

Step 6: Start and Enable PgBouncer

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Reload systemd
sudo systemctl daemon-reload

# Enable service to start on boot
sudo systemctl enable pgbouncer

# Start service
sudo systemctl start pgbouncer

# Check status
sudo systemctl status pgbouncer

Expected output:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
● pgbouncer.service - PgBouncer PostgreSQL Connection Pooler
     Loaded: loaded (/etc/systemd/system/pgbouncer.service; enabled)
     Active: active (running) since Mon 2026-01-29 10:00:00 EST; 5s ago
    Process: 12345 ExecStart=/usr/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
   Main PID: 12346 (pgbouncer)
      Tasks: 1
     Memory: 2.3M
     CGroup: /system.slice/pgbouncer.service
             └─12346 /usr/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini

Jan 29 10:00:00 server systemd[1]: Starting PgBouncer PostgreSQL Connection Pooler...
Jan 29 10:00:00 server systemd[1]: Started PgBouncer PostgreSQL Connection Pooler.

View Logs

1
2
3
4
5
6
7
8
# View recent logs
sudo journalctl -u pgbouncer --no-pager | tail -50

# Follow logs in real-time
sudo journalctl -u pgbouncer -f

# Check log file
sudo tail -f /var/log/pgbouncer/pgbouncer.log

Step 7: Testing Your Setup

Test Application User Connection

1
2
3
4
5
6
# Install PostgreSQL client tools
sudo dnf install -y postgresql

# Test connection through PgBouncer
psql -h localhost -p 6432 -U webapp_user -d myapp_production \
  -c "SELECT current_user, current_database();"

Expected output:

1
2
3
4
 current_user | current_database
--------------+------------------
 webapp_user  | myapp_production
(1 row)

Access Admin Console

1
2
# Connect to admin console
psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer

Run admin commands:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
-- Show connection pools
SHOW POOLS;

-- Show active clients
SHOW CLIENTS;

-- Show server connections
SHOW SERVERS;

-- Show databases
SHOW DATABASES;

-- Show configuration
SHOW CONFIG;

-- Show statistics
SHOW STATS;

-- Quit
\q

Example SHOW POOLS output:

1
2
3
4
5
 database         | user        | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | pool_mode
------------------+-------------+-----------+------------+-----------+---------+---------+-----------+----------+---------+-----------
 myapp_production | webapp_user |         5 |          0 |         2 |       3 |       5 |         0 |        0 |       0 | transaction
 pgbouncer        | pgbouncer   |         1 |          0 |         0 |       0 |       0 |         0 |        0 |       0 | statement
(2 rows)

Understanding the output:

cl_active: Active client connections
cl_waiting: Clients waiting for a connection
sv_active: Active server connections to PostgreSQL
sv_idle: Idle server connections available for reuse
maxwait: Maximum time (seconds) a client has been waiting

Step 8: Security Hardening

Configure Firewall

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# Allow PgBouncer port from application servers only
sudo firewall-cmd --permanent --add-rich-rule='
  rule family="ipv4"
  source address="192.168.1.0/24"
  port protocol="tcp" port="6432" accept'

# Allow admin access from specific IP only
sudo firewall-cmd --permanent --add-rich-rule='
  rule family="ipv4"
  source address="192.168.1.10/32"
  port protocol="tcp" port="6432" accept'

# Reload firewall
sudo firewall-cmd --reload

# Verify rules
sudo firewall-cmd --list-all

Configure SELinux

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Check SELinux status
getenforce

# If Enforcing, configure for PgBouncer
sudo semanage port -a -t postgresql_port_t -p tcp 6432

# If you get errors, check audit log
sudo ausearch -m avc -ts recent | grep pgbouncer

# Create custom policy if needed
sudo grep pgbouncer /var/log/audit/audit.log | audit2allow -M pgbouncer_custom
sudo semodule -i pgbouncer_custom.pp

SSL/TLS Configuration

For encrypted client connections:

1. Generate Certificates

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
# Create SSL directory
sudo mkdir -p /etc/pgbouncer/ssl
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/ssl
sudo chmod 700 /etc/pgbouncer/ssl

# Generate self-signed certificate (for testing)
sudo openssl req -new -x509 -days 365 -nodes \
  -out /etc/pgbouncer/ssl/server.crt \
  -keyout /etc/pgbouncer/ssl/server.key \
  -subj "/C=US/ST=State/L=City/O=Organization/CN=pgbouncer.yourdomain.com"

# Set permissions
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/ssl/server.*
sudo chmod 600 /etc/pgbouncer/ssl/server.key
sudo chmod 644 /etc/pgbouncer/ssl/server.crt

2. Configure SSL in pgbouncer.ini

Edit /etc/pgbouncer/pgbouncer.ini and add:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[pgbouncer]
; Client SSL settings
client_tls_sslmode = prefer
client_tls_key_file = /etc/pgbouncer/ssl/server.key
client_tls_cert_file = /etc/pgbouncer/ssl/server.crt
;client_tls_ca_file = /etc/pgbouncer/ssl/ca.crt

; Server SSL settings (to PostgreSQL)
server_tls_sslmode = prefer
;server_tls_ca_file = /etc/pgbouncer/ssl/ca.crt

Reload PgBouncer:

1
sudo systemctl reload pgbouncer

Verify File Permissions

1
sudo ls -la /etc/pgbouncer/

Should show:

1
2
3
drwx------  pgbouncer pgbouncer  .
-rw-r-----  pgbouncer pgbouncer  pgbouncer.ini
-rw-------  pgbouncer pgbouncer  userlist.txt

Step 9: Set Up Monitoring

Configure Log Rotation

Create /etc/logrotate.d/pgbouncer:

1
sudo vim /etc/logrotate.d/pgbouncer

Add:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
/var/log/pgbouncer/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 0640 pgbouncer pgbouncer
    sharedscripts
    postrotate
        /usr/bin/systemctl reload pgbouncer > /dev/null 2>&1 || true
    endscript
}

Create Health Check Script

Create /usr/local/bin/monitor-pgbouncer.sh:

1
sudo vim /usr/local/bin/monitor-pgbouncer.sh

Add:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/bin/bash
# Monitor PgBouncer health

PGBOUNCER_HOST="localhost"
PGBOUNCER_PORT="6432"
ADMIN_USER="pgbouncer_admin"

# Check if PgBouncer is running
if ! systemctl is-active --quiet pgbouncer; then
    echo "ERROR: PgBouncer service is not running"
    exit 1
fi

# Check if listening on port
if ! ss -tln | grep -q ":6432"; then
    echo "ERROR: PgBouncer is not listening on port 6432"
    exit 1
fi

# Check pool statistics
WAITING=$(psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $ADMIN_USER pgbouncer -t -c "
    SELECT SUM(cl_waiting) FROM pgbouncer.pools WHERE database != 'pgbouncer'
" 2>/dev/null)

if [ "$WAITING" -gt 0 ]; then
    echo "WARNING: $WAITING clients waiting for connections"
fi

# Check max wait time
MAXWAIT=$(psql -h $PGBOUNCER_HOST -p $PGBOUNCER_PORT -U $ADMIN_USER pgbouncer -t -c "
    SELECT MAX(maxwait) FROM pgbouncer.pools WHERE database != 'pgbouncer'
" 2>/dev/null)

if [ "$MAXWAIT" -gt 5 ]; then
    echo "WARNING: Max wait time is $MAXWAIT seconds"
fi

echo "PgBouncer health check passed"
exit 0

Make it executable:

1
sudo chmod +x /usr/local/bin/monitor-pgbouncer.sh

Add to Cron

1
sudo crontab -e

Add:

1
2
# Run health check every 5 minutes
*/5 * * * * /usr/local/bin/monitor-pgbouncer.sh >> /var/log/pgbouncer-monitor.log 2>&1

Advanced Configuration

Multi-Database Setup

Edit /etc/pgbouncer/pgbouncer.ini:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
[databases]
; Production databases
webapp_prod = host=db1.internal port=5432 dbname=webapp_production pool_size=50
api_prod = host=db1.internal port=5432 dbname=api_production pool_size=30

; Staging databases
webapp_staging = host=db2.internal port=5432 dbname=webapp_staging pool_size=20
api_staging = host=db2.internal port=5432 dbname=api_staging pool_size=15

; Analytics database on separate server
analytics = host=analytics.internal port=5432 dbname=analytics pool_size=10 max_db_connections=20

; Read replicas
webapp_readonly = host=replica.internal port=5432 dbname=webapp_production pool_size=40

; Database with custom settings
reporting = host=db.internal port=5432 dbname=reporting \
            pool_size=10 \
            connect_query='SET timezone="UTC"; SET statement_timeout="30s"'

Reload:

1
sudo systemctl reload pgbouncer

Performance Tuning

Adjust Pool Sizes

1
2
3
4
5
6
7
[pgbouncer]
; For high-traffic applications
default_pool_size = 50
min_pool_size = 20
max_db_connections = 200
max_user_connections = 200
max_client_conn = 5000

Connection Timeout Tuning

1
2
3
4
5
[pgbouncer]
; Aggressive timeout for web applications
server_idle_timeout = 300      ; 5 minutes
server_lifetime = 1800          ; 30 minutes
client_idle_timeout = 300       ; 5 minutes

Common Operations

Reload Configuration

1
2
3
4
5
6
7
8
# Method 1: Systemd (recommended)
sudo systemctl reload pgbouncer

# Method 2: Admin console
psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer -c "RELOAD"

# Method 3: Send HUP signal
sudo kill -HUP $(cat /var/run/pgbouncer/pgbouncer.pid)

Add New User

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
# 1. Create user in PostgreSQL
psql -h localhost -U postgres -c "CREATE USER newuser WITH PASSWORD 'secure_password';"
psql -h localhost -U postgres -d myapp_production -c "GRANT CONNECT ON DATABASE myapp_production TO newuser;"
psql -h localhost -U postgres -d myapp_production -c "GRANT USAGE ON SCHEMA public TO newuser;"
psql -h localhost -U postgres -d myapp_production -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO newuser;"

# 2. Generate MD5 hash
echo -n "secure_passwordnewuser" | md5sum

# 3. Add to userlist.txt
sudo vim /etc/pgbouncer/userlist.txt
# Add: "newuser" "md5<hash>"

# 4. Reload PgBouncer
sudo systemctl reload pgbouncer

# 5. Test
psql -h localhost -p 6432 -U newuser -d myapp_production -c "SELECT current_user"

Pause and Resume

1
2
3
4
5
# Pause all connections (for maintenance)
psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer -c "PAUSE"

# Resume connections
psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer -c "RESUME"

Troubleshooting

Issue: Cannot Connect to PgBouncer

Check service status:

1
sudo systemctl status pgbouncer

Check if listening:

1
sudo ss -tlnp | grep 6432

View logs:

1
sudo journalctl -u pgbouncer --no-pager | tail -50

Issue: Authentication Failed

Verify userlist:

1
sudo cat /etc/pgbouncer/userlist.txt | grep username

Check auth_type:

1
sudo grep auth_type /etc/pgbouncer/pgbouncer.ini

Regenerate MD5 hash:

1
echo -n "PASSWORDusername" | md5sum

Issue: Pool Saturated

Check pool status:

1
psql -h localhost -p 6432 -U pgbouncer_admin pgbouncer -c "SHOW POOLS"

Look for cl_waiting > 0 (clients waiting).

Solution: Increase pool size:

1
2
3
sudo vim /etc/pgbouncer/pgbouncer.ini
# Increase: default_pool_size = 50
sudo systemctl reload pgbouncer

Enable Debug Logging

Edit /etc/pgbouncer/pgbouncer.ini:

1
2
3
4
5
[pgbouncer]
verbose = 2
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

Reload and watch logs:

1
2
sudo systemctl reload pgbouncer
sudo tail -f /var/log/pgbouncer/pgbouncer.log

Production Checklist

Before going live, verify:

All configuration files have correct permissions (640 for config, 640 for userlist)
PgBouncer user has no shell access (/bin/false)
Firewall rules restrict access to application servers only
SELinux policies are configured correctly
SSL/TLS is configured (if required)
Log rotation is configured
Monitoring script is running in cron
Systemd service is enabled for auto-start
All users tested through PgBouncer
Pool sizes tuned for expected load
Connection limits set appropriately
Admin access restricted to specific IPs
Backup of configuration files created
Documentation updated with server-specific details

Performance Monitoring

Key Metrics to Watch

Pool Utilization

1
2
SHOW POOLS;
-- Watch: sv_active / (sv_active + sv_idle)

Client Wait Time

1
2
SHOW POOLS;
-- Watch: cl_waiting and maxwait

Connection Churn

1
2
SHOW STATS;
-- Watch: total_requests, total_received

Query Duration

1
2
SHOW STATS;
-- Watch: avg_query_time

Set Up Alerts

Create alerts for:

cl_waiting > 10 (clients waiting for connections)
maxwait > 5 (clients waiting more than 5 seconds)
Pool utilization > 80%
PgBouncer process down

Service Management Quick Reference

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# Start PgBouncer
sudo systemctl start pgbouncer

# Stop PgBouncer
sudo systemctl stop pgbouncer

# Restart (drops all connections)
sudo systemctl restart pgbouncer

# Reload (keeps connections, reloads config)
sudo systemctl reload pgbouncer

# Check status
sudo systemctl status pgbouncer

# View recent logs
sudo journalctl -u pgbouncer --since "10 minutes ago"

# Follow logs in real-time
sudo journalctl -u pgbouncer -f

# Enable automatic start on boot
sudo systemctl enable pgbouncer

# Disable automatic start
sudo systemctl disable pgbouncer

Conclusion

Congratulations! You now have PgBouncer running on Rocky Linux 10 with:

Proper user authentication and permissions
Security hardening (firewall, SELinux, file permissions)
SSL/TLS encryption
Monitoring and health checks
Log rotation
Production-ready configuration

Next Steps

Integrate with Applications: Update your application connection strings to use port 6432 instead of 5432
Load Testing: Test with realistic load to tune pool sizes
Monitoring: Set up Prometheus + Grafana for visual monitoring
High Availability: Consider deploying multiple PgBouncer instances behind a load balancer
Automation: Use Ansible or similar tools to automate deployment

Key Takeaways

PgBouncer reduces PostgreSQL memory usage by up to 40x
Transaction mode is the recommended pool mode for most applications
Always test direct PostgreSQL connections before troubleshooting PgBouncer
Monitor pool saturation (cl_waiting) to prevent performance issues
Use reload instead of restart to avoid dropping connections

Additional Resources

Questions or Issues?

If you encounter any issues or have questions about this setup, feel free to leave a comment below. Happy pooling!

About the Author: This guide is part of the Citizix DevOps series, providing production-ready tutorials for system administrators and DevOps engineers.

Related Posts:

PostgreSQL Performance Tuning on Rocky Linux
Setting Up PostgreSQL High Availability with Patroni
Docker Compose for PostgreSQL + PgBouncer
Kubernetes Deployment of PgBouncer

Tags: #pgbouncer #postgresql #rockylinux #devops #database #connectionpooling #productiondeployment