# Ubuntu/Debian
apt update && apt install -y pgbouncer

# CentOS/Rocky Linux
dnf install -y pgbouncer

# Verificar la versión instalada
pgbouncer --version

# Directorio de configuración
ls -la /etc/pgbouncer/

# Habilitar e iniciar el servicio
systemctl enable pgbouncer
systemctl start pgbouncer

# En la mayoría de aplicaciones de producción, transaction mode es el óptimo
# Verificar la compatibilidad de tu aplicación:

# Estas características NO funcionan en transaction mode:
# - SET SESSION commands que persisten entre transacciones
# - LISTEN/NOTIFY
# - PREPARE statements del lado servidor
# - Advisory locks con pg_try_advisory_lock y similares
# - WITH HOLD cursors

cat > /etc/pgbouncer/pgbouncer.ini << 'EOF'
[databases]
# Formato: nombre_db = host=servidor port=puerto dbname=nombre_real
miapp = host=postgres.miempresa.local port=5432 dbname=miapp
miapp_readonly = host=postgres-replica.miempresa.local port=5432 dbname=miapp

# Wildcard: redirigir cualquier base de datos al servidor PostgreSQL
# * = host=postgres.miempresa.local port=5432

[pgbouncer]
# Dirección y puerto donde escucha pgBouncer
listen_addr = 0.0.0.0
listen_port = 5432

# Número máximo de conexiones al servidor PostgreSQL por pool
# Regla general: max_server_conn = (número de núcleos * 2) + número de discos
server_pool_size = 20

# Modo de pooling: session, transaction o statement
pool_mode = transaction

# Número máximo de conexiones de CLIENTES (aplicaciones)
# Puede ser muy alto ya que pgBouncer las gestiona eficientemente
max_client_conn = 1000

# Número por defecto de conexiones al servidor por pool
default_pool_size = 20

# Conexiones de reserva para situaciones de alta demanda
reserve_pool_size = 5

# Tiempo (segundos) antes de usar las conexiones de reserva
reserve_pool_timeout = 3

# Conexiones mínimas al servidor (para tenerlas listas)
min_pool_size = 5

# Tiempo máximo de vida de una conexión al servidor (segundos)
server_lifetime = 3600

# Tiempo de inactividad antes de cerrar una conexión al servidor
server_idle_timeout = 600

# Tiempo de inactividad antes de cerrar una conexión del cliente
client_idle_timeout = 300

# Tiempo máximo para que un cliente espere una conexión del pool
query_wait_timeout = 30

# Tiempo máximo para ejecutar una consulta (0 = sin límite)
query_timeout = 0

# Archivo de autenticación de usuarios
auth_file = /etc/pgbouncer/userlist.txt
auth_type = md5

# Archivo PID
pidfile = /var/run/pgbouncer/pgbouncer.pid

# Logs
logfile = /var/log/pgbouncer/pgbouncer.log
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

# Estadísticas
stats_period = 60

# Ignorar los parámetros de startup que pueden causar problemas
# (útil cuando la aplicación envía parámetros que pgBouncer no entiende)
ignore_startup_parameters = extra_float_digits,application_name

# Verificar el estado del servidor PostgreSQL
server_check_query = SELECT 1
server_check_delay = 30

# Tiempo para establecer la conexión al servidor
server_connect_timeout = 15

# Tiempo máximo para la autenticación al servidor
server_login_retry = 15

# Usuario administrador para la consola de pgBouncer
admin_users = pgbouncer_admin
stats_users = pgbouncer_stats, prometheus_user
EOF

# Crear el archivo de usuarios (userlist.txt)
# Las contraseñas deben estar hasheadas en md5 o SCRAM-SHA-256

# Generar el hash md5 de la contraseña (formato requerido por pgBouncer)
# El hash es md5(contraseña + usuario)
echo -n "contraseña_seguramiapp_user" | md5sum
# Resultado: abc123def456...

# Formato del archivo userlist.txt
cat > /etc/pgbouncer/userlist.txt << 'EOF'
"miapp_user" "md5abc123def456abc123def456abc123"
"readonly_user" "md5def456abc123def456abc123def456"
"pgbouncer_admin" "md5admin_hash_aqui"
EOF

# Alternativa: usar la autenticación HBA de PostgreSQL
# En pgbouncer.ini cambiar:
# auth_type = hba
# auth_hba_file = /etc/pgbouncer/pg_hba.conf

# O usar auth_query para que pgBouncer verifique credenciales contra PostgreSQL
# En pgbouncer.ini:
# auth_type = scram-sha-256
# auth_user = pgbouncer_auth
# auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename = $1

# Configurar TLS para las conexiones cliente → pgBouncer
cat >> /etc/pgbouncer/pgbouncer.ini << 'EOF'

[tls]
# TLS para conexiones de clientes (aplicación → pgBouncer)
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/tls/pgbouncer.crt
client_tls_key_file = /etc/pgbouncer/tls/pgbouncer.key
client_tls_ca_file = /etc/pgbouncer/tls/ca.crt

# TLS para conexiones al servidor (pgBouncer → PostgreSQL)
server_tls_sslmode = require
server_tls_ca_file = /etc/pgbouncer/tls/postgres-ca.crt
EOF

# Generar certificados autofirmados para desarrollo
mkdir -p /etc/pgbouncer/tls
openssl req -new -x509 -days 3650 -nodes \
  -out /etc/pgbouncer/tls/pgbouncer.crt \
  -keyout /etc/pgbouncer/tls/pgbouncer.key \
  -subj "/CN=pgbouncer.miempresa.local"

