Daily Backup on a VPS

What is it? #

This lesson builds a real daily backup for a database on a VPS, one piece at a time, explaining why each piece exists.

The shape is simple: a dedicated backup user, a directory with the right permissions, a pg_dump with a timestamped filename, compression, deletion of old files, and a check that it actually worked.

Every one of those steps exists because of a specific way backups fail in practice. A backup running as a superuser is an unnecessary risk. A backup without a timestamp overwrites yesterday's good copy with today's broken one. A backup with no retention fills the disk and takes the database down with it.

The next lesson turns this into an automated script with proper error handling. This lesson is about understanding each command first.

Think of it like this #

Taking a photograph of your accounts every night and filing it by date.

If every photo is saved as "accounts.jpg", you only ever have last night's — and if last night's camera was broken, you have a broken photo and nothing else. Dating each file is what gives you history to fall back on.

Keeping every photo forever eventually fills the cupboard, and a full cupboard means tonight's photo cannot be filed at all. So you keep a sensible number and discard the rest.

And a photo you never look at might be a picture of the lens cap. Checking is part of the job, not an optional extra.

Simple example #

A single VPS running an application and its PostgreSQL database.

The goal: every night at 02:00, a compressed, dated dump lands in /var/backups/postgresql, copies older than fourteen days are removed, and anything that goes wrong is visible rather than silent.

Code #

# ---------- STEP 1: a dedicated backup role, with minimum privileges ----------

sudo -u postgres psql

-- A role that can read everything but change nothing.
CREATE ROLE backup_user WITH LOGIN PASSWORD 'a-long-random-password';

-- pg_read_all_data is a built-in role (PostgreSQL 14+) granting SELECT on
-- every table — exactly what a backup needs and nothing more.
GRANT pg_read_all_data TO backup_user;

-- On PostgreSQL 13 and earlier, grant it manually:
--   GRANT CONNECT ON DATABASE shop TO backup_user;
--   GRANT USAGE ON SCHEMA public TO backup_user;
--   GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup_user;

\q

# ---------- STEP 2: the password file (NEVER put passwords in the command) ----------

# A password typed on the command line is visible to every user via "ps",
# and ends up in shell history. Use ~/.pgpass instead.

# Format:  hostname:port:database:username:password
echo "localhost:5432:*:backup_user:a-long-random-password" > ~/.pgpass

chmod 600 ~/.pgpass       # !! REQUIRED — PostgreSQL IGNORES the file otherwise
ls -l ~/.pgpass           # must show -rw-------

# Test that it works with no password prompt:
psql -h localhost -U backup_user -d shop -c "SELECT 1;"

# ---------- STEP 3: the backup directory ----------

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo chmod 700 /var/backups/postgresql
#      700 = only the owner may read, write or enter.
#      Backups contain ALL your data — treat the directory as sensitive.

# Check there is actually room before relying on it:
df -h /var/backups

# ---------- STEP 4: the backup command itself ----------

# Timestamped filename: sortable, unambiguous, never overwrites.
pg_dump -h localhost -U backup_user -d shop \
        -F c \
        -f /var/backups/postgresql/shop_$(date +%F_%H-%M).dump

# Produces: shop_2026-09-23_02-00.dump
#   %F = 2026-09-23   (YYYY-MM-DD sorts correctly as text — use this format)
#   -F c = custom format: already compressed, and supports selective restore

# ---------- STEP 5: compression ----------

# -F c is ALREADY compressed (zlib). Control the level:
pg_dump -F c -Z 9 -d shop -f shop.dump      # 9 = smallest, slowest
pg_dump -F c -Z 1 -d shop -f shop.dump      # 1 = fastest, larger
# Default is 6, which is a good balance. Only change it if you have measured.

# Plain SQL needs external compression. zstd is faster and smaller than gzip:
pg_dump -F p -d shop | zstd -T0 -o shop.sql.zst
#   -T0 uses all CPU cores

# Compare what you actually get:
ls -lh /var/backups/postgresql/

# ---------- STEP 6: the globals (roles and passwords) ----------

# pg_dump does NOT include roles. Back them up too, or a restore onto a new
# server will have no users at all.
pg_dumpall -h localhost -U postgres --globals-only \
    -f /var/backups/postgresql/globals_$(date +%F).sql

# This needs a superuser, because it reads password hashes.
# It is small and fast, so daily is fine.

# ---------- STEP 7: retention — delete old backups ----------

# Keep 14 days:
find /var/backups/postgresql -name "shop_*.dump" -mtime +14 -delete
#   -mtime +14   modified more than 14 days ago
#   -name        !! ALWAYS restrict by name. Without it you could delete
#                   anything else that happens to be in the directory.

# ALWAYS test with -print BEFORE using -delete:
find /var/backups/postgresql -name "shop_*.dump" -mtime +14 -print
# Read the list. Confirm it is what you expect. THEN run the -delete version.

# ---------- STEP 8: verify the backup is real ----------

BACKUP=/var/backups/postgresql/shop_2026-09-23_02-00.dump

# Is it non-trivially sized? An empty dump is still a file.
ls -lh "$BACKUP"

# Is it readable and structurally valid?
pg_restore --list "$BACKUP" > /dev/null && echo "readable" || echo "CORRUPT"

