DeployEasy
EN

Select language

Tiếng ViệtEnglish✓
DatabaseIntermediate

PostgreSQL Backups on a VPS with pg_dump and cron: 18 Steps with Restore Testing

Learn how to automate PostgreSQL backups on a VPS using pg_dump, cron, checksums, retention, offsite copies, and scheduled restore tests.

· 16 min read· 3,346 words
Table of contents

PostgreSQL backup workflow on a VPS using pg_dump, cron, and restore testing

A reliable PostgreSQL backup on a VPS is more than running pg_dump and seeing a .dump file appear. A backup is useful only when it runs on schedule, does not leave failed output looking successful, follows a retention policy, is copied away from the VPS, and has been restored successfully.

This guide builds a practical workflow for small and medium self-managed applications on Ubuntu. You will verify PostgreSQL versions, create a backup account, configure .pgpass, produce a custom-format archive, prevent overlapping jobs, generate checksums, delete expired copies, schedule the script with cron, and restore the result into an isolated test database.

Quick summary: Use pg_dump -Fc for a logical archive, write to a temporary file first, validate it with pg_restore --list, generate a checksum, retain multiple versions, copy at least one version off the VPS, and run scheduled restore tests.

PostgreSQL backup flow from the database to offsite storage

Terminal demo: create and inspect a backup

The commands below create a custom archive, inspect its table of contents, and generate a checksum:

Terminal demo for PostgreSQL backup with pg_dump and pg_restore

mkdir -p ~/postgres-backup-demo

pg_dump \
  -h 127.0.0.1 \
  -p 5432 \
  -U backup_user \
  -d appdb \
  -Fc \
  -f ~/postgres-backup-demo/appdb.dump

pg_restore --list ~/postgres-backup-demo/appdb.dump > /dev/null
sha256sum ~/postgres-backup-demo/appdb.dump

Security note: Do not place database passwords directly in commands, shell history, repositories, or terminal screenshots. Use .pgpass, a secret manager, or another secret delivery mechanism appropriate for your environment.

Scope of this guide

This workflow is suitable when:

PostgreSQL runs on a VPS or in Docker
The database is small or medium-sized
Point-in-time snapshots are acceptable
Minute-level Point-in-Time Recovery is not required
Resources are available for periodic restore testing

pg_dump creates a logical export of one database. Systems with a very low RPO, large databases, or a requirement to recover to a precise moment should also evaluate physical backups, WAL archiving, streaming replication, or a managed database service.

Target state

A dedicated backup connection method exists
Passwords are not stored in the script
Backups are created through temporary files
Failed jobs do not leave false-success files
Checksums and logs are available
A retention policy is enforced
At least one copy is stored outside the VPS
Restore testing is documented
Real RPO and RTO are known
PostgreSQL
    │
    ├── pg_dump -Fc
    │
    ▼
Temporary file on the VPS
    │
    ├── pg_restore --list
    ├── sha256sum
    └── rename to the completed file
    │
    ▼
Local backup directory
    │
    ├── retention
    └── copy to another server or storage service
    │
    ▼
Scheduled restore test

Step 1: Inventory the database

Do not begin with cron. First record:

Database name:
Host:
Port:
PostgreSQL version:
Database size:
Applications using it:
Low-traffic window:
Required RPO:
Required RTO:
Offsite backup destination:

Check client versions:

pg_dump --version
pg_restore --version
psql --version

Check the server:

psql \
  -h 127.0.0.1 \
  -p 5432 \
  -U postgres \
  -d postgres \
  -c "SHOW server_version;"

Check database size:

psql \
  -h 127.0.0.1 \
  -U postgres \
  -d appdb \
  -c "SELECT pg_size_pretty(pg_database_size(current_database()));"

Step 2: Define RPO and RTO

What is RPO?

Recovery Point Objective is the maximum acceptable amount of data loss.

Examples:

Backup every 24 hours → almost 24 hours of data may be lost
Backup every 6 hours  → almost 6 hours of data may be lost
Backup every hour     → almost 1 hour of data may be lost

These limits assume the scheduled job succeeds and the backup is restorable.

What is RTO?

Recovery Time Objective is the target time for returning the service to operation.

RTO includes:

  • Downloading the backup.
  • Creating a replacement server.
  • Installing PostgreSQL.
  • Restoring the database.
  • Updating application secrets and DNS.
  • Running application checks.

You cannot know the real RTO without performing a recovery drill.

Step 3: Choose a backup format

Plain SQL

pg_dump -d appdb > appdb.sql

