Setting up Postgres: Can't connect remotely to Postgres server

Question

I am having a problem getting postgres to accept connections from my other server. Here's my setup:

• APP06 (10.55.129.31): Server running Postgres
• APP05 (10.55.129.30): Server trying to connect to Postgres

Here's my /var/lib/pgsql/data/pg_hba.conf file on app06:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         10.55.129.30/32       trust
host    all         all         10.55.129.31/32       trust
host    all         all         0.0.0.0/0             trust
# IPv6 local connections:
host    all         all         ::1/128               trust

It's not exactly what I want (I'd like to use LDAP), but it should at least work. I want to connect 10.55.129.30 to 10.44.129.31. The first host line should be all I need. The third host line should allow a connection from anywhere.

Now, we start up Postgres on app06

 $ /etc/init.d/postgres start
 $ /etc/init.d/postgres status

 pg_ctl: server is running (PID: 30091)
 /usr/bin/postgres "-D" "/var/lib/pgsql/data"

Looks good...

Let's look at Netstat:

$ netstat -nlp | grep 5432
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN      30091/postmaster    
unix  2      [ ACC ]     STREAM     LISTENING     16490154 30091/postmaster    /tmp/.s.PGSQL.5432

Okay... I think... Not being a real system administrator, I can't say exactly what that means, but it looks like PID 30091 (process postmaster) has port 5432 open.

Let's see if anything is listening on port 5432

$ telnet localhost 5432
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.

Okay, Port 5432 is open and accepting connections.

Now, we'll go to APP05 and see if we can connect...

I've already tried running psql from app05 with no success. Can I talk to app06?

$ ping app06

PING app06 (10.55.129.31) 56(84) bytes of data.
64 bytes from app06 (10.55.129.31): icmp_seq=1 ttl=64 time=0.901 ms
64 bytes from app06 (10.55.129.31): icmp_seq=2 ttl=64 time=0.230 ms

--- app06 ping statistics ---
2 packets transmitted, 6 received, 0% packet loss, time 5001ms

Okay, I can talk to app06 from app05. Let's see if I can connect to Postgres' port:

$ telnet app06 5432
Trying 10.55.129.31...
telnet: connect to address 10.55.129.31: Connection refused
telnet: Unable to connect to remote host: Connection refused

I'm not able to connect to port 5432

I ran /usr/sbin/lokkit and verified that the software firewall is turned off.

I'm not a network or system administrator by any means. I am not a postgres administrator either. It's very possible I missed some simple, stupid thing, but I don't know what.

I'm pretty sure that Port 5432 on these machines are open on the network. I tried switching the Postgres port to 8999 which I sure is not blocked, but I get the same issue.

Any idea what I am doing wrong?

Answer 1

Open postgresql.conf in a text editor and look for the listen_addresses keyword. Set it to *

Restart pgsql.

Answer 2

Just FYI ... one incredibly dumb thing that I nevertheless did was to forget to remove the leading "#" in the "listen_addresses" line in the configuration file when I went to change it. (Yep... therefore, the line was [still] a comment! Duh-h-h-hhh.)

Answer 3

the answer is very simple, your postgress installation is binded to 127.0.0.1 interface only

tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN      30091/postmaster    

you need to reconfigure it and bind it to your network interface / IP address and you'll be able to access

Answer 4

Just let some body who met the same problem:

1. I am sure postgres is running pretty well in local.
2. I can visit and operate postgres in my local. ( 192.168.10.14)
3. I am sure all the config items are correct ( listening to '*' , and allow 0.0.0.0/0 to visit )
4. I can telenet postgres from another PC. ( 192.168.10.20) , however can not connect postgres.

   telnet 192.168.10.14 5432   # ->  OK
   ping 192.168.10.14          # ->  OK
   psql -h 192.168.10.14       # ->  failed.  remote server closed ...

4. finally I got the answer： I incorrectly setup my network. the network mask should be 255.255.255.0, but not 0.0.0.0 :

# correct mask: 
255.255.255.0

# incorrect mask
0.0.0.0 

5. so I corrected the network-mask of my network, restart, everything goes well!