PostgreSQL Backup Strategies for VPS: pg_dump, WAL Archiving, pgBackRest, and S3

A PostgreSQL database without a tested backup is a liability. A VPS with a single-disk PostgreSQL installation and no off-server backup copy has a single point of failure that disk failure, provider incident, or human error can trigger. This guide covers three backup strategies in order of complexity: pg_dump automation (suitable for most cases), WAL archiving for point-in-time recovery, and pgBackRest for incremental backups at scale.

Backup Strategy Comparison

Strategy 1: Automated pg_dump to S3

Install and Configure Rclone

curl https://rclone.org/install.sh | sudo bash

# Configure S3 remote (Backblaze B2, Cloudflare R2, or AWS S3)
rclone config
# Follow prompts: new remote → s3 → your provider → access keys → save

# Test connection
rclone ls s3:your-backup-bucket

Create Backup Script

sudo nano /usr/local/bin/pg-backup.sh

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/opt/backups/postgres"
S3_BUCKET="s3:your-backup-bucket/postgres"
KEEP_LOCAL_DAYS=3
KEEP_S3_DAYS=30
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOGFILE="/var/log/pg-backup.log"

log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOGFILE"; }

mkdir -p "$BACKUP_DIR"
log "Starting PostgreSQL backup..."

# Backup all databases (recommended for full recovery capability)
BACKUP_FILE="$BACKUP_DIR/pg_all_${TIMESTAMP}.sql.gz"
sudo -u postgres pg_dumpall | gzip > "$BACKUP_FILE"
log "Backed up all databases → $BACKUP_FILE ($(du -sh "$BACKUP_FILE" | cut -f1))"

# Upload to S3
log "Uploading to $S3_BUCKET..."
rclone copy "$BACKUP_FILE" "$S3_BUCKET"
log "Upload complete"

# Clean local backups older than KEEP_LOCAL_DAYS
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_LOCAL_DAYS -delete
log "Removed local backups older than $KEEP_LOCAL_DAYS days"

# Clean S3 backups older than KEEP_S3_DAYS
rclone delete "$S3_BUCKET" --min-age "${KEEP_S3_DAYS}d"
log "Backup complete"

sudo chmod +x /usr/local/bin/pg-backup.sh
sudo mkdir -p /opt/backups/postgres

# Test it works
sudo /usr/local/bin/pg-backup.sh

Schedule Daily Backup with Health Check Monitoring

sudo crontab -e

# Daily backup at 2 AM with Healthchecks.io monitoring
0 2 * * * curl -fsS https://hc-ping.com/YOUR-UUID/start > /dev/null; \
          /usr/local/bin/pg-backup.sh \
          && curl -fsS https://hc-ping.com/YOUR-UUID > /dev/null \
          || curl -fsS https://hc-ping.com/YOUR-UUID/fail > /dev/null

Strategy 2: WAL Archiving for Point-in-Time Recovery

WAL (Write-Ahead Log) is PostgreSQL’s transaction log. Archiving WAL segments allows recovery to any exact moment in time — not just daily backup snapshots.

Enable WAL Archiving

sudo nano /etc/postgresql/16/main/postgresql.conf

wal_level = replica               # Required for archiving (must be 'replica' or 'logical')
archive_mode = on                 # Enable WAL archiving
archive_command = 'rclone copy %p s3:your-backup-bucket/wal/%f'
archive_timeout = 300             # Force WAL segment switch every 5 minutes (5-min max RPO)
max_wal_senders = 3               # Needed if you also use replication

sudo systemctl restart postgresql

Take a Base Backup

sudo -u postgres pg_basebackup \
    --pgdata=/opt/backups/postgres/basebackup_$(date +%Y%m%d) \
    --format=tar \
    --gzip \
    --checkpoint=fast \
    --progress

# Upload base backup to S3
rclone copy /opt/backups/postgres/basebackup_$(date +%Y%m%d) \
    s3:your-backup-bucket/basebackups/$(date +%Y%m%d)/

Point-in-Time Recovery

# In postgresql.conf (after restoring base backup):
restore_command = 'rclone copy s3:your-backup-bucket/wal/%f %p'
recovery_target_time = '2026-06-01 14:30:00 UTC'   # Recover to this exact moment
recovery_target_action = 'promote'                  # Make server primary after recovery

Strategy 3: pgBackRest for Incremental Backups

pgBackRest is the production-grade PostgreSQL backup tool — supports incremental/differential backups, parallel processing, compression, encryption, and native S3 storage.

sudo apt install -y pgbackrest

sudo nano /etc/pgbackrest/pgbackrest.conf

[global]
# S3 storage backend
repo1-type=s3
repo1-s3-bucket=your-backup-bucket
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-region=us-east-1
repo1-s3-key=YOUR_AWS_ACCESS_KEY
repo1-s3-key-secret=YOUR_AWS_SECRET_KEY
repo1-path=/pgbackrest

# Retention
repo1-retention-full=2       # Keep 2 full backups
repo1-retention-diff=7       # Keep 7 differential backups

# Security
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=YourStrongEncryptionPassphrase

# Performance
start-fast=y
compress-type=lz4

[mydb]
pg1-path=/var/lib/postgresql/16/main

# Initialize repository and take first full backup
sudo -u postgres pgbackrest --stanza=mydb stanza-create
sudo -u postgres pgbackrest --stanza=mydb --type=full backup

# Schedule in crontab:
# Full backup weekly (Sunday 1 AM)
0 1 * * 0 sudo -u postgres pgbackrest --stanza=mydb --type=full backup

# Differential backup daily Mon–Sat 1 AM
0 1 * * 1-6 sudo -u postgres pgbackrest --stanza=mydb --type=diff backup

# View available backups
sudo -u postgres pgbackrest --stanza=mydb info

Verify Backups — The Non-Negotiable Step

# Verify a pg_dump backup is readable
gunzip -c /opt/backups/postgres/pg_all_latest.sql.gz | \
    psql -U postgres -d test_restore_db --quiet
echo "Restore exit code: $?"

# Verify pgBackRest backup integrity
sudo -u postgres pgbackrest --stanza=mydb check

# Test actual restore to a different directory (non-destructive)
sudo -u postgres pgbackrest --stanza=mydb restore \
    --delta \
    --target-action=promote \
    --pg1-path=/tmp/pg_restore_test

Getting Started

Start with the pg_dump + S3 strategy — it covers most VPS use cases with minimal setup. Add WAL archiving when losing more than 1 hour of data is unacceptable. Use pgBackRest with incremental backups for databases over 50 GB. All strategies work on Ubuntu VPS at VPS.DO. Backblaze B2 is a cost-effective S3-compatible backup target at $6/TB/month.

Conclusion

Choose your PostgreSQL backup strategy based on recovery objectives: pg_dump is sufficient for small databases where 24-hour RPO is acceptable; WAL archiving enables point-in-time recovery for transactional systems; pgBackRest provides incremental backups for large databases with fast recovery requirements. The non-negotiable requirement across all strategies: verify that backups actually restore. An untested backup is not a backup you can rely on during a real incident.