In this article I’m writing a way to retrieve a backup of a PostgreSQL database in a remote server when you have access to the server machine via SSH.

This would come handy when you can’t directly connect to the database server from your local machine. For example, in a situation where the PostgreSQL server only listens to localhost for connections or there is firewall preventing you from accessing the database port on the server machine.

Following are the steps, and I will go throgh them in detail.

  1. Open an SSH tunnel from your local machine to the PostgreSQL server to bind ports
  2. Locally run pg_dump to take the database backup
  3. Close the SSH tunnel
  4. Restore the database dump locally

Open an SSH tunnel

An SSH tunnel listens on a local port and forwards all traffic to a port on the remote machine. Traffic sent to the remote port can arrive on its localhost address, or different bind address if desired; it does not appear as coming from your local machine. This command creates a secure tunnel from the client machine to the remote machine.

We are going to use -L [bind_address:]port:host:hostport option with ssh command to create the tunnel. The process will run in the foreground by default, so we can add -fNT options to make it run in the background.

However once we finish, to close the tunnel (and the process), we will use ssh master mode(-M) and a path to specify the socket file (-S).

In the following example, I set my local machine’s port 3333 to bind with the remote binding address (127.0.0.1) and port 5432.
Assuming PostgreSQL server listens on 127.0.0.1:5432 on it’s machine, then when I connect to 3333, PostgreSQL server will see my connection.

ssh -L 3333:127.0.0.1:5432 \
-M -S /tmp/db-backup-socket \
-fNT [email protected]

See reference to the options on https://linux.die.net/man/1/ssh

Take the database backup using pg_dump

We can now use pg_dump to connect to our localhost port 3333 and take the backup.

For example, in the following command, I am taking a backup of my_database which is technically in the PostgreSQL server but appears as if it is in my machine due to the SSH tunnel. Additionally I use more options such as --jobs to improve backup speed.

pg_dump -p 3333 -h localhost -U postgres \
-d my_database --clean \
--exclude-table=audit_* --jobs=8 -Fd --file=/tmp/db_dump_location

See reference for pg_dump options on https://www.postgresql.org/docs/12/app-pgdump.html

Close the SSH tunnel

Once you have all the backups you need, you should close the SSH tunnel.

We have to now use the socket file path to specify which SSH process to close by using -S option.

ssh -S /tmp/db-backup-socket -O exit [email protected]

Restore the database locally

Once you have the backup, then you can use pg_restore to restore the backup to a specific database locally.

In the following example, I restore the backup to my database called ‘my_local_database’ and providing the backup file location.

pg_restore -h localhost -U postgres \
--dbname=my_local_database --jobs=8 --clean /tmp/db_dump_location

Reference for pg_restore: https://www.postgresql.org/docs/12/app-pgrestore.html