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 ar 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 bellow 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.

Using psql

psql is the best Postgres client you’ll find. In addition, 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. Normaly, Postgres explains pretty good 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 one ? If yes, is your Postgres instance running ?

Where do my Postgres settings come from ?

April 19, 2020
Hacking PostgreSQL Administration

Debian Default Logging Explained

April 4, 2020
Hacking PostgreSQL Debian

Restoring Through timeline change

March 24, 2020
Installation PostgreSQL