pgAdmin cannot connect to PostgreSQL 9.1

Question

I am trying to use pgAdmin on Windows to connect to postgresql 9.1.8 running on localhost's Ubuntu 12.04 VM. The host's port 5432 forwards to VM's port 5432.

pgAdmin Error:

Error connecting to the server: could not receive data from server: Software caused connection abortion (0x00002745/10053)

postgresql.conf

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'
port = 5432 

pg_hba.conf

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

host    all             all             0.0.0.0/0               md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5 

netstat -nlp | 5432

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      29035/postgres
unix  2      [ ACC ]     STREAM     LISTENING     50823    29035/postgres      /var/run/postgresql/.s.PGSQL.5432

iptables rule

iptables -I INPUT -p tcp --dport 5432 -j ACCEPT

PostgreSQL service has been restarted and pgAdmin still gives the error. Any ideas what have caused it?

Solution

Had the same issue and it boiled down to 3 steps:

1- On Mavericks (same for 10.6+) port 5432 is already taken so needed this: --- config.vm.network "forwarded_port", guest: 5432, host: 5433' on 'Vagrantfile' and then you use port 5433 to connect through pgadmin3

2- listen_address = '*' # in postgresql.conf, allows for the server to listen as a socket connection from all ip's

3- need to enable host in 'pg_hba.conf'

I put the provisioning shell script required for postgresql on vagrant here:

https://gist.github.com/haknick/7394776

Comments

[Nyxynyx]

I made it such that localhost:5432 on my Windows host machine forwards to localhost:5432 on the Ubuntu VM provisioned by Vagrant/puppet. Port 80 forwarding works well, I can access http://localhost from my host system

[Jenny D]

Can you connect to the server if you try 'telnet localhost 5432'?

[pupkinsen]

Port forwarding doesn't really help in your case. You probably need to do SNAT on your Linux machine but anyway it looks like a backwards solution. I think it is better to make pg listen on the eth interface. If you need security, you can always restrict access to it using your firewall.

[Nyxynyx]

I cant telnet to localhost:5432. I get the error Connection closed by remote host when telneting using Putty.

[haknick]

Were you able to figure this out ?

[Admin]

can you please post the screenshot about telnet?