Instalación y Configuración de Pg

Qué configuramos y por qué

Instalaremos y configuraremos PgBouncer, un servidor proxy ligero que se sitúa entre sus aplicaciones cliente y el servidor PostgreSQL. Su tarea principal es el pooling (agrupación) de conexiones a la base de datos. En una arquitectura típica, cada aplicación cliente (por ejemplo, un servidor web) abre su propia conexión a PostgreSQL. Con un gran número de clientes o conexiones/desconexiones frecuentes, esto genera una carga significativa en el servidor de la base de datos, ya que crear y cerrar cada conexión es una operación que consume muchos recursos. PostgreSQL asigna memoria y tiempo de CPU a cada conexión activa, lo que puede agotar rápidamente los recursos y ralentizar el rendimiento.

Al final, el lector obtendrá un sistema de base de datos optimizado y más estable. PgBouncer mantendrá un número fijo de conexiones "calientes" con PostgreSQL, y las aplicaciones cliente se conectarán a PgBouncer. Esto reduce los gastos generales de creación de conexiones, disminuye el consumo de memoria en el servidor PostgreSQL y permite que la base de datos procese más solicitudes con menores latencias. Su aplicación funcionará más rápido y el servidor PostgreSQL será más resistente a las cargas máximas.

Existen alternativas, como las bases de datos gestionadas en la nube (por ejemplo, Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL), que a menudo incluyen soluciones integradas para el pooling de conexiones u ofrecen escalado de recursos bajo demanda. Sin embargo, elegir una solución self-hosted en un VPS tiene sus ventajas. Primero, ofrece control total sobre la configuración y optimización, lo cual es crítico para cargas de trabajo específicas. Segundo, a menudo es significativamente más económico a largo plazo, especialmente con una carga estable o predecible. Finalmente, una solución self-hosted brinda flexibilidad para la integración con otros servicios en su VPS y no lo vincula a un proveedor de nube específico, lo cual es importante para minimizar el vendor lock-in.

Qué configuración de VPS se necesita para esta tarea

La elección de la configuración del VPS para PgBouncer y PostgreSQL depende de la carga esperada, el número de conexiones simultáneas y el volumen de datos. PgBouncer en sí mismo es muy ligero y consume una cantidad mínima de recursos, pero actúa como proxy para PostgreSQL, que puede ser bastante exigente.

Requisitos mínimos para proyectos pequeños y medianos (hasta 50-100 conexiones simultáneas a PgBouncer)

• CPU: 2 vCPU. PostgreSQL utiliza activamente el procesador, especialmente con consultas complejas.
• RAM: 4 GB. PostgreSQL puede consumir mucha RAM para el almacenamiento en caché de datos y búferes. PgBouncer, por su parte, requiere muy poca memoria, decenas de megabytes.
• Disco: 80 GB NVMe SSD. Para una base de datos, la velocidad del subsistema de disco es extremadamente importante. NVMe proporciona un rendimiento significativamente mayor en comparación con los SSD o HDD normales. El volumen depende del tamaño de su base de datos y de las tasas de crecimiento.
• Red: 100 Mbps o 1 Gbps. Para la mayoría de las tareas, 100 Mbps son suficientes, pero para aplicaciones de alta carga con un gran volumen de datos transferidos entre la aplicación y la base de datos, es mejor 1 Gbps.

Plan de VPS recomendado para la mayoría de las tareas

Para proyectos más serios, donde se esperan hasta 200-300 conexiones simultáneas a través de PgBouncer y un trabajo activo con la base de datos, considere las siguientes características:

• CPU: 4 vCPU
• RAM: 8-16 GB
• Disco: 160-320 GB NVMe SSD
• Red: 1 Gbps

Por ejemplo, se puede adquirir un VPS con las características indicadas, que proporcionará suficiente rendimiento y estabilidad para la mayoría de las aplicaciones y servicios web.

Cuándo se necesita un servidor dedicado y no un VPS

Un servidor dedicado se vuelve necesario cuando:

• Se requiere máximo rendimiento y aislamiento: Para bases de datos muy grandes, sistemas OLTP de alta carga o plataformas analíticas, donde cada milisegundo de respuesta es crítico y no desea compartir recursos con otros usuarios.
• Altos requisitos de E/S: Cuando el subsistema de disco del VPS no puede manejar el volumen de operaciones de entrada/salida, especialmente al usar arreglos RAID en un servidor dedicado.
• Requisitos especiales de seguridad y cumplimiento: Algunas regulaciones pueden exigir el uso de hardware físicamente aislado.
• Escalabilidad: Si los recursos del plan de VPS más grande ya no son suficientes y se requiere una mayor escalabilidad horizontal o vertical, un servidor dedicado ofrece más posibilidades.

