pg_dump via SSH Tunnel
I’ve not had all that much experience with tunneling via SSH before, usually if I want to access a database I’m using something like pgAdmin4 so setting up an SSH tunnel by hand doesn’t usually need to be done.
Today that changed for the first time, I wanted to dump some databases hosted on a server which required access via a tunnel. This turned out to be super simple, here are the two commands I used.
First, we setup a tunnel which connects my
5433 to the tunnel database hosts
5432, I do this using the
-L flag on the SSH command which allows me to bind a local port to a remote port. In the below command, db-host is the host of my database, and firstname.lastname@example.org are the credentials for my tunnel server.
ssh -L localhost:5433:db-host:5432 email@example.com
That will open an SSH session which will stick around until killed, just like any other SSH session.
Once that’s running, I can then open a new terminal on my machine and run the normal
pg_dump command using the bound port.
pg_dump -f ~/Dekstop/db.sql -d postgres -h localhost -p 5433
That’s it! Successful binding of a port to achieve tunneling for interacting with a postgres database via command line.