Domina herramientas y mejores prácticas de DevOps - DevOps Knowledge Hub Domina herramientas y mejores prácticas de DevOps - DevOps Knowledge Hub

Solución de Errores Comunes de Conexión en PostgreSQL

Soluciona errores de conexión en PostgreSQL verificando el estado del servicio, direcciones de escucha, pg_hba.conf, credenciales, DNS, SSL y registros.

Solución de Errores Comunes de Conexión en PostgreSQL

Los errores de conexión en PostgreSQL se parecen hasta que te detienes a leer el mensaje exacto. Connection refused normalmente significa que el cliente alcanzó un host, pero nada aceptó la conexión TCP en esa dirección y puerto. Connection timed out a menudo significa que el paquete nunca obtuvo una respuesta útil. password authentication failed significa que se alcanzó el servidor y se rechazaron las credenciales. no pg_hba.conf entry significa que PostgreSQL no encontró una regla de acceso coincidente.

Esa secuencia importa. Empieza desde afuera hacia adentro: host, puerto, servicio, listener, cortafuegos, pg_hba.conf, usuario, base de datos, contraseña, SSL, luego pooling de la aplicación. Adivinar contraseñas cuando el servicio no está escuchando pierde tiempo. Editar pg_hba.conf cuando el DNS apunta al host equivocado también pierde tiempo.

Comprendiendo los Fundamentos de Conexión en PostgreSQL

Antes de profundizar en errores específicos, es crucial entender cómo PostgreSQL maneja las conexiones. PostgreSQL opera en un modelo cliente-servidor. Un cliente (por ejemplo, la herramienta de línea de comandos psql, una aplicación web o un cliente de escritorio) intenta conectarse a un proceso servidor de PostgreSQL. Este proceso normalmente escucha conexiones entrantes en una interfaz de red y puerto específicos (por defecto 5432).

Dos archivos de configuración principales gobiernan cómo se aceptan y autentican las conexiones:

  • postgresql.conf: Controla el comportamiento general del servidor, incluyendo qué interfaces de red escuchar (listen_addresses) y el puerto (port).
  • pg_hba.conf: (Autenticación Basada en Host) Dicta quién puede conectarse desde dónde a qué base de datos, usando qué método de autenticación. Este archivo es crítico para la seguridad y el control de acceso.

Comprender los roles de estos archivos y la interacción cliente-servidor es fundamental para una solución de problemas efectiva.

Errores Comunes de Conexión y Soluciones

Desglosemos los errores de conexión más frecuentes que podrías encontrar y sus respectivas soluciones.

Error 1: FATAL: database "..." does not exist

Este error significa que la base de datos especificada por el cliente no existe en el servidor PostgreSQL.

Explicación: La aplicación cliente o el comando psql está intentando conectarse a una base de datos que no ha sido creada o cuyo nombre está mal escrito.

Solución:

  1. Verifica el nombre de la base de datos: Asegúrate de que el nombre de la base de datos en tu cadena de conexión o comando psql sea correcto.
  2. Lista las bases de datos existentes: Conéctate a una base de datos por defecto (como postgres o template1) y lista todas las bases de datos disponibles usando \l (o \list).
# Intenta conectarte a la base de datos por defecto 'postgres'
psql -U tu_usuario -h tu_host -d postgres

# Una vez conectado, lista todas las bases de datos
\l

# Ejemplo de creación de una base de datos faltante
CREATE DATABASE mi_app_db;

Error 2: FATAL: role "..." does not exist

Esto indica que el nombre de usuario (rol) especificado para la conexión no existe.

Explicación: Similar al error de base de datos, la cuenta de usuario que intenta conectarse no existe o está mal escrita.

Solución:

  1. Verifica el nombre de usuario: Revisa el nombre de usuario en tu cadena de conexión.
  2. Lista los roles existentes: Conéctate con una cuenta de superusuario (por ejemplo, usuario postgres) y lista todos los roles usando \du.
# Conéctate como el superusuario por defecto 'postgres'
psql -U postgres -h tu_host -d postgres

# Lista todos los roles (usuarios)
\du

# Ejemplo de creación de un rol faltante
CREATE ROLE mi_usuario_app WITH LOGIN PASSWORD 'mi_contraseña_fuerte';

Error 3: FATAL: password authentication failed for user "..."

Este es un error común que indica que se proporcionó una contraseña incorrecta para el usuario especificado.