Advantages:

  • Human-readable.
  • Restored with psql.
  • Can be edited manually in exceptional cases.

Limitations:

  • Less flexible object selection.
  • Cannot be processed by pg_restore.
  • Often less convenient for operational restores.

Custom archive

pg_dump -Fc -d appdb -f appdb.dump

Advantages:

  • Works with pg_restore.
  • Compressed by default.
  • Provides an archive table of contents.
  • Supports selective restore.
  • Supports parallel restore.

This guide uses the custom archive format.

Step 4: Verify version compatibility

Run:

pg_dump --version

Important rules:

  • pg_dump can dump supported older PostgreSQL servers.
  • It cannot dump a server with a newer major version than the client.
  • Restoring into a newer PostgreSQL major version is the better-supported direction.
  • Restoring into an older major version is not guaranteed.

For example, a PostgreSQL 16 pg_dump client will refuse to dump a PostgreSQL 18 server.

Install the matching PostgreSQL client major version from a package source appropriate for your operating system.

Step 5: Create a backup database role

A routine logical backup does not always require a superuser account.

Open an administrative session:

sudo -u postgres psql

Create a login role:

CREATE ROLE backup_user
WITH LOGIN
PASSWORD 'REPLACE_WITH_A_STRONG_PASSWORD';

Allow database connections:

GRANT CONNECT ON DATABASE appdb TO backup_user;

Connect to the application database:

\c appdb

Grant access to the public schema:

GRANT USAGE ON SCHEMA public TO backup_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user;

Cover future objects:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO backup_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON SEQUENCES TO backup_user;

Repeat these grants for every application schema that must be included.

Note: Exact permissions depend on database ownership, extensions, Row-Level Security, and schema design. Test pg_dump with the backup role and solve specific permission errors instead of granting superuser access by default.

Step 6: Create a dedicated Linux user

Create a system account without an interactive login shell:

sudo useradd \
  --system \
  --create-home \
  --home-dir /var/lib/postgresql-backup \
  --shell /usr/sbin/nologin \
  backupsvc

Verify it:

id backupsvc
sudo -u backupsvc sh -c 'echo "$HOME"'

Benefits:

  • Backup files and credentials are separated from the deploy user.
  • Permissions can be restricted.
  • Cron jobs and logs are easier to audit.
  • Access can be revoked independently.

Step 7: Configure .pgpass

Create the file:

sudo -u backupsvc nano /var/lib/postgresql-backup/.pgpass

Add:

127.0.0.1:5432:appdb:backup_user:REPLACE_WITH_A_STRONG_PASSWORD

Set ownership and permissions:

sudo chown backupsvc:backupsvc \
  /var/lib/postgresql-backup/.pgpass

sudo chmod 600 \
  /var/lib/postgresql-backup/.pgpass

On Unix, PostgreSQL requires the password file to deny access to group and other users. A file with broader permissions may be ignored.

Test the connection:

sudo -u backupsvc \
  psql \
  -h 127.0.0.1 \
  -p 5432 \
  -U backup_user \
  -d appdb \
  -c "SELECT current_database(), current_user;"

Avoid placing this in cron when a safer method is available:

PGPASSWORD=password pg_dump ...

Step 8: Create backup and log directories

sudo mkdir -p \
  /var/backups/postgresql/appdb \
  /var/log/postgresql-backup

Set ownership:

sudo chown -R backupsvc:backupsvc \
  /var/backups/postgresql \
  /var/log/postgresql-backup

Restrict access:

sudo chmod 700 /var/backups/postgresql
sudo chmod 700 /var/backups/postgresql/appdb
sudo chmod 750 /var/log/postgresql-backup

Check capacity:

df -h /var/backups/postgresql
df -i /var/backups/postgresql

Do not allow backups to fill the same disk used by PostgreSQL.

Step 9: Run a manual backup

Run the command as the backup operating-system user:

sudo -u backupsvc \
  pg_dump \
  -h 127.0.0.1 \
  -p 5432 \
  -U backup_user \
  -d appdb \
  -Fc \
  --verbose \
  -f /var/backups/postgresql/appdb/appdb-manual.dump

Check the exit code immediately:

echo $?

A successful command returns:

0

Inspect the file:

sudo -u backupsvc \
  ls -lh /var/backups/postgresql/appdb/appdb-manual.dump

Do not treat file existence as proof of success. A failed process can leave partial output behind.

Step 10: Inspect the archive and generate a checksum

List archive contents:

sudo -u backupsvc \
  pg_restore \
  --list \
  /var/backups/postgresql/appdb/appdb-manual.dump \
  > /dev/null

