How to back up a PostgreSQL database, step by step

PostgreSQL gives you pg_dump and pg_dumpall for consistent backups. Here is how to make them, automate them and keep them off-server.

1. Manual backup with pg_dump

A single database, compressed:

pg_dump -U postgres mydb | gzip > mydb-$(date +%F).sql.gz

All databases and roles:

pg_dumpall -U postgres | gzip > all-$(date +%F).sql.gz

2. Restore

gunzip < mydb-2026-07-18.sql.gz | psql -U postgres mydb

3. Automate with cron

0 3 * * * PGPASSWORD=PASSWORD pg_dump -U postgres mydb | gzip > /mnt/backup/mydb-$(date +\%F).sql.gz

Keep copies on a Backup Box

A Backup Box gives you dedicated storage that you mount as an extra disk, so backups do not sit on the same server as the database. The address, username and password are on the Backup Box page in your panel.

Mount as an extra disk (SMB)

apt-get install -y cifs-utils
mkdir -p /mnt/backup
mount -t cifs //BACKUP_BOX_ADDRESS/backup /mnt/backup -o username=USER,password=PASSWORD,vers=3.0

Mount over SFTP (SSHFS)

apt-get install -y sshfs
sshfs -p 22 USER@BACKUP_BOX_ADDRESS:/ /mnt/backup

With /mnt/backup on the Backup Box, your copies are safe off-server. See also the general backup guide.