Explicación: La contraseña suministrada por el cliente no coincide con la almacenada para el usuario (rol) de PostgreSQL.

Solución:

  1. Revisa la configuración de la aplicación: Verifica la cadena de conexión o las variables de entorno de tu aplicación para asegurarte de que la contraseña sea correcta.

  2. Restablece la contraseña (si tienes acceso de superusuario):

    # Conéctate como superusuario postgres
psql -U postgres -h tu_host -d postgres

# Cambia la contraseña para el usuario problemático
ALTER USER mi_usuario_app WITH PASSWORD 'nueva_contraseña_fuerte';

    Consejo: Asegúrate de que la entrada pg_hba.conf para el usuario especifique un método de autenticación basado en contraseña (por ejemplo, md5, scram-sha-256) en lugar de trust o ident si pretendes usar contraseñas.

Error 4: FATAL: no pg_hba.conf entry for host "...", user "...", database "...", SSL off/on

Este error es un problema de configuración de pg_hba.conf, lo que significa que el servidor denegó explícitamente la conexión basándose en sus reglas de acceso.

Explicación: El archivo pg_hba.conf no contiene una regla que coincida con los parámetros de la conexión entrante (IP del cliente, usuario, base de datos y método de autenticación).

Solución:

  1. Localiza pg_hba.conf: La ubicación varía según el sistema operativo y el método de instalación (por ejemplo, /etc/postgresql/14/main/pg_hba.conf en Debian/Ubuntu, o especificado por SHOW hba_file; en psql).

  2. Edita pg_hba.conf: Añade o modifica una entrada para permitir la conexión. Una entrada común para permitir conexiones desde cualquier lugar con autenticación de contraseña se ve así:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    mi_app_db       mi_usuario_app  203.0.113.25/32         scram-sha-256
    • TYPE: host para conexiones TCP/IP.
    • DATABASE: all (o un nombre de base de datos específico).
    • USER: all (o un nombre de usuario específico).
    • ADDRESS: El rango de direcciones IP del cliente (por ejemplo, 192.168.1.0/24, 127.0.0.1/32 para local, o una IP de cliente público única).
    • METHOD: El método de autenticación. Prefiere scram-sha-256 para autenticación por contraseña en implementaciones modernas de PostgreSQL cuando los clientes lo soporten.

    Advertencia: Evita host all all 0.0.0.0/0 ... a menos que haya un control de red muy deliberado delante. Una base de datos, rol y CIDR estrechos facilitan la detección de errores.

  3. Recarga PostgreSQL: Después de editar pg_hba.conf, debes recargar la configuración de PostgreSQL para que los cambios surtan efecto.

    # En sistemas basados en systemd

sudo systemctl reload postgresql

