Domine Ferramentas e Melhores Práticas de DevOps - Hub de Conhecimento DevOps Domine Ferramentas e Melhores Práticas de DevOps - Hub de Conhecimento DevOps

Resolução de Erros Comuns de Conexão no PostgreSQL

Resolva erros de conexão no PostgreSQL verificando status do serviço, endereços de escuta, pg_hba.conf, credenciais, DNS, SSL e logs.

Resolução de Erros Comuns de Conexão no PostgreSQL

Os erros de conexão no PostgreSQL parecem semelhantes até que você diminua o ritmo e leia a mensagem exata. Connection refused geralmente significa que o cliente alcançou um host, mas nada aceitou a conexão TCP naquele endereço e porta. Connection timed out frequentemente significa que o pacote nunca obteve uma resposta útil. password authentication failed significa que o servidor foi alcançado e rejeitou as credenciais. no pg_hba.conf entry significa que o PostgreSQL não encontrou uma regra de acesso correspondente.

Essa sequência importa. Comece de fora para dentro: host, porta, serviço, ouvinte, firewall, pg_hba.conf, usuário, banco de dados, senha, SSL e, em seguida, pool da aplicação. Adivinhar senhas quando o serviço não está ouvindo é perda de tempo. Editar pg_hba.conf quando o DNS aponta para o host errado também é perda de tempo.

Compreendendo os Fundamentos da Conexão PostgreSQL

Antes de mergulhar em erros específicos, é crucial entender como o PostgreSQL lida com conexões. O PostgreSQL opera em um modelo cliente-servidor. Um cliente (por exemplo, ferramenta de linha de comando psql, uma aplicação web ou um cliente desktop) tenta se conectar a um processo servidor PostgreSQL. Esse processo normalmente escuta conexões recebidas em uma interface de rede e porta específicas (padrão 5432).

Dois arquivos de configuração principais governam como as conexões são aceitas e autenticadas:

  • postgresql.conf: Controla o comportamento geral do servidor, incluindo quais interfaces de rede escutar (listen_addresses) e a porta (port).
  • pg_hba.conf: (Autenticação Baseada em Host) Dita quem pode se conectar de onde a qual banco de dados, usando qual método de autenticação. Este arquivo é crítico para segurança e controle de acesso.

Compreender os papéis desses arquivos e a interação cliente-servidor é fundamental para uma resolução de problemas eficaz.

Erros Comuns de Conexão e Soluções

Vamos detalhar os erros de conexão mais frequentes que você pode encontrar e suas respectivas soluções.

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

Este erro significa que o nome do banco de dados especificado pelo cliente não existe no servidor PostgreSQL.

Explicação: A aplicação cliente ou comando psql está tentando se conectar a um banco de dados que não foi criado ou cujo nome está escrito incorretamente.

Solução:

  1. Verifique o nome do banco de dados: Certifique-se de que o nome do banco de dados em sua string de conexão ou comando psql está correto.
  2. Liste os bancos de dados existentes: Conecte-se a um banco de dados padrão (como postgres ou template1) e liste todos os bancos de dados disponíveis usando \l (ou \list).
# Tente conectar ao banco de dados padrão 'postgres'
psql -U seu_usuario -h seu_host -d postgres

# Uma vez conectado, liste todos os bancos de dados
\l

# Exemplo de criação de um banco de dados ausente
CREATE DATABASE my_app_db;

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

Isso indica que o nome de usuário (função) especificado para a conexão não existe.

Explicação: Semelhante ao erro de banco de dados, a conta de usuário tentando se conectar é inexistente ou está escrita incorretamente.

Solução:

  1. Verifique o nome de usuário: Confira o nome de usuário em sua string de conexão.
  2. Liste as funções existentes: Conecte-se com uma conta de superusuário (por exemplo, usuário postgres) e liste todas as funções usando \du.
# Conecte-se como superusuário 'postgres' padrão
psql -U postgres -h seu_host -d postgres

# Liste todas as funções (usuários)
\du

# Exemplo de criação de uma função ausente
CREATE ROLE my_app_user WITH LOGIN PASSWORD 'minha_senha_forte';

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

Este é um erro comum, indicando que uma senha incorreta foi fornecida para o usuário especificado.

