Optimize PostgreSQL connection pooling with PgBouncer for high-traffic applications

#!/usr/bin/env bash

set -euo pipefail

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Default values
DB_HOST="${1:-127.0.0.1}"
DB_PORT="${2:-5432}"
DB_NAME="${3:-myapp_production}"
DB_USER="${4:-myappuser}"
DB_PASSWORD="${5:-securepass123}"

# Usage function
usage() {
    echo "Usage: $0 [DB_HOST] [DB_PORT] [DB_NAME] [DB_USER] [DB_PASSWORD]"
    echo "Example: $0 127.0.0.1 5432 myapp_production myappuser securepass123"
    exit 1
}

# Logging functions
log_info() { echo -e "${GREEN}[INFO]${NC} $1"; }
log_warn() { echo -e "${YELLOW}[WARN]${NC} $1"; }
log_error() { echo -e "${RED}[ERROR]${NC} $1"; }

# Cleanup function
cleanup() {
    log_error "Installation failed. Cleaning up..."
    systemctl stop pgbouncer 2>/dev/null || true
    systemctl disable pgbouncer 2>/dev/null || true
    userdel pgbouncer 2>/dev/null || true
    rm -rf /var/lib/pgbouncer /var/log/pgbouncer /etc/pgbouncer 2>/dev/null || true
    rm -f /etc/systemd/system/pgbouncer.service 2>/dev/null || true
    systemctl daemon-reload 2>/dev/null || true
}

trap cleanup ERR

# Check if running as root
if [[ $EUID -ne 0 ]]; then
    log_error "This script must be run as root or with sudo"
    exit 1
fi

# Detect distribution and package manager
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) 
            PKG_MGR="apt"
            PKG_UPDATE="apt update && apt upgrade -y"
            PKG_INSTALL="apt install -y"
            PGBOUNCER_PKG="pgbouncer postgresql-client"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora) 
            PKG_MGR="dnf"
            PKG_UPDATE="dnf update -y"
            PKG_INSTALL="dnf install -y"
            PGBOUNCER_PKG="pgbouncer postgresql"
            ;;
        amzn) 
            PKG_MGR="yum"
            PKG_UPDATE="yum update -y"
            PKG_INSTALL="yum install -y"
            PGBOUNCER_PKG="pgbouncer postgresql"
            ;;
        *) 
            log_error "Unsupported distribution: $ID"
            exit 1 
            ;;
    esac
else
    log_error "Cannot detect distribution. /etc/os-release not found."
    exit 1
fi

log_info "Detected distribution: $ID using $PKG_MGR"

# Step 1: Update system packages
echo "[1/8] Updating system packages..."
$PKG_UPDATE

# Step 2: Install PgBouncer
echo "[2/8] Installing PgBouncer..."
$PKG_INSTALL $PGBOUNCER_PKG

# Step 3: Create PgBouncer user and directories
echo "[3/8] Creating PgBouncer user and directories..."
if ! id "pgbouncer" &>/dev/null; then
    useradd --system --home /var/lib/pgbouncer --shell /bin/bash pgbouncer
fi

mkdir -p /var/lib/pgbouncer /var/log/pgbouncer /etc/pgbouncer /var/run/pgbouncer
chown pgbouncer:pgbouncer /var/lib/pgbouncer /var/log/pgbouncer /var/run/pgbouncer
chmod 750 /var/lib/pgbouncer /var/log/pgbouncer /var/run/pgbouncer
chmod 755 /etc/pgbouncer

# Step 4: Create authentication file
echo "[4/8] Creating authentication file..."
touch /etc/pgbouncer/userlist.txt
chown pgbouncer:pgbouncer /etc/pgbouncer/userlist.txt
chmod 600 /etc/pgbouncer/userlist.txt

# Generate MD5 hash for password
MD5_HASH=$(echo -n "${DB_PASSWORD}${DB_USER}" | md5sum | cut -d' ' -f1)

cat > /etc/pgbouncer/userlist.txt << EOF
"${DB_USER}" "md5${MD5_HASH}"
"pgbouncer" "md5${MD5_HASH}"
EOF

