PostgreSQL provides two complementary backup tools: pg_dump for logical backups of individual databases (SQL statements that can be restored to any PostgreSQL version) and pg_basebackup for physical backups of the entire database cluster (exact byte-for-byte copy of the data directory, used for streaming replication setup and point-in-time recovery). pg_dump is the right tool for most backup scenarios — it can back up a single database, specific tables, or schema-only while the database is running and accepting connections. pg_basebackup is used when you need a consistent cluster-level backup for standby provisioning or PITR (Point-In-Time Recovery) with WAL archiving. This guide covers both tools, automated backup scheduling, backup verification, and restoration procedures.

Prerequisites

  • PostgreSQL 16 installed on RHEL 9

Step 1 — Logical Backup with pg_dump

# Dump a single database to a SQL file
sudo -u postgres pg_dump myapp > /var/backups/pgsql/myapp-$(date +%Y%m%d).sql

# Compressed SQL format
sudo -u postgres pg_dump myapp | gzip > /var/backups/pgsql/myapp-$(date +%Y%m%d).sql.gz

# Custom format (fastest restore, supports parallel restore with pg_restore)
sudo -u postgres pg_dump -Fc myapp > /var/backups/pgsql/myapp-$(date +%Y%m%d).dump

# Schema only (no data)
sudo -u postgres pg_dump --schema-only myapp > /var/backups/pgsql/myapp-schema-$(date +%Y%m%d).sql

# Specific tables
sudo -u postgres pg_dump -t users -t orders myapp > /var/backups/pgsql/myapp-tables-$(date +%Y%m%d).sql

Step 2 — Backup All Databases with pg_dumpall

# pg_dumpall includes global objects (roles, tablespaces) that pg_dump misses
sudo -u postgres pg_dumpall | gzip > /var/backups/pgsql/all-databases-$(date +%Y%m%d).sql.gz

# Globals only (roles and tablespaces, no database data)
sudo -u postgres pg_dumpall --globals-only > /var/backups/pgsql/globals-$(date +%Y%m%d).sql

Step 3 — Restore with pg_restore and psql

# Restore SQL format dump
sudo -u postgres psql myapp < /var/backups/pgsql/myapp-20241201.sql

# Restore gzip-compressed SQL
gunzip < /var/backups/pgsql/myapp-20241201.sql.gz | sudo -u postgres psql myapp

# Restore custom format dump (parallel restore with 4 workers)
sudo -u postgres pg_restore -d myapp -j 4 /var/backups/pgsql/myapp-20241201.dump

# Restore all databases
gunzip < /var/backups/pgsql/all-databases-20241201.sql.gz | sudo -u postgres psql postgres

Step 4 — Physical Backup with pg_basebackup

# Create a physical backup (cluster-level, for PITR or standby provisioning)
sudo -u postgres pg_basebackup 
    -D /var/backups/pgsql/basebackup-$(date +%Y%m%d)/ 
    -Ft -z -P -Xs
# -Ft: tar format, -z: gzip compress, -Xs: stream WAL during backup

Step 5 — Automate Daily pg_dump Backups

# /usr/local/bin/pgsql-backup.sh
#!/bin/bash
BACKUP_DIR="/var/backups/pgsql"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d)
mkdir -p "$BACKUP_DIR"

# Backup all non-template databases
sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE datistemplate=false" | while read db; do
    sudo -u postgres pg_dump -Fc "$db" > "$BACKUP_DIR/${db}-${DATE}.dump"
    echo "Backed up: $db"
done

# Backup globals
sudo -u postgres pg_dumpall --globals-only > "$BACKUP_DIR/globals-${DATE}.sql"

# Retention
find "$BACKUP_DIR" -name "*.dump" -o -name "globals-*.sql" | xargs -I{} find {} -mtime +$RETENTION_DAYS -delete
echo "Backup complete: $(date)"
chmod +x /usr/local/bin/pgsql-backup.sh
echo "0 3 * * * root /usr/local/bin/pgsql-backup.sh >> /var/log/pgsql-backup.log 2>&1" > /etc/cron.d/pgsql-backup
mkdir -p /var/backups/pgsql && chown postgres:postgres /var/backups/pgsql

Conclusion

PostgreSQL backups on RHEL 9 use pg_dump for flexible per-database logical backups and pg_basebackup for physical cluster-level backups. The custom dump format (-Fc) with parallel restore (-j 4) is the fastest approach for large databases. Automating daily backups with a 30-day retention policy and storing backups off-server ensures data can be recovered after hardware failure or accidental deletion.

Next steps: How to Configure PostgreSQL Streaming Replication on RHEL 9, How to Configure Database Connection Pooling with PgBouncer on RHEL 9, and How to Back Up MySQL with mysqldump on RHEL 9.