Configure application connection pooling for CockroachDB with PgBouncer and HAProxy

#!/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'

# Global variables
SCRIPT_NAME=$(basename "$0")
CRDB_NODES=""
DB_NAME="myappdb"
DB_USER="myapp"
DB_PASSWORD=""

# Usage message
usage() {
    echo "Usage: $SCRIPT_NAME --nodes=IP1:PORT,IP2:PORT,IP3:PORT --dbname=NAME --user=USER --password=PASS"
    echo "Example: $SCRIPT_NAME --nodes=10.0.1.10:26257,10.0.1.11:26257,10.0.1.12:26257 --dbname=myapp --user=appuser --password=secret123"
    exit 1
}

# Parse arguments
for arg in "$@"; do
    case $arg in
        --nodes=*) CRDB_NODES="${arg#*=}" ;;
        --dbname=*) DB_NAME="${arg#*=}" ;;
        --user=*) DB_USER="${arg#*=}" ;;
        --password=*) DB_PASSWORD="${arg#*=}" ;;
        --help|-h) usage ;;
        *) echo -e "${RED}Unknown argument: $arg${NC}"; usage ;;
    esac
done

# Validate required arguments
if [[ -z "$CRDB_NODES" || -z "$DB_PASSWORD" ]]; then
    echo -e "${RED}Error: --nodes and --password are required${NC}"
    usage
fi

# Cleanup function
cleanup() {
    echo -e "${YELLOW}Script failed. Rolling back changes...${NC}"
    systemctl stop pgbouncer haproxy 2>/dev/null || true
    systemctl disable pgbouncer haproxy 2>/dev/null || true
}

trap cleanup ERR

# Check prerequisites
echo -e "${GREEN}[1/10] Checking prerequisites...${NC}"
if [[ $EUID -ne 0 ]]; then
    echo -e "${RED}This script must be run as root${NC}"
    exit 1
fi

# Auto-detect distribution
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) 
            PKG_MGR="apt"
            PKG_INSTALL="apt install -y"
            PKG_UPDATE="apt update && apt upgrade -y"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora) 
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf update -y"
            ;;
        amzn) 
            PKG_MGR="yum"
            PKG_INSTALL="yum install -y"
            PKG_UPDATE="yum update -y"
            ;;
        *) 
            echo -e "${RED}Unsupported distro: $ID${NC}"
            exit 1
            ;;
    esac
else
    echo -e "${RED}/etc/os-release not found${NC}"
    exit 1
fi

# Update system packages
echo -e "${GREEN}[2/10] Updating system packages...${NC}"
$PKG_UPDATE

# Install PgBouncer and HAProxy
echo -e "${GREEN}[3/10] Installing PgBouncer and HAProxy...${NC}"
$PKG_INSTALL pgbouncer haproxy

# Generate MD5 hash for password
echo -e "${GREEN}[4/10] Generating password hash...${NC}"
PASSWORD_HASH="md5$(echo -n "${DB_PASSWORD}${DB_USER}" | md5sum | cut -d' ' -f1)"

# Create PgBouncer user database file
echo -e "${GREEN}[5/10] Creating PgBouncer user database...${NC}"
mkdir -p /etc/pgbouncer
cat > /etc/pgbouncer/userlist.txt << EOF
"$DB_USER" "$PASSWORD_HASH"
EOF

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

# Configure HAProxy
echo -e "${GREEN}[6/10] Configuring HAProxy...${NC}"
cp /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.backup

cat > /etc/haproxy/haproxy.cfg << 'EOF'
global
    log stdout local0
    chroot /var/lib/haproxy
    stats socket /run/haproxy/admin.sock mode 660 level admin
    stats timeout 30s
    user haproxy
    group haproxy
    daemon

defaults
    mode tcp
    timeout connect 5000ms
    timeout client 50000ms
    timeout server 50000ms
    log global
    option tcplog
    option dontlognull

frontend cockroachdb_frontend
    bind *:26257
    default_backend cockroachdb_backend

backend cockroachdb_backend
    balance roundrobin
    option tcp-check
    tcp-check connect
EOF

# Parse nodes and add to HAProxy config
IFS=',' read -ra NODES <<< "$CRDB_NODES"
counter=1
for node in "${NODES[@]}"; do
    echo "    server crdb$counter $node check" >> /etc/haproxy/haproxy.cfg
    ((counter++))
done

cat >> /etc/haproxy/haproxy.cfg << 'EOF'

frontend stats
    bind *:8404
    stats enable
    stats uri /stats
    stats refresh 30s
EOF

# Configure PgBouncer
echo -e "${GREEN}[7/10] Configuring PgBouncer...${NC}"
cat > /etc/pgbouncer/pgbouncer.ini << EOF
[databases]
$DB_NAME = host=127.0.0.1 port=26257 dbname=$DB_NAME user=$DB_USER

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
admin_users = pgbouncer
stats_users = pgbouncer
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
max_db_connections = 100
reserve_pool_size = 10
reserve_pool_timeout = 3
max_prepared_statements = 0
server_reset_query = DISCARD ALL
server_check_query = SELECT 1
server_lifetime = 3600
server_idle_timeout = 600
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
EOF

# Set proper permissions
echo -e "${GREEN}[8/10] Setting file permissions...${NC}"
chown -R pgbouncer:pgbouncer /etc/pgbouncer
chmod 644 /etc/pgbouncer/pgbouncer.ini
chmod 600 /etc/pgbouncer/userlist.txt
mkdir -p /var/log/postgresql
chown postgres:postgres /var/log/postgresql
mkdir -p /var/run/postgresql
chown postgres:postgres /var/run/postgresql

# Configure systemd services
echo -e "${GREEN}[9/10] Configuring services...${NC}"
systemctl enable haproxy
systemctl enable pgbouncer
systemctl restart haproxy
systemctl restart pgbouncer

# Configure firewall if available
if command -v ufw >/dev/null 2>&1; then
    ufw allow 6432/tcp comment "PgBouncer"
    ufw allow 8404/tcp comment "HAProxy stats"
elif command -v firewall-cmd >/dev/null 2>&1; then
    firewall-cmd --permanent --add-port=6432/tcp
    firewall-cmd --permanent --add-port=8404/tcp
    firewall-cmd --reload
fi

# Verification
echo -e "${GREEN}[10/10] Verifying installation...${NC}"
sleep 3

if systemctl is-active --quiet haproxy; then
    echo -e "${GREEN}✓ HAProxy is running${NC}"
else
    echo -e "${RED}✗ HAProxy failed to start${NC}"
fi

if systemctl is-active --quiet pgbouncer; then
    echo -e "${GREEN}✓ PgBouncer is running${NC}"
else
    echo -e "${RED}✗ PgBouncer failed to start${NC}"
fi

echo -e "${GREEN}Installation complete!${NC}"
echo -e "${YELLOW}Connect to PgBouncer on: localhost:6432${NC}"
echo -e "${YELLOW}HAProxy stats available at: http://localhost:8404/stats${NC}"
echo -e "${YELLOW}Connection string: postgresql://$DB_USER:$DB_PASSWORD@localhost:6432/$DB_NAME${NC}"