Fehlerbehebung bei häufigen PostgreSQL-Verbindungsfehlern

PostgreSQL-Verbindungsfehler sehen ähnlich aus, bis Sie langsamer vorgehen und die genaue Meldung lesen. Connection refused bedeutet normalerweise, dass der Client einen Host erreicht hat, aber nichts die TCP-Verbindung auf dieser Adresse und diesem Port akzeptiert hat. Connection timed out bedeutet oft, dass das Paket nie eine nützliche Antwort erhalten hat. password authentication failed bedeutet, dass der Server erreicht wurde und die Anmeldedaten abgelehnt hat. no pg_hba.conf entry bedeutet, dass PostgreSQL keine passende Zugriffsregel gefunden hat.

Diese Reihenfolge ist wichtig. Beginnen Sie von außen und arbeiten Sie sich nach innen vor: Host, Port, Dienst, Listener, Firewall, pg_hba.conf, Benutzer, Datenbank, Passwort, SSL, dann Anwendungspooling. Das Raten von Passwörtern, wenn der Dienst nicht lauscht, verschwendet Zeit. Das Bearbeiten der pg_hba.conf, wenn DNS auf den falschen Host verweist, verschwendet ebenfalls Zeit.

Grundlagen der PostgreSQL-Verbindung verstehen

Bevor wir uns mit spezifischen Fehlern befassen, ist es entscheidend zu verstehen, wie PostgreSQL Verbindungen handhabt. PostgreSQL arbeitet nach einem Client-Server-Modell. Ein Client (z. B. das psql-Befehlszeilentool, eine Webanwendung oder ein Desktop-Client) versucht, eine Verbindung zu einem PostgreSQL-Serverprozess herzustellen. Dieser Prozess lauscht normalerweise auf eingehende Verbindungen an einer bestimmten Netzwerkschnittstelle und einem bestimmten Port (standardmäßig 5432).

Zwei primäre Konfigurationsdateien bestimmen, wie Verbindungen akzeptiert und authentifiziert werden:

• postgresql.conf: Steuert das allgemeine Serververhalten, einschließlich der Netzwerkschnittstellen, auf denen gelauscht werden soll (listen_addresses) und des Ports (port).
• pg_hba.conf: (Host-basierte Authentifizierung) Legt fest, wer sich von wo aus mit welcher Datenbank unter Verwendung welcher Authentifizierungsmethode verbinden darf. Diese Datei ist entscheidend für Sicherheit und Zugriffskontrolle.

Das Verständnis der Rollen dieser Dateien und der Client-Server-Interaktion ist grundlegend für eine effektive Fehlerbehebung.

Häufige Verbindungsfehler und Lösungen

Lassen Sie uns die häufigsten Verbindungsfehler, auf die Sie stoßen könnten, und ihre jeweiligen Lösungen aufschlüsseln.

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

Dieser Fehler bedeutet, dass die vom Client angegebene Datenbank auf dem PostgreSQL-Server nicht existiert.

Erklärung: Die Clientanwendung oder der psql-Befehl versucht, eine Verbindung zu einer Datenbank herzustellen, die nicht erstellt wurde oder deren Name falsch geschrieben ist.

Lösung:

1. Überprüfen Sie den Datenbanknamen: Stellen Sie sicher, dass der Datenbankname in Ihrem Verbindungsstring oder psql-Befehl korrekt ist.
2. Listen Sie vorhandene Datenbanken auf: Stellen Sie eine Verbindung zu einer Standarddatenbank (wie postgres oder template1) her und listen Sie alle verfügbaren Datenbanken mit \l (oder \list) auf.

# Versuchen Sie, eine Verbindung zur Standarddatenbank 'postgres' herzustellen
psql -U your_username -h your_host -d postgres

# Sobald verbunden, listen Sie alle Datenbanken auf
\l

# Beispiel zum Erstellen einer fehlenden Datenbank
CREATE DATABASE my_app_db;

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

Dies weist darauf hin, dass der für die Verbindung angegebene Benutzername (die Rolle) nicht existiert.

Erklärung: Ähnlich wie beim Datenbankfehler ist das Benutzerkonto, das die Verbindung herzustellen versucht, entweder nicht vorhanden oder falsch geschrieben.

Lösung:

1. Überprüfen Sie den Benutzernamen: Überprüfen Sie den Benutzernamen in Ihrem Verbindungsstring.
2. Listen Sie vorhandene Rollen auf: Stellen Sie eine Verbindung mit einem Superuser-Konto (z. B. dem postgres-Benutzer) her und listen Sie alle Rollen mit \du auf.

# Stellen Sie eine Verbindung als Standard-Postgres-Superuser her
psql -U postgres -h your_host -d postgres

# Listen Sie alle Rollen (Benutzer) auf
\du

# Beispiel zum Erstellen einer fehlenden Rolle
CREATE ROLE my_app_user WITH LOGIN PASSWORD 'my_strong_password';

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

Dies ist ein häufiger Fehler, der auf ein falsches Passwort für den angegebenen Benutzer hinweist.

Erklärung: Das vom Client bereitgestellte Passwort stimmt nicht mit dem für den PostgreSQL-Benutzer (die Rolle) gespeicherten überein.

Lösung:

1. Überprüfen Sie die Anwendungskonfiguration: Überprüfen Sie den Verbindungsstring Ihrer Anwendung oder die Umgebungsvariablen, um sicherzustellen, dass das Passwort korrekt ist.

2. Setzen Sie das Passwort zurück (wenn Sie Superuser-Zugriff haben):

   # Stellen Sie eine Verbindung als Postgres-Superuser her
   psql -U postgres -h your_host -d postgres
   
   # Ändern Sie das Passwort für den problematischen Benutzer
   ALTER USER my_app_user WITH PASSWORD 'new_strong_password';
   

   Tipp: Stellen Sie sicher, dass der pg_hba.conf-Eintrag für den Benutzer eine passwortbasierte Authentifizierungsmethode (z. B. md5, scram-sha-256) angibt und nicht trust oder ident, wenn Sie Passwörter verwenden möchten.

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

Dieser Fehler ist ein Konfigurationsproblem der pg_hba.conf, was bedeutet, dass der Server die Verbindung basierend auf seinen Zugriffsregeln explizit abgelehnt hat.

Erklärung: Die Datei pg_hba.conf enthält keine Regel, die den Parametern der eingehenden Verbindung (Client-IP, Benutzer, Datenbank und Authentifizierungsmethode) entspricht.

Lösung:

1. Lokalisieren Sie pg_hba.conf: Der Speicherort variiert je nach Betriebssystem und Installationsmethode (z. B. /etc/postgresql/14/main/pg_hba.conf auf Debian/Ubuntu oder angegeben durch SHOW hba_file; in psql).

2. Bearbeiten Sie pg_hba.conf: Fügen Sie einen Eintrag hinzu oder ändern Sie einen vorhandenen, um die Verbindung zuzulassen. Ein üblicher Eintrag zum Zulassen von Verbindungen von überall mit Passwortauthentifizierung sieht so aus:

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   host    my_app_db       my_app_user     203.0.113.25/32         scram-sha-256
   

   • TYPE: host für TCP/IP-Verbindungen.
   • DATABASE: all (oder ein spezifischer Datenbankname).
   • USER: all (oder ein spezifischer Benutzername).
   • ADDRESS: Der IP-Adressbereich des Clients (z. B. 192.168.1.0/24, 127.0.0.1/32 für lokal oder eine einzelne öffentliche Client-IP).
   • METHOD: Die Authentifizierungsmethode. Bevorzugen Sie scram-sha-256 für die Passwortauthentifizierung bei modernen PostgreSQL-Bereitstellungen, wenn die Clients dies unterstützen.

   Warnung: Vermeiden Sie host all all 0.0.0.0/0 ..., es sei denn, es gibt eine sehr bewusste Netzwerkkontrolle davor. Eine enge Datenbank, Rolle und CIDR erleichtern das Erkennen von Fehlern.

3. Laden Sie PostgreSQL neu: Nachdem Sie pg_hba.conf bearbeitet haben, müssen Sie die PostgreSQL-Konfiguration neu laden, damit die Änderungen wirksam werden.

   # Auf systemd-basierten Systemen
   sudo systemctl reload postgresql
   
   # Oder mit pg_ctl (erfordert Angabe des Datenverzeichnisses)
   # pg_ctl reload -D /var/lib/postgresql/14/main
   

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