Ubicación: en qué influye

La elección de la ubicación del VPS tiene un impacto directo en la latencia entre su aplicación y la base de datos. Idealmente, su VPS con PgBouncer y PostgreSQL debería estar en el mismo centro de datos o región geográfica que los servidores de sus aplicaciones cliente. Esto minimiza el ping y garantiza la mejor velocidad de intercambio de datos.

• Proximidad a los usuarios: Si sus usuarios principales se encuentran en una región específica, ubicar el servidor allí mejorará su experiencia.
• Proximidad a las aplicaciones: Si su servidor web está en una región y la base de datos en otra, esto agregará latencia a cada consulta a la BD.
• Legislación: Algunos países tienen leyes estrictas sobre el almacenamiento de datos, lo que puede requerir que los datos se ubiquen en una jurisdicción específica.

Preparación del servidor

Después de aprovisionar un nuevo VPS, es necesario realizar una serie de configuraciones básicas para garantizar la seguridad y la facilidad de gestión. Utilizaremos Ubuntu Server 24.04 LTS, ya que es una versión actual y estable para 2026.

1. Actualización del sistema

Primero, actualicemos la lista de paquetes y los paquetes instalados a las últimas versiones.

sudo apt update && sudo apt upgrade -y # Actualizamos la lista de paquetes e instalamos las actualizaciones

2. Creación de un nuevo usuario con permisos sudo

Trabajar como usuario root no es seguro. Crearemos un nuevo usuario y le otorgaremos permisos sudo.

sudo adduser username # Creamos un nuevo usuario (reemplace 'username' por el nombre deseado)
sudo usermod -aG sudo username # Añadimos el usuario al grupo 'sudo'

Ahora, salga de la sesión root e inicie sesión con el nuevo usuario.

3. Configuración de la autenticación por claves SSH

Este es un método de autenticación más seguro que la contraseña. Si aún no tiene un par de claves SSH, genérelas en su máquina local (ssh-keygen).

mkdir -p ~/.ssh # Creamos el directorio para las claves, si no existe
chmod 700 ~/.ssh # Establecemos los permisos correctos para el directorio
nano ~/.ssh/authorized_keys # Abrimos el archivo para añadir la clave pública

Pegue su clave SSH pública (que comienza con ssh-rsa AAAA... o ssh-ed25519 AAAA...) en este archivo y guárdelo (Ctrl+X, Y, Enter). Luego, establezca los permisos correctos:

chmod 600 ~/.ssh/authorized_keys # Establecemos los permisos correctos para el archivo de claves

Después de verificar el inicio de sesión con clave, desactive el inicio de sesión con contraseña para root y todos los usuarios en /etc/ssh/sshd_config:

sudo nano /etc/ssh/sshd_config # Abrimos la configuración del servidor SSH

Encuentre y modifique (o añada) las siguientes líneas:

PermitRootLogin no
PasswordAuthentication no
ChallengeResponseAuthentication no
UsePAM no

Guarde los cambios y reinicie el servicio SSH:

sudo systemctl restart sshd # Reiniciamos el servidor SSH para aplicar los cambios

4. Instalación y configuración de Fail2Ban

Fail2Ban escanea los registros y bloquea temporalmente las direcciones IP que muestran signos de ataques maliciosos (por ejemplo, múltiples intentos fallidos de inicio de sesión SSH).

sudo apt install fail2ban -y # Instalamos Fail2Ban (versión 0.12.0+ para Ubuntu 24.04)
sudo cp /etc/fail2ban/jail.conf /etc/fail2ban/jail.local # Copiamos la configuración para cambios locales
sudo nano /etc/fail2ban/jail.local # Editamos la configuración local

En el archivo jail.local, asegúrese de que la sección [sshd] esté habilitada (enabled = true) y configure los parámetros según desee (bantime, findtime, maxretry). Por ejemplo:

[sshd]
enabled = true
port = ssh
logpath = %(sshd_log)s
backend = systemd
bantime = 1h # Tiempo de bloqueo de IP (1 hora)
findtime = 10m # Tiempo durante el cual se cuentan los intentos (10 minutos)
maxretry = 3 # Número máximo de intentos antes del bloqueo

