Cada conexión a PostgreSQL crea un proceso nuevo en el servidor, con su coste de memoria y de arranque. Cuando una aplicación abre cientos de conexiones cortas, el servidor pasa más tiempo gestionándolas que ejecutando consultas. PgBouncer es un pooler ligero que se sitúa entre la aplicación y PostgreSQL y reparte muchas conexiones de clientes sobre un número pequeño de conexiones reales. En este tutorial instalarás PgBouncer en Ubuntu 24.04, lo configurarás en modo transacción con autenticación SCRAM y comprobarás con pgbench cómo reutiliza las conexiones.

Requisitos previos

  • Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath, con PostgreSQL 16 instalado y en marcha (sudo apt install postgresql).
  • Un usuario no root con privilegios sudo.
  • PostgreSQL usando scram-sha-256 para cifrar contraseñas, que es el valor por defecto en PostgreSQL 14 y posteriores.

En esta guía PgBouncer se instala en el mismo servidor que PostgreSQL. Si lo pones en otra máquina, solo cambia la dirección host de la sección [databases].

Paso 1: Crear la base de datos y los usuarios

Crea un usuario para la aplicación y una base de datos de la que sea propietario:

sudo -u postgres psql -c "CREATE ROLE appuser WITH LOGIN PASSWORD 'your_strong_password';"
sudo -u postgres createdb -O appuser appdb

Crea también un rol para administrar PgBouncer. No necesita acceso a ninguna base de datos real, pero así PostgreSQL genera su hash SCRAM, que reutilizarás en el paso 3:

sudo -u postgres psql -c "CREATE ROLE pgb_admin WITH LOGIN PASSWORD 'your_admin_password';"

Sustituye your_strong_password y your_admin_password por contraseñas propias. Comprueba que la aplicación puede conectarse directamente a PostgreSQL por TCP:

psql -h 127.0.0.1 -U appuser -d appdb -c "SELECT current_user;"

 current_user
--------------
 appuser
(1 row)

Paso 2: Instalar PgBouncer

PgBouncer está en los repositorios de Ubuntu:

sudo apt update
sudo apt install pgbouncer

Comprueba la versión instalada:

pgbouncer --version

PgBouncer 1.22.0
...

El paquete crea el servicio pgbouncer, que se ejecuta con el usuario postgres, y deja la configuración en /etc/pgbouncer/.

Paso 3: Crear el archivo de usuarios

PgBouncer autentica a los clientes con el archivo userlist.txt. Si guardas en él el hash SCRAM de cada usuario, tal y como está en PostgreSQL, PgBouncer puede validar al cliente y también iniciar sesión en PostgreSQL en su nombre sin guardar contraseñas en texto plano. Extrae los hashes:

sudo -u postgres psql -Atc "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname IN ('appuser', 'pgb_admin');"

"appuser" "SCRAM-SHA-256$4096:Xb3n...$Qm8...:Zp1..."
"pgb_admin" "SCRAM-SHA-256$4096:k9Td...$Wc2...:Hr7..."

Crea el archivo con esas dos líneas:

sudo nano /etc/pgbouncer/userlist.txt

"appuser" "SCRAM-SHA-256$4096:Xb3n...$Qm8...:Zp1..."
"pgb_admin" "SCRAM-SHA-256$4096:k9Td...$Wc2...:Hr7..."

Solo el usuario postgres, que ejecuta PgBouncer, debe poder leerlo:

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

Paso 4: Configurar PgBouncer

Haz una copia del archivo original, que está lleno de comentarios útiles como referencia:

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

Abre el archivo y reemplaza 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

auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = pgb_admin

pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3

server_idle_timeout = 600
max_prepared_statements = 100
ignore_startup_parameters = extra_float_digits

Qué hace cada bloque:

  • [databases]: nombre que verán los clientes (appdb) y a qué base real apunta.
  • listen_addr y listen_port: PgBouncer escucha en el puerto 6432 solo en localhost. Si tus aplicaciones están en otros servidores, pon aquí la IP privada y abre el puerto en el firewall solo para esa red.
  • pool_mode = transaction: la conexión al servidor se asigna a un cliente solo durante una transacción y después vuelve al pool. Es el modo que más conexiones ahorra.
  • max_client_conn: máximo de clientes conectados a PgBouncer. default_pool_size: conexiones reales a PostgreSQL por cada par usuario/base de datos.
  • reserve_pool_size y reserve_pool_timeout: hasta 5 conexiones extra si un cliente lleva más de 3 segundos esperando.
  • max_prepared_statements: permite que los drivers que usan sentencias preparadas a nivel de protocolo funcionen en modo transacción.
  • ignore_startup_parameters = extra_float_digits: algunos drivers, como el JDBC de PostgreSQL, envían este parámetro al conectar y PgBouncer lo rechazaría.

