# Ubuntu/Debian
sudo apt-get update
sudo apt-get install -y pgbouncer

# CentOS/Rocky Linux
sudo yum install -y epel-release
sudo yum install -y pgbouncer

# Or compile from source for latest version
sudo apt-get install -y libevent-dev libssl-dev libpq-dev

wget https://www.pgbouncer.org/downloads/files/1.23.1/pgbouncer-1.23.1.tar.gz
tar xzf pgbouncer-1.23.1.tar.gz
cd pgbouncer-1.23.1
./configure --prefix=/usr/local
make
sudo make install

# Verify installation
pgbouncer --version

# Create config directory and user
sudo useradd -r -s /bin/false pgbouncer
sudo mkdir -p /etc/pgbouncer /var/log/pgbouncer /var/run/pgbouncer
sudo chown pgbouncer:pgbouncer /etc/pgbouncer /var/log/pgbouncer /var/run/pgbouncer

# /etc/pgbouncer/pgbouncer.ini

[databases]
# Format: dbname = host=host port=port dbname=dbname
myapp = host=postgres-primary.internal port=5432 dbname=myapp
myapp_read = host=postgres-replica.internal port=5432 dbname=myapp pool_mode=session

# Wildcard: forward all databases to backend (useful for multi-tenant)
* = host=postgres.internal port=5432

[pgbouncer]
# Network settings
listen_addr = 0.0.0.0
listen_port = 5432
unix_socket_dir = /var/run/pgbouncer

# Authentication
auth_type = scram-sha-256          # Strongest auth method
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgbouncer_admin
stats_users = pgbouncer_monitor

# Pool mode (critical setting):
# session   - client holds server connection for entire session (compatible with all features)
# transaction - server connection released after each transaction (best for web apps)
# statement - server connection released after each statement (most restrictive)
pool_mode = transaction

# Pool sizing
# Rule of thumb: max_client_conn = your total app connections
# default_pool_size = connections to maintain to PostgreSQL
max_client_conn = 2000           # Total allowed client connections
default_pool_size = 25           # Backend connections per database+user pair
min_pool_size = 5                # Always keep at least 5 connections ready
reserve_pool_size = 5            # Extra connections for spikes
reserve_pool_timeout = 5         # Seconds before using reserve pool

# Connection timeouts
server_connect_timeout = 15      # Timeout connecting to PostgreSQL
server_login_retry = 15          # Retry interval on failed backend connect
client_login_timeout = 60        # Drop client if auth takes too long
query_timeout = 0                # 0 = no timeout (set at app level instead)
query_wait_timeout = 120         # Max time to wait for a free connection
client_idle_timeout = 600        # Drop idle client connections after 10 min
server_idle_timeout = 600        # Close idle backend connections after 10 min
server_lifetime = 3600           # Recycle backend connections every hour

# Logging
log_connections = 0              # 0 for production (1 for debugging)
log_disconnections = 0
log_pooler_errors = 1
stats_period = 60

# Performance
tcp_keepalive = 1
tcp_keepidle = 60
tcp_keepintvl = 5

# Create userlist.txt with hashed passwords
# Generate scram-sha-256 hash using psql
psql -h postgres -U postgres -c "SELECT rolpassword FROM pg_authid WHERE rolname='appuser';"

# Add to userlist.txt
cat > /etc/pgbouncer/userlist.txt <<EOF
"appuser" "SCRAM-SHA-256$<hash-from-above>"
"pgbouncer_admin" "md5<md5-hash>"
EOF

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

# Start PgBouncer
sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer
sudo systemctl status pgbouncer

# Option 1: Use prepared statement tracking (PgBouncer 1.22+)
# In [pgbouncer] section:
prepared_statement_cache_queries = 100  # Cache up to 100 prepared statements per backend

# Option 2: For applications that must use prepared statements with transaction pooling,
# use session pooling for those specific connections:
[databases]
myapp_prepared = host=postgres.internal port=5432 dbname=myapp pool_mode=session
myapp = host=postgres.internal port=5432 dbname=myapp pool_mode=transaction

# Application-side workaround for ORMs (e.g., psycopg2/Django)
# Disable prepared statements in application:

# Django: In settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'pgbouncer-host',
        'PORT': '5432',
        'OPTIONS': {
            'options': '-c default_transaction_isolation=read\ committed'
        },
        # Disable server-side cursors for pgbouncer compatibility
        'DISABLE_SERVER_SIDE_CURSORS': True,
    }
}

# Node.js with pg: Use simple query mode
const pool = new Pool({
  host: 'pgbouncer-host',
  // Disable prepared statements
  ssl: { rejectUnauthorized: false }
});

# Generate or copy TLS certificates
sudo cp /etc/ssl/certs/your-cert.pem /etc/pgbouncer/server.crt
sudo cp /etc/ssl/private/your-key.pem /etc/pgbouncer/server.key
sudo chown pgbouncer:pgbouncer /etc/pgbouncer/server.{crt,key}
sudo chmod 640 /etc/pgbouncer/server.key

# TLS settings in pgbouncer.ini

[pgbouncer]
# Client-to-PgBouncer TLS
client_tls_sslmode = require       # require, verify-ca, verify-full
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_ca_file = /etc/ssl/certs/ca-certificates.crt
client_tls_protocols = secure      # TLSv1.2+
client_tls_ciphers = fast          # AES-128 preferred for speed

# PgBouncer-to-PostgreSQL TLS
server_tls_sslmode = require
server_tls_ca_file = /etc/ssl/certs/ca-certificates.crt
server_tls_protocols = secure

