Can't connect to Postgres

March 30, 2020
Administration PostgreSQL Troubleshooting

This is kind of frustrating… You’ve been at it for the best part of the day and you can’t figure it out. You already tested that Postgres was running and that you were attempting to connect to the right port and you checked the host several times but you still can’t connect to Postgres!

I won’t give you a full review of all possible causes, because they are too many and I’m pretty sure I will forget some, but here are some clues for you.

General solving problems diagram

Here is a general diagram to help you figuring out what could be wrong:

Troubleshooting Postgres

You’ll find below several explanations about those steps:

listen_addresses

By default, Postgres only listen on 127.0.0.1, meaning you can’t reach the database from the outside. This is a security feature. You can either listen on all ethernet interfaces (using ‘*') or list your IP addresses.

This setting needs a restart of Postgres to be taken into account.

Opening ports

If you’re having trouble connecting remotely only, it might be because the port is not open between your Postgres host and the host you’re trying to connect from. If you don’t know how to find out if your port is open, I wrote an article called “Is my port open ?

Using psql

psql is the best Postgres client you’ll find. Also, it’s the official Postgres client, shipped in Postgres packages. Lastly, it should give you an error message explaining what’s going wrong.

Before trying to connect with your own code, your software, or anything else, try connecting with psql. If you can do it, then maybe the problem is not Postgres! ;-)

Reading the error messages

Yes, I know! You need to read the error messages. Normally, Postgres explains pretty well what’s wrong and what you need to do to fix the problem.

Here are some examples:

FATAL: password authentication failed for user “XXX”

It simply means your password is not good. Try with the right password, just in case…

password file “XXX” has group or world access; permissions should be u=rw (0600) or less

Postgres refuses to use your password file because it’s not secured. Secure it before being able to use it!

FATAL: Peer authentication failed for user “XXX”

You must have messed up with the pg_hba.conf file. If you have no idea what you’re doing, reading this documentation page might be a good thing!

Is the server running locally and accepting connections on Unix domain socket “XXX/.s.PGSQL.PPP”?

You attempted a Unix socket connection, but it seems like your Postgres instance is not listening on that port (PPP)… Two solutions: Postgres is not running, or you’re using the wrong port number.

Is the server running on host “XXX” and accepting TCP/IP connections on port 5433

You attempted a connection on host XXX and port 5433. Are you sure your host and port are the good ones? If yes, is your Postgres instance running?

Preferred types

September 24, 2022
Internals PostgreSQL

Taking children to PostgreSQL Conference Europe

September 22, 2022
PostgreSQL event

Removing some pedantry off Postgres

May 21, 2022
Hacking PostgreSQL