Reinicia el servicio para aplicar la configuración:

sudo systemctl restart pgbouncer
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 ...

Si no arranca, sudo journalctl -u pgbouncer -n 30 muestra la línea del archivo que falla.

Paso 5: Conectarse a través de PgBouncer

Conecta a la base de datos usando el puerto 6432 en lugar del 5432:

psql -h 127.0.0.1 -p 6432 -U appuser -d appdb -c "SELECT current_user, inet_server_port();"

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

inet_server_port() devuelve 5432 porque la consulta la ejecuta PostgreSQL a través de una conexión del pool. En tu aplicación solo tienes que cambiar el puerto de la cadena de conexión, por ejemplo postgresql://appuser:[email protected]:6432/appdb.

Paso 6: Consultar la consola de administración

PgBouncer expone una base de datos virtual llamada pgbouncer con comandos de administración. Conéctate con el usuario de admin_users:

psql -h 127.0.0.1 -p 6432 -U pgb_admin pgbouncer

Muestra el estado de los pools:

SHOW POOLS;

Las columnas más útiles son cl_active (clientes con una conexión asignada), cl_waiting (clientes esperando una conexión libre), sv_active y sv_idle (conexiones reales a PostgreSQL en uso y libres) y maxwait (segundos que lleva esperando el cliente más antiguo). Otros comandos útiles:

SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;
RELOAD;

RELOAD vuelve a leer pgbouncer.ini y userlist.txt sin cortar conexiones. Sal con \q.

Paso 7: Probar el pooling con pgbench

pgbench, incluido con PostgreSQL, permite comprobar el efecto del pooler. Inicializa sus tablas de prueba en appdb a través de PgBouncer:

pgbench -h 127.0.0.1 -p 6432 -U appuser -i appdb

Lanza una prueba de solo lectura con 100 clientes simultáneos durante 30 segundos:

pgbench -h 127.0.0.1 -p 6432 -U appuser -c 100 -j 4 -T 30 -S appdb

Mientras corre, en otra terminal ejecuta SHOW POOLS; en la consola de administración. Verás 100 clientes (cl_active más cl_waiting) servidos por un máximo de 20 a 25 conexiones reales (sv_active más sv_idle). Confírmalo desde PostgreSQL:

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

 count
-------
    25
(1 row)

Al terminar, pgbench muestra el resultado:

number of clients: 100
number of threads: 4
...
tps = 18432.118305 (without initial connection time)

Cuándo no usar el modo transacción

En modo transacción, dos transacciones consecutivas del mismo cliente pueden ir por conexiones distintas. Todo lo que dependa del estado de la sesión deja de funcionar de forma fiable:

  • SET fuera de una transacción (usa SET LOCAL dentro de ella).
  • LISTEN/NOTIFY y bloqueos consultivos a nivel de sesión (pg_advisory_lock).
  • Tablas temporales que deban sobrevivir entre transacciones.
  • WITH HOLD en cursores.

Si tu aplicación usa estas funciones, crea una entrada aparte para ella con pool_mode=session en la sección [databases]:

appdb_session = host=127.0.0.1 port=5432 dbname=appdb pool_mode=session

Las migraciones de esquema y los trabajos que usan bloqueos consultivos suelen conectarse directamente al puerto 5432.

Solución de problemas

  • password authentication failed solo a través de PgBouncer: el hash de userlist.txt no coincide con el de pg_authid (por ejemplo, tras cambiar la contraseña). Vuelve a copiarlo y ejecuta RELOAD.
  • no such database: appdb: el nombre que usa el cliente no está en [databases].
  • unsupported startup parameter: añade el parámetro indicado a ignore_startup_parameters.
  • prepared statement "..." does not exist: el driver usa sentencias preparadas con nombre; comprueba que max_prepared_statements es mayor que 0 o usa el modo sesión para esa aplicación.
  • cl_waiting crece y sube la latencia: el pool se queda corto. Aumenta default_pool_size sin superar el max_connections de PostgreSQL menos las conexiones que usen otros servicios.

Conclusión

Has instalado PgBouncer en Ubuntu 24.04 con pooling en modo transacción, autenticación SCRAM sin contraseñas en texto plano y una consola de administración para vigilar los pools. Como siguientes pasos, puedes reducir max_connections en PostgreSQL para liberar memoria, exportar las estadísticas de SHOW STATS a tu sistema de monitorización y activar TLS entre clientes y PgBouncer con client_tls_sslmode si las aplicaciones se conectan desde otras máquinas.