Back up/Restore with pgAdmin3 through SSH Tunnel

When first using pgAdmin3, i thought it is pretty cool that it supports SSH Tunnel, especially when its newer version pgAdmin 4 does not at the moment. It was all working fine for a long while until ... I need to create a backup. Turn out the feature is still quite buggy. You can't seem to Backup or Restore DB at all.

For example, if i try to backup my PostgreSQL DB through SSH tunnel on pgAdmin3, here how it will go:

So the error is "Password authentication failed...", my guess would be that it did not pass the correct password. And the --no-password added to the confusion. As it turns out, the app did give a warning when you first tick that "Use SSH Tunneling" like this:

Therefore, no wonder it cannot find any password to pass. Out of frustration, I did try to edit ~/.pg_pass directly without any success.

In the end, I've given up on the feature and set up the SSH tunneling manually with this command:

ssh -fNg -L 5555:localhost:5432 {username}@{server_ip}  

Basically, it maps port 5432 from remote server to port 5555 of my localhost. Changing the port is to avoid any conflict with local postgresql server (you can use the same port 5432 if you want to). So the setting would roughly be the same as before except no "Use SSH Tunneling" and the port change to 5555

With this, i am able to backup/restore the DB as I wanted. And it seems faster to connect to DB as well compare to built in SSH tunneling... I guess in the case of setting up SSH tunneling, Terminal:1 and pgAdmin3:0. This post is to remind myself not to use the built in anymore. To be fair, pgAdmin 3 is no longer support, so there is no use to hope for an update on this.

After finishing with the tunneling, you can run this to stop any further ssh connections:

sudo killall ssh  

Furthermore, since pgAdmin4 does not support built-in ssh tunneling, so this is the only way out anyway. Hope it saves you some time, not to look for why backup/restore not woking


comments powered by Disqus