Set Up PostgreSQL Backups With pg_dump + Restore Test

A PostgreSQL dump that you’ve never restored is a guess, not a backup. This tutorial walks through a practical PostgreSQL backup and restore setup on a real VPS. You’ll automate pg_dump, set retention, add optional encryption, and run a repeatable restore test after changes.

The steps assume Ubuntu 24.04/26.04 or Debian 12/13 on a VPS or dedicated server. AlmaLinux/Rocky work too, but some package names and paths differ.

If you’re backing up customer data or production apps, run this on a HostMyCode VPS. If you need local NVMe and steadier I/O for larger databases, use dedicated servers.

What you’ll build (and what it does not do)

You’ll set up daily logical backups with pg_dump. You can also add a globals dump using pg_dumpall.

This approach is quick to deploy and easy to migrate between servers. It’s a strong fit for small to mid-sized databases.

• Includes: automated dumps per database, compression, retention, logging, verification, and a restore test into a scratch DB.
• Also includes: optional GPG encryption and copying backups off the server via SFTP.
• Does not include: physical/base backups (pg_basebackup), PITR, or WAL archiving. If you need point-in-time recovery, plan that separately.

Prerequisites and safety checks

Before you automate anything, confirm three basics. PostgreSQL must be reachable locally, you need enough disk space, and you must know which user will run dumps.

1) Confirm PostgreSQL is reachable

sudo -u postgres psql -c "SELECT version();"

If this fails, fix local auth first. A common cause is peer auth on localhost.

Don’t move on until local psql works reliably.

2) Estimate required space

Logical dumps often compress well, but the ratio depends on your data. Start by checking database sizes:

sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database WHERE datistemplate = false ORDER BY pg_database_size(datname) DESC;"

Plan for: sum(db_sizes) * compression_factor * retention_days.

Gzip often lands around 20–40% for typical web workloads. Large JSON/text blobs (or already-compressed data) may barely shrink.

3) Decide the backup user strategy

The simplest option is to run dumps as the postgres OS user over the local socket. That avoids passwords and keeps the pipeline local to the host.

If you also want roles and privileges, include a globals dump with pg_dumpall --globals-only. That requires a superuser connection.

Install required tools (Ubuntu/Debian and Alma/Rocky)

You’ll need PostgreSQL client tools plus compression utilities. If you want encryption, install GPG as well.

Ubuntu/Debian

sudo apt update
sudo apt install -y postgresql-client pigz gnupg jq

pigz runs gzip in parallel, which helps on multi-core VPS plans. If you prefer plain gzip, you can skip it.

AlmaLinux/Rocky

sudo dnf install -y postgresql pigz gnupg2 jq

If you’re unsure which client version to install, match your server’s major version. Newer clients can usually dump older servers.

Staying close in major versions reduces edge cases around newer features.

Create backup directories with tight permissions

Keep backups in a predictable location with restrictive permissions. This walkthrough uses /var/backups/postgresql.

sudo install -d -m 0700 -o postgres -g postgres /var/backups/postgresql
sudo install -d -m 0700 -o postgres -g postgres /var/backups/postgresql/logs

Common mistake: dropping dumps into a world-readable directory like /tmp or a shared home folder.

Backups contain the same sensitive data as your live database.

Write the backup script (pg_dump + globals + retention)

You’ll create one script that runs the full backup end-to-end:

• dumps each non-template database in custom format (-Fc)
• captures globals (roles, grants) once per run
• compresses dumps
• retains N days
• logs everything

Create /usr/local/sbin/pgdump-backup.sh:

sudo tee /usr/local/sbin/pgdump-backup.sh >/dev/null <<'EOF'
#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgresql"
LOG_DIR="${BACKUP_DIR}/logs"
RETENTION_DAYS="14"

# Timestamp format keeps backups sortable.
TS="$(date -u +%Y%m%dT%H%M%SZ)"
HOST="$(hostname -f 2>/dev/null || hostname)"

LOG_FILE="${LOG_DIR}/pgdump-${TS}.log"

# Use postgres local socket by default.
export PGHOST="/var/run/postgresql"

umask 077

exec > >(tee -a "${LOG_FILE}") 2>&1

