rw-r--r--

chown -R toon blog/ && chmod -R u=rw,go=r $_

How to download a PostgreSQL dump from server

posted on 2020-10-14

Situation

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.

Breaking out psql

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.

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):

  • 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

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:

(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.