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.
- Open an SSH tunnel from your local machine to the PostgreSQL server to bind ports
- Locally run
pg_dumpto take the database backup
- Close the SSH tunnel
- 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@example.com
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 firstname.lastname@example.org
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