Dies ist ein allgemeiner Fehler, der darauf hinweist, dass der Client keine Verbindung zum PostgreSQL-Server herstellen konnte. Der Server hat den Verbindungsversuch aktiv abgelehnt, oft weil nichts auf der Ziel-IP/dem Ziel-Port lauscht.

Erklärung: Dies deutet typischerweise auf einen der folgenden Punkte hin:

• Der PostgreSQL-Dienst läuft nicht.
• PostgreSQL lauscht nicht auf der erwarteten Netzwerkschnittstelle oder dem erwarteten Port.
• Eine Firewall blockiert die Verbindung.

Lösungen:

1. Läuft PostgreSQL?

   • Überprüfen Sie den Dienststatus:

     sudo systemctl status postgresql
     # Oder für ältere Systeme/andere Setups:
     # sudo service postgresql status
     

     Wenn es nicht läuft, starten Sie es:

     sudo systemctl start postgresql
     

   • Überprüfen Sie die Protokolle: Überprüfen Sie die PostgreSQL-Protokolle (z. B. /var/log/postgresql/) auf Startfehler.

2. Lauscht es auf der richtigen Adresse/dem richtigen Port?

   • Überprüfen Sie postgresql.conf: Stellen Sie sicher, dass listen_addresses korrekt konfiguriert ist. Für Verbindungen von anderen Hosts sollte es * oder die spezifische IP-Adresse der Netzwerkschnittstelle des Servers sein, nicht nur localhost (127.0.0.1).

     # In postgresql.conf
     listen_addresses = '*'    # Auf allen verfügbaren Netzwerkschnittstellen lauschen
     port = 5432               # Standardport
     

     Nachdem Sie listen_addresses geändert haben, müssen Sie PostgreSQL neu starten (ein Neuladen reicht nicht aus).

     sudo systemctl restart postgresql
     

   • Überprüfen Sie den lauschenden Port: Verwenden Sie netstat oder ss, um zu überprüfen, ob PostgreSQL tatsächlich auf Port 5432 (oder Ihrem konfigurierten Port) lauscht.

     sudo ss -ltnp | grep 5432
     # Beispiel für erwartete Ausgabe:
     # tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      12345/postgres
     

     Wenn Sie 0.0.0.0:5432 oder your_server_ip:5432 nicht sehen, lauscht PostgreSQL wahrscheinlich nur auf 127.0.0.1:5432 oder gar nicht.

3. Blockiert eine Firewall die Verbindung?

   • Serverseitige Firewall: Überprüfen Sie ufw (Ubuntu/Debian), firewalld (CentOS/RHEL) oder iptables, um sicherzustellen, dass Port 5432 für eingehende Verbindungen von der IP-Adresse des Clients geöffnet ist.

     # Beispiel für UFW
     sudo ufw allow 5432/tcp
     sudo ufw enable
     sudo ufw status
     
     # Beispiel für firewalld
     sudo firewall-cmd --permanent --add-port=5432/tcp
     sudo firewall-cmd --reload
     sudo firewall-cmd --list-ports
     

   • Clientseitige Firewall: Weniger häufig, aber stellen Sie sicher, dass die Firewall des Clients keine ausgehenden Verbindungen zum Server auf Port 5432 blockiert.

Fehler 6: timeout expired oder connection timed out

Dieser Fehler deutet darauf hin, dass der Client versucht hat, eine Verbindung herzustellen, aber innerhalb eines bestimmten Zeitrahmens keine Antwort vom Server erhalten hat.

Erklärung: Im Gegensatz zu Connection refused, einer aktiven Ablehnung, impliziert ein Timeout, dass der Verbindungsversuch den Server nie erreicht hat oder der Server nicht geantwortet hat. Dies deutet oft auf Netzwerkkonnektivitätsprobleme oder einen stark überlasteten Server hin.

Lösungen:

1. Netzwerkkonnektivität:
   • Pingen Sie den Server: ping server_ip_address. Wenn Ping fehlschlägt, liegt ein grundlegendes Netzwerkproblem vor (Kabel, Router, Server offline).
   • Traceroute/MTR: traceroute server_ip_address (Linux/macOS) oder tracert server_ip_address (Windows) kann helfen zu identifizieren, wo die Verbindung entlang des Netzwerkpfads fehlschlägt.