# O usando pg_ctl (requiere especificar el directorio de datos)
# pg_ctl reload -D /var/lib/postgresql/14/main
```

Error 5: could not connect to server: Connection refused (0x0000274D/10061)

Este es un error genérico que indica que el cliente no pudo establecer una conexión con el servidor PostgreSQL. El servidor rechazó activamente el intento de conexión, a menudo porque nada está escuchando en la IP/puerto de destino.

Explicación: Esto típicamente apunta a uno de los siguientes:

  • El servicio PostgreSQL no se está ejecutando.
  • PostgreSQL no está escuchando en la interfaz de red o puerto esperados.
  • Un cortafuegos está bloqueando la conexión.

Soluciones:

  1. ¿Está PostgreSQL en ejecución?

    • Verifica el estado del servicio:
      sudo systemctl status postgresql
# O, para sistemas antiguos/otras configuraciones:
# sudo service postgresql status
      Si no se está ejecutando, inícialo:
      sudo systemctl start postgresql
    • Revisa los registros: Revisa los registros de PostgreSQL (por ejemplo, /var/log/postgresql/) para errores de inicio.

  2. ¿Está escuchando en la dirección/puerto correctos?

    • Verifica postgresql.conf: Asegúrate de que listen_addresses esté configurado correctamente. Para conexiones desde otros hosts, debe ser * o la dirección IP específica de la interfaz de red del servidor, no solo localhost (127.0.0.1).
      # En postgresql.conf
listen_addresses = '*'    # Escucha en todas las interfaces de red disponibles
port = 5432               # Puerto por defecto
      Después de cambiar listen_addresses, debes reiniciar PostgreSQL (una recarga no es suficiente).
      sudo systemctl restart postgresql
    • Verifica el puerto de escucha: Usa netstat o ss para comprobar si PostgreSQL está realmente escuchando en el puerto 5432 (o tu puerto configurado).
      sudo ss -ltnp | grep 5432
# Ejemplo de salida esperada:
# tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      12345/postgres
      Si no ves 0.0.0.0:5432 o tu_ip_servidor:5432, PostgreSQL probablemente solo está escuchando en 127.0.0.1:5432 o no está escuchando en absoluto.

  3. ¿Está un cortafuegos bloqueando la conexión?

    • Cortafuegos del lado del servidor: Verifica ufw (Ubuntu/Debian), firewalld (CentOS/RHEL), o iptables para asegurarte de que el puerto 5432 esté abierto para conexiones entrantes desde la dirección IP del cliente.
      # Ejemplo para UFW
sudo ufw allow 5432/tcp
sudo ufw enable
sudo ufw status

# Ejemplo para firewalld
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-ports
    • Cortafuegos del lado del cliente: Menos común, pero asegúrate de que el cortafuegos del cliente no esté bloqueando conexiones salientes al servidor en el puerto 5432.

Error 6: timeout expired o connection timed out

Este error sugiere que el cliente intentó conectarse pero no recibió una respuesta del servidor dentro de un período de tiempo especificado.

Explicación: A diferencia de Connection refused, que es un rechazo activo, un timeout implica que el intento de conexión nunca llegó al servidor o el servidor no respondió. Esto a menudo apunta a problemas de conectividad de red o a un servidor severamente sobrecargado.

Soluciones:

  1. Conectividad de Red:
    • Haz ping al servidor: ping dirección_ip_servidor. Si el ping falla, hay un problema de red fundamental (cable, router, servidor fuera de línea).
    • Traceroute/MTR: traceroute dirección_ip_servidor (Linux/macOS) o tracert dirección_ip_servidor (Windows) puede ayudar a identificar dónde está fallando la conexión a lo largo de la ruta de red.
  2. listen_addresses del Servidor y Cortafuegos: Revisa las soluciones para el Error 5, ya que listen_addresses mal configurados o cortafuegos también pueden causar timeouts si el servidor no es accesible.
  3. Carga del Servidor: Si el servidor está bajo carga extrema (CPU alta, poca memoria, E/S de disco excesiva), podría estar demasiado ocupado para aceptar nuevas conexiones rápidamente, lo que lleva a timeouts. Verifica la utilización de recursos del sistema.

Error 7: SSL Required, SSL Disabled, o Certificate Verification Failed

PostgreSQL puede aceptar conexiones cifradas y no cifradas, dependiendo de la configuración del servidor y las reglas de pg_hba.conf. Un cliente puede fallar con mensajes sobre SSL desactivado, SSL requerido o fallo de verificación de certificado.

Verifica tres lugares:

# Ver si el servidor tiene SSL habilitado
psql -U postgres -d postgres -c "SHOW ssl;"

# Intentar una conexión cifrada desde el cliente
psql "host=db.example.com port=5432 dbname=mi_app_db user=mi_usuario_app sslmode=require"

# Si se espera validación de certificado, usa verify-full y un certificado raíz de confianza
psql "host=db.example.com dbname=mi_app_db user=mi_usuario_app sslmode=verify-full sslrootcert=/ruta/al/root.crt"

sslmode=require cifra la conexión pero no verifica la identidad del servidor de la misma manera que verify-full. Para desarrollo interno, eso puede ser suficiente. Para tráfico de producción a través de redes no confiables, usa verificación de certificado y asegúrate de que el nombre de host en la cadena de conexión coincida con el certificado.

También verifica si pg_hba.conf usa hostssl o hostnossl. Una regla hostssl no coincidirá con una conexión no SSL, y una regla hostnossl no coincidirá con una conexión SSL.

Error 8: Too Many Clients Already

Si PostgreSQL devuelve FATAL: sorry, too many clients already, la ruta de conexión está funcionando. El servidor está rechazando nuevas sesiones porque se ha alcanzado max_connections, o porque solo quedan espacios reservados para superusuarios.

Primero, mira qué está conectado:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

Luego busca patrones. Cientos de sesiones idle a menudo apuntan a un pool de aplicación configurado demasiado grande, una fuga de procesos de trabajo, o varias réplicas de aplicación abriendo su propio pool. Aumentar max_connections puede ganar tiempo, pero también aumenta la presión de memoria porque cada backend tiene sobrecarga y puede usar work_mem. En la mayoría de las aplicaciones web, PgBouncer en modo de pooling de transacciones es una mejor solución a largo plazo que permitir que cada proceso de aplicación mantenga muchas sesiones directas de PostgreSQL.

Un Flujo Rápido de Triage

Cuando alguien dice "la base de datos está caída", uso un camino corto antes de tocar la configuración:

# 1. ¿Se está resolviendo el nombre al host que espero?
getent hosts db.example.com

# 2. ¿Es accesible el puerto TCP desde este cliente?
nc -vz db.example.com 5432

# 3. ¿Puede psql conectarse con el mismo host, puerto, usuario y base de datos?
psql "host=db.example.com port=5432 dbname=mi_app_db user=mi_usuario_app connect_timeout=5"

# 4. ¿Qué registra PostgreSQL para el intento fallido?
sudo tail -n 100 /var/log/postgresql/postgresql-*.log

Si nc falla, quédate en el territorio de red y listener. Si nc tiene éxito pero psql falla con FATAL, PostgreSQL es accesible y la respuesta suele estar en la autenticación, nombre de base de datos, nombre de rol, modo SSL o pg_hba.conf.

Pasos Generales para Solucionar Problemas

Cuando te enfrentes a un problema de conexión persistente, sigue estos pasos generales para un diagnóstico sistemático:

  1. Revisa los Registros de PostgreSQL: Los archivos de registro son tu mejor amigo. Contienen información detallada sobre problemas de inicio, errores e intentos de conexión denegados. La ubicación suele estar especificada por log_directory en postgresql.conf (por ejemplo, /var/log/postgresql/ en Debian/Ubuntu, o pg_log dentro del directorio de datos).

    # Ejemplo de revisión de registros recientes
sudo tail -f /var/log/postgresql/postgresql-14-main.log

  2. Verifica los Archivos de Configuración: Vuelve a verificar postgresql.conf y pg_hba.conf en busca de errores de sintaxis, errores tipográficos o valores incorrectos. Incluso un solo carácter mal colocado puede impedir que el servidor se inicie o acepte conexiones.

  3. Reinicia PostgreSQL (como último recurso para cambios de configuración): Mientras que reload suele ser suficiente para pg_hba.conf y algunos parámetros de postgresql.conf, ciertos cambios críticos (como listen_addresses) requieren un reinicio completo.

    sudo systemctl restart postgresql

  4. Prueba Localmente en el Servidor: Si la conexión desde una máquina remota falla, intenta conectarte directamente en el propio servidor. Esto ayuda a determinar si el problema es del lado del servidor o de la red.

    # Conéctate usando un socket de dominio Unix (si está disponible)
psql -U tu_usuario -d tu_base_de_datos

# O conéctate vía TCP/IP a localhost
psql -U tu_usuario -h 127.0.0.1 -p 5432 -d tu_base_de_datos

    Si la conexión local funciona pero la remota no, el problema es probablemente listen_addresses, pg_hba.conf o un cortafuegos.

  5. Verifica la Configuración del Cliente: Asegúrate de que la cadena de conexión de tu aplicación (por ejemplo, variables de entorno PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, o una cadena de conexión libpq) esté configurada correctamente para coincidir con la configuración del servidor.

Consejos y Mejores Prácticas

  • Principio de Mínimo Privilegio: Evita usar el superusuario postgres para conexiones de aplicación rutinarias. Crea roles específicos con solo los privilegios necesarios.
  • Contraseñas Fuertes: Usa siempre contraseñas fuertes y únicas para tus roles de base de datos.
  • Restringe pg_hba.conf: En lugar de 0.0.0.0/0, especifica direcciones IP de cliente exactas o rangos CIDR estrechos en pg_hba.conf para mejorar la seguridad.
  • Monitorea los Registros Regularmente: Establece una rutina para revisar los registros de PostgreSQL. Muchos problemas pueden detectarse temprano observando las entradas del registro.
  • Documenta tu Configuración: Mantén registros claros de tus configuraciones de postgresql.conf y pg_hba.conf, especialmente para entornos de producción.

La solución más rápida generalmente proviene de hacer coincidir el error con la capa que lo produjo. Las herramientas de red te dicen si el host y el puerto son accesibles. Los registros de PostgreSQL te dicen por qué el servidor rechazó una sesión. pg_hba.conf te dice si la conexión está permitida. La configuración de la aplicación te dice si los mismos valores se están utilizando realmente en producción.