sudo systemctl enable fail2ban # Habilitamos el inicio automático de Fail2Ban al arrancar el sistema
sudo systemctl start fail2ban # Iniciamos el servicio Fail2Ban

5. Configuración del firewall (UFW)

UFW (Uncomplicated Firewall) es una interfaz sencilla para configurar reglas de iptables. Por defecto, permitiremos solo SSH, HTTP(S) y los puertos para PostgreSQL/PgBouncer.

sudo apt install ufw -y # Instalamos UFW
sudo ufw allow ssh # Permitimos SSH (puerto 22)
sudo ufw allow http # Permitimos HTTP (puerto 80)
sudo ufw allow https # Permitimos HTTPS (puerto 443)
sudo ufw allow 5432/tcp # Permitimos el puerto estándar de PostgreSQL
sudo ufw allow 6432/tcp # Permitimos el puerto estándar de PgBouncer (lo usaremos)
sudo ufw enable # Habilitamos el firewall (confirme 'y')
sudo ufw status # Verificamos el estado del firewall

Instalación de Software — Paso a Paso

Ahora que el servidor está preparado, procederemos con la instalación de PostgreSQL y PgBouncer. Utilizaremos las versiones actuales disponibles en los repositorios de Ubuntu 24.04 LTS o de fuentes oficiales.

1. Instalación de PostgreSQL

Para el año 2026, es probable que PostgreSQL 16.x o 17.x esté disponible en Ubuntu 24.04 LTS a través de los repositorios estándar, pero para una máxima actualidad y el uso de nuevas funciones, nos centraremos en PostgreSQL 18.0+. Para ello, añadiremos el repositorio oficial de PostgreSQL.

sudo apt install curl gnupg2 -y # Instalamos utilidades para trabajar con claves y repositorios
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg # Añadimos la clave GPG de PostgreSQL
echo "deb http://apt.postgresql.org/pub/repos/apt/ noble-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list > /dev/null # Añadimos el repositorio de PostgreSQL para Ubuntu 24.04 (Noble Numbat)
sudo apt update # Actualizamos la lista de paquetes después de añadir el repositorio
sudo apt install postgresql-18 -y # Instalamos PostgreSQL 18 (versión actual para 2026)

Verificamos el estado de PostgreSQL:

sudo systemctl status postgresql # Verificamos que PostgreSQL esté en ejecución

Por defecto, PostgreSQL crea el usuario postgres y la base de datos postgres. Para mayor seguridad y comodidad, crearemos un usuario y una base de datos separados para su aplicación.

sudo -i -u postgres # Cambiamos al usuario postgres
psql # Iniciamos la consola psql

En la consola psql:

CREATE USER myappuser WITH PASSWORD 'your_strong_password'; -- Creamos un usuario para la aplicación
CREATE DATABASE myappdb OWNER myappuser; -- Creamos una base de datos para la aplicación, cuyo propietario es myappuser
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myappuser; -- Otorgamos todos los privilegios sobre la base de datos
\q # Salimos de psql
exit # Salimos del usuario postgres

2. Instalación de PgBouncer

PgBouncer también está disponible a través de los repositorios estándar de Ubuntu. Para el año 2026, se espera la versión 1.23.0 o superior.

sudo apt install pgbouncer -y # Instalamos PgBouncer

Verificamos que PgBouncer esté instalado y su versión:

pgbouncer --version # Verificamos la versión de PgBouncer (se espera 1.23.0+)

Por defecto, PgBouncer no está iniciado o está configurado con parámetros mínimos. Lo configuraremos en la siguiente sección.

3. Configuración de PostgreSQL para trabajar con PgBouncer

PgBouncer se conectará a PostgreSQL como un cliente normal. Es necesario asegurarse de que PostgreSQL permite conexiones desde el host local. Editamos el archivo pg_hba.conf.

sudo nano /etc/postgresql/18/main/pg_hba.conf # Abrimos el archivo de configuración de acceso al host

Añada la siguiente línea al final del archivo para permitir que PgBouncer se conecte desde el host local:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

También, asegúrese de que PostgreSQL escucha en la interfaz local. Editamos postgresql.conf:

sudo nano /etc/postgresql/18/main/postgresql.conf # Abrimos el archivo de configuración principal de PostgreSQL

Busque la línea listen_addresses y asegúrese de que incluye localhost:

listen_addresses = 'localhost' # PostgreSQL escuchará solo en la interfaz local

