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