PostgreSQL offers several backup strategies depending on your recovery objectives. This guide covers all three: logical backups with pg_dump, physical backups with pg_basebackup, and Point-in-Time Recovery (PITR) using WAL archiving.
Backup Strategy Overview
| Method | RTO | RPO | Use Case |
|---|---|---|---|
pg_dump | Minutes–hours | Hours (schedule-based) | Single databases, migrations |
pg_basebackup | Minutes | Minutes (WAL interval) | Full cluster backup |
| WAL + PITR | Minutes | Seconds | Mission-critical, zero data loss |
RTO = Recovery Time Objective (how long to restore) RPO = Recovery Point Objective (max data loss acceptable)
Logical Backups with pg_dump
pg_dump exports a single database to SQL or a custom binary format.
Basic Usage
# Plain SQL format (human-readable, large files)
pg_dump -U postgres -h localhost mydb > mydb_backup.sql
# Custom format (compressed, faster restore, recommended)
pg_dump -U postgres -h localhost -Fc mydb > mydb_backup.dump
# Directory format (parallel dump support)
pg_dump -U postgres -h localhost -Fd mydb -j 4 -f mydb_backup_dir/
Dump Options
# Dump only schema (no data)
pg_dump -U postgres -s mydb > schema_only.sql
# Dump only data (no schema)
pg_dump -U postgres -a mydb > data_only.sql
# Dump specific tables
pg_dump -U postgres -t users -t orders mydb > users_orders.sql
# Exclude a table
pg_dump -U postgres --exclude-table=logs mydb > mydb_no_logs.dump
# Include large objects (blobs)
pg_dump -U postgres -b mydb > mydb_with_blobs.sql
Restore with pg_restore
# Restore custom format dump
pg_restore -U postgres -h localhost -d newdb mydb_backup.dump
# Create DB and restore in one step
createdb -U postgres newdb
pg_restore -U postgres -d newdb -j 4 mydb_backup.dump
# Restore specific table
pg_restore -U postgres -d mydb -t users mydb_backup.dump
# Restore with verbose output
pg_restore -U postgres -d newdb -v mydb_backup.dump
# Restore SQL format (use psql)
psql -U postgres -d newdb < mydb_backup.sql
Restore to Different Schema
# Dump with original schema
pg_dump -U postgres -n public mydb > public_schema.sql
# Restore into different schema
sed 's/public\./analytics\./g' public_schema.sql | psql -U postgres -d newdb
Dump All Databases
# pg_dumpall — dumps all databases + roles + tablespaces
pg_dumpall -U postgres > full_cluster.sql
# Restore
psql -U postgres < full_cluster.sql
# Dump only global objects (roles, tablespaces)
pg_dumpall -U postgres -g > globals_only.sql
Automated pg_dump with Cron
backup.sh:
#!/bin/bash
set -euo pipefail
BACKUP_DIR="/var/backups/postgresql"
DB_NAME="mydb"
DB_USER="postgres"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"
mkdir -p "$BACKUP_DIR"
pg_dump -U "$DB_USER" -Fc "$DB_NAME" > "$BACKUP_FILE"
# Compress if using SQL format
# gzip "$BACKUP_FILE"
echo "Backup created: $BACKUP_FILE ($(du -sh $BACKUP_FILE | cut -f1))"
# Delete backups older than retention period
find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
echo "Old backups cleaned up"
# Optional: upload to S3
# aws s3 cp "$BACKUP_FILE" "s3://my-bucket/postgresql/"
Add to cron (crontab -e):
# Daily backup at 2 AM
0 2 * * * /usr/local/bin/backup.sh >> /var/log/pg_backup.log 2>&1
# Weekly full backup on Sunday
0 1 * * 0 /usr/local/bin/backup_weekly.sh >> /var/log/pg_backup.log 2>&1
Physical Backup with pg_basebackup
pg_basebackup copies the entire PostgreSQL data directory — includes all databases, system catalogs, and WAL files. Faster to restore than pg_dump for large clusters.
Prerequisites
Edit postgresql.conf:
wal_level = replica
archive_mode = on
max_wal_senders = 3
Add to pg_hba.conf:
local replication postgres trust
host replication postgres 127.0.0.1/32 scram-sha-256
Reload PostgreSQL:
sudo systemctl reload postgresql
Run pg_basebackup
# Basic backup to directory
pg_basebackup -U postgres -h localhost -D /var/backups/pg_base -P -Xs -R
# Options:
# -D output directory
# -P show progress
# -Xs stream WAL during backup
# -R write recovery config (for standby use)
# Tar format (compressed)
pg_basebackup -U postgres -h localhost -D /var/backups/pg_base -Ft -z -P
# With checkpoint
pg_basebackup -U postgres -h localhost -D /var/backups/pg_base -P -Xs --checkpoint=fast
Restore from pg_basebackup
# Stop PostgreSQL
sudo systemctl stop postgresql
# Backup current data dir (safety)
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.old
# Copy backup
sudo cp -a /var/backups/pg_base /var/lib/postgresql/16/main
sudo chown -R postgres:postgres /var/lib/postgresql/16/main
# Start PostgreSQL
sudo systemctl start postgresql
WAL Archiving and Point-in-Time Recovery
PITR lets you restore the database to any point in time — even seconds before an accidental DELETE.
Configure WAL Archiving
postgresql.conf:
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300 # archive every 5 minutes even if no activity
For S3 archiving (use WAL-G or pgBackRest):
archive_command = 'wal-g wal-push %p'
Reload:
sudo systemctl reload postgresql
Test archiving:
# Force WAL switch
psql -U postgres -c "SELECT pg_switch_wal();"
# Check WAL archive directory
ls /var/lib/postgresql/wal_archive/
Take a Base Backup for PITR
pg_basebackup -U postgres -D /var/backups/pitr_base -Xs -P
Record the backup start time:
psql -U postgres -c "SELECT now(), pg_current_wal_lsn();"
Simulate a Disaster
-- Record the time before the "accident"
SELECT now(); -- e.g., 2026-05-07 14:30:00 UTC
-- Accidental mass delete
DELETE FROM orders;
Restore to Point in Time
# Stop PostgreSQL
sudo systemctl stop postgresql
# Replace data directory with base backup
sudo rsync -av --delete /var/backups/pitr_base/ /var/lib/postgresql/16/main/
sudo chown -R postgres:postgres /var/lib/postgresql/16/main/
# Create recovery signal file (PostgreSQL 12+)
sudo touch /var/lib/postgresql/16/main/recovery.signal
postgresql.conf (add recovery settings):
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-05-07 14:29:00 UTC'
recovery_target_action = 'promote'
Start PostgreSQL — it will replay WAL up to the target time:
sudo systemctl start postgresql
# Monitor recovery in logs
sudo journalctl -u postgresql -f
Verify recovery:
-- Check if orders table has data again
SELECT count(*) FROM orders;
-- Confirm recovery is complete
SELECT pg_is_in_recovery(); -- returns false when done
Using pgBackRest (Production Recommended)
pgBackRest is the modern choice for production backup management. It supports:
- Full, differential, and incremental backups
- Parallel backup and restore
- S3/Azure/GCS storage
- Delta restore
- Backup verification
Install:
sudo apt install -y pgbackrest
pgbackrest.conf (/etc/pgbackrest/pgbackrest.conf):
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y
log-level-console=info
[mydb]
pg1-path=/var/lib/postgresql/16/main
Initialize and take backup:
# Initialize repository
sudo -u postgres pgbackrest --stanza=mydb stanza-create
# Full backup
sudo -u postgres pgbackrest --stanza=mydb backup --type=full
# Differential backup
sudo -u postgres pgbackrest --stanza=mydb backup --type=diff
# List backups
sudo -u postgres pgbackrest --stanza=mydb info
Restore:
# Stop PostgreSQL
sudo systemctl stop postgresql
# Full restore
sudo -u postgres pgbackrest --stanza=mydb --delta restore
# PITR restore
sudo -u postgres pgbackrest --stanza=mydb restore \
--target="2026-05-07 14:29:00" \
--target-action=promote
sudo systemctl start postgresql
Backup Verification
Never trust an untested backup:
# Verify backup integrity
pg_restore --list mydb_backup.dump > /dev/null && echo "OK"
# Test restore to separate instance
pg_restore -U postgres -d test_restore mydb_backup.dump
# Count rows after restore
psql -U postgres -d test_restore -c "SELECT count(*) FROM users;"
# Compare checksums
psql -U postgres -c "SELECT sum(n_live_tup) FROM pg_stat_user_tables;" mydb
psql -U postgres -c "SELECT sum(n_live_tup) FROM pg_stat_user_tables;" test_restore
Monitoring Backup Age
-- Check last successful backup (using a backup log table)
CREATE TABLE IF NOT EXISTS backup_log (
id SERIAL PRIMARY KEY,
backup_type VARCHAR(20),
started_at TIMESTAMPTZ,
completed_at TIMESTAMPTZ,
size_bytes BIGINT,
status VARCHAR(10)
);
-- Alert if no backup in 25 hours
SELECT CASE
WHEN MAX(completed_at) < NOW() - INTERVAL '25 hours'
THEN 'ALERT: No recent backup!'
ELSE 'OK: Last backup ' || MAX(completed_at)::text
END AS backup_status
FROM backup_log
WHERE status = 'success';
Quick Reference
# Logical backup (recommended for most cases)
pg_dump -U postgres -Fc mydb > mydb_$(date +%Y%m%d).dump
# Restore logical backup
pg_restore -U postgres -d mydb_new mydb_20260507.dump
# Physical backup (full cluster)
pg_basebackup -U postgres -D /backups/base -Xs -P
# Dump all databases
pg_dumpall -U postgres > all_databases.sql
# Check WAL archiving status
psql -U postgres -c "SELECT * FROM pg_stat_archiver;"
# Force WAL archive
psql -U postgres -c "SELECT pg_switch_wal();"
Summary
pg_dump/pg_restore: Best for per-database backups, migrations, and development. Use custom format (-Fc) for compressed, parallel-restore-capable dumps.pg_basebackup: Full cluster physical backup. Fast restore for large databases.- WAL + PITR: Zero data loss recovery to any point in time. Essential for production.
- pgBackRest: Production-grade solution combining all of the above with S3 support and verification.
For any production PostgreSQL database, implement at least daily pg_dump + WAL archiving. Test restores monthly — backup processes that have never been tested are not backups.