What is the difference between pg_dump and pg_basebackup, and when should I use each?

pg_dump creates logical backups by exporting SQL statements that recreate your schema and data — it is portable across PostgreSQL versions and suitable for databases under 10GB with a recovery point objective of a few hours. pg_basebackup creates a physical binary copy of the data directory, which is faster to restore for large databases and required for point-in-time recovery (PITR), but is version-specific and cannot restore individual tables. For production, you need both: physical backups for disaster recovery and logical backups for specific-table restores and cross-version migrations.

Why is testing restore processes essential, and how often should it be done?

A backup that runs silently and fails silently is worse than no backup because it creates false confidence. The post describes a real incident where pg_dump was silently failing due to a disk space issue on the backup VPS — the cron job reported success because pg_dump exited with code 0 on the partial write, resulting in 3 weeks of empty backup files. Testing restores monthly by provisioning a temporary VPS, restoring the latest backup, and running a sanity check query is the only way to confirm that backups actually work.

What is WAL archiving and why does it matter for ERP or financial databases?

WAL (Write-Ahead Log) is the transaction log PostgreSQL writes before applying changes to data files. By archiving WAL segments continuously alongside a base backup, you can restore the database to any specific point in time — not just the timestamp of the last backup. For databases where data changes frequently, such as ERP or financial systems, this means you can recover to the exact moment before an accidental DELETE or data corruption event occurred.

What is the 3-2-1 backup rule and how does it apply to VPS-hosted PostgreSQL?

The 3-2-1 rule requires 3 copies of data, stored on 2 different media types, with 1 copy offsite. The post illustrates why this matters with a real incident where a DigitalOcean Droplet failure corrupted both the server filesystem and the backup volume mounted on the same server — local backups on the same server or datacenter are convenience copies, not real backups. A minimum compliant setup includes automated daily pg_dump shipped to offsite object storage (Spaces, GCS, or B2), a nightly physical backup on a separate Droplet in the same region, and a monthly backup to a different cloud region.

PostgreSQL Backup Strategy for VPS: pg

Q: Why is pgBackRest recommended over manual WAL archiving for production PostgreSQL?

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

A practical PostgreSQL backup strategy for VPS deployments — covering pg_dump, pg_basebackup, WAL archiving, and automated offsite backup with real configuration examples.

Data loss is career-ending. A PostgreSQL database without a tested, automated backup strategy is a liability waiting to materialize. ERP data is the kind of data where a day of lost transactions could mean serious financial and legal consequences. This guide covers the practical layers of PostgreSQL backup, from the simplest pg_dump to WAL-based point-in-time recovery, across DigitalOcean Managed PostgreSQL and self-hosted instances.

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

From experience with PostgreSQL backups, always test your restore process monthly. Set a calendar reminder, provision a temporary VPS, restore your latest backup, and run a sanity check query against the restored data. A pg_dump job can fail silently, for example when the backup VPS runs out of disk space, while the cron job still reports success. You could then end up with weeks of empty or partial backup files. Test restores are the only way to know your backups actually work.

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.

I once had a DigitalOcean Droplet fail with a corrupted filesystem that also corrupted the backup volume mounted on the same server. Local backups on the same server or even the same datacenter are not real backups — they are convenience copies. Follow the 3-2-1 rule: 3 copies of data, 2 different media types, 1 offsite. At minimum: automated daily pg_dump shipped to Spaces/GCS/B2 (offsite), nightly physical backup on the same region but a different Droplet, and monthly backup to a different cloud region.

Retention Policy and Storage Costs

Retention policy balances RPO, storage cost, and compliance requirements. For most small business ERP data, a reasonable policy is: 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 bills storage and egress per GB, so a 50GB PostgreSQL database with this retention policy stays inexpensive in backup storage — cheap insurance. Check the current rate card before you budget.

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.

PostgreSQL Backup Strategy for VPS: From pg_dump to WAL Archiving