Guarde los cambios y reinicie PostgreSQL:

sudo systemctl restart postgresql # Reiniciamos PostgreSQL para aplicar los cambios

Configuración

Ahora pasaremos a la configuración detallada de PgBouncer. El archivo de configuración principal se encuentra en /etc/pgbouncer/pgbouncer.ini.

1. Archivo de configuración principal de PgBouncer (pgbouncer.ini)

Abramos el archivo para editarlo:

sudo nano /etc/pgbouncer/pgbouncer.ini # Abrimos el archivo de configuración de PgBouncer

Aquí hay un ejemplo de configuración con comentarios importantes:

[databases]
# Nombre de la base de datos, tal como la verán los clientes de PgBouncer
# Formato:  = host= port= dbname= user=
# En nuestro caso, PgBouncer se conectará a PostgreSQL local
myappdb = host=127.0.0.1 port=5432 dbname=myappdb user=pgbouncer_admin

[pgbouncer]
listen_addr = 0.0.0.0 # PgBouncer escuchará en todas las interfaces de red
listen_port = 6432 # Puerto en el que PgBouncer aceptará conexiones de clientes

auth_type = md5 # Tipo de autenticación para clientes que se conectan a PgBouncer (md5, plain, hba, cert, trust, any)
auth_file = /etc/pgbouncer/userlist.txt # Archivo con la lista de usuarios y sus contraseñas para PgBouncer

admin_users = pgbouncer_admin # Usuarios que pueden conectarse a la pseudo-BD "pgbouncer" para administración
stats_users = pgbouncer_admin # Usuarios que pueden ver estadísticas

pool_mode = session # Modo de pooling: session, transaction, statement
# session: la conexión se devuelve al pool solo después de que el cliente se desconecta. El más simple, pero el menos eficiente.
# transaction: la conexión se devuelve al pool después de cada transacción. Recomendado para la mayoría de las aplicaciones web.
# statement: la conexión se devuelve al pool después de cada consulta (statement). El más agresivo, pero puede romper la lógica de las aplicaciones que usan variables de sesión.

default_pool_size = 20 # Número de conexiones que PgBouncer mantendrá con cada BD por defecto
min_pool_size = 5 # Número mínimo de conexiones que PgBouncer mantendrá abiertas
max_client_conn = 1000 # Número máximo de clientes que pueden conectarse a PgBouncer
max_db_connections = 0 # Número máximo de conexiones de PgBouncer a una BD (0 = sin límite)
max_user_connections = 0 # Número máximo de conexiones para un solo usuario (0 = sin límite)

# Tiempos de espera
server_lifetime = 3600 # La conexión con el servidor PostgreSQL se recreará cada 3600 segundos (1 hora)
server_idle_timeout = 600 # Cerrar conexiones inactivas con PostgreSQL después de 600 segundos
client_idle_timeout = 300 # Cerrar conexiones inactivas de clientes de PgBouncer después de 300 segundos

# Registro
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

# Configuración TLS/SSL para conexiones entre PgBouncer y PostgreSQL
# Si su PostgreSQL está configurado para usar TLS, PgBouncer debe usarlo
server_tls_sslmode = prefer # Modo SSL para PgBouncer -> PostgreSQL (disable, allow, prefer, require, verify-ca, verify-full)
server_tls_ca_file = /etc/ssl/certs/ca-certificates.crt # Certificado CA para verificar el servidor PostgreSQL
server_tls_key_file = # Si PgBouncer necesita un certificado de cliente para PostgreSQL
server_tls_cert_file = # Si PgBouncer necesita un certificado de cliente para PostgreSQL

# Configuración TLS/SSL para conexiones entre clientes y PgBouncer
# Los clientes pueden conectarse a PgBouncer a través de TLS
client_tls_sslmode = disable # Modo SSL para Cliente -> PgBouncer (disable, allow, prefer, require, verify-ca, verify-full)
client_tls_key_file = /etc/ssl/private/pgbouncer-key.pem # Clave privada para TLS
client_tls_cert_file = /etc/ssl/certs/pgbouncer-cert.pem # Certificado para TLS

Guarde los cambios.

2. Archivo de usuarios de PgBouncer (userlist.txt)

PgBouncer requiere un archivo separado con la lista de usuarios y sus contraseñas para la autenticación de clientes. También crearemos el usuario pgbouncer_admin, que será utilizado por PgBouncer para conectarse a PostgreSQL y para la administración del propio PgBouncer.