2. Server listen_addresses und Firewalls: Überprüfen Sie erneut die Lösungen für Fehler 5, da falsch konfigurierte listen_addresses oder Firewalls ebenfalls Timeouts verursachen können, wenn der Server nicht erreichbar ist.
3. Serverlast: Wenn der Server extrem ausgelastet ist (hohe CPU, wenig Arbeitsspeicher, übermäßige Festplatten-E/A), könnte er zu beschäftigt sein, um neue Verbindungen rechtzeitig anzunehmen, was zu Timeouts führt. Überprüfen Sie die Systemressourcennutzung.

Fehler 7: SSL erforderlich, SSL deaktiviert oder Zertifikatsüberprüfung fehlgeschlagen

PostgreSQL kann verschlüsselte und unverschlüsselte Verbindungen akzeptieren, abhängig von den Servereinstellungen und den pg_hba.conf-Regeln. Ein Client kann mit Meldungen über deaktiviertes SSL, erforderliches SSL oder fehlgeschlagene Zertifikatsüberprüfung fehlschlagen.

Überprüfen Sie drei Stellen:

# Sehen Sie, ob der Server SSL aktiviert hat
psql -U postgres -d postgres -c "SHOW ssl;"

# Versuchen Sie eine verschlüsselte Verbindung vom Client
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user sslmode=require"

# Wenn eine Zertifikatsüberprüfung erwartet wird, verwenden Sie verify-full und ein vertrauenswürdiges Root-Zertifikat
psql "host=db.example.com dbname=my_app_db user=my_app_user sslmode=verify-full sslrootcert=/path/to/root.crt"

sslmode=require verschlüsselt die Verbindung, überprüft aber nicht die Serveridentität auf die gleiche Weise wie verify-full. Für die interne Entwicklung kann das ausreichen. Für den Produktionsverkehr über nicht vertrauenswürdige Netzwerke verwenden Sie die Zertifikatsüberprüfung und stellen Sie sicher, dass der Hostname im Verbindungsstring mit dem Zertifikat übereinstimmt.

Überprüfen Sie auch, ob pg_hba.conf hostssl oder hostnossl verwendet. Eine hostssl-Regel wird keine Nicht-SSL-Verbindung akzeptieren, und eine hostnossl-Regel wird keine SSL-Verbindung akzeptieren.

Fehler 8: Zu viele Clients bereits

Wenn PostgreSQL FATAL: sorry, too many clients already zurückgibt, funktioniert der Verbindungspfad. Der Server lehnt neue Sitzungen ab, weil max_connections erreicht wurde oder nur noch reservierte Superuser-Slots übrig sind.

Sehen Sie sich zunächst an, was verbunden ist:

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

Suchen Sie dann nach Mustern. Hunderte von idle-Sitzungen deuten oft auf einen zu groß konfigurierten Anwendungspool, ein Worker-Process-Leck oder mehrere Anwendungsreplikate hin, die jeweils ihren eigenen Pool öffnen. Die Erhöhung von max_connections kann Zeit erkaufen, erhöht aber auch den Speicherdruck, da jeder Backend-Prozess Overhead hat und möglicherweise work_mem verwendet. In den meisten Webanwendungen ist PgBouncer im Transaktions-Pooling-Modus eine bessere langfristige Lösung, als jedem App-Prozess zu erlauben, viele direkte PostgreSQL-Sitzungen zu halten.

Ein schneller Triage-Ablauf

Wenn jemand sagt "die Datenbank ist down", verwende ich einen kurzen Pfad, bevor ich die Konfiguration berühre:

# 1. Löst der Name den Host auf, den ich erwarte?
getent hosts db.example.com

# 2. Ist der TCP-Port von diesem Client aus erreichbar?
nc -vz db.example.com 5432

# 3. Kann psql mit demselben Host, Port, Benutzer und derselben Datenbank verbinden?
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user connect_timeout=5"

# 4. Was protokolliert PostgreSQL für den fehlgeschlagenen Versuch?
sudo tail -n 100 /var/log/postgresql/postgresql-*.log

Wenn nc fehlschlägt, bleiben Sie im Netzwerk- und Listener-Bereich. Wenn nc erfolgreich ist, aber psql mit FATAL fehlschlägt, ist PostgreSQL erreichbar, und die Antwort liegt normalerweise in der Authentifizierung, dem Datenbanknamen, dem Rollennamen, dem SSL-Modus oder der pg_hba.conf.

