--- title: "Configurar PgBouncer para PostgreSQL en Ubuntu 24.04" description: "Configura PgBouncer en Ubuntu 24.04 en modo transaction, con autenticación SCRAM por auth_query, prepared statements, TLS y monitorización del pool." category: "Operaciones de Base de Datos" language: es source: https://cubepath.com/es/docs/operaciones-de-base-de-datos/configuracion-avanzada-de-pgbouncer-postgresql updated: 2026-09-25 --- # Configurar PgBouncer para PostgreSQL en Ubuntu 24.04 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: ```bash sudo apt update sudo apt install -y postgresql pgbouncer ``` Comprueba la versión de PgBouncer: ```bash pgbouncer --version ``` ```output 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: ```bash 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: ```sql 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: ```sql \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: ```bash psql "host=127.0.0.1 dbname=appdb user=pgbouncer_auth" -c "SELECT uname FROM pgbouncer.user_lookup('appuser');" ``` ```output 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: ```bash sudo nano /etc/pgbouncer/userlist.txt ``` Añade estas dos líneas, con las contraseñas entre comillas dobles: ```text "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: ```bash 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: | Modo | La conexión al servidor se libera | Uso típico | |---|---|---| | `session` | Cuando el cliente se desconecta | Aplicaciones que dependen del estado de la sesión | | `transaction` | Al terminar cada transacción | La mayoría de aplicaciones web y APIs | | `statement` | Tras cada sentencia | Solo 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: ```bash sudo cp /etc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini.orig ``` Abre el fichero y sustituye todo su contenido: ```bash sudo nano /etc/pgbouncer/pgbouncer.ini ``` ```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: ```bash sudo systemctl restart pgbouncer sudo systemctl status pgbouncer --no-pager ``` ```output ● 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: ```bash psql "host=127.0.0.1 port=6432 dbname=appdb user=appuser" -c "SELECT current_user, inet_server_port();" ``` ```output 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: ```bash psql "host=127.0.0.1 port=6432 dbname=pgbouncer user=pgbouncer_admin" ``` Muestra el estado de los pools: ```sql SHOW POOLS; ``` ```output 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: ```sql 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`: ```bash 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: ```bash 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`: ```bash pgbench -c 200 -j 4 -T 30 -S -M prepared -h 127.0.0.1 -p 6432 -U appuser appdb ``` ```output transaction type: 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: ```bash 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: ```bash 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`: ```bash sudo nano /etc/pgbouncer/pgbouncer.ini ``` Cambia `listen_addr` y añade las opciones de TLS dentro de la sección `[pgbouncer]`: ```ini 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: ```bash 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: ```bash 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: ```bash 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.