echo "[$(date -u +%F' '%T)] Starting pg_dump backups on ${HOST}"

echo "[$(date -u +%F' '%T)] Dumping globals (roles/grants)"
sudo -u postgres pg_dumpall --globals-only > "${BACKUP_DIR}/globals-${TS}.sql"

# List databases excluding templates.
DBS=$(sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false;")

for db in ${DBS}; do
  echo "[$(date -u +%F' '%T)] Dumping database: ${db}"

  # Custom format is portable and supports parallel restore.
  sudo -u postgres pg_dump -Fc "${db}" -f "${BACKUP_DIR}/${db}-${TS}.dump"

done

# Compress dumps and globals. pigz uses multiple cores.
echo "[$(date -u +%F' '%T)] Compressing outputs"
if command -v pigz >/dev/null 2>&1; then
  sudo -u postgres pigz -9 "${BACKUP_DIR}"/*.sql "${BACKUP_DIR}"/*.dump
else
  sudo -u postgres gzip -9 "${BACKUP_DIR}"/*.sql "${BACKUP_DIR}"/*.dump
fi

# Retention policy
# Delete files older than RETENTION_DAYS.
echo "[$(date -u +%F' '%T)] Applying retention: ${RETENTION_DAYS} days"
find "${BACKUP_DIR}" -maxdepth 1 -type f \( -name '*.gz' \) -mtime +"${RETENTION_DAYS}" -print -delete

echo "[$(date -u +%F' '%T)] Backup completed" 
EOF

sudo chmod 0750 /usr/local/sbin/pgdump-backup.sh

Why custom format? Dumps created with -Fc restore via pg_restore. They also support selective restores and parallel jobs (-j) on multi-core machines.

Another common mistake: using plain SQL dumps for everything, then discovering the pain during a restore.

SQL dumps work, but they don’t parallelize well. They can also get slow at scale.

(Optional) Encrypt backups with GPG

If you copy backups off the server (you should), encryption reduces risk if remote storage is exposed. Symmetric encryption works, but public-key encryption is usually easier to operate.

With public-key encryption, generate the key on your workstation. Upload only the public key to the database host. Keep the private key offline.

Import a public key on the server

Copy your public key to the server (example uses backup-public.asc):

sudo -u postgres gpg --import /root/backup-public.asc
sudo -u postgres gpg --list-keys

Encrypt in-place after compression

Add this block to the script after compression (replace backup@example.com with your key ID/email):

# Encrypt outputs (optional)
RECIPIENT="backup@example.com"
if sudo -u postgres gpg --list-keys "${RECIPIENT}" >/dev/null 2>&1; then
  echo "[$(date -u +%F' '%T)] Encrypting backups for ${RECIPIENT}"
  for f in "${BACKUP_DIR}"/*.gz; do
    [ -e "$f" ] || continue
    sudo -u postgres gpg --batch --yes --trust-model always -r "${RECIPIENT}" -o "${f}.gpg" -e "$f"
    sudo -u postgres shred -u "$f"
  done
fi

Verification: after the run, you should see only .gpg files.

Also decrypt one file on a safe machine that holds the private key. That confirms the workflow end-to-end.

Run a manual backup once and verify outputs

Run the script manually before scheduling it. Get one clean, boring run first.

sudo /usr/local/sbin/pgdump-backup.sh

Confirm the files and logs look right:

sudo ls -lh /var/backups/postgresql | sed -n '1,30p'
sudo tail -n 80 /var/backups/postgresql/logs/pgdump-*.log

Quick integrity check: list archive contents without restoring anything.

# Pick one dump file from your directory
DUMP_FILE=$(ls -1 /var/backups/postgresql/*.dump.gz 2>/dev/null | head -n 1)

# If you used gzip/pigz, decompress to stdout and ask pg_restore to list
sudo -u postgres bash -c "gzip -dc '${DUMP_FILE}' | pg_restore -l | head -n 30"

If pg_restore -l fails, stop here and fix it while you still have context. Common causes include permissions, full disks, broken dumps, or mismatched tools.

Automate with systemd timer (preferred over cron)

Systemd timers are easier to audit than cron. You get predictable execution, status checks, and logs in the journal.

You’ll create a oneshot service and a daily timer.

Create the systemd service

sudo tee /etc/systemd/system/pgdump-backup.service >/dev/null <<'EOF'
[Unit]
Description=PostgreSQL pg_dump backup
Wants=network-online.target
After=network-online.target

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/pgdump-backup.sh

# Hardening
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=full
ProtectHome=true
EOF

Create the systemd timer

sudo tee /etc/systemd/system/pgdump-backup.timer >/dev/null <<'EOF'
[Unit]
Description=Run PostgreSQL pg_dump backup daily

[Timer]
OnCalendar=*-*-* 02:15:00
Persistent=true
RandomizedDelaySec=15m

[Install]
WantedBy=timers.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now pgdump-backup.timer

Confirm it’s scheduled:

systemctl list-timers --all | grep -E 'pgdump-backup|NEXT'
systemctl status pgdump-backup.timer --no-pager

Run it on-demand (the same code path the timer uses):

sudo systemctl start pgdump-backup.service
sudo journalctl -u pgdump-backup.service -n 200 --no-pager

If you insist on cron, point cron at the same script. Keep the cron entry simple.

On modern distros, the timer is usually the cleaner option.

Restore test: prove you can recover (repeatable procedure)

This is where many backup plans fail. People don’t test restores.

A restore test catches missing roles, broken ownership, extension mismatches, and the classic “it dumped fine but won’t restore” problem.

You’ll restore into a scratch database on the same server. On small servers, run this off-hours. Drop the test database afterward.

1) Pick a dump file and identify the database name

Example dump naming: myapp-20261106T021500Z.dump.gz. That implies a database named myapp.

ls -1 /var/backups/postgresql | tail -n 10

2) Create a scratch database

Create a new database name so you can’t overwrite production. This example uses myapp_restore_test.

DB="myapp"
TEST_DB="${DB}_restore_test"

sudo -u postgres psql -v ON_ERROR_STOP=1 <<SQL
DROP DATABASE IF EXISTS ${TEST_DB};
CREATE DATABASE ${TEST_DB};
SQL

Common mistake: restoring into the original database by accident.

Treat restore tests like fire drills. Always use a new name.

3) Restore (using pg_restore)

If the dump is gzip-compressed, stream it into pg_restore. Use parallel jobs (-j) if you have CPU headroom.

DUMP_GZ=$(ls -1 /var/backups/postgresql/${DB}-*.dump.gz | tail -n 1)

# Use 4 jobs as a safe default on small VPS; adjust to CPU cores.
sudo -u postgres bash -c "gzip -dc '${DUMP_GZ}' | pg_restore -d '${TEST_DB}' -j 4 --no-owner --role=postgres"

About ownership: restores often fail on a new host because roles don’t exist yet.

For a test restore, --no-owner is fine. For real migrations, restore roles first (see globals below) or create matching roles.

4) Sanity-check the restored data

Minimum bar: you can connect, enumerate tables, and run a query or two that matters.

sudo -u postgres psql -d "${TEST_DB}" -c "SELECT count(*) FROM pg_catalog.pg_tables;"
sudo -u postgres psql -d "${TEST_DB}" -c "SELECT now();"

For application databases, check a few high-value tables:

# Replace public.users with a real table
sudo -u postgres psql -d "${TEST_DB}" -c "SELECT count(*) FROM public.users;"

If you use extensions (uuid-ossp, pgcrypto, pgvector, etc.), confirm they’re present:

sudo -u postgres psql -d "${TEST_DB}" -c "\dx"

If you rely on pgvector, keep your extension setup consistent across environments. This related guide helps when you’re standardizing hosts: set up pgvector on a PostgreSQL VPS.

5) (Recommended) Restore globals into a test environment

Your globals dump includes roles and grants. On a staging server, restoring this first makes database restores smoother. It also keeps the result closer to production.

To inspect globals:

GLOBALS_GZ=$(ls -1 /var/backups/postgresql/globals-*.sql.gz | tail -n 1)
sudo -u postgres bash -c "gzip -dc '${GLOBALS_GZ}' | head -n 40"

To restore globals (only do this on a test server unless you know what you’re doing):

sudo -u postgres bash -c "gzip -dc '${GLOBALS_GZ}' | psql -v ON_ERROR_STOP=1"

Copy backups off the server (simple SFTP pull)

Backups that live only on the same VPS won’t survive common failures. A simple offsite pattern is a second host that pulls backups nightly over SFTP with a dedicated key.

On your database server, create an SFTP-only account on the backup destination. If you want a tight SFTP-only setup, this HostMyCode tutorial covers it: set up SFTP-only users on Ubuntu VPS.

Then, from the backup destination server, pull the backups:

# On the backup destination server
rsync -avz -e "ssh -i /root/.ssh/pgbackup_key" \
  backupuser@DB_SERVER_IP:/var/backups/postgresql/ \
  /srv/backup/postgresql/DB_SERVER_HOSTNAME/

Verification: confirm the newest file exists offsite and sizes look plausible.

If you encrypt with GPG, make sure you’re pulling .gpg files.

Monitoring and alerts: detect failures fast

Backup jobs fail quietly unless you look. You don’t need a complex alerting stack.

You do need a habit: check the timer, check logs, and confirm fresh files exist.

• Check last run: systemctl status pgdump-backup.service
• Check logs: journalctl -u pgdump-backup.service
• Confirm fresh backup files exist: ls -lt /var/backups/postgresql | head

If you want a basic dashboard for host health and job outcomes, add monitoring. This related deployment guide shows a practical pattern (Docker + TLS + backups) you can reuse: deploy Grafana on a VPS with Docker, TLS, and backups. For live host metrics, Netdata is also lightweight: run Netdata on a VPS for live server monitoring.

Troubleshooting common pg_dump restore failures

Role does not exist / ownership errors

Symptom: pg_restore: error: could not execute query: ERROR: role "appuser" does not exist

• Fix for test restores: add --no-owner.
• Fix for real restores: restore globals first (pg_dumpall --globals-only), or create roles manually.

Extension missing

Symptom: errors about missing uuid-ossp, pgcrypto, pgvector, etc.

• Install the extension package on the target server.
• Ensure the same major version compatibility.
• Pre-create extensions if needed, then restore.

Disk fills up mid-backup

Symptom: dump stops, truncated files appear.

• Check df -h and confirm retention cleanup runs.
• Move backups to a larger volume (attach extra disk) or reduce retention.
• Offload to another server faster (SFTP pull after each run).

Backups succeed but restores are slow

Use custom format (-Fc) and restore with parallel jobs (-j). Also confirm the target storage isn’t the bottleneck.

On busy database nodes, a dedicated server with local NVMe can make restore time more predictable for large dumps.

Summary checklist (what “done” looks like)

• You have daily dumps in /var/backups/postgresql with restrictive permissions.
• A systemd timer runs at a known time and logs to journal + files.
• Retention removes old backups automatically.
• You performed at least one restore test into a scratch database.
• You copy backups off the server (ideally encrypted).

If you’re provisioning a new node and want fewer surprises, start with a managed VPS hosting plan. Someone can double-check security, updates, and storage basics.

If you’re managing it yourself, a HostMyCode VPS gives you full root access for scripts like this. It also gives steady performance for scheduled dumps.

FAQ: PostgreSQL backup and restore

How often should I run pg_dump backups?

Daily is a sensible minimum for most websites and apps. If you take orders or accept user-generated content around the clock, run it more often (for example every 6 hours) and adjust retention if space is tight.

Is pg_dump enough for production PostgreSQL?

For small to medium databases, yes—especially if you actually test restores. For large databases or strict RPO/RTO targets, add physical backups and WAL archiving for point-in-time recovery.

Should I back up “globals” too?

Yes, if you want clean restores on a new server. Globals cover roles and grants. Without them, restores often fail or come up with the wrong ownership.

What’s the fastest way to restore a pg_dump?

Use pg_dump -Fc and restore with pg_restore -j (parallel jobs). Fast local storage (NVMe) also matters more than most people expect.

How do I know my backups aren’t corrupt?

Do two checks: (1) run pg_restore -l against the dump file, and (2) complete a real restore test into a scratch database and run application-relevant queries.