Generate a checksum:

sudo -u backupsvc \
  sha256sum \
  /var/backups/postgresql/appdb/appdb-manual.dump \
  > /var/backups/postgresql/appdb/appdb-manual.dump.sha256

Verify it:

cd /var/backups/postgresql/appdb

sudo -u backupsvc \
  sha256sum --check appdb-manual.dump.sha256

A checksum detects changes or corruption during transfer. It does not replace a restore test.

Step 11: Back up roles and cluster-wide objects

pg_dump exports one database. It does not fully cover roles and tablespaces, which are cluster-wide objects.

Create a globals dump:

sudo -u postgres \
  pg_dumpall \
  --globals-only \
  > /var/backups/postgresql/appdb/globals.sql

Protect the file:

sudo chown backupsvc:backupsvc \
  /var/backups/postgresql/appdb/globals.sql

sudo chmod 600 \
  /var/backups/postgresql/appdb/globals.sql

A globals dump can contain sensitive role definitions and password hashes. Protect it as a secret.

If the main job runs as backupsvc, design the required privileges carefully or keep the globals export in a separate administrative job. Do not grant broad operating-system privileges only for convenience.

Step 12: Write a safer backup script

Create the script:

sudo nano /usr/local/sbin/backup-postgresql-appdb.sh

Add:

#!/usr/bin/env bash

set -Eeuo pipefail
umask 077

DB_HOST="127.0.0.1"
DB_PORT="5432"
DB_NAME="appdb"
DB_USER="backup_user"

BACKUP_DIR="/var/backups/postgresql/appdb"
LOG_DIR="/var/log/postgresql-backup"
RETENTION_DAYS="14"

TIMESTAMP="$(date '+%Y-%m-%d_%H-%M-%S')"
TEMP_FILE="${BACKUP_DIR}/.${DB_NAME}_${TIMESTAMP}.dump.tmp"
FINAL_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"
CHECKSUM_FILE="${FINAL_FILE}.sha256"
LOG_FILE="${LOG_DIR}/backup.log"

mkdir -p "${BACKUP_DIR}" "${LOG_DIR}"

exec 9>"/var/lock/postgresql-appdb-backup.lock"

if ! flock -n 9; then
  printf '%s %s\n' \
    "$(date --iso-8601=seconds)" \
    "ERROR: another backup job is running" \
    >> "${LOG_FILE}"
  exit 1
fi

cleanup() {
  rm -f "${TEMP_FILE}"
}

trap cleanup EXIT

printf '%s %s\n' \
  "$(date --iso-8601=seconds)" \
  "INFO: backup started" \
  >> "${LOG_FILE}"

pg_dump \
  -h "${DB_HOST}" \
  -p "${DB_PORT}" \
  -U "${DB_USER}" \
  -d "${DB_NAME}" \
  -Fc \
  --verbose \
  -f "${TEMP_FILE}" \
  2>> "${LOG_FILE}"

pg_restore --list "${TEMP_FILE}" > /dev/null

mv "${TEMP_FILE}" "${FINAL_FILE}"

(
  cd "${BACKUP_DIR}"
  sha256sum "$(basename "${FINAL_FILE}")" \
    > "$(basename "${CHECKSUM_FILE}")"
)

find "${BACKUP_DIR}" \
  -type f \
  \( -name "${DB_NAME}_*.dump" -o -name "${DB_NAME}_*.dump.sha256" \) \
  -mtime "+${RETENTION_DAYS}" \
  -delete

printf '%s %s %s\n' \
  "$(date --iso-8601=seconds)" \
  "INFO: backup completed" \
  "${FINAL_FILE}" \
  >> "${LOG_FILE}"

Why use a temporary file?

.dump.tmp → backup is still running
.dump     → basic validation completed

If pg_dump fails, the trap removes the temporary output. Other automation will not mistake it for a completed backup.

Why use flock?

flock prevents two copies of the job from running at the same time, such as when the previous backup is still running when cron starts the next one.

Set ownership and execution permission:

sudo chmod 750 \
  /usr/local/sbin/backup-postgresql-appdb.sh

sudo chown root:backupsvc \
  /usr/local/sbin/backup-postgresql-appdb.sh

Step 13: Test the script before scheduling it

Check shell syntax:

sudo bash -n \
  /usr/local/sbin/backup-postgresql-appdb.sh

Run it as the intended user:

sudo -u backupsvc \
  /usr/local/sbin/backup-postgresql-appdb.sh

Read the log:

sudo tail -n 100 \
  /var/log/postgresql-backup/backup.log