Explicação: A senha fornecida pelo cliente não corresponde à armazenada para o usuário (função) do PostgreSQL.

Solução:

  1. Verifique a configuração da aplicação: Revise a string de conexão ou variáveis de ambiente da sua aplicação para garantir que a senha está correta.

  2. Redefina a senha (se você tiver acesso de superusuário):

    # Conecte-se como superusuário postgres
psql -U postgres -h seu_host -d postgres

# Altere a senha para o usuário problemático
ALTER USER my_app_user WITH PASSWORD 'nova_senha_forte';

    Dica: Certifique-se de que a entrada pg_hba.conf para o usuário especifica um método de autenticação baseado em senha (por exemplo, md5, scram-sha-256) em vez de trust ou ident se você pretende usar senhas.

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

Este erro é um problema de configuração do pg_hba.conf, significando que o servidor negou explicitamente a conexão com base em suas regras de acesso.

Explicação: O arquivo pg_hba.conf não contém uma regra que corresponda aos parâmetros da conexão recebida (IP do cliente, usuário, banco de dados e método de autenticação).

Solução:

  1. Localize pg_hba.conf: A localização varia por sistema operacional e método de instalação (por exemplo, /etc/postgresql/14/main/pg_hba.conf no Debian/Ubuntu, ou especificado por SHOW hba_file; no psql).

  2. Edite pg_hba.conf: Adicione ou modifique uma entrada para permitir a conexão. Uma entrada comum para permitir conexões de qualquer lugar com autenticação por senha se parece com isso:

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    my_app_db       my_app_user     203.0.113.25/32         scram-sha-256
    • TYPE: host para conexões TCP/IP.
    • DATABASE: all (ou um nome de banco de dados específico).
    • USER: all (ou um nome de usuário específico).
    • ADDRESS: O intervalo de endereços IP do cliente (por exemplo, 192.168.1.0/24, 127.0.0.1/32 para local, ou um único IP de cliente público).
    • METHOD: O método de autenticação. Prefira scram-sha-256 para autenticação por senha em implantações modernas do PostgreSQL quando os clientes suportarem.

    Aviso: Evite host all all 0.0.0.0/0 ... a menos que haja um controle de rede muito deliberado na frente dele. Um banco de dados, função e CIDR estreitos tornam os erros mais fáceis de identificar.

  3. Recarregue o PostgreSQL: Após editar pg_hba.conf, você deve recarregar a configuração do PostgreSQL para que as alterações tenham efeito.

    # Em sistemas baseados em systemd

sudo systemctl reload postgresql

