Troubleshooting Common PostgreSQL Connection Errors

PostgreSQL connection errors look similar until you slow down and read the exact message. Connection refused usually means the client reached a host, but nothing accepted the TCP connection on that address and port. Connection timed out often means the packet never got a useful reply. password authentication failed means the server was reached and rejected the credentials. no pg_hba.conf entry means PostgreSQL found no matching access rule.

That sequence matters. Start from the outside and move inward: host, port, service, listener, firewall, pg_hba.conf, user, database, password, SSL, then application pooling. Guessing at passwords when the service is not listening wastes time. Editing pg_hba.conf when DNS points to the wrong host wastes time too.

Understanding PostgreSQL Connection Basics

Before diving into specific errors, it's crucial to understand how PostgreSQL handles connections. PostgreSQL operates on a client-server model. A client (e.g., psql command-line tool, a web application, or a desktop client) attempts to connect to a PostgreSQL server process. This process typically listens for incoming connections on a specific network interface and port (defaulting to 5432).

Two primary configuration files govern how connections are accepted and authenticated:

• postgresql.conf: Controls general server behavior, including which network interfaces to listen on (listen_addresses) and the port (port).
• pg_hba.conf: (Host-Based Authentication) Dictates who can connect from where to which database, using what authentication method. This file is critical for security and access control.

Understanding the roles of these files and the client-server interaction is fundamental to effective troubleshooting.

Common Connection Errors and Solutions

Let's break down the most frequent connection errors you might encounter and their respective solutions.

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

This error means the database name specified by the client does not exist on the PostgreSQL server.

Explanation: The client application or psql command is trying to connect to a database that has not been created or whose name is misspelled.

Solution:

1. Verify the database name: Ensure the database name in your connection string or psql command is correct.
2. List existing databases: Connect to a default database (like postgres or template1) and list all available databases using \l (or \list).

# Try connecting to the default 'postgres' database
psql -U your_username -h your_host -d postgres

# Once connected, list all databases
\l

# Example of creating a missing database
CREATE DATABASE my_app_db;

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

This indicates that the username (role) specified for the connection does not exist.

Explanation: Similar to the database error, the user account attempting to connect is either non-existent or misspelled.

Solution:

1. Verify the username: Check the username in your connection string.
2. List existing roles: Connect with a superuser account (e.g., postgres user) and list all roles using \du.

# Connect as the default 'postgres' superuser
psql -U postgres -h your_host -d postgres

# List all roles (users)
\du

# Example of creating a missing role
CREATE ROLE my_app_user WITH LOGIN PASSWORD 'my_strong_password';

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

This is a common error, indicating an incorrect password was provided for the specified user.

Explanation: The password supplied by the client does not match the one stored for the PostgreSQL user (role).

Solution:

1. Check application configuration: Review your application's connection string or environment variables to ensure the password is correct.

2. Reset the password (if you have superuser access):

   # Connect as postgres superuser
   psql -U postgres -h your_host -d postgres
   
   # Change the password for the problematic user
   ALTER USER my_app_user WITH PASSWORD 'new_strong_password';
   

   Tip: Ensure the pg_hba.conf entry for the user specifies a password-based authentication method (e.g., md5, scram-sha-256) rather than trust or ident if you intend to use passwords.

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

This error is a pg_hba.conf configuration issue, meaning the server explicitly denied the connection based on its access rules.

Explanation: The pg_hba.conf file does not contain a rule that matches the incoming connection's parameters (client IP, user, database, and authentication method).

Solution:

1. Locate pg_hba.conf: The location varies by OS and installation method (e.g., /etc/postgresql/14/main/pg_hba.conf on Debian/Ubuntu, or specified by SHOW hba_file; in psql).

2. Edit pg_hba.conf: Add or modify an entry to permit the connection. A common entry for allowing connections from anywhere with password authentication looks like this:

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

   • TYPE: host for TCP/IP connections.
   • DATABASE: all (or a specific database name).
   • USER: all (or a specific username).
   • ADDRESS: The IP address range of the client (e.g., 192.168.1.0/24, 127.0.0.1/32 for local, or a single public client IP).
   • METHOD: The authentication method. Prefer scram-sha-256 for password authentication on modern PostgreSQL deployments when clients support it.

   Warning: Avoid host all all 0.0.0.0/0 ... unless there is a very deliberate network control in front of it. A narrow database, role, and CIDR makes mistakes easier to spot.

