Remote Postgres Connection

malexdev asked:

I have followed everything I can find online about how to do this, and as far as I can tell everything is correct- but it’s not working.

I have a Linux 12.04 server running Postgres 9.1.
I can SSH into the server and work with Postgres perfectly from there, connected via local connection.

When I tried to set up Postgres for remote access however I cannot get it to work.
I have made the following changes: (All listed files are in /etc/postgresql/9.1/main)

  1. In /environment, I’ve added PGOPTIONS='-i'
  2. In /pg_hba.conf, I’ve added host all all md5
  3. In /postgresql.conf, I’ve changed listen_addresses='*'
  4. I’ve checked the firewall, it was default config but opened postgres port just in case
  5. Netstat -a shows tcp 0 0 localhost:5432 *:* LISTEN
  6. I’ve made sure my postgres user password and name is correct, and can connect locally with that user
  7. I’ve tried restarting (services postgresql restart) and start/stop.

And yet, still, I can’t connect remotely at all. I get the following error:

$ psql -h [server address] -U [username] -d [database name] psql: could
not connect to server: Connection refused   Is the server running on
host "[server address]" ([ip address]) and accepting    TCP/IP
connections on port 5432?

Any ideas?

My answer:

Are you SURE you restarted postgresql after this? The configuration is correct, but it’s still bound only to localhost.

View the full question and any other answers on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.