sudo nano /etc/pgbouncer/userlist.txt # Abrimos el archivo para editarlo

Añada la siguiente línea, reemplazando your_pgbouncer_admin_password por una contraseña segura. Para la contraseña del cliente myappuser, utilice la misma contraseña que al crear el usuario en PostgreSQL.

"pgbouncer_admin" "your_pgbouncer_admin_password"
"myappuser" "your_strong_password"

Importante: Las contraseñas se almacenan aquí en texto plano. Asegúrese de que los permisos del archivo userlist.txt sean muy estrictos.

sudo chmod 600 /etc/pgbouncer/userlist.txt # Establecemos permisos estrictos

Crearemos el usuario pgbouncer_admin en PostgreSQL, que PgBouncer utilizará para conectarse a la base de datos.

sudo -i -u postgres # Cambiamos al usuario postgres
psql # Iniciamos la consola psql

En la consola psql:

CREATE USER pgbouncer_admin WITH PASSWORD 'your_pgbouncer_admin_password'; -- Creamos el usuario de PgBouncer
GRANT CONNECT ON DATABASE myappdb TO pgbouncer_admin; -- Otorgamos permisos de conexión a la base de datos
\q # Salimos de psql
exit # Salimos del usuario postgres

3. Configuración de TLS/SSL para PgBouncer

Para una conexión segura entre el cliente y PgBouncer, así como entre PgBouncer y PostgreSQL, se recomienda usar TLS. Si utiliza Certbot para su servidor web, puede reutilizar sus certificados o generar certificados autofirmados para necesidades internas. Para acceso público, use Certbot/Caddy.

Supongamos que desea usar Certbot para obtener certificados. Si ya tiene un dominio configurado en su VPS y utiliza Caddy o Nginx, puede obtener un certificado para su dominio (por ejemplo, pg.yourdomain.com).

sudo apt install certbot -y # Instalamos Certbot
sudo certbot certonly --standalone -d pg.yourdomain.com # Obtenemos un certificado para el dominio (reemplace con el suyo)

Después de obtener los certificados, Certbot los colocará en /etc/letsencrypt/live/pg.yourdomain.com/. Cópielos en una ubicación accesible para PgBouncer y establezca los permisos correctos:

sudo mkdir -p /etc/ssl/pgbouncer # Creamos el directorio para los certificados de PgBouncer
sudo cp /etc/letsencrypt/live/pg.yourdomain.com/fullchain.pem /etc/ssl/pgbouncer/pgbouncer-cert.pem
sudo cp /etc/letsencrypt/live/pg.yourdomain.com/privkey.pem /etc/ssl/pgbouncer/pgbouncer-key.pem
sudo chmod 600 /etc/ssl/pgbouncer/pgbouncer-key.pem # La clave privada debe ser accesible solo para PgBouncer
sudo chown pgbouncer:pgbouncer /etc/ssl/pgbouncer/* # Cambiamos el propietario de los archivos al usuario pgbouncer

Actualice pgbouncer.ini, especificando las rutas correctas a los certificados:

client_tls_sslmode = require # Requerir TLS de los clientes
client_tls_key_file = /etc/ssl/pgbouncer/pgbouncer-key.pem
client_tls_cert_file = /etc/ssl/pgbouncer/pgbouncer-cert.pem

Si desea que PgBouncer también utilice TLS para conectarse a PostgreSQL (lo cual se recomienda), asegúrese de que su PostgreSQL esté configurado para TLS y especifique server_tls_sslmode = require, así como server_tls_ca_file. Si PostgreSQL se encuentra en el mismo servidor, puede usar los certificados CA del sistema. Para PostgreSQL 18+, TLS está habilitado por defecto con un certificado autofirmado, pero para producción es mejor usar certificados firmados por una CA.

4. Inicio y verificación de PgBouncer

Después de todas las configuraciones, reinicie PgBouncer.

sudo systemctl restart pgbouncer # Reiniciamos PgBouncer
sudo systemctl enable pgbouncer # Habilitamos el inicio automático de PgBouncer
sudo systemctl status pgbouncer # Verificamos el estado de PgBouncer

Asegúrese de que PgBouncer esté en ejecución y no contenga errores en los registros (sudo journalctl -u pgbouncer).

5. Verificación de la operatividad

Ahora intentaremos conectarnos a la base de datos a través de PgBouncer desde el servidor local.

psql -h 127.0.0.1 -p 6432 -U myappuser -d myappdb # Nos conectamos a PgBouncer

Se le pedirá que introduzca la contraseña para myappuser. Después de una conexión exitosa, se encontrará en la consola psql, pero ya a través de PgBouncer. Ejecute una consulta simple:

SELECT 1;
\conninfo # Mostrará que está conectado a PgBouncer, no directamente a PostgreSQL
\q

Para verificar las estadísticas de PgBouncer, conéctese a la pseudo-base de datos especial pgbouncer con el usuario pgbouncer_admin:

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer # Nos conectamos a la interfaz de administración de PgBouncer

En la consola psql:

SHOW STATS; # Mostrar estadísticas generales de pooling
SHOW POOLS; # Mostrar estado de los pools de conexiones
SHOW CLIENTS; # Mostrar clientes activos
SHOW SERVERS; # Mostrar conexiones activas con PostgreSQL
\q

Estos comandos le ayudarán a monitorear el funcionamiento de PgBouncer.

Copias de seguridad y mantenimiento

Una estrategia de copia de seguridad fiable y un mantenimiento regular son cruciales para cualquier sistema de producción con una base de datos.

1. Qué respaldar

• Bases de datos PostgreSQL: Este es el componente más importante. Es necesario crear volcados completos de las bases de datos regularmente.
• Archivos de configuración: /etc/postgresql/18/main/postgresql.conf, /etc/postgresql/18/main/pg_hba.conf, /etc/pgbouncer/pgbouncer.ini, /etc/pgbouncer/userlist.txt, así como cualquier script o archivo de Certbot relacionado con TLS.
• Datos de la aplicación: Si su aplicación almacena archivos de usuario u otros datos en el disco del VPS, también deben ser respaldados.

2. Script simple de copia de seguridad automática de PostgreSQL

Crearemos un script simple que creará un volcado de todas las bases de datos PostgreSQL y lo guardará de forma cifrada. Para el cifrado y el almacenamiento eficiente, se pueden usar borgbackup o restic, pero por simplicidad, por ahora nos limitaremos a pg_dumpall y la compresión.

sudo mkdir -p /opt/backups # Creamos el directorio para las copias de seguridad
sudo nano /opt/backups/backup_pg.sh # Creamos el script de copia de seguridad

Contenido de backup_pg.sh:

#!/bin/bash

# Ruta para guardar las copias de seguridad
BACKUP_DIR="/opt/backups"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/postgresql_all_databases_$DATE.sql.gz"
CONFIG_BACKUP_DIR="$BACKUP_DIR/configs"

# Creamos los directorios si no existen
mkdir -p "$BACKUP_DIR"
mkdir -p "$CONFIG_BACKUP_DIR"

echo "Iniciando la creación de la copia de seguridad de PostgreSQL..."

# Creamos un volcado de todas las bases de datos PostgreSQL
sudo -u postgres pg_dumpall | gzip > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    echo "Copia de seguridad de la base de datos creada con éxito: $BACKUP_FILE"
else
    echo "¡Error al crear la copia de seguridad de la base de datos!"
    exit 1
fi

echo "Iniciando la copia de seguridad de los archivos de configuración..."

# Copiamos los archivos de configuración
cp /etc/postgresql/18/main/postgresql.conf "$CONFIG_BACKUP_DIR/postgresql.conf_$DATE"
cp /etc/postgresql/18/main/pg_hba.conf "$CONFIG_BACKUP_DIR/pg_hba.conf_$DATE"
cp /etc/pgbouncer/pgbouncer.ini "$CONFIG_BACKUP_DIR/pgbouncer.ini_$DATE"
cp /etc/pgbouncer/userlist.txt "$CONFIG_BACKUP_DIR/userlist.txt_$DATE"

echo "Copia de seguridad de los archivos de configuración completada."

# Eliminamos las copias de seguridad antiguas (por ejemplo, más de 7 días)
find "$BACKUP_DIR" -name "postgresql_all_databases_.sql.gz" -mtime +7 -delete
find "$CONFIG_BACKUP_DIR" -name "_$DATE" -mtime +7 -delete

echo "Copias de seguridad antiguas eliminadas."
echo "Copia de seguridad completada."

sudo chmod +x /opt/backups/backup_pg.sh # Hacemos el script ejecutable

sudo crontab -e # Abrimos crontab para root (o sudo crontab -e -u username)

0 3    /opt/backups/backup_pg.sh > /var/log/backup_pg.log 2>&1 # Copia de seguridad diaria a las 3 AM

3. Dónde almacenar las copias de seguridad

Nunca almacene las copias de seguridad en el mismo servidor que los datos originales. Si el servidor falla, perderá tanto los datos como las copias de seguridad.

• Almacenamiento de objetos externo compatible con S3: La opción más recomendada. Barato, fiable, escalable. Utilice herramientas como s3cmd, rclone o awscli para la sincronización automática de las copias de seguridad con S3.
• VPS separado: Puede alquilar un VPS pequeño específicamente para almacenar copias de seguridad y usar rsync o scp para transferirlas a través de SSH.
• Almacenamiento en red (NFS/SMB): Si tiene su propia infraestructura.

Ejemplo de envío a S3 con rclone (después de su configuración):

# Añada a su script backup_pg.sh después de crear las copias de seguridad
echo "Enviando copias de seguridad a S3..."
rclone sync "$BACKUP_DIR" "my-s3-remote:my-backup-bucket/pgbouncer-vps/" # Reemplace 'my-s3-remote' y 'my-backup-bucket'
echo "Envío a S3 completado."

4. Actualizaciones: rolling vs ventana de mantenimiento

• Actualizaciones de SO y PgBouncer: Para actualizaciones de seguridad menores, se pueden usar actualizaciones continuas (rolling updates), pero siempre con una ejecución de prueba. Para versiones mayores, es mejor planificar una ventana de mantenimiento. PgBouncer se puede reiniciar sin detener PostgreSQL, pero todas las conexiones de cliente activas se desconectarán.
• Actualizaciones de PostgreSQL: Las actualizaciones mayores de PostgreSQL (por ejemplo, de 17 a 18) requieren migración de datos y siempre deben realizarse en una ventana de mantenimiento previamente planificada, con una copia de seguridad completa y un plan de reversión. Las actualizaciones menores (por ejemplo, de 18.1 a 18.2) suelen ser seguras y se pueden aplicar con un tiempo de inactividad mínimo.
• Monitorización: Después de cualquier actualización, es fundamental monitorizar los logs y las métricas de rendimiento.

Resolución de problemas + Preguntas frecuentes

En esta sección, abordaremos problemas típicos que pueden surgir al trabajar con PgBouncer y PostgreSQL, y responderemos a preguntas frecuentes.

1. Error "Connection refused" al conectar a PgBouncer

Qué verificar: Asegúrese de que PgBouncer esté en ejecución y escuchando en la dirección IP y el puerto correctos. Verifique el estado del servicio pgbouncer (sudo systemctl status pgbouncer) y los logs (sudo journalctl -u pgbouncer). También asegúrese de que el firewall (UFW) permita las conexiones entrantes al puerto de PgBouncer (por defecto 6432) – sudo ufw status.

Cómo solucionarlo: Si PgBouncer no está en ejecución, inícielo (sudo systemctl start pgbouncer). Si el puerto está bloqueado, añada una regla UFW (sudo ufw allow 6432/tcp). Verifique listen_addr y listen_port en /etc/pgbouncer/pgbouncer.ini.

2. Error "Authentication failed" al conectar el cliente a PgBouncer

Qué verificar: Asegúrese de que el usuario y la contraseña que el cliente utiliza para conectar a PgBouncer estén correctamente especificados en el archivo /etc/pgbouncer/userlist.txt. Verifique el uso de mayúsculas/minúsculas y los errores tipográficos. Asegúrese de que auth_type en pgbouncer.ini coincida con lo esperado (por ejemplo, md5).

Cómo solucionarlo: Corrija la contraseña en userlist.txt o actualice la contraseña del cliente. Reinicie PgBouncer (sudo systemctl restart pgbouncer) después de modificar userlist.txt.

3. PgBouncer no puede conectar a PostgreSQL (error "server connection failed")

Qué verificar: Asegúrese de que PostgreSQL esté en ejecución y escuchando en la dirección IP y el puerto especificados en la sección [databases] del archivo pgbouncer.ini (por defecto 127.0.0.1:5432). Verifique el estado de postgresql (sudo systemctl status postgresql). Asegúrese de que el usuario que PgBouncer utiliza para conectar a PostgreSQL (por ejemplo, pgbouncer_admin) exista en PostgreSQL y tenga la contraseña correcta, y que pg_hba.conf permita a este usuario conectar desde 127.0.0.1.

Cómo solucionarlo: Verifique listen_addresses en /etc/postgresql/18/main/postgresql.conf y las reglas en /etc/postgresql/18/main/pg_hba.conf. Asegúrese de que el usuario de PgBouncer (por ejemplo, pgbouncer_admin) esté creado en PostgreSQL con la misma contraseña que en userlist.txt. Reinicie PostgreSQL después de los cambios en sus configuraciones.

4. Rendimiento lento de la aplicación después de implementar PgBouncer

Qué verificar: Verifique el modo de pooling (pool_mode) en pgbouncer.ini. El modo statement puede causar problemas si su aplicación se basa en variables de sesión o tablas temporales. Asegúrese de que default_pool_size y max_client_conn sean lo suficientemente grandes para su carga.

Cómo solucionarlo: Intente cambiar pool_mode a transaction, que es adecuado para la mayoría de las aplicaciones web. Aumente default_pool_size para que PgBouncer admita más conexiones activas con PostgreSQL. Analice los logs de PgBouncer y PostgreSQL en busca de errores o advertencias relacionadas con bloqueos o falta de conexiones.

5. ¿Qué configuración mínima de VPS es adecuada?

La configuración mínima de VPS para PgBouncer y PostgreSQL para proyectos pequeños (hasta 50 conexiones simultáneas) debe incluir 2 vCPU, 4 GB de RAM y 80 GB de SSD NVMe. Esto será suficiente para el funcionamiento básico y las pruebas, así como para pequeñas aplicaciones web. Para tareas más serias, se recomienda aumentar los recursos.

6. ¿Qué elegir: VPS o dedicado para esta tarea?

Para la mayoría de los proyectos medianos y startups, un VPS será la opción óptima, ofreciendo un buen equilibrio entre costo y rendimiento. Un servidor dedicado se recomienda para sistemas con cargas muy altas, aplicaciones críticas con requisitos estrictos de rendimiento de E/S, o cuando se requiere aislamiento total de recursos y máximo control sobre el hardware. Si recién está comenzando, un VPS es una opción más flexible y económica.

7. ¿Cómo monitorizar PgBouncer?

Puede usar una pseudo-base de datos especial pgbouncer para obtener estadísticas. Conéctese a ella como psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer, y luego use los comandos SHOW STATS;, SHOW POOLS;, SHOW CLIENTS;, SHOW SERVERS;. Para una monitorización más avanzada, puede integrar PgBouncer con Prometheus y Grafana, utilizando el exportador de PgBouncer.

8. ¿Cómo actualizar PgBouncer sin tiempo de inactividad?

Para actualizar PgBouncer, puede usar el comando RELOAD a través de la interfaz de administración (psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer, luego RELOAD;). Esto recargará la configuración sin interrumpir las conexiones existentes. Para actualizaciones mayores o actualizaciones que requieran el reinicio del servicio, puede usar el comando PAUSE, luego RESTART, lo que permitirá que las transacciones existentes finalicen antes de que PgBouncer se reinicie y acepte nuevas conexiones.

Conclusiones y próximos pasos

¡Felicidades! Ha instalado y configurado PgBouncer con éxito en su VPS, mejorando significativamente la gestión de conexiones con PostgreSQL. Ahora su aplicación funcionará de manera más estable, consumirá menos recursos de la base de datos y será más resistente a las cargas máximas. Ha obtenido control total sobre el pooling de conexiones, lo cual es un elemento clave para sistemas escalables y de alto rendimiento.

Próximos pasos para una mayor optimización y desarrollo:

1. Monitorización del rendimiento: Implemente un sistema de monitorización integral (por ejemplo, Prometheus + Grafana) para rastrear las métricas de PgBouncer (número de conexiones, latencias) y PostgreSQL (carga de CPU/RAM, operaciones de disco, consultas lentas). Esto ayudará a identificar cuellos de botella y ajustar la configuración.
2. Optimización de consultas: Analice las consultas lentas en PostgreSQL (usando pg_stat_statements) y optimícelas añadiendo índices, reescribiendo consultas o desnormalizando datos. Incluso con PgBouncer, las consultas no optimizadas pueden convertirse en un cuello de botella.
3. Escalado de PostgreSQL: A medida que la carga aumente, considere las opciones de escalado horizontal de PostgreSQL, como la replicación (para lectura), el sharding o el uso de replicación lógica para tareas analíticas. PgBouncer se puede configurar para trabajar con múltiples réplicas.