chown -R pgbouncer:pgbouncer /etc/pgbouncer/tls
chmod 600 /etc/pgbouncer/tls/pgbouncer.key

# En transaction mode, los prepared statements del servidor no funcionan directamente
# Solución 1: Usar la opción max_prepared_statements (pgBouncer 1.21+)
cat >> /etc/pgbouncer/pgbouncer.ini << 'EOF'
# Número máximo de prepared statements por cliente que pgBouncer trackea
max_prepared_statements = 200
EOF

# Solución 2: Deshabilitar prepared statements en la aplicación
# En Django (Python):
# DATABASES = {'OPTIONS': {'options': '-c statement_timeout=30000'}}
# Y usar CONN_MAX_AGE = 0 para no reutilizar conexiones

# En psycopg2/psycopg3:
# conn = psycopg2.connect(dsn, prepared_statement_cache_size=0)

# En Node.js (node-postgres):
# const pool = new Pool({max: 20, statement_timeout: 30000})
# pool.query({text: '...', values: [...]})  # Nunca usar .prepare()

# Verificar que no hay prepared statements problemáticos
# Conectarse a la consola de pgBouncer
psql -h localhost -p 5432 -U pgbouncer_admin pgbouncer -c "SHOW PREPARED;"

# Conectarse a la consola de administración de pgBouncer
psql -h localhost -p 5432 -U pgbouncer_admin pgbouncer

# Comandos de la consola de pgBouncer:

# Ver estadísticas de los pools
SHOW POOLS;

# Ver estadísticas globales
SHOW STATS;

# Ver las conexiones de clientes
SHOW CLIENTS;

# Ver las conexiones al servidor PostgreSQL
SHOW SERVERS;

# Ver los databases configurados
SHOW DATABASES;

# Ver la configuración actual
SHOW CONFIG;

# Suspender/reanudar pgBouncer (para mantenimiento)
SUSPEND;
RESUME;

# Recargar la configuración sin reiniciar
RELOAD;

# Matar una conexión específica
KILL miapp;

# Exportar métricas en formato Prometheus (pgBouncer Exporter)
docker run -d \
  --name pgbouncer-exporter \
  -e DATA_SOURCE_NAME="postgresql://pgbouncer_stats:contraseña@localhost:5432/pgbouncer" \
  -p 9127:9127 \
  prometheuscommunity/pgbouncer-exporter

# Verificar las métricas
curl -s http://localhost:9127/metrics | grep pgbouncer_pool

# Métricas importantes:
# pgbouncer_pool_client_active_connections  - Clientes con transacción activa
# pgbouncer_pool_client_waiting_connections - Clientes esperando conexión
# pgbouncer_pool_server_active_connections  - Conexiones al servidor en uso
# pgbouncer_pool_server_idle_connections    - Conexiones al servidor libres
# pgbouncer_stats_total_requests            - Total de peticiones procesadas
# pgbouncer_stats_avg_query_time            - Tiempo medio de consulta

# Configurar múltiples instancias de pgBouncer con HAProxy para HA
cat > /etc/haproxy/haproxy.cfg << 'EOF'
frontend postgresql_frontend
  bind *:5432
  mode tcp
  default_backend postgresql_backend

backend postgresql_backend
  mode tcp
  balance leastconn
  
  # Chequeo de salud de pgBouncer
  option tcp-check
  
  server pgbouncer-1 pgbouncer-1.miempresa.local:5432 check inter 3s
  server pgbouncer-2 pgbouncer-2.miempresa.local:5432 check inter 3s backup
EOF

# Configurar keepalived para la IP virtual flotante
cat > /etc/keepalived/keepalived.conf << 'EOF'
vrrp_instance pgbouncer_vip {
  state MASTER
  interface eth0
  virtual_router_id 51
  priority 100
  advert_int 1
  
  authentication {
    auth_type PASS
    auth_pass secreto123
  }
  
  virtual_ipaddress {
    192.168.1.200/24
  }
  
  track_script {
    check_pgbouncer
  }
}

vrrp_script check_pgbouncer {
  script "pg_isready -h localhost -p 5432 -U pgbouncer_admin -d pgbouncer"
  interval 2
}
EOF

# Ver cuántos clientes están esperando
psql -h localhost -p 5432 -U pgbouncer_admin pgbouncer \
  -c "SHOW POOLS;" | grep -v "|  0 |  0 |  0 |"

# Si cl_waiting > 0, aumentar el tamaño del pool
# En pgbouncer.ini: aumentar default_pool_size o max_server_conn

# Ver el tiempo de espera de los clientes
psql -h localhost -p 5432 -U pgbouncer_admin pgbouncer -c "SHOW CLIENTS;"

# Estás en transaction mode y la aplicación usa prepared statements del servidor
# Opciones:
# 1. Cambiar a session mode (menos eficiente pero compatible)
# 2. Configurar max_prepared_statements en pgBouncer 1.21+
# 3. Deshabilitar prepared statements en la aplicación

# Verificar el modo actual
psql -h localhost -p 5432 -U pgbouncer_admin pgbouncer \
  -c "SHOW CONFIG;" | grep pool_mode

# Verificar que PostgreSQL acepta conexiones desde pgBouncer
# En pg_hba.conf del servidor PostgreSQL:
# host  all  all  <ip-pgbouncer>/32  md5

# Probar la conexión directamente
psql -h postgres.miempresa.local -p 5432 -U miapp_user miapp

# Ver los logs de pgBouncer
tail -f /var/log/pgbouncer/pgbouncer.log

# Activar logs verbose temporalmente
psql -h localhost -p 5432 -U pgbouncer_admin pgbouncer \
  -c "SET log_connections=1; SET log_disconnections=1;"