Welcome, Guest! Registration


Thursday, 2019-02-21
Main » 2012 » March » 23 » Getting pgAdmin working with PostgreSQL at Amazon EC2
2:42 AM
Getting pgAdmin working with PostgreSQL at Amazon EC2

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):
Attachments: Image 1 · Image 2
Views: 8532 | Added by: loc2log | Tags: pgAdmin, amazon ec2, Tunnel, postgresql, Linux | Rating: 5.0/1
Total comments: 5
0 Spam
5 dhiren_patel  
Thanks man. You are very helpful.

0 Spam
4 andru  
Not so far I have found new cool tool to work with postgresql - Valentina Studio. Its free edition can do things more than many commercial tools!!
I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

0 Spam
3 ep  
It looks like port 5432 is being used by something, e.g. your local postgresql instance :-) One option is changing the "incoming" port to some other value, which you know is free to use, e.g. 63333:localhost:5432, then connect your localhost (mac) client to that new port.

You can check currently used port numbers with "netstat -ln" from command prompt.

0 Spam
1 john  

0 Spam
2 ep  
You're welcome! Glad if it helped a bit. smile

Only registered users can add comments.
[ Registration | Login ]