Allgemeine Schritte zur Fehlerbehebung

Wenn Sie mit einem anhaltenden Verbindungsproblem konfrontiert sind, befolgen Sie diese allgemeinen Schritte für eine systematische Diagnose:

1. Überprüfen Sie die PostgreSQL-Protokolle: Die Protokolldateien sind Ihr bester Freund. Sie enthalten detaillierte Informationen zu Startproblemen, Fehlern und abgelehnten Verbindungsversuchen. Der Speicherort wird normalerweise durch log_directory in postgresql.conf angegeben (z. B. /var/log/postgresql/ auf Debian/Ubuntu oder pg_log im Datenverzeichnis).

   # Beispiel zum Überprüfen der letzten Protokolle
   sudo tail -f /var/log/postgresql/postgresql-14-main.log
   

2. Überprüfen Sie die Konfigurationsdateien: Überprüfen Sie postgresql.conf und pg_hba.conf auf Syntaxfehler, Tippfehler oder falsche Werte. Schon ein einzelnes falsch platziertes Zeichen kann den Server daran hindern, zu starten oder Verbindungen anzunehmen.

3. Starten Sie PostgreSQL neu (als letztes Mittel für Konfigurationsänderungen): Während ein Neuladen oft für pg_hba.conf und einige postgresql.conf-Parameter ausreicht, erfordern bestimmte kritische Änderungen (wie listen_addresses) einen vollständigen Neustart.

   sudo systemctl restart postgresql
   

4. Testen Sie lokal auf dem Server: Wenn die Verbindung von einem entfernten Rechner fehlschlägt, versuchen Sie, direkt auf dem Server selbst eine Verbindung herzustellen. Dies hilft festzustellen, ob das Problem serverseitig oder netzwerkbedingt ist.

   # Verbinden Sie sich über einen Unix-Domain-Socket (falls verfügbar)
   psql -U your_username -d your_database
   
   # Oder verbinden Sie sich über TCP/IP mit localhost
   psql -U your_username -h 127.0.0.1 -p 5432 -d your_database
   

   Wenn die lokale Verbindung funktioniert, die entfernte jedoch nicht, liegt das Problem wahrscheinlich an listen_addresses, pg_hba.conf oder einer Firewall.

5. Überprüfen Sie die Client-Konfiguration: Stellen Sie sicher, dass der Verbindungsstring Ihrer Anwendung (z. B. Umgebungsvariablen PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE oder ein libpq-Verbindungsstring) korrekt konfiguriert ist, um den Servereinstellungen zu entsprechen.

Tipps und bewährte Methoden

• Prinzip der geringsten Privilegien: Vermeiden Sie die Verwendung des postgres-Superusers für routinemäßige Anwendungsverbindungen. Erstellen Sie spezifische Rollen mit nur den notwendigen Berechtigungen.
• Starke Passwörter: Verwenden Sie immer starke, eindeutige Passwörter für Ihre Datenbankrollen.
• Beschränken Sie pg_hba.conf: Geben Sie anstelle von 0.0.0.0/0 genaue Client-IP-Adressen oder enge CIDR-Bereiche in pg_hba.conf an, um die Sicherheit zu erhöhen.
• Überwachen Sie die Protokolle regelmäßig: Etablieren Sie eine Routine zur Überprüfung der PostgreSQL-Protokolle. Viele Probleme können frühzeitig erkannt werden, indem Protokolleinträge beobachtet werden.
• Dokumentieren Sie Ihre Konfiguration: Führen Sie klare Aufzeichnungen über Ihre postgresql.conf- und pg_hba.conf-Einstellungen, insbesondere für Produktionsumgebungen.

Die schnellste Lösung kommt normalerweise davon, den Fehler der Ebene zuzuordnen, die ihn verursacht hat. Netzwerktools sagen Ihnen, ob der Host und der Port erreichbar sind. PostgreSQL-Protokolle sagen Ihnen, warum der Server eine Sitzung abgelehnt hat. pg_hba.conf sagt Ihnen, ob die Verbindung erlaubt ist. Die Anwendungskonfiguration sagt Ihnen, ob dieselben Werte tatsächlich in der Produktion verwendet werden.