# Ou usando pg_ctl (requer especificar o diretório de dados)
# pg_ctl reload -D /var/lib/postgresql/14/main
```

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

Este é um erro genérico indicando que o cliente não conseguiu estabelecer uma conexão com o servidor PostgreSQL. O servidor rejeitou ativamente a tentativa de conexão, muitas vezes porque nada está ouvindo no IP/porta de destino.

Explicação: Isso normalmente aponta para um dos seguintes:

  • O serviço PostgreSQL não está em execução.
  • O PostgreSQL não está ouvindo na interface de rede ou porta esperada.
  • Um firewall está bloqueando a conexão.

Soluções:

  1. O PostgreSQL está em execução?

    • Verifique o status do serviço:
      sudo systemctl status postgresql
# Ou, para sistemas mais antigos/outras configurações:
# sudo service postgresql status
      Se não estiver em execução, inicie-o:
      sudo systemctl start postgresql
    • Verifique os logs: Revise os logs do PostgreSQL (por exemplo, /var/log/postgresql/) em busca de erros de inicialização.

  2. Está ouvindo no endereço/porta corretos?

    • Verifique postgresql.conf: Certifique-se de que listen_addresses está configurado corretamente. Para conexões de outros hosts, deve ser * ou o endereço IP específico da interface de rede do servidor, não apenas localhost (127.0.0.1).
      # Em postgresql.conf
listen_addresses = '*'    # Ouvir em todas as interfaces de rede disponíveis
port = 5432               # Porta padrão
      Após alterar listen_addresses, você deve reiniciar o PostgreSQL (um recarregamento não é suficiente).
      sudo systemctl restart postgresql
    • Verifique a porta de escuta: Use netstat ou ss para verificar se o PostgreSQL está realmente ouvindo na porta 5432 (ou na porta configurada).
      sudo ss -ltnp | grep 5432
# Exemplo de saída esperada:
# tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      12345/postgres
      Se você não vir 0.0.0.0:5432 ou ip_do_seu_servidor:5432, o PostgreSQL provavelmente está ouvindo apenas em 127.0.0.1:5432 ou não está ouvindo.

  3. Um firewall está bloqueando a conexão?

    • Firewall do lado do servidor: Verifique ufw (Ubuntu/Debian), firewalld (CentOS/RHEL) ou iptables para garantir que a porta 5432 esteja aberta para conexões recebidas do endereço IP do cliente.
      # Exemplo para UFW
sudo ufw allow 5432/tcp
sudo ufw enable
sudo ufw status

# Exemplo para firewalld
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-ports
    • Firewall do lado do cliente: Menos comum, mas certifique-se de que o firewall do cliente não está bloqueando conexões de saída para o servidor na porta 5432.

Erro 6: timeout expired ou connection timed out

Este erro sugere que o cliente tentou se conectar, mas não recebeu uma resposta do servidor dentro de um prazo especificado.

Explicação: Ao contrário de Connection refused, que é uma rejeição ativa, um timeout implica que a tentativa de conexão nunca alcançou o servidor ou o servidor não respondeu. Isso geralmente aponta para problemas de conectividade de rede ou um servidor severamente sobrecarregado.

Soluções:

  1. Conectividade de Rede:
    • Ping no servidor: ping ip_do_servidor. Se o ping falhar, há um problema fundamental de rede (cabo, roteador, servidor offline).
    • Traceroute/MTR: traceroute ip_do_servidor (Linux/macOS) ou tracert ip_do_servidor (Windows) pode ajudar a identificar onde a conexão está falhando ao longo do caminho de rede.
  2. listen_addresses e Firewalls do Servidor: Revisite as soluções para o Erro 5, pois listen_addresses mal configurados ou firewalls também podem causar timeouts se o servidor não estiver acessível.
  3. Carga do Servidor: Se o servidor estiver sob carga extrema (CPU alta, pouca memória, E/S de disco excessiva), ele pode estar muito ocupado para aceitar novas conexões prontamente, levando a timeouts. Verifique a utilização de recursos do sistema.

Erro 7: SSL Required, SSL Disabled, ou Certificate Verification Failed

O PostgreSQL pode aceitar conexões criptografadas e não criptografadas, dependendo das configurações do servidor e das regras do pg_hba.conf. Um cliente pode falhar com mensagens sobre SSL estar desligado, SSL ser obrigatório ou falha na verificação do certificado.

Verifique três lugares:

# Veja se o servidor tem SSL habilitado
psql -U postgres -d postgres -c "SHOW ssl;"

# Tente uma conexão criptografada do cliente
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user sslmode=require"

# Se a validação do certificado for esperada, use verify-full e um certificado raiz confiável
psql "host=db.example.com dbname=my_app_db user=my_app_user sslmode=verify-full sslrootcert=/caminho/para/root.crt"

sslmode=require criptografa a conexão, mas não verifica a identidade do servidor da mesma forma que verify-full. Para desenvolvimento interno, isso pode ser suficiente. Para tráfego de produção em redes não confiáveis, use verificação de certificado e certifique-se de que o nome do host na string de conexão corresponda ao certificado.

Verifique também se pg_hba.conf usa hostssl ou hostnossl. Uma regra hostssl não corresponderá a uma conexão não SSL, e uma regra hostnossl não corresponderá a uma conexão SSL.

Erro 8: Too Many Clients Already

Se o PostgreSQL retornar FATAL: sorry, too many clients already, o caminho de conexão está funcionando. O servidor está recusando novas sessões porque max_connections foi atingido, ou porque apenas os slots reservados para superusuário restam.

Primeiro, veja o que está conectado:

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

Em seguida, procure por padrões. Centenas de sessões idle geralmente apontam para um pool de aplicação configurado muito grande, um vazamento de processo de trabalho, ou várias réplicas de aplicação cada uma abrindo seu próprio pool. Aumentar max_connections pode ganhar tempo, mas também aumenta a pressão sobre a memória porque cada backend tem sobrecarga e pode usar work_mem. Na maioria das aplicações web, o PgBouncer no modo de pooling de transações é uma correção de longo prazo melhor do que deixar cada processo de aplicação manter muitas sessões diretas do PostgreSQL.

Um Fluxo de Triagem Rápido

Quando alguém diz "o banco de dados está fora", eu uso um caminho curto antes de tocar na configuração:

# 1. O nome está resolvendo para o host que espero?
getent hosts db.example.com

# 2. A porta TCP está acessível a partir deste cliente?
nc -vz db.example.com 5432

# 3. O psql consegue conectar com o mesmo host, porta, usuário e banco de dados?
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user connect_timeout=5"

# 4. O que o PostgreSQL registra para a tentativa falha?
sudo tail -n 100 /var/log/postgresql/postgresql-*.log

Se nc falhar, fique no território de rede e ouvinte. Se nc for bem-sucedido, mas psql falhar com FATAL, o PostgreSQL está acessível e a resposta geralmente está na autenticação, nome do banco de dados, nome da função, modo SSL ou pg_hba.conf.

Etapas Gerais de Resolução de Problemas

Ao enfrentar um problema persistente de conexão, siga estas etapas gerais para um diagnóstico sistemático:

  1. Verifique os Logs do PostgreSQL: Os arquivos de log são seus melhores amigos. Eles contêm informações detalhadas sobre problemas de inicialização, erros e tentativas de conexão negadas. A localização geralmente é especificada por log_directory no postgresql.conf (por exemplo, /var/log/postgresql/ no Debian/Ubuntu, ou pg_log dentro do diretório de dados).

    # Exemplo de verificação de logs recentes
sudo tail -f /var/log/postgresql/postgresql-14-main.log

  2. Verifique os Arquivos de Configuração: Verifique novamente postgresql.conf e pg_hba.conf em busca de erros de sintaxe, erros de digitação ou valores incorretos. Mesmo um único caractere mal colocado pode impedir o servidor de iniciar ou aceitar conexões.

  3. Reinicie o PostgreSQL (como último recurso para alterações de configuração): Embora reload seja frequentemente suficiente para pg_hba.conf e alguns parâmetros do postgresql.conf, certas alterações críticas (como listen_addresses) exigem uma reinicialização completa.

    sudo systemctl restart postgresql

  4. Teste Localmente no Servidor: Se a conexão de uma máquina remota falhar, tente conectar diretamente no próprio servidor. Isso ajuda a determinar se o problema é do lado do servidor ou relacionado à rede.

    # Conecte usando um socket de domínio Unix (se disponível)
psql -U seu_usuario -d seu_banco_de_dados

# Ou conecte via TCP/IP para localhost
psql -U seu_usuario -h 127.0.0.1 -p 5432 -d seu_banco_de_dados

    Se a conexão local funcionar, mas a remota não, o problema provavelmente é listen_addresses, pg_hba.conf ou um firewall.

  5. Verifique a Configuração do Cliente: Certifique-se de que a string de conexão da sua aplicação (por exemplo, variáveis de ambiente PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, ou uma string de conexão libpq) está configurada corretamente para corresponder às configurações do servidor.

Dicas e Melhores Práticas

  • Princípio do Menor Privilégio: Evite usar o superusuário postgres para conexões rotineiras de aplicação. Crie funções específicas com apenas os privilégios necessários.
  • Senhas Fortes: Sempre use senhas fortes e únicas para suas funções de banco de dados.
  • Restrinja pg_hba.conf: Em vez de 0.0.0.0/0, especifique endereços IP de clientes exatos ou intervalos CIDR estreitos no pg_hba.conf para aumentar a segurança.
  • Monitore os Logs Regularmente: Estabeleça uma rotina para revisar os logs do PostgreSQL. Muitos problemas podem ser detectados precocemente observando as entradas de log.
  • Documente Sua Configuração: Mantenha registros claros das configurações do seu postgresql.conf e pg_hba.conf, especialmente para ambientes de produção.

A correção mais rápida geralmente vem de combinar o erro com a camada que o produziu. Ferramentas de rede informam se o host e a porta estão acessíveis. Os logs do PostgreSQL informam por que o servidor rejeitou uma sessão. O pg_hba.conf informa se a conexão é permitida. A configuração da aplicação informa se os mesmos valores estão realmente sendo usados em produção.