PgBouncer es un pool de conexiones ligero para PostgreSQL: las aplicaciones abren cientos o miles de conexiones contra PgBouncer y este las reparte sobre unas pocas decenas de conexiones reales al servidor. Cada conexión de PostgreSQL es un proceso con su propia memoria, así que reducir su número mejora la estabilidad y el rendimiento cuando hay mucha concurrencia. En este tutorial configurarás PgBouncer en Ubuntu 24.04 en modo transaction, con autenticación SCRAM delegada en PostgreSQL mediante auth_query, soporte de prepared statements, TLS para los clientes y monitorización desde su consola de administración.

Requisitos previos

  • Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath, con al menos 1 GB de RAM.
  • Un usuario no root con privilegios sudo.
  • PostgreSQL instalado en el mismo servidor. En esta guía se usa PostgreSQL 16, la versión de los repositorios de Ubuntu 24.04. Si PostgreSQL está en otro host, cambia 127.0.0.1 por su IP en la sección [databases].
  • Conocimientos básicos de psql.

Ubuntu 24.04 incluye PgBouncer 1.22, que ya soporta prepared statements en modo transaction a través de max_prepared_statements.

Paso 1: Instalar PostgreSQL y PgBouncer

Instala ambos paquetes desde los repositorios de Ubuntu:

sudo apt update
sudo apt install -y postgresql pgbouncer

Comprueba la versión de PgBouncer:

pgbouncer --version

PgBouncer 1.22.0
libevent 2.1.12-stable
adns: c-ares 1.27.0
tls: OpenSSL 3.0.13 30 Jan 2024

En Ubuntu el servicio pgbouncer se ejecuta con el usuario del sistema postgres, lee /etc/pgbouncer/pgbouncer.ini y escribe su log en /var/log/postgresql/pgbouncer.log. Tras la instalación queda activo con una configuración vacía.

Paso 2: Crear la base de datos y los roles

Vas a crear dos roles en PostgreSQL:

  • appuser: el usuario de la aplicación, dueño de la base de datos appdb.
  • pgbouncer_auth: un rol sin privilegios que PgBouncer usa solo para consultar los hashes de contraseña de los demás usuarios.

Abre una sesión de psql como superusuario:

sudo -u postgres psql

Crea el usuario de la aplicación y su base de datos. Sustituye your_app_password por una contraseña fuerte:

CREATE ROLE appuser LOGIN PASSWORD 'your_app_password';
CREATE DATABASE appdb OWNER appuser;
CREATE ROLE pgbouncer_auth LOGIN PASSWORD 'your_auth_password';

PostgreSQL 16 guarda las contraseñas como SCRAM-SHA-256 por defecto (password_encryption = scram-sha-256), que es lo que PgBouncer necesita para reenviar la autenticación.

Ahora crea, dentro de appdb, una función SECURITY DEFINER que devuelve el hash de un usuario. Así pgbouncer_auth no necesita acceso directo a pg_shadow, que solo puede leer un superusuario:

\c appdb
CREATE SCHEMA pgbouncer;
CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(IN i_username text, OUT uname text, OUT phash text)
RETURNS record AS $$
BEGIN
    SELECT usename, passwd FROM pg_catalog.pg_shadow
    WHERE usename = i_username INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog;
REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM PUBLIC;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer_auth;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer_auth;
\q

auth_query se ejecuta en la base de datos a la que se conecta el cliente, por lo que la función debe existir en cada base de datos que publiques a través de PgBouncer.

Comprueba que el rol de autenticación puede usar la función:

psql "host=127.0.0.1 dbname=appdb user=pgbouncer_auth" -c "SELECT uname FROM pgbouncer.user_lookup('appuser');"

  uname
---------
 appuser
(1 row)

Paso 3: Preparar el fichero de usuarios

PgBouncer necesita la contraseña de pgbouncer_auth para conectarse a PostgreSQL y ejecutar la consulta de autenticación. También definirás aquí un usuario pgbouncer_admin que solo existe en PgBouncer y sirve para entrar en su consola de administración.

Edita el fichero:

sudo nano /etc/pgbouncer/userlist.txt

Añade estas dos líneas, con las contraseñas entre comillas dobles:

"pgbouncer_auth" "your_auth_password"
"pgbouncer_admin" "your_admin_password"

El usuario de la aplicación no aparece aquí: PgBouncer obtiene su hash SCRAM de PostgreSQL con auth_query. Como el fichero contiene contraseñas en claro, restringe sus permisos:

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

Paso 4: Configurar PgBouncer en modo transaction

PgBouncer tiene tres modos de pool:

ModoLa conexión al servidor se liberaUso típico
sessionCuando el cliente se desconectaAplicaciones que dependen del estado de la sesión
transactionAl terminar cada transacciónLa mayoría de aplicaciones web y APIs
statementTras cada sentenciaSolo autocommit; prohíbe transacciones de varias sentencias