Inspect output:

sudo ls -lh \
  /var/backups/postgresql/appdb

Verify the newest checksum:

cd /var/backups/postgresql/appdb

LATEST_CHECKSUM="$(
  find . -maxdepth 1 -type f -name '*.sha256' \
  -printf '%T@ %p\n' |
  sort -nr |
  head -n 1 |
  cut -d' ' -f2-
)"

sudo -u backupsvc \
  sha256sum --check "${LATEST_CHECKSUM}"

Do not add cron until the manual execution succeeds.

Step 14: Schedule the job with cron

Create a cron file:

sudo nano /etc/cron.d/postgresql-appdb-backup

Add:

SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

15 2 * * * backupsvc /usr/local/sbin/backup-postgresql-appdb.sh >> /var/log/postgresql-backup/cron.log 2>&1

The job runs every day at 02:15 in the server timezone.

Check timezone:

timedatectl
date

Check the cron service:

sudo systemctl status cron
sudo journalctl -u cron --since today

Set the cron file permissions:

sudo chmod 644 \
  /etc/cron.d/postgresql-appdb-backup

sudo chown root:root \
  /etc/cron.d/postgresql-appdb-backup

Cron runs with a minimal environment. Use predictable paths and avoid depending on interactive shell configuration.

Step 15: Define retention

The script example retains files for 14 days:

RETENTION_DAYS="14"

A more mature retention policy might keep:

7 daily copies
4 weekly copies
6 monthly copies

The right policy depends on:

  • Database size.
  • Change frequency.
  • Legal requirements.
  • Storage cost.
  • How late corruption may be detected.
  • Required RPO and RTO.

Before using find ... -delete, preview the matches:

find /var/backups/postgresql/appdb \
  -type f \
  -mtime +14 \
  -print

Enable deletion only after confirming the result.

Step 16: Copy backups away from the VPS

A backup stored only on the database VPS does not protect against:

  • VPS deletion.
  • Disk failure.
  • Cloud account compromise.
  • Ransomware.
  • Automation deleting both the database and its backups.
  • Provider incidents.

Copy to a separate backup server with rsync:

rsync -az \
  --partial \
  /var/backups/postgresql/appdb/ \
  backup@backup-server:/srv/backups/appdb/

Alternatively, use object storage with:

Versioning
Retention policy
Encryption
Lifecycle rules
Access logs
A dedicated backup credential

Avoid using the same credential with unrestricted production deletion permissions.

A minimum design is:

One local copy for fast restore
One offsite copy for server-loss recovery
Multiple historical versions

Step 17: Restore into an isolated database

Do not overwrite production for a test.

Create a clean database from template0:

sudo -u postgres \
  createdb \
  -T template0 \
  appdb_restore_test

Select the newest dump:

LATEST_DUMP="$(
  find /var/backups/postgresql/appdb \
  -maxdepth 1 \
  -type f \
  -name 'appdb_*.dump' \
  -printf '%T@ %p\n' |
  sort -nr |
  head -n 1 |
  cut -d' ' -f2-
)"

echo "${LATEST_DUMP}"

Restore it:

sudo -u postgres \
  pg_restore \
  --dbname=appdb_restore_test \
  --no-owner \
  --no-privileges \
  --exit-on-error \
  "${LATEST_DUMP}"

Refresh planner statistics:

sudo -u postgres \
  psql \
  -d appdb_restore_test \
  -c "ANALYZE;"

Inspect tables:

sudo -u postgres \
  psql \
  -d appdb_restore_test \
  -c "\dt"

Run a business-data check:

sudo -u postgres \
  psql \
  -d appdb_restore_test \
  -c "SELECT COUNT(*) FROM users;"

Remove the test database after validation:

sudo -u postgres \
  dropdb appdb_restore_test

A restore test should confirm:

The archive can be read
Schemas are created
Critical data exists
Migration state is correct
The application can connect
Important business queries work
Restore duration is recorded

Step 18: Run a full recovery drill

A useful recovery drill does more than restore on the original VPS.

Test this scenario:

1. Provision a clean test server
2. Install the required PostgreSQL major version
3. Restore required roles
4. Download the backup from offsite storage
5. Verify its checksum
6. Create the database from template0
7. Run pg_restore
8. Run ANALYZE
9. Update test application secrets
10. Run smoke tests
11. Record the duration of each step
12. Update the recovery runbook

Record:

Test date:
Backup file:
Size:
Download time:
Restore time:
Total RTO:
Problems found:
Resolution:
Approver:

When PostgreSQL runs in Docker Compose