# auth_type options:
# scram-sha-256 - modern, secure (PostgreSQL 10+)
# md5 - legacy, still supported
# hba - use pg_hba.conf style rules
# cert - client certificate authentication
# plain - plain text (do not use in production)

[pgbouncer]
auth_type = hba
auth_hba_file = /etc/pgbouncer/pg_hba.conf

# auth_query: Fetch passwords dynamically from PostgreSQL
# Avoids maintaining userlist.txt manually
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
auth_user = pgbouncer_auth  # User to run auth queries as

# Create minimal auth user in PostgreSQL
sudo -u postgres psql <<EOF
CREATE USER pgbouncer_auth WITH ENCRYPTED PASSWORD 'auth-password';
GRANT pg_read_all_data ON TABLE pg_shadow TO pgbouncer_auth;
-- More restrictive:
-- GRANT SELECT ON pg_shadow TO pgbouncer_auth;
EOF

# HBA file for PgBouncer (/etc/pgbouncer/pg_hba.conf)
cat > /etc/pgbouncer/pg_hba.conf <<EOF
# TYPE  DATABASE  USER       ADDRESS         METHOD
local   all       all                        scram-sha-256
host    myapp     appuser    10.0.0.0/8      scram-sha-256
host    myapp     appuser    172.16.0.0/12   scram-sha-256
hostssl myapp     appuser    0.0.0.0/0       scram-sha-256
EOF

# Connect to PgBouncer admin interface
psql -h 127.0.0.1 -p 5432 -U pgbouncer_admin pgbouncer

# Available commands in admin console:
SHOW HELP;
SHOW STATS;         -- Per-database statistics
SHOW POOLS;         -- Pool status and connection counts
SHOW CLIENTS;       -- Active client connections
SHOW SERVERS;       -- Backend server connections
SHOW DATABASES;     -- Database configurations
SHOW CONFIG;        -- Current configuration
SHOW LISTS;         -- Summary statistics
SHOW USERS;         -- User configurations

-- Monitor connection pool utilization
SHOW POOLS;
-- Key columns:
-- cl_active: clients with assigned server connection
-- cl_waiting: clients waiting for connection
-- sv_active: connections in use
-- sv_idle: free connections ready
-- sv_used: connections released but not cleaned yet

-- Check for connection wait times
SHOW STATS_TOTALS;

-- Online reload after config changes
RELOAD;

-- Pause/resume for maintenance
PAUSE myapp;      -- Wait for transactions to complete, stop new ones
RESUME myapp;     -- Resume normal operation

# Use pgbouncer_exporter for Prometheus metrics
docker run -d \
  --name pgbouncer-exporter \
  -p 9127:9127 \
  -e PGBOUNCER_EXPORTER_HOST=pgbouncer-host \
  -e PGBOUNCER_EXPORTER_PORT=5432 \
  -e PGBOUNCER_EXPORTER_USER=pgbouncer_monitor \
  -e PGBOUNCER_EXPORTER_PASSWORD=monitor-password \
  spreaker/prometheus-pgbouncer-exporter

# Key metrics to alert on:
# pgbouncer_pools_cl_waiting > 0 (clients waiting for connections)
# pgbouncer_pools_sv_idle < 1 (no free connections)
# pgbouncer_stats_total_wait_time increasing rapidly

# Option 1: PgBouncer on each application server
# Each app server runs its own PgBouncer to avoid single point of failure

# Option 2: Two PgBouncer nodes with HAProxy
cat > /etc/haproxy/haproxy.cfg <<EOF
frontend postgres_frontend
  bind *:5432
  mode tcp
  default_backend pgbouncer_backend

backend pgbouncer_backend
  mode tcp
  option tcp-check
  balance roundrobin
  server pgbouncer-1 10.0.0.11:5432 check
  server pgbouncer-2 10.0.0.12:5432 check backup
EOF

# Option 3: Keepalived for VIP-based failover
# Primary PgBouncer node:
cat > /etc/keepalived/keepalived.conf <<EOF
vrrp_instance pgbouncer_vip {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    authentication {
        auth_type PASS
        auth_pass pgbouncer123
    }
    virtual_ipaddress {
        10.0.0.100/24
    }
    notify_master "/usr/local/bin/switch_pgbouncer.sh master"
    notify_backup "/usr/local/bin/switch_pgbouncer.sh backup"
}
EOF

sudo systemctl enable --now keepalived

# Check current connection counts
psql -h 127.0.0.1 -U pgbouncer_admin pgbouncer -c "SHOW POOLS;"

# Increase max_client_conn if needed
# Edit pgbouncer.ini and reload:
sudo systemctl reload pgbouncer

# Check if max_client_conn is higher than PostgreSQL's max_connections
psql -h postgres -U postgres -c "SHOW max_connections;"

# Increase default_pool_size (more connections to PostgreSQL)
# Or reduce query_wait_timeout to fail fast
# Check PostgreSQL for long-running queries
psql -h postgres -U postgres -c "
  SELECT pid, query, now() - query_start AS duration
  FROM pg_stat_activity
  WHERE state = 'active' AND query_start < now() - interval '30 seconds'
  ORDER BY duration DESC;"

# Look for error: "prepared statement ... does not exist"
# This means your application uses prepared statements with transaction pool mode
# Solutions:
# 1. Switch to session pool mode for that database
# 2. Enable prepared statement cache (pgbouncer 1.22+)
# 3. Configure application to not use prepared statements

# Enable detailed auth logging
# Add to pgbouncer.ini: log_connections = 1
sudo journalctl -u pgbouncer -n 50

# Test auth_query manually
psql -h postgres -U pgbouncer_auth -c \
  "SELECT usename, passwd FROM pg_shadow WHERE usename='appuser';"