postgresql server doesn't listen

Question

I just recently install PostgreSQL on our server via SSH. The installation went successful, until the time I tried to connect to it using pgAdmin on my Windows machine.I received this kind of error:

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "xxx.xxx.xxx.xxx" and accepting TCP/IP connections on port 5432?

xxx.xxx.xxx.xxx = my server's public IP.

The docs suggest this can be fixed by setting the value of listen_addresses = '*' in the /etc/postgresql/9.1/main/postgresql.conf. I did that but still it won't let me.

additional error came up

FATAL: no pg_hba.conf entry for host "xxx.xx.xxx.xxx", user "postgres", database "postgres", SSL on FATAL: no pg_hba.conf entry for host "xxx.xx.xxx.xxx", user "postgres", database "postgres", SSL off

xxx.xx.xxx.xxx = my IP address.

What seems to be I'm missing?

Answer 1

Things that could block a postgres connection:

1. misconfigured listen_address in postgresql.conf
2. selinux (?)
3. iptables
4. pg_hba.conf (although this should cause a different error, not server doesn't listen)

Can you connect to the server locally, if you ssh in and run psql?

On our internal dev servers, I just turn off selinux and iptables. This is a bad idea from a security standpoint, but it might serve as a temporary step to help you narrow down where the problem is.

Answer 2

You might need to change more than one configuration file. In your case, you probably need to edit pg_hba.conf, too. Search that file for "non-local connections".

I like to keep configuration files under version control. It's easier to recover from mistakes that way.

You probably need to restart the PostgreSQL server after making those changes.

Answer 3

• After changing listen_addresses settings on the server, make sure to restart the PostgreSQL server (send SIGHUP to the postmaster process, with kill -HUP, etc).
• Make sure that postgresql.conf port is set to 5432
• Make sure that if a firewall is running on the server, that port 5432 is open for connections coming from the window's (client) machine you are using
• Check pg_hba.conf to make sure that the subnet of your client machine is given access
• Try using psql locally