# Step 5: Create PgBouncer configuration
echo "[5/8] Creating PgBouncer configuration..."
cat > /etc/pgbouncer/pgbouncer.ini << EOF
[databases]
${DB_NAME} = host=${DB_HOST} port=${DB_PORT} dbname=${DB_NAME} user=${DB_USER}

[pgbouncer]
; Connection pooling settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 5
reserve_pool_size = 5
max_db_connections = 50

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

; Network settings
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql

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

; Performance tuning
server_lifetime = 3600
server_idle_timeout = 600
client_idle_timeout = 0
query_timeout = 300
query_wait_timeout = 120
client_login_timeout = 60

; Admin settings
admin_users = pgbouncer
stats_users = pgbouncer
EOF

chown pgbouncer:pgbouncer /etc/pgbouncer/pgbouncer.ini
chmod 640 /etc/pgbouncer/pgbouncer.ini

# Step 6: Create systemd service
echo "[6/8] Creating systemd service..."
cat > /etc/systemd/system/pgbouncer.service << EOF
[Unit]
Description=PgBouncer connection pooler for PostgreSQL
Documentation=man:pgbouncer(1)
After=network.target postgresql.service
Requires=network.target

[Service]
Type=forking
User=pgbouncer
Group=pgbouncer
ExecStart=/usr/bin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -HUP \$MAINPID
PIDFile=/var/run/pgbouncer/pgbouncer.pid
Restart=always
RestartSec=5
TimeoutSec=60

; Security settings
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/var/lib/pgbouncer /var/log/pgbouncer /var/run/pgbouncer

[Install]
WantedBy=multi-user.target
EOF

# Step 7: Configure firewall
echo "[7/8] Configuring firewall..."
if command -v firewall-cmd &> /dev/null; then
    firewall-cmd --permanent --add-port=6432/tcp || log_warn "Failed to add firewall rule"
    firewall-cmd --reload || log_warn "Failed to reload firewall"
elif command -v ufw &> /dev/null; then
    ufw allow 6432/tcp || log_warn "Failed to add UFW rule"
else
    log_warn "No supported firewall detected. Please manually allow port 6432/tcp"
fi

# Configure SELinux if present
if command -v setsebool &> /dev/null; then
    setsebool -P nis_enabled 1 || log_warn "Failed to set SELinux boolean"
fi

# Step 8: Start and enable services
echo "[8/8] Starting and enabling PgBouncer service..."
systemctl daemon-reload
systemctl enable pgbouncer
systemctl start pgbouncer

# Verification checks
echo ""
log_info "Performing verification checks..."

if systemctl is-active --quiet pgbouncer; then
    log_info "✓ PgBouncer service is running"
else
    log_error "✗ PgBouncer service is not running"
    systemctl status pgbouncer
    exit 1
fi

if netstat -tlnp 2>/dev/null | grep -q ":6432"; then
    log_info "✓ PgBouncer is listening on port 6432"
else
    log_warn "⚠ PgBouncer may not be listening on port 6432"
fi

if [ -f /var/log/pgbouncer/pgbouncer.log ]; then
    log_info "✓ Log file created successfully"
else
    log_warn "⚠ Log file not found"
fi

echo ""
log_info "PgBouncer installation completed successfully!"
echo ""
echo "Configuration details:"
echo "  - Connection string: host=localhost port=6432 dbname=${DB_NAME} user=${DB_USER}"
echo "  - Admin interface: psql -h localhost -p 6432 -U pgbouncer pgbouncer"
echo "  - Config file: /etc/pgbouncer/pgbouncer.ini"
echo "  - Log file: /var/log/pgbouncer/pgbouncer.log"
echo ""
echo "Next steps:"
echo "  1. Update your application connection string to use port 6432"
echo "  2. Monitor logs: tail -f /var/log/pgbouncer/pgbouncer.log"
echo "  3. Check stats: echo 'SHOW STATS;' | psql -h localhost -p 6432 -U pgbouncer pgbouncer"