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 Timescale), 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 connection errors in PostgreSQL and how you can solve them.

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 Timescale, 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).

💡 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

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

This 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: 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