Assume you have access to a remote server that has access to a PostgreSQL
server, but when connecting you get loaded straight into a
Now you want to download a database dump from that server, of testing or back up purposes.
Breaking out psql
You cannot create a PostgreSQL dump from
psql, you’ll need to use
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
Creating the dump
I suggest to create a gzipped dump, because it will be faster to download it. To create that, run:
pg_dump -Z <dbname> > dump.sql.gz
(obviously you’ll need to replace
<dbname> with the actual name of the
database you want to dump)
Open SSH tunnel back to local machine
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):
ENTERto start with a clean line
- Your prompt will change to
ssh>, type here
- It will say
ENTERagain 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
Download the dump
Now we have the tunnel set up, run the following command again in the same prompt:
scp -P 22042 dump.sql.gz <user>@localhost:
<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.