1

I want to setup a connection between a webserver and a PostgreSQL 9.3 server. Both are hosted on Amazon EC2 servers and are part of the same security group. I can't get the connection working. The same goes for a direct connection from my desktop to Postgres, something we have done with another Postgres server, the one which is going to be replaced by this one.

This is what I did up til now:

  • Checked Amazon Security Groups: Server is part of the Default group, and the Default group accepts traffic from my local IP for all ports: this works for other servers and I can connect to the server using SSH.
  • IPTables firewall service is stopped on the server.
  • PostgreSQL is running: I can setup an SSH tunnel and then connect using PGadmin, open databases etc. So the server is running, and the login is correct. Even if this would work for connecting from my desktop, it is not a solution for the connection between the servers. I suspect though that if I get this working, the other connection will work as well.
  • pg_hba.conf: I copied the connection list from the other server, and my local IP is in here, as well as the local IP (10.x.x.x) of the Amazon webserver.

After installing the server I changed the database password for the postgres user using MD5. As the connection over the SSH tunnel worked, I guess this should work for all connections automatically, but I'm not sure. I haven't setup the other server, and when I query the postgres users table (pg_catalog.pg_user), I don't see any difference between the two servers.

I'm not 100% sure about the security groups, so I'm going to look into that again, but besides that I don't know where to look.

How can I get the connection working?


Update

Netstat output

netstat -an | grep 5432
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN      
unix  2      [ ACC ]     STREAM     LISTENING     63088  /tmp/.s.PGSQL.5432
unix  2      [ ACC ]     STREAM     LISTENING     63087  /var/run/postgresql/.s.PGSQL.5432

Telnet

Unable to connect to remote host: Connection refused
2

You need to work your way up the networking stack to determine where the issue is.

Can you ping the destination from the source?

Can you telnet to the postgres port(5432 by default) from the source?

Can you connect to the postgres service with a management tool(pgadmin or psql) from the source?

  • Ping works. I can login to the database server using SSH, from my desktop and from the webserver using the 10.x.x.x address. Telnet: Unable to connect to remote host: Connection refused. I can connect to postgres via an SSH tunnel. – SPRBRN Feb 10 '15 at 16:09
  • 1
    And you did a telnet 5432? If so you have an issue on the host- either os-level(firewalling) or app-level(postgres hba conf). Netstat -an on the host should show if the postgres service is listening on that port. – Jeff Sacksteder Feb 10 '15 at 16:57
  • It turned out to be a problem with the postgresql.conf file. This wasn't configured properly. – SPRBRN Feb 12 '15 at 13:22
0

You need to set the local_addresses in your postgresql.conf to * instead of local host

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.