Postgresql – Connect to remote EC2 Postgresql database

connectionspostgresqlpostgresql-9.3

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

Best Answer

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?

Related Question