PostgreSQL Backup Strategy for VPS: From pg_dump to WAL Archiving

Data loss is career-ending. A PostgreSQL database without a tested, automated backup strategy is a liability waiting to materialize. At Commsult Indonesia, we manage ERP data for clients where a day of lost transactions could mean serious financial and legal consequences. I have spent significant time designing and testing our backup strategy across DigitalOcean Managed PostgreSQL and self-hosted instances. This guide covers the practical layers of PostgreSQL backup, from the simplest pg_dump to WAL-based point-in-time recovery.

Understanding PostgreSQL Backup Types

PostgreSQL has two fundamental backup approaches: logical backups (pg_dump) and physical backups (pg_basebackup, pgBackRest). Logical backups export SQL statements that recreate your schema and data — they are portable, human-readable, and work across PostgreSQL versions. Physical backups copy the raw data files — they are faster for large databases, required for point-in-time recovery (PITR), but version-specific. For production, you need both: physical backups for disaster recovery, logical backups for specific-table restores and cross-version migrations.

pg_dump: The Starting Point

pg_dump is the simplest backup tool and sufficient for databases under 10GB with RPO (recovery point objective) of a few hours. The command pg_dump -Fc -f backup.dump dbname creates a compressed custom-format dump that restores with pg_restore. For multiple databases, pg_dumpall dumps the entire cluster including roles. Schedule pg_dump via cron at your desired frequency (hourly for critical data, daily for most), and immediately ship the dump to offsite storage: DigitalOcean Spaces, GCP Cloud Storage, or Backblaze B2.

pg_basebackup for Physical Backups

pg_basebackup creates a binary copy of the PostgreSQL data directory while the server is running. It is the basis for streaming replication and PITR. Use pg_basebackup -D /path/to/backup -Ft -z -P for a tarball backup with progress output. Physical backups are faster to restore for large databases — restoring 100GB via pg_restore from a pg_dump takes hours; restoring a physical backup takes minutes. The downside: physical backups are PostgreSQL version-specific and cannot restore individual tables.

┌─────────────────────────────────────────────────────┐
│          POSTGRESQL BACKUP STRATEGY LAYERS          │
└─────────────────────────────────────────────────────┘

  PostgreSQL DB
       │
       ├── [1] pg_dump (daily) ──────► Offsite Object Storage
       │         (logical backup)       (Spaces / GCS / B2)
       │
       ├── [2] pg_basebackup (weekly) ► Backup VPS
       │         (physical backup)       (same region)
       │
       ├── [3] WAL Archiving ──────────► Object Storage
       │         (continuous)            (PITR to any timestamp)
       │
       └── [4] pgBackRest (full+incr) ► Encrypted S3-compat
                 (production-grade)

  3-2-1 Rule: 3 copies | 2 media types | 1 offsite

WAL Archiving and Point-in-Time Recovery

WAL (Write-Ahead Log) is the transaction log PostgreSQL writes before applying changes to data files. By archiving WAL segments continuously, you can restore to any point in time between base backups — not just the backup timestamp. For a database where data changes frequently (ERP, financial systems), PITR lets you say: restore to 10:47:23 yesterday, before the accidental DELETE ran. Configure WAL archiving with archive_mode = on and archive_command in postgresql.conf, pointing to your backup storage.

pgBackRest: The Production-Grade Solution

pgBackRest is the recommended backup tool for production PostgreSQL. It supports full, incremental, and differential backups; parallel backup and restore; compression and encryption; multiple storage backends (local, S3, Azure, GCS); and simplified PITR. A nightly full backup with continuous WAL archiving gives you a recovery point within seconds of any transaction. pgBackRest handles the complexity of coordinating base backups and WAL segments — the manual approach is error-prone.

# Daily pg_dump backup script
#!/bin/bash
DB_NAME="production_db"
BACKUP_DIR="/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.dump"

pg_dump -Fc -f "$DUMP_FILE" "$DB_NAME"

# Upload to DigitalOcean Spaces
s3cmd put "$DUMP_FILE" s3://my-backups/postgres/

# Delete local files older than 3 days
find "$BACKUP_DIR" -name "*.dump" -mtime +3 -delete

# Send success signal (dead man's switch)
curl -s "https://hc-ping.com/YOUR-UUID"

# WAL archiving in postgresql.conf
# archive_mode = on
# archive_command = 'aws s3 cp %p s3://my-backups/wal/%f'
# wal_level = replica

Automating the Backup Pipeline

A production backup pipeline for a DigitalOcean Managed PostgreSQL instance: use the built-in automated backups (included with managed service, 7-day retention), add your own pg_dump daily backups to DigitalOcean Spaces for 30-day retention and cross-region protection, and test restore monthly. For self-hosted PostgreSQL: configure pgBackRest with a dedicated backup server, enable WAL archiving to GCS or Spaces, schedule full weekly + incremental daily backups, and automate restore testing with a script that provisions, restores, validates, and destroys a test Droplet.

Retention Policy and Storage Costs

Retention policy balances RPO, storage cost, and compliance requirements. For most small business ERP data at Commsult Indonesia: 7-day daily backups (covers accidental deletion), 4-week weekly backups (covers delayed discovery of corruption), 12-month monthly backups (covers compliance and audit requirements). DigitalOcean Spaces costs /bin/zsh.02/GB/month for storage and /bin/zsh.01/GB for egress. A 50GB PostgreSQL database with this retention policy costs roughly -15/month in backup storage — cheap insurance.

Monitoring Your Backup Health

A backup that runs silently and fails silently is worse than no backup, because it creates false confidence. Monitor backup health: send a Telegram alert when a backup job succeeds (so you notice when the notification stops), send an immediate alert on backup failure, track backup file size and alert if it drops significantly (indicates truncation or corruption), and log restore test results. Set up a dead man’s switch: if no backup success notification arrives by 8am, an alert fires. The absence of success IS the failure signal.

Sources & Further Reading

• PostgreSQL — Official Backup and Restore Documentation — https://www.postgresql.org/docs/current/backup.html
• Percona — PostgreSQL Backup Strategy for Enterprise-Grade Environments — https://www.percona.com/blog/postgresql-backup-strategy-enterprise-grade-environment/
• Crunchy Data — Introduction to Postgres Backups — https://www.crunchydata.com/blog/introduction-to-postgres-backups