Identify the service:

docker compose ps

Create a backup through the container:

mkdir -p /var/backups/postgresql/appdb

docker compose exec -T postgres \
  pg_dump \
  -U postgres \
  -d appdb \
  -Fc \
  > /var/backups/postgresql/appdb/appdb-docker.dump

The host shell handles >, so the output file is created on the host.

Inspect it:

docker compose exec -T postgres \
  pg_restore --list \
  < /var/backups/postgresql/appdb/appdb-docker.dump \
  > /dev/null

Create a test database:

docker compose exec -T postgres \
  createdb \
  -U postgres \
  -T template0 \
  appdb_restore_test

Restore:

docker compose exec -T postgres \
  pg_restore \
  -U postgres \
  -d appdb_restore_test \
  --no-owner \
  --no-privileges \
  --exit-on-error \
  < /var/backups/postgresql/appdb/appdb-docker.dump

Do not copy a running PostgreSQL container’s data directory and assume the copy is a valid backup. Physical backups require their own consistency procedure.

Minimum backup monitoring

Track:

Time of the last successful backup
Exit code
Backup file size
Execution duration
Disk capacity
Number of retained versions
Offsite copy status
Date of the last successful restore test

Follow the log:

sudo tail -f \
  /var/log/postgresql-backup/backup.log

Search for failures:

sudo grep -iE \
  'error|fatal|failed' \
  /var/log/postgresql-backup/*.log

List recent dumps:

find /var/backups/postgresql/appdb \
  -maxdepth 1 \
  -type f \
  -name '*.dump' \
  -printf '%TY-%Tm-%Td %TH:%TM %s %p\n' |
sort -r |
head

A missing backup inside the expected schedule window should trigger an alert.

What not to do

Check only that a file exists

The file may be empty, partial, or invalid.

Store passwords in the script

Scripts are commonly backed up, shared, read by multiple users, or accidentally committed.

Keep only the newest backup

If corruption began several days ago, the newest backup may already contain it.

Store every backup on the same VPS

Losing the VPS can remove both the database and every backup.

Skip restore tests

pg_restore --list is only a structural check. It does not prove that the application works after recovery.

Grant superuser access by default

Start with the minimum required permissions and expand only for a documented reason.

Run retention before a new backup succeeds

If the job deletes old versions and then fails, the safety margin may shrink unexpectedly.

Production checklist

[ ] RPO and RTO are documented
[ ] pg_dump client is compatible with the server
[ ] A dedicated backup role or documented access model exists
[ ] Passwords are not stored in the script
[ ] .pgpass permissions are 600
[ ] Backup directories are not public
[ ] Temporary output files are used
[ ] The script uses set -Eeuo pipefail
[ ] flock prevents overlapping jobs
[ ] Exit codes are checked
[ ] pg_restore --list succeeds
[ ] Checksums are generated
[ ] Retention is configured
[ ] Logs are available
[ ] Cron runs under a dedicated user
[ ] An offsite copy exists
[ ] Cluster-wide objects are considered
[ ] A restore test succeeds
[ ] A recovery runbook exists
[ ] Failed or late backups generate alerts

Frequently asked questions

Does pg_dump lock the database?

pg_dump creates a consistent export while the database remains available. It does not block normal readers or writers, but it still uses the locks required to protect objects during the dump. DDL operations or requests for exclusive locks can affect a backup.

How often should backups run?

Base the schedule on the RPO. If no more than one hour of data loss is acceptable, one backup per day is not enough.

Should I use .sql or .dump?

Use plain SQL when a readable script restored by psql is important. Use a custom archive for compression, object listing, selective restore, or parallel restore.

Does pg_dump back up roles?

Not completely. Roles and tablespaces are cluster-wide objects. Evaluate pg_dumpall --globals-only.

Can a backup be restored into an older PostgreSQL version?

Do not assume it will work. Restoring into an older major version is not guaranteed.

Does a valid checksum prove the backup works?

No. It proves that the file matches the bytes used to generate that checksum. A restore test is still required.

Is pg_dump enough for every production system?

No. Large databases, low RPO targets, or Point-in-Time Recovery requirements should use an appropriate physical backup and WAL archiving design or a managed PostgreSQL service.

Conclusion

A production PostgreSQL backup process must answer three questions:

Did the backup run on schedule?
Is a safe copy stored outside the VPS?
Has it been restored successfully recently?

pg_dump, cron, and checksums provide a solid foundation for small and medium applications. The real goal is not to collect backup files. It is to restore the service within the promised RPO and RTO.

References

Continue reading

Related articles