How to Back Up Postgres on a VPS (and Prove the Restore Works)

Hi, I'm Arthur. Picture this: a migration meant for staging runs against production, and a table is gone. You open your backup folder and find a file from three weeks ago, or nothing at all because the cron job has been failing quietly since a disk filled up.

Most "back up Postgres" guides stop at one pg_dump command. That's the easy part. What saves you is knowing the job ran, having a copy that isn't on the same disk, and having restored it at least once. This guide builds all three on a single Ubuntu VPS.

What you need

• A VPS running Ubuntu with Postgres installed and root access. HelloServer offers VPS plans with full root access, and any similar provider works the same way.
• Somewhere off the server to store copies: any S3-compatible bucket, Backblaze B2, or similar.
• About 30 minutes.

Why pg_dump and not a file copy

Copying Postgres's data directory while it's running can give you files that don't match each other. pg_dump takes a consistent snapshot of the database without blocking writers. The -Fc (custom) format also compresses the dump and lets you restore single tables with pg_restore.

Step 1: A place for backups

sudo mkdir -p /var/backups/postgres
sudo chown postgres:postgres /var/backups/postgres
sudo chmod 700 /var/backups/postgres

Only the postgres user can read it. A dump contains all your data, so treat the folder like the database itself.

Step 2: Set up offsite storage

Install rclone and configure a remote as the postgres user, so the cron job can find the config later:

sudo apt install -y rclone
sudo -u postgres rclone config

Follow the prompts to add your storage provider and name the remote remote. Then check it works:

sudo -u postgres rclone lsd remote:

Dumps leave your server here, so encrypt them. rclone has a crypt remote type that wraps another remote: create one pointing at remote:my-backups and use it in the script below instead of the plain one.

Step 3: The script

Save this as /usr/local/bin/pg-backup.sh:

#!/usr/bin/env bash
set -euo pipefail

DB="myapp"
DIR="/var/backups/postgres"
STAMP="$(date +%F_%H%M)"
FILE="$DIR/${DB}_${STAMP}.dump"

# 1. Dump to a temp file first, so a half-written file never looks valid
pg_dump -Fc "$DB" > "$FILE.tmp"
test -s "$FILE.tmp"
mv "$FILE.tmp" "$FILE"

# 2. Roles and passwords live outside any single database
pg_dumpall --globals-only > "$DIR/globals_${STAMP}.sql"

# 3. Copy offsite
rclone copy "$FILE" remote:my-backups/postgres/
rclone copy "$DIR/globals_${STAMP}.sql" remote:my-backups/postgres/

# 4. Prune: 7 days locally, 30 days offsite
find "$DIR" -type f \( -name '*.dump' -o -name 'globals_*.sql' \) -mtime +7 -delete
rclone delete remote:my-backups/postgres/ --min-age 30d

# 5. Tell the monitor we finished
curl -fsS -m 10 --retry 3 https://hc-ping.com/YOUR-UUID-HERE > /dev/null

A few details are worth understanding before you copy it:

• set -euo pipefail stops the script at the first failure. That matters because step 5 only runs if everything before it worked.
• pg_dumpall --globals-only saves roles and their passwords. Without it, a restore onto a fresh server fails because the app's database user doesn't exist.
• The pruning lines keep the folder and bucket from growing forever. Disks that fill up are one of the most common reasons backup jobs stop.

Make it executable and run it once by hand:

sudo chmod +x /usr/local/bin/pg-backup.sh
sudo -u postgres /usr/local/bin/pg-backup.sh
ls -lh /var/backups/postgres

Check that the file has a real size and that it also appears in your bucket.

Step 4: Schedule it and get alerted when it fails

Add a cron job for the postgres user:

sudo crontab -u postgres -e

15 3 * * * /usr/local/bin/pg-backup.sh >> /var/backups/postgres/backup.log 2>&1

That runs at 03:15 every night. Now the monitoring: a service like Healthchecks.io gives you a unique ping URL and emails you if no ping arrives on schedule. Paste that URL into step 5 of the script. This catches the failure cron never reports: the job that silently stopped running. Set the expected period to one day, with a grace period of an hour or two.

Step 5: The restore drill

This is the step that turns a file into a backup. Restore into a scratch database, not your live one:

sudo -u postgres createdb myapp_restore_test
sudo -u postgres pg_restore -d myapp_restore_test --no-owner \
  /var/backups/postgres/myapp_2026-10-14_0315.dump
sudo -u postgres psql myapp_restore_test -c "SELECT count(*) FROM users;"
sudo -u postgres dropdb myapp_restore_test

Compare the row count with production. Note how long the restore took, since that's your real downtime in an emergency.

A full rebuild on a brand-new server goes in this order: install Postgres (same major version or newer), restore the globals with sudo -u postgres psql -f globals_....sql, then createdb and pg_restore the dump. Do this drill once now, not for the first time during an outage.

If Postgres runs in Docker

Run the dump through docker exec, but don't add -t:

docker exec db pg_dump -U postgres -Fc myapp > myapp.dump

The -t flag allocates a terminal, which can corrupt binary output, and you won't notice until you try to restore.

Where this setup falls short

• You can lose up to a day of data. A nightly dump only captures that moment. If that's too much, look at continuous archiving with a tool like pgBackRest or WAL-G for point-in-time recovery.
• Big databases slow down while dumping. It doesn't block writes, but it uses CPU and disk. Schedule it for your quietest hour.
• Same provider is still a single risk. For a second layer, a small separate VPS can act as a backup target over SSH with rsync. A second small plan from HelloServer would work for that.

Your checklist

1. Run the script once by hand and check the file size.
2. Confirm the copy landed in your bucket.
3. Set up the monitor and let it alert you once, on purpose, by skipping a run.
4. Do the restore drill and write down how long it took.
5. Put a reminder on your calendar to repeat step 4 every few months.

If you want a safe place to try this end to end, a small VPS from HelloServer is enough, because you can break things and rebuild without touching a real project. How often do you actually test your restores? I'd like to know whether anyone does it on a schedule.