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.