5 Common Connection Errors in PostgreSQL and How to Solve Them

So you’ve got a PostgreSQL instance (maybe locally, maybe in Kubernetes, or it could be a cloud service like RDS or Tiger Cloud), and you’re ready to go. You fire up psql to start running queries… but your connection doesn’t work.

This post examines the five most common PostgreSQL connection errors—such as "postgres connection refused" and "psql: error: connection to server on socket"—and provides step-by-step guidance on how to troubleshoot PostgreSQL connection issues effectively.

How to Troubleshoot PostgreSQL Connection Issues

To troubleshoot PostgreSQL connection issues, start by identifying common errors like the above-mentioned "postgres connection refused." These issues often arise from configuration mismatches, firewall restrictions, or server-side problems. This guide will help you systematically resolve these problems.

1. Verify basic connectivity

• Ping the server: Ensure the PostgreSQL server's host is reachable (e.g., ping <hostname> or telnet <hostname> <port>).
• Check the port: Ensure PostgreSQL is listening on the correct port (default: 5432).
  • Use: netstat, ss, or lsof to confirm.

2. Check client configuration

• Verify connection parameters: host, port, dbname, user, and password.
• Ensure the correct client is being used (psql, an application, etc.).
• Test with psql to confirm:

psql -h <hostname> -p <port> -U <username> -d <dbname>

3. Review PostgreSQL server logs

• Check logs for error messages related to authentication, connections, or configurations.
• Default log file location: /var/lib/pgsql/data/log/ or as specified in postgresql.conf.

4. Inspect pg_hba.conf

• Ensure the host-based authentication file (pg_hba.conf) allows connections from the client:
  • Confirm IP, user, database, and authentication method are correctly configured.
• Reload the configuration after making changes:SELECT pg_reload_conf();

5. Validate PostgreSQL configuration

• Verify critical parameters in postgresql.conf:
  • listen_addresses includes the correct IPs or '*'.
  • max_connections is not exceeded.
• Restart the server after changes:systemctl restart postgresql

6. Check network and firewall

• Ensure firewalls or security groups allow traffic on the PostgreSQL port.
• Test with tools like nc (netcat) or telnet:nc -vz <hostname> <port>

7. Database-specific issues

• Check if the database exists and is accessible to the user.
• Validate user privileges: \du

8. Advanced debugging

• Enable verbose client logging with PGOPTIONS='--client_min_messages=debug5'.
• Use pg_stat_activity to view active connections:SELECT * FROM pg_stat_activity;

Now on to the five most common errors.

Sorry, Too Many Clients Already

This message is a classic, usually followed up with “remaining connection slots are reserved for non-replication superuser connections.”

PostgreSQL has a limited number of connections to the database, controlled by the max_connections parameter. This could be set globally, on a role, or in a database. When you exceed this number, new connections will get this error, although superusers may still be able to connect (another parameter controls the limit for them, max_superuser_connections).

💡 Solution: You need to reduce your connection counts in the short term. Either stop some applications that are connected to the database, log in a superuser, and terminate some of the connections, or, as a last resort, restart the PostgreSQL service to dump all connections (but you’d also lose all open transactions).

To stop hitting this again and again, you have three options:

1. Audit why you used so many connections, and try reducing that number.
2. Increase max_connections (but remember that each connection is a process; ideally, you don’t want to have more than 2x your thread/CPU count actively doing things).
3. Add a connection pool in front of PostgreSQL, which will allow a large number of application connections to be efficiently mapped on a smaller number of PostgreSQL connections. PgBouncer is a good option (and even better news, if you’re using TimescaleDB, you can add it with a single click).

No pg_hba.conf Entry for Host

Client authentication in PostgreSQL is controlled by a file called pg_hba.conf (which usually lives in your data directory). The file contains a series of mappings between {connection_type, host, username, database} and authentication methods. Each time a connection happens, it will be compared against the records top-down. The first one that matches will be used to handle the connections. If the connection does not match any records, it will be rejected.

When you connect, your host is not matched in the pg_hba.conf file and the connection has been rejected. In this case, you’re trying to connect over a network connection (that will match the word host in the first column of pg_hba.conf).

In some cases, "postgresql unable to connect to server" errors may indicate a missing or incorrect pg_hba.conf entry for your host. Review the pg_hba.conf file and add an appropriate rule for your connection type.

💡 Solution: You could add a specific record for your host or a generic record to allow all hosts. More information can be found in the pg_hba.confdocumentation, but a generic record might look like this:

host 	all	all	all	scram-sha-256

So, match a host connection for all databases, for all users, and from all hosts, and use scram authentication. Remember that records are read top-down, and the first matching one is selected. After making the change, you’d need to reload or restart your PostgreSQL server.

Connection to Server on Socket Failed: No Such File or Directory

The error message "psql: error: connection to server on socket" commonly appears when the PostgreSQL server is running on a non-standard port or the socket file is missing. This error is often encountered when attempting to connect to the PostgreSQL server using psql without any hostname (from the -h flag, or the URI).

In this case, a local UNIX socket connection will be made, but psql can’t find the socket file. This issue usually arises due to a discrepancy in the expected location of the socket file or the PostgreSQL server not running.

💡 Solution: Verify that the PostgreSQL server is running. If it’s not, then this could be an easy fix. Start it up and try again.

If the error persists, one of three situations are possible:

• The version of psql you’re using isn’t the one that came with the PostgreSQL binary that is running.
• The PostgreSQL server is running on a non-standard port.
• The unix_socket_directories setting has been changed.

Check the postgresql.conf file to confirm the port and unix_socket_directories settings. Try to use them in the psql command as follows:

psql -p <PORT> -h <UNIX_SOCKET_DIRECTORIES>

Hopefully, you can connect! 

As a last resort, you could try connecting with psql -h localhost, which will connect via the local network and not a socket.

Connection to Server Failed: Connection Refused

The "postgres connection refused" error message appears when the PostgreSQL server does not respond to a network connection attempt (in fact, it’s the mirror to the previous error when connecting via the network and not a local socket). It can occur if the server is not running, it is listening on a different port than the one being connected to, or network issues are preventing the connection.

💡 Solution: If you encounter the "postgres connection refused" error, ensure the PostgreSQL server is up and check the postgresql.conf file for the listen_addresses and port settings. 

To ensure that PostgreSQL is listening on all interfaces, listen_addresses should be set to *. If this and the port are what you expect, then it’s possible that either a firewall or network element is blocking your connection (maybe the server is on a private network), or you have the hostname wrong. 

Database "X" Does Not Exist

This error occurs when a user tries to connect to a PostgreSQL database that does not exist or the database name is incorrectly specified in the connection string. It's common to mistakenly use a username as the database name if not specified properly. In fact, if you specify a user when running psql with the -U flag, the database name will default to match the user name. In this case, you could also use -D to specify a database.

💡 Solution: Double-check the database name in your connection command or string to ensure it is spelled correctly and corresponds to one of the databases on your PostgreSQL server. You can list all databases by connecting with psql and running \l or using a graphical tool like PGAdmin or PopSQL.

If the database does not exist, create it with CREATE DATABASE [database_name]; or adjust your connection parameters to reference an existing database.

Next Steps

In this post, we covered the five most common PostgreSQL connection errors and showed you how to solve them. This is not our first rodeo, so be sure to check the following articles if you’re struggling with the following PostgreSQL errors:

• ERROR: no partition of relation {table-name} found for row
• XID wraparound