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 tunneling 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 [email protected]

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 [email protected].

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.