El modo transaction es el que más reduce las conexiones reales, pero algunas funciones de PostgreSQL dependen de la sesión y no se comportan como esperas: SET sin LOCAL, LISTEN, los advisory locks a nivel de sesión, las tablas temporales que sobreviven a la transacción, los cursores WITH HOLD y el comando SQL PREPARE. Las prepared statements del protocolo, las que usan la mayoría de drivers, sí funcionan con max_prepared_statements.

Guarda una copia de la configuración original:

sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.orig

Abre el fichero y sustituye todo su contenido:

sudo nano /etc/pgbouncer/pgbouncer.ini

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql

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

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pgbouncer_auth
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)

admin_users = pgbouncer_admin

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 50

server_lifetime = 3600
server_idle_timeout = 600
query_wait_timeout = 120

max_prepared_statements = 200
ignore_startup_parameters = extra_float_digits

Lo más importante de este bloque:

  • max_client_conn: conexiones de cliente que acepta PgBouncer. Cada una consume muy poca memoria, así que puede ser alto.
  • default_pool_size: conexiones al servidor por cada pareja usuario/base de datos. Es el número que realmente llega a PostgreSQL.
  • reserve_pool_size y reserve_pool_timeout: conexiones extra que se abren si un cliente lleva más de 3 segundos esperando.
  • max_db_connections: tope de conexiones al servidor por base de datos, sumando todos los usuarios. Mantenlo por debajo de max_connections de PostgreSQL (100 por defecto) dejando margen para conexiones de administración.
  • query_wait_timeout: segundos que un cliente puede esperar en cola antes de recibir un error.
  • ignore_startup_parameters = extra_float_digits: algunos drivers, como JDBC, envían este parámetro al conectar y PgBouncer lo rechazaría.

Deja listen_addr = 127.0.0.1 si la aplicación corre en el mismo servidor. Si se conecta desde otro host, cámbialo a 0.0.0.0 y abre el puerto solo para esa IP en el paso 7.

Reinicia el servicio y revisa su estado:

sudo systemctl restart pgbouncer
sudo systemctl status pgbouncer --no-pager

● pgbouncer.service - connection pooler for PostgreSQL
     Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; enabled; preset: enabled)
     Active: active (running) since ...

Conéctate a appdb a través de PgBouncer con el usuario de la aplicación:

psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser" -c "SELECT current_user, inet_server_port();"

 current_user | inet_server_port
--------------+------------------
 appuser      |             5432
(1 row)

El puerto 5432 en la respuesta confirma que la consulta ha llegado a PostgreSQL a través de PgBouncer, que escucha en el 6432.

Paso 5: Monitorizar desde la consola de administración

PgBouncer expone una base de datos virtual llamada pgbouncer con comandos SHOW. Entra con el usuario administrador:

psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pgbouncer_admin"

Muestra el estado de los pools:

SHOW POOLS;

 database  |      user       | cl_active | cl_waiting | ... | sv_active | sv_idle | ... | maxwait | maxwait_us | pool_mode
-----------+-----------------+-----------+------------+-----+-----------+---------+-----+---------+------------+-------------
 appdb     | appuser         |         0 |          0 | ... |         0 |       1 | ... |       0 |          0 | transaction
 pgbouncer | pgbouncer       |         1 |          0 | ... |         0 |       0 | ... |       0 |          0 | statement

Las columnas que conviene vigilar:

  • cl_active y cl_waiting: clientes emparejados con una conexión al servidor y clientes en cola. Si cl_waiting es mayor que cero de forma sostenida, el pool se queda corto.
  • sv_active y sv_idle: conexiones al servidor en uso y libres.
  • maxwait: segundos que lleva esperando el cliente más antiguo de la cola.

Otros comandos útiles de la consola:

SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;
SHOW CONFIG;
RELOAD;

SHOW STATS muestra transacciones, consultas y tiempos medios por base de datos, y RELOAD vuelve a leer pgbouncer.ini sin cortar conexiones. Los cambios de listen_addr o listen_port sí requieren reiniciar el servicio. Sal con \q.

Paso 6: Probar el pool con pgbench

pgbench viene con PostgreSQL y permite comprobar que PgBouncer absorbe muchos clientes con pocas conexiones reales. Para que no pida la contraseña en cada conexión, guárdala en ~/.pgpass:

echo "127.0.0.1:*:appdb:appuser:your_app_password" >> ~/.pgpass
chmod 600 ~/.pgpass

Inicializa las tablas de prueba en appdb conectando directamente a PostgreSQL:

pgbench -i -s 10 -h 127.0.0.1 -p 5432 -U appuser appdb

