Back up PostgreSQL on a VPS
pg_dump, cron, and why that is not enough in production.
You run PostgreSQL on a VPS (OVH, Scaleway, Hetzner, DigitalOcean) and you want automatic backups. Here is the classic approach with pg_dump and cron, then its real limits in production.
All the code below works on a standard Linux distribution with the PostgreSQL client installed (the postgresql-client package).
1. Back up a database with pg_dump
pg_dump exports a PostgreSQL database to a file. The custom format (-Fc) is compressed and restores with pg_restore, database by database.
pg_dump -Fc -U postgres -h localhost my_db > /var/backups/my_db_$(date +%F).dump
For every database at once, use pg_dumpall (SQL format, uncompressed).
2. Automate with cron
Put the command in a script, add a retention rule, make it executable, then schedule it in crontab.
#!/usr/bin/env bash
set -euo pipefail
DEST=/var/backups/postgres
mkdir -p "$DEST"
pg_dump -Fc -U postgres my_db > "$DEST/my_db_$(date +%F_%H%M).dump"
# retention: delete dumps older than 7 days
find "$DEST" -name '*.dump' -mtime +7 -delete
Then in crontab -e: 0 3 * * * /usr/local/bin/backup-pg.sh (every day at 3am).
3. Encrypt and move the backup off-site
A plaintext dump, on the same server as the database, protects you from nothing. Encrypt it and copy it elsewhere: another provider, another region, an S3 bucket.
age is a modern, simple and robust encryption tool, a perfect fit for this.
# encrypt the dump with age, then send it to an S3 bucket
age -r age1example... -o my_db.dump.age my_db.dump
aws s3 cp my_db.dump.age s3://my-bucket/postgres/
4. Restore
To restore, decrypt the dump then replay it with pg_restore into a target database. Always test the restore in a separate database, never in production.
age -d -i my_key.txt -o my_db.dump my_db.dump.age
pg_restore -U postgres -d my_db_restored --clean my_db.dump
The limits of pg_dump + cron
- A cron that fails does so silently: you only find out the day you need to restore.
- No alert if the VPS is off, if the disk is full, or if pg_dump crashes.
- Retention, encryption, off-site copy and key rotation are all yours to write and maintain.
- A backup you never tested is a hope, not a guarantee.
- If the VPS burns or is compromised, dumps stored next to it disappear with it.
Do it in 5 minutes with rlbk
rlbk installs a lightweight agent on your VPS, schedules your PostgreSQL backups, encrypts them with age directly on the server, sends them to rlbk or to your own S3 bucket, and alerts you the moment one fails.
Your data only ever travels encrypted, and you can test a restore in an isolated environment without ever touching production.