PostgreSQL Server doesn't listen: Tried everything and failed

4
votes

When attempting to connect to PostgreSQL from a remote Windows server using pgAdmin 1.16.1 I get the dreaded 'Server doesn't listen' message with 'could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "xxx.xx.xxx.xx" and accepting TCP/IP connections on port 5432'.

I'm running PostgreSQL 9.3 on CentOS 6.4. Here's what I've tried so far:

  • I can access the database locally with psql --username=postgres. The database is there, it's running and I can query it

  • In postgresql.conf, I've set 

    listen_addresses = '*'
port = 5432

  • In pg_hba.conf, I've got the server that I am trying to access the database from listed as:

    host    all             all             xxx.xx.xxx.0/24         md5
  • SELinux is turned off (getenforce gets the response Disabled)

  • Just in case I've added port 5432 to the IPTables

    iptables -A INPUT -p tcp -m tcp --dport 5432 -j ACCEPT

  • I've gone into postgresql with psql and set the password (although I'm convinced it was already set correctly)

    ALTER USER postgres WITH PASSWORD '*************';

  • Typing netstat -angives these references to port 5432 (not sure they are relevant):

    Proto Recv-Q Send-Q Local Address               Foreign Address             State      
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      

tcp        0      0 :::5432                     :::*                        LISTEN      

Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node Path
unix  2      [ ACC ]     STREAM     LISTENING     677454 /tmp/.s.PGSQL.5432

  • I can ping from the remote server to the database server

  • I have no problem connecting to another server running PostgreSQL 9.1 on Ubuntu from the same remote server using the same installation of pgAdmin

I'm stumped. Does anyone have a clue to what more could be wrong? And yes, I did remember to restart the server after changing the config files. I believe that I've read every other post on the subject.

1
postgresqlcentos
Do you see anything in the PostgreSQL server logs when you attempt a connection? If in doubt, fire up Wireshark and see what's happening on the network interface. - Craig Ringer
@CraigRinger The PostgreSQL server logs show nothing when I attempt to connect. Running telnet xxx.xx.xxx.xx 5432 doesn't connect either, while telnet xxx.xx.xxx.xx 22 does. Running telnet localhost 5432 on the database server connects. Not sure how to interpret that. Still trying to grasp what Wireshark can do for me, but don't I need to install it on the database server - there is no Linux version as far as I can see. - MiG62
Sounds like firewall. As for Wireshark, yes, there most certainly is a Linux version! Practically every distro has it packaged. yum install wireshark-gnome on Fedora; probably the same on CentOS. - Craig Ringer
Sorry, there's no GUI on my server. I have now further confirmed that I can access the database via ODBC from the database server itself. I think this pretty much narrows it down to a firewall issue. I'll get someone familiar with linux firewalls to have a look at it. - MiG62
Good idea. Just FYI, there's a command-line-only wireshark too. - Craig Ringer

1 Answers

2
votes

Maybe you forgot add OUTPUT rule?

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d xx.xx.xx.xx  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s xx.xx.xx.xx --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

xx.xx.xx.xx - your server IP