Connecting to remote Postgresql database with SSH tunnelling

In this post I discuss about how to connect to remote Postgresql database  with SSH tunneling. Basically, with SSH you are capable to do almost whatever  that you can do with a normal computer including, port forwarding, X forwarding, audio and video forwarding and many other awesome things. Therefore, connecting to remote postgres database should not be any problem at all and matter of fact it is quite simple. You just only need to have OpenSSH or Putty plus Postgresql database locally installed in your machine.
In order to do SSH tunnelling for Postgresql database you the following command,

$ ssh -L [Local Port]:[Host Domain Name]:[Remote port] example@yourssh

For example,

$ ssh -L 3333:madadipouya.com:5432 kasra@madadipouya.com

In the above example, Postgresql database would be available on port ‘3333’  of your local machine. Also ‘madadipouya.com’ in this example is the remote host domain name and ‘5432’ is port number of that Postgresql. Lastly, is your SSH account, which in this example is ‘kasra@madadipouya.com’.
Now after this step, you should be able to access to the remote Postgresql database with the following command,

$ psql -h localhost -p [Local Port] [Database Name] -U [SSH username]

Example,

$ psql -h localhost -p 3333 TestDB -U kasra

In the example, ‘3333’ is the port number that used in ssh command in front of -L argument, ‘TestDB’ is the name of database you want to access, and ‘kasra’ is your SSH account name.

Send your idea and information to kasra@madadipouya.com

Leave a Reply