Your PostgreSQL backup script runs pg_dump every night and you feel safe. Then one morning you discover pg_dump takes 4 hours and your last consistent backup is from yesterday. pg_basebackup gives you a physical backup in minutes with WAL archiving for point-in-time recovery. Here's how to set it up correctly.
pg_basebackuptakes a consistent physical copy of the entire cluster while PostgreSQL is running- Combined with WAL archiving, it enables point-in-time recovery (PITR) to any second in history
- Target recovery time: pg_basebackup restore + WAL replay is typically 10–30 minutes for 100GB databases
- Test your restore procedure monthly — an untested backup is not a backup
Setting Up WAL Archiving
# postgresql.conf -- enable WAL archiving
wal_level = replica # minimum for archiving
archive_mode = on
archive_command = 'cp %p /backup/wal_archive/%f'
# Better: use WAL-G or pgBackRest for cloud storage
# archive_command = 'wal-g wal-push %p'
# Keep enough WAL for recovery window
wal_keep_size = 1024 # MB: keep 1GB of WAL locally
archive_cleanup_command = 'pg_archivecleanup /backup/wal_archive %r'Taking a Base Backup
# Create replication user for backups
psql -c "CREATE USER backup REPLICATION LOGIN PASSWORD 'backup_pass';"
# pg_hba.conf: allow backup user from backup server
# host replication backup 10.0.0.20/32 scram-sha-256
# Take base backup (runs while database is live)
pg_basebackup \
-h postgres.internal \
-U backup \
-D /backup/base/$(date +%Y%m%d_%H%M) \
-Ft \
-z \
--wal-method=stream \
--checkpoint=fast \
--progress
# -Ft: tar format (smaller, easier to store)
# -z: gzip compression
# --wal-method=stream: stream WAL during backup (no gap)
# --checkpoint=fast: force immediate checkpoint to minimize backup timePoint-in-Time Recovery (PITR)
# Scenario: accidental DELETE at 14:32:00, need to recover to 14:31:59
# Step 1: Stop PostgreSQL on recovery target
systemctl stop postgresql
# Step 2: Restore base backup
rm -rf /var/lib/postgresql/16/main
tar -xzf /backup/base/20250115_0200/base.tar.gz \
-C /var/lib/postgresql/16/main
tar -xzf /backup/base/20250115_0200/pg_wal.tar.gz \
-C /var/lib/postgresql/16/main/pg_wal
# Step 3: Create recovery config (postgresql.conf in PG 12+)
cat >> /var/lib/postgresql/16/main/postgresql.conf <Using WAL-G for Cloud Storage (Recommended)
# WAL-G uploads backups directly to S3, GCS, or Azure
# Much faster than manual cp-based archiving
# Install
curl -L https://github.com/wal-g/wal-g/releases/download/v3.0.0/wal-g-pg-ubuntu-20.04-amd64.tar.gz | tar xz
mv wal-g /usr/local/bin/
# Configure (environment variables or .walg.json)
export WALG_S3_PREFIX=s3://my-backup-bucket/postgres
export AWS_REGION=us-east-1
# postgresql.conf
# archive_command = 'wal-g wal-push %p'
# restore_command = 'wal-g wal-fetch %f %p'
# Take backup
wal-g backup-push /var/lib/postgresql/16/main
# List backups
wal-g backup-list
# Restore to specific time
wal-g backup-fetch /var/lib/postgresql/16/main LATESTTest your restore procedure on a separate instance every month. Document the exact recovery time objective (RTO): how many minutes from "disaster declared" to "database accepting writes". An untested restore procedure will fail during the actual emergency.
pg_basebackup --wal-method=streamensures no WAL gap between base backup and archive — critical for PITR completeness.- WAL archiving to S3 via WAL-G is the production-grade approach: cloud-durable, versioned, and supports instant PITR to any second.
- PITR recovery requires: base backup + all WAL files from backup time to recovery target. Missing any WAL file stops recovery at that point.
- Test restore monthly. Calculate your actual RTO, not a theoretical estimate.
Working with JusDB on PostgreSQL Backup
JusDB designs and implements PostgreSQL backup strategies using WAL-G + S3, verifying PITR coverage and testing restore procedures on a monthly cadence. We define recovery objectives, build runbooks, and ensure your team can execute a restore under pressure.
