Assume you have access to a remote server that has access to a PostgreSQL server, but when connecting you get loaded straight into a psql session.

Now you want to download a database dump from that server, of testing or back up purposes.

You cannot create a PostgreSQL dump from psql, you’ll need to use pg_dump instead. To start a shell prompt from a psql session, type:

\!

This will give you shell, as if you’ve never ended up in psql in the first place.

I suggest the following command to create a dump:

pg_dump --verbose --no-acl --no-owner --format=c <dbname> > db.dump

Where you replace <dbname> with the actual name of the database you want to dump.

I’m using --format=c here which formats the dump in a format specific for pg_restore. If you like a plain SQL dump you can drop it, but I suggest to use --compress=9 instead.

Here it gets tricky. I got this from AskUbuntu but I struggled with it, so I’ll be a bit more wordy.

In you existing SSH session (which is a shell prompt now):

• Press ENTER to start with a clean line
• Press ~ and SHIFT+C
• Your prompt will change to ssh>, type here -R 22042:localhost:22
• It will say Forwarding port.
• Press ENTER again to go back to the prompt

The sequence in the first two steps is weird, but you can read more about the SSH escape characters in the man page.

Important note: This guide assumes you have an SSH server running on your local machine. If not, make sure it’s installed and start it with:

sudo systemctl start sshd

Now we have the tunnel set up, run the following command again in the same prompt:

scp -P 22042 db.dump <user>@localhost:

(with <user> your username on the local machine)

And that’s it! Now you’ll find the dump in the $HOME of you’re local machine.

Loading with pg_restore is simple, just specify the database:

pg_restore --dbname=<dbname> db.dump