psql cant connect to PostgreSQL server (postmaster) on IP and port 5432?

Question

Please read before replying it as duplicate (as it perhaps can happen). I am running my postmaster (postgres) server. See below for 'sudo netstat -anp|grep 5432' output?

tcp  0  0 127.0.0.1:5432    0.0.0.0:*     LISTEN      29606/postmaster       
unix  2      [ ACC ]     STREAM     LISTENING     1650581 29606/postmaster /var/run/postgresql/.s.PGSQL.5432
unix  2      [ ACC ]     STREAM     LISTENING     1650582 29606/postmaster    /tmp/.s.PGSQL.5432               

I am able to connect from localhost using

psql -h localhost (OR 127.0.0.1) -d <DB> -U user -W

But when I try to connect from other hosts using tcp, by specifying

psql -h ip_add_postmaster -d <DB> -U user -W

It throws:

psql: could not connect to server: Connection refused Is the server running on host XXXXXX and accepting TCP/IP connections on port 5432?

What's wrong here?

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only                  
local   all             all                                     peer  
# IPv4 local connections:                                             
host    all             all             127.0.0.1/32            md5   
# IPv6 local connections:                                             
host    all             all             ::1/128                 md5   

In postgresql.conf,

listen_addresses = 'localhost, 127.0.0.1, ip_add_postmaster'

Note: ip_add_postmaster is same as my Elastic IP and not public DNS. If this information
matters.

What am I doing wrong here? Machine is hosted on Amazon EC2 and have open the port 5432.

Answer 1

As your netstat output indicates, it's listening at 127.0.0.1:5432 which is localhost. That is only connectable from localhost ;)

Set listen_addresses='*' in your config and it will work.

[edit] Other things to check:

• is the amazon firewall blocking anything?
• is iptables blocking anything?

But first make sure the listening address is correct, your netstat output shows that it won't work like this.

Answer 2

listen_addresses='localhost, private_ip' fixed the issue. I was not able to start postmaster server on elastic IPs. Once postgres server started o localhost and private IPs, I was able to connect.

Answer 3

One other issue I have found was if you end up with two Postgres installations, the second one can choose non-default port (in my case it was 5433 i/o 5432). So checking the port in postgresql.conf might be a good idea.

Answer 4

I ran into this issue and tried all sorts of fixes I found across SO, and want to add a simple solution that worked for me after realizing it had to do with permissions in my case.

Simply, if you're running a psql server on Windows, you are initially restricted to the default postgres superuser for logging in, launching the server, and so on.

So, first try running from the command line: psql -U postgres -h localhost -p 5432 and enter your password at the prompt. If you've managed to login and the server is up, then it was a permissions issues. From here, you can create a role for yourself that has login privileges to whatever database you are trying to run.

If the error persists, then consider checking postgresql.conf as mentioned above, to make sure default IP is set to * or localhost, and the port set to 5432 or whatever port you want as default.

Answer 5

I also ran into the same issue. On debugging, it was nothing related to the port, but due to some missing directories in the Postgres folder.

While updating Mac OS (from 10.13.1 -> 10.13.13), some folders in the directory /usr/local/var/postgres/ gets deleted. The fix was the adding the missing directories:

mkdir /usr/local/var/postgres/pg_tblspc
mkdir /usr/local/var/postgres/pg_twophase
mkdir /usr/local/var/postgres/pg_stat
mkdir /usr/local/var/postgres/pg_stat_tmp
mkdir /usr/local/var/postgres/pg_replslot
mkdir /usr/local/var/postgres/pg_snapshots
mkdir /usr/local/var/postgres/pg_logical/{snapshots,mappings}