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.