# How many tables does it contain?
pg_restore --list "$BACKUP" | grep -c "TABLE DATA"

# THE REAL TEST — restore it and query it:
createdb -U postgres verify_tmp
pg_restore -U postgres -d verify_tmp "$BACKUP"
psql -U postgres -d verify_tmp -c "SELECT count(*) FROM orders;"
dropdb -U postgres verify_tmp

# ---------- STEP 9: get it OFF this server ----------

# A backup on the same machine does not survive that machine being lost.

# To object storage (works with S3, Backblaze B2, Cloudflare R2, and others):
aws s3 cp /var/backups/postgresql/shop_$(date +%F)*.dump \
    s3://my-backups/postgresql/ --storage-class STANDARD_IA

# Or to another host over SSH:
rsync -az --remove-source-files \
    /var/backups/postgresql/ backup@other-host:/backups/db/

# Enable VERSIONING or OBJECT LOCK on the bucket. Without it, anyone who
# compromises the server can delete the backups along with the database.

# ---------- STEP 10: schedule it ----------

sudo crontab -u postgres -e

# Add:
0 2 * * * pg_dump -h localhost -U backup_user -d shop -F c -f /var/backups/postgresql/shop_$(date +\%F_\%H-\%M).dump

# !! In crontab, % MUST be escaped as \%  — an unescaped % is treated as a
# !! newline, and the command silently does the wrong thing. This catches
# !! almost everyone once.

# Better: put the logic in a script and call that instead — which is
# exactly what the next lesson builds.
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

How it works #

The dedicated role applies least privilege. A backup needs to read everything and change nothing, and pg_read_all_data expresses exactly that. If the credentials leak, the damage is limited to disclosure rather than destruction.

The .pgpass file exists because a password on a command line is visible in the process list to every user on the machine, and is recorded in shell history and often in logs. PostgreSQL requires 600 permissions on the file and silently ignores it if they are wider — which produces the confusing symptom of a script that works interactively and hangs on a password prompt under cron.

The timestamped filename is what makes history possible. A fixed filename means each run overwrites the previous one, so a dump that fails halfway through replaces your last good copy with a truncated file. Using %F (YYYY-MM-DD) also means filenames sort chronologically as plain text, which makes scripting straightforward.

Custom format (-F c) compresses as it writes and stores a table of contents, which is what allows selective restore later. There is rarely a reason to produce plain SQL for routine backups.

Globals are separate because roles and passwords live at cluster level. Skipping them produces a restore where the data exists but nobody can own or access it.

Retention protects the disk. Backups grow, disks do not, and a full disk stops PostgreSQL accepting writes — a backup job that takes the database down is a real and avoidable failure. The -name filter on find matters more than it looks: find /path -mtime +14 -delete without a name pattern will delete anything in that directory older than the cutoff. Always run it with -print first.

Verification is the step that separates a backup from a hopeful file. pg_restore --list proves the file is structurally readable, which catches truncation and corruption cheaply. Only an actual restore proves the contents are usable, which is why it belongs on a schedule.

Offsite copies address the failure mode where the server itself is lost or compromised. Versioning or object lock on the destination matters specifically for ransomware: an attacker with server access can otherwise delete the backups as easily as the database.

Real-world use #

On a single VPS, the daily backup script is frequently the only thing standing between a mistake and permanent data loss. It deserves more care than it usually gets.

The percent-sign escaping in cron catches almost everyone once. An unescaped % becomes a newline, so the command runs truncated — often producing a dump with no filename argument, which writes to standard output and vanishes. Putting the logic in a script and calling the script avoids the problem entirely, which is the main structural reason the next lesson exists.

Think about timing. pg_dump reads every row, which competes with application traffic and, on a large database, holds a long transaction that blocks VACUUM for its duration. Run it when the system is quiet. If a dump takes hours, that is a signal the database has outgrown logical backups and wants the physical approach from the WAL and PITR lessons.

Check the size of each backup, not just its existence. A dump that suddenly drops from 2 GB to 4 KB is the classic signature of a failure that reported success — a permissions change, an expired password, a renamed database. Comparing against the previous run catches it immediately.

Watch disk space actively rather than relying on retention alone. If the database grows faster than expected, fourteen days of backups may quietly become more than the disk holds.

Finally, document the restore procedure next to the backup script, and make sure someone other than its author has followed it successfully. Backups are taken by scripts; restores are performed by people, usually under pressure.

Common mistakes #

• Forgetting to escape % as % in crontab, so the command silently runs truncated.
• Leaving ~/.pgpass with permissions wider than 600, which makes PostgreSQL ignore it entirely.
• Using a fixed filename, so a failed run overwrites the last good backup.
• Running find ... -delete without a -name filter, risking deletion of unrelated files.
• Keeping backups only on the same server, where they are lost with the machine.

Practice #

On a test VPS, work through all ten steps by hand before automating anything. Create the backup role, set up .pgpass with correct permissions and prove it works without a prompt, create the directory, take a timestamped compressed dump, back up the globals separately, and verify the dump with pg_restore --list. Then restore it into a scratch database and confirm a row count. Finally, run the retention find with -print and read the output carefully before ever adding -delete.