Master PostgreSQL Backup & Recovery: Logical, Physical, PITR, Automation, and Best Practices

Database backup and recovery are core tasks in PostgreSQL administration, affecting availability, integrity and security.

1. Logical Backup and Restore

1.1 Using pg_dump for single‑database backup

# Backup entire database to SQL file
pg_dump -U username -d dbname -f backup.sql
# Custom format (compressed, parallel restore)
pg_dump -U username -d dbname -F c -f backup.dump
# Directory format (parallel backup)
pg_dump -U username -d dbname -F d -f backup_dir -j 4
# Data‑only backup
pg_dump -U username -d dbname -a -f data_only.sql
# Schema‑only backup
pg_dump -U username -d dbname -s -f schema_only.sql

1.2 Using pg_dumpall for all‑database backup

# Backup all databases and global objects
pg_dumpall -U postgres -f full_backup.sql
# Backup only global objects (roles, tablespaces)
pg_dumpall -U postgres -g -f globals_only.sql

1.3 Restoring logical backups

# Restore SQL format
psql -U username -d dbname -f backup.sql
# Restore custom format
pg_restore -U username -d dbname backup.dump
# Clean database before restore
pg_restore -U username -d dbname -c backup.dump
# Restore schema only
pg_restore -U username -d dbname -s backup.dump

2. Physical Backup (WAL Archiving)

2.1 Configure WAL archiving in postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300

Prepare archive directory:

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive

2.2 Basic physical backup steps

# Start backup
psql -U postgres -c "SELECT pg_start_backup('base_backup_$(date +%Y%m%d)');"
# Archive data directory (exclude pg_wal)
 tar -czf /backup/base_backup_$(date +%Y%m%d).tar.gz --exclude=pg_wal /var/lib/postgresql/data
# Stop backup
psql -U postgres -c "SELECT pg_stop_backup();"

3. Point‑In‑Time Recovery (PITR)

3.1 Recovery configuration

PostgreSQL 12 and earlier use recovery.conf; later versions place the same settings in postgresql.conf.

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2024-01-01 12:00:00'
recovery_target_action = promote

3.2 Recovery procedure

systemctl stop postgresql
rm -rf /var/lib/postgresql/data/*
 tar -xzf base_backup_20231201.tar.gz -C /
 echo "restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'" > /var/lib/postgresql/data/recovery.conf
 echo "recovery_target_time = '2024-01-01 12:00:00'" >> /var/lib/postgresql/data/recovery.conf
systemctl start postgresql

4. Automated Backup Scripts

4.1 Logical backup script

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/var/backups/postgresql"
LOG_FILE="${BACKUP_DIR}/backup_${DATE}.log"
mkdir -p $BACKUP_DIR
pg_dumpall -U postgres | gzip > $BACKUP_DIR/full_backup_${DATE}.sql.gz
pg_dump -U postgres important_db -F c | gzip > $BACKUP_DIR/important_db_${DATE}.dump.gz
find $BACKUP_DIR -name "*.gz" -mtime +7 -delete
echo "Backup completed at $(date)" >> $LOG_FILE

4.2 Physical backup script

#!/bin/bash
DATE=$(date +%Y%m%d)
BACKUP_DIR="/var/backups/postgresql/physical"
DATA_DIR="/var/lib/postgresql/data"
psql -U postgres -c "SELECT pg_start_backup('physical_backup_$DATE');"
 tar -czf $BACKUP_DIR/base_backup_$DATE.tar.gz --exclude=pg_wal $DATA_DIR
psql -U postgres -c "SELECT pg_stop_backup();"
ls -t $BACKUP_DIR/base_backup_*.tar.gz | tail -n +6 | xargs rm -f

5. Best Practices and Caveats

5.1 Backup strategy

Full logical backup: weekly

Incremental/differential: daily (WAL‑based)

Recovery verification: regular drills

Off‑site storage: ensure disaster recovery

5.2 Monitoring backup status

-- Archive status
SELECT * FROM pg_stat_archiver;
-- Current WAL location
SELECT pg_current_wal_lsn();
-- Base backup progress
SELECT * FROM pg_stat_progress_basebackup;

5.3 Common configuration parameters

max_wal_size = 1GB
min_wal_size = 80MB
wal_keep_segments = 64

6. Production‑Level Add‑Ons

6.1 Security and compliance

Encrypt backup files with GPG/OpenSSL.

Transfer via rsync+ssh or S3 API.

Restrict directory permissions to the postgres user.

Meet GDPR/PCI‑DSS requirements for regulated industries.

6.2 Large‑scale data scenarios

Use pg_dump -j for parallel logical backup.

Test import/export of partitioned tables.

Run backups on read‑only replicas to reduce primary load.

6.3 Incremental backup tools

Barman : mature enterprise solution.

pgBackRest : incremental, compression, parallel, S3 support.

wal‑g : lightweight, cloud‑friendly.

6.4 Recovery drills and monitoring

Periodically restore to a fresh environment.

Watch pg_stat_archiver to catch archive failures.

Monitor archive directory disk space.

6.5 Cloud and container scenarios

Kubernetes: StatefulSet + PVC + wal‑g.

Managed cloud databases (RDS, Cloud SQL): use vendor snapshots plus logical backups.

Hybrid strategy: logical for migration, physical for fast recovery.

✅ Summary

Logical backup: suitable for migration and partial restores.

Physical backup + WAL: handles large datasets and point‑in‑time recovery.

Tooling: pgBackRest, wal‑g, Barman are production‑grade.

Automation & drills: ensure backups are usable.

Security & compliance: encryption, permissions, audit.

The ultimate goal is backup peace of mind, reliable restores, compliant policies, and efficient operation.