Lanza ahora 200 clientes durante 30 segundos contra PgBouncer, usando prepared statements del protocolo (-M prepared) para validar max_prepared_statements:

pgbench -c 200 -j 4 -T 30 -S -M prepared -h 127.0.0.1 -p 6432 -U appuser appdb

transaction type: <builtin: select only>
scaling factor: 10
query mode: prepared
number of clients: 200
number of threads: 4
...
number of failed transactions: 0 (0.000%)
...
tps = 18450.123456 (without initial connection time)

Mientras corre la prueba, cuenta las conexiones reales desde otra terminal:

sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity WHERE datname = 'appdb';"

El resultado no supera 25 (default_pool_size más reserve_pool_size), aunque haya 200 clientes conectados a PgBouncer. Sin max_prepared_statements la prueba con -M prepared fallaría con errores del tipo prepared statement "P_1" does not exist.

Paso 7: Activar TLS y aceptar conexiones remotas

Si las aplicaciones se conectan desde otros servidores, cifra el tráfico entre ellas y PgBouncer. Genera un certificado autofirmado (en producción usa uno de tu CA interna o de Let's Encrypt), sustituyendo your_domain por el nombre con el que se conectarán los clientes:

sudo mkdir -p /etc/pgbouncer/tls
sudo openssl req -new -x509 -days 825 -nodes \
  -subj "/CN=your_domain" \
  -keyout /etc/pgbouncer/tls/server.key \
  -out /etc/pgbouncer/tls/server.crt
sudo chown -R postgres:postgres /etc/pgbouncer/tls
sudo chmod 600 /etc/pgbouncer/tls/server.key

Edita /etc/pgbouncer/pgbouncer.ini:

sudo nano /etc/pgbouncer/pgbouncer.ini

Cambia listen_addr y añade las opciones de TLS dentro de la sección [pgbouncer]:

listen_addr = 0.0.0.0
client_tls_sslmode = require
client_tls_cert_file = /etc/pgbouncer/tls/server.crt
client_tls_key_file = /etc/pgbouncer/tls/server.key

Con client_tls_sslmode = require, PgBouncer rechaza cualquier conexión sin cifrar. Si PostgreSQL está en otro servidor, añade también server_tls_sslmode = require para cifrar el tramo entre PgBouncer y PostgreSQL.

Reinicia el servicio y abre el puerto 6432 solo para el servidor de la aplicación, sustituyendo app_server_ip por su IP:

sudo systemctl restart pgbouncer
sudo ufw allow from app_server_ip to any port 6432 proto tcp

Desde el servidor de la aplicación, comprueba que la conexión usa TLS:

psql "host=your_server_ip port=6432 dbname=appdb user=appuser sslmode=require" -c "SELECT 1;"

Dentro de una sesión interactiva de psql, la línea SSL connection (protocol: TLSv1.3, ...) que aparece al conectar confirma el cifrado. Si intentas conectar con sslmode=disable, PgBouncer rechaza la conexión.

Solución de problemas

FATAL: SASL authentication failed o password authentication failed. Revisa el log de PgBouncer:

sudo tail -n 50 /var/log/postgresql/pgbouncer.log

Las causas más comunes son una contraseña de pgbouncer_auth en userlist.txt que no coincide con la de PostgreSQL, que falte la función pgbouncer.user_lookup en la base de datos a la que conecta el cliente, o un usuario creado con hash MD5. En este último caso vuelve a asignarle la contraseña con ALTER ROLE appuser PASSWORD '...' para que se guarde como SCRAM.

no more connections allowed (max_client_conn). Hay más clientes que max_client_conn. Súbelo y comprueba el límite de descriptores de fichero del servicio, porque cada cliente consume uno.

Clientes lentos y cl_waiting siempre mayor que cero. El pool es pequeño para la carga o hay transacciones largas que retienen conexiones. Busca transacciones abiertas en PostgreSQL con SELECT pid, state, xact_start, query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%'; antes de aumentar default_pool_size.

Errores con SET, LISTEN o advisory locks. Son funciones de sesión incompatibles con el modo transaction. Usa SET LOCAL dentro de la transacción o crea una segunda entrada en [databases] con pool_mode=session para los procesos que las necesiten.

Conclusión

Ahora PgBouncer atiende cientos de clientes con un número acotado de conexiones a PostgreSQL, autentica contra los hashes SCRAM del propio servidor y admite los prepared statements de los drivers en modo transaction. Como siguientes pasos, puedes exportar las métricas de SHOW POOLS y SHOW STATS a Prometheus, desplegar dos instancias de PgBouncer detrás de una IP flotante o un balanceador TCP para eliminar el punto único de fallo, y ajustar max_connections de PostgreSQL al tamaño real de los pools.