3. Reload PostgreSQL: After editing pg_hba.conf, you must reload the PostgreSQL configuration for changes to take effect.

   # On systemd-based systems
   

sudo systemctl reload postgresql

# Or using pg_ctl (requires specifying data directory)
# pg_ctl reload -D /var/lib/postgresql/14/main
```

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

This is a generic error indicating that the client could not establish a connection to the PostgreSQL server. The server actively rejected the connection attempt, often because nothing is listening on the target IP/port.

Explanation: This typically points to one of the following:

• PostgreSQL service is not running.
• PostgreSQL is not listening on the expected network interface or port.
• A firewall is blocking the connection.

Solutions:

1. Is PostgreSQL running?

   • Check service status:

     sudo systemctl status postgresql
     # Or, for older systems/other setups:
     # sudo service postgresql status
     

     If it's not running, start it:

     sudo systemctl start postgresql
     

   • Check logs: Review PostgreSQL logs (e.g., /var/log/postgresql/) for startup errors.

2. Is it listening on the right address/port?

   • Check postgresql.conf: Ensure listen_addresses is configured correctly. For connections from other hosts, it should be * or the specific IP address of the server's network interface, not just localhost (127.0.0.1).

     # In postgresql.conf
     listen_addresses = '*'    # Listen on all available network interfaces
     port = 5432               # Default port
     

     After changing listen_addresses, you must restart PostgreSQL (a reload is not enough).

     sudo systemctl restart postgresql
     

   • Verify listening port: Use netstat or ss to check if PostgreSQL is actually listening on port 5432 (or your configured port).

     sudo ss -ltnp | grep 5432
     # Expected output example:
     # tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      12345/postgres
     

     If you don't see 0.0.0.0:5432 or your_server_ip:5432, PostgreSQL is likely only listening on 127.0.0.1:5432 or not at all.

3. Is a firewall blocking the connection?

   • Server-side firewall: Check ufw (Ubuntu/Debian), firewalld (CentOS/RHEL), or iptables to ensure port 5432 is open for incoming connections from the client's IP address.

     # Example for UFW
     sudo ufw allow 5432/tcp
     sudo ufw enable
     sudo ufw status
     
     # Example for firewalld
     sudo firewall-cmd --permanent --add-port=5432/tcp
     sudo firewall-cmd --reload
     sudo firewall-cmd --list-ports
     

   • Client-side firewall: Less common, but ensure the client's firewall isn't blocking outbound connections to the server on port 5432.

Error 6: timeout expired or connection timed out

This error suggests that the client attempted to connect but did not receive a response from the server within a specified timeframe.

Explanation: Unlike Connection refused, which is an active rejection, a timeout implies the connection attempt never reached the server or the server didn't respond. This often points to network connectivity issues or a severely overloaded server.

Solutions:

1. Network Connectivity:
   • Ping the server: ping server_ip_address. If ping fails, there's a fundamental network problem (cable, router, server offline).
   • Traceroute/MTR: traceroute server_ip_address (Linux/macOS) or tracert server_ip_address (Windows) can help identify where the connection is failing along the network path.
2. Server listen_addresses and Firewalls: Revisit solutions for Error 5, as misconfigured listen_addresses or firewalls can also cause timeouts if the server isn't reachable.
3. Server Load: If the server is under extreme load (high CPU, low memory, excessive disk I/O), it might be too busy to accept new connections promptly, leading to timeouts. Check system resource utilization.

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

PostgreSQL can accept encrypted and unencrypted connections, depending on server settings and pg_hba.conf rules. A client may fail with messages about SSL being off, SSL being required, or certificate verification failing.

Check three places:

# See whether the server has SSL enabled
psql -U postgres -d postgres -c "SHOW ssl;"

# Try an encrypted connection from the client
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user sslmode=require"

# If certificate validation is expected, use verify-full and a trusted root cert
psql "host=db.example.com dbname=my_app_db user=my_app_user sslmode=verify-full sslrootcert=/path/to/root.crt"

sslmode=require encrypts the connection but does not verify the server identity the same way verify-full does. For internal development that may be enough. For production traffic across untrusted networks, use certificate verification and make sure the hostname in the connection string matches the certificate.

Also check whether pg_hba.conf uses hostssl or hostnossl. A hostssl rule will not match a non-SSL connection, and a hostnossl rule will not match an SSL connection.

Error 8: Too Many Clients Already

If PostgreSQL returns FATAL: sorry, too many clients already, the connection path is working. The server is refusing new sessions because max_connections has been reached, or because reserved superuser slots are all that remain.

First, see what is connected:

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

Then look for patterns. Hundreds of idle sessions often point to an application pool configured too large, a worker process leak, or several application replicas each opening their own pool. Increasing max_connections can buy time, but it also increases memory pressure because each backend has overhead and may use work_mem. In most web applications, PgBouncer in transaction pooling mode is a better long-term fix than letting every app process hold many direct PostgreSQL sessions.

A Fast Triage Flow

When someone says "the database is down," I use a short path before touching configuration:

# 1. Is the name resolving to the host I expect?
getent hosts db.example.com

# 2. Is the TCP port reachable from this client?
nc -vz db.example.com 5432

# 3. Can psql connect with the same host, port, user, and database?
psql "host=db.example.com port=5432 dbname=my_app_db user=my_app_user connect_timeout=5"

# 4. What does PostgreSQL log for the failed attempt?
sudo tail -n 100 /var/log/postgresql/postgresql-*.log

If nc fails, stay in network and listener territory. If nc succeeds but psql fails with FATAL, PostgreSQL is reachable and the answer is usually in authentication, database name, role name, SSL mode, or pg_hba.conf.

General Troubleshooting Steps

When faced with a persistent connection issue, follow these general steps for systematic diagnosis:

1. Check PostgreSQL Logs: The log files are your best friend. They contain detailed information about startup issues, errors, and denied connection attempts. The location is usually specified by log_directory in postgresql.conf (e.g., /var/log/postgresql/ on Debian/Ubuntu, or pg_log within the data directory).

   # Example of checking recent logs
   sudo tail -f /var/log/postgresql/postgresql-14-main.log
   

2. Verify Configuration Files: Double-check postgresql.conf and pg_hba.conf for syntax errors, typos, or incorrect values. Even a single misplaced character can prevent the server from starting or accepting connections.

3. Restart PostgreSQL (as a last resort for config changes): While reload is often sufficient for pg_hba.conf and some postgresql.conf parameters, certain critical changes (like listen_addresses) require a full restart.

   sudo systemctl restart postgresql
   

4. Test Locally on the Server: If connecting from a remote machine fails, try connecting directly on the server itself. This helps determine if the issue is server-side or network-related.

   # Connect using a Unix domain socket (if available)
   psql -U your_username -d your_database
   
   # Or connect via TCP/IP to localhost
   psql -U your_username -h 127.0.0.1 -p 5432 -d your_database
   

   If local connection works but remote doesn't, the problem is likely listen_addresses, pg_hba.conf, or a firewall.

5. Check Client Configuration: Ensure your application's connection string (e.g., PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE environment variables, or a libpq connection string) is correctly configured to match the server's settings.

Tips and Best Practices

• Principle of Least Privilege: Avoid using the postgres superuser for routine application connections. Create specific roles with only the necessary privileges.
• Strong Passwords: Always use strong, unique passwords for your database roles.
• Restrict pg_hba.conf: Instead of 0.0.0.0/0, specify exact client IP addresses or narrow CIDR ranges in pg_hba.conf to enhance security.
• Monitor Logs Regularly: Establish a routine for reviewing PostgreSQL logs. Many issues can be caught early by observing log entries.
• Document Your Configuration: Keep clear records of your postgresql.conf and pg_hba.conf settings, especially for production environments.

The fastest fix usually comes from matching the error to the layer that produced it. Network tools tell you whether the host and port are reachable. PostgreSQL logs tell you why the server rejected a session. pg_hba.conf tells you whether the connection is allowed. The application config tells you whether the same values are actually being used in production.