PostgreSQL's command line client (psql) is indispensable, but visual tools definitely have their perks. Here how I’ve got my local pgAdmin III working with my Amazon EC2 machine.
pgAdmin III - is a GUI client to administer and develop PostgreSQL databases. The official downloads page is at http://www.pgadmin.org/download/. I used pgAdmin III version 1.14.2 for MS Windows.
pgAdmin III installation is really trivial: On your local computer, download a version for your OS, unzip, start the msi. Follow the wizard. Done.
In order to connect to the postages instance in the cloud, I chose localhost tunnel ssh option as secure and not requiring any tweaks to the PostgreSQL config. I’ll be pretending localhost to the postgresql instance. Here is the official manual page for PostgreSQL tunnels http://www.postgresql.org/docs/8.4/interactive/ssh-tunnels.html
ssh client is needed to establish the tunnel. If you are on Linux or cygwin, make sure you have at your local machine your private key for your Amazon EC2 account in your ssh home:
ls -l /home/your_user_name/.ssh/id_rsa
-rw------- 1 your_user_name ... id_rsa
Note that .ssh must have -rwx------ (chmod 700) and id_rsa must have -rw------- (chmod 600) permissions, otherwise self-respecting ssh will refuse to give you a protected connection.
Now, let’s bring your_ec_public_ip’s port 5432 (the default PostgreSQL port), to your local machine’s (localhost) port 5432 using default ssh port 22 as the tunnel. Execute:
ssh -o TCPKeepAlive=yes -o ServerAliveInterval=300 -L 5432:localhost:5432 ec2-user@your_ec_public_ip -p 22
Your EC2 login shall pass and you shall see the remote console.
Now, to get the database, you start the pgAdmin III, and set a database server connection, pretending you have the database on your localhost. It shall be available as long as the tunnelled connection is alive.
P.S. If you are on MS Windows, and you've got only Putty on your hands, just complement your Amazon EC2 connection with tunnel like this:
I.e. Source port = L:5432, Destination = localhost:5432
And of course, you have already set a keepalive, so your connection would not drop while you are idle (errrr, thinking):
