How to Back Up PostgreSQL with pg_dump and pg_basebackup on RHEL 7

A reliable backup strategy is one of the most critical operational requirements for any PostgreSQL deployment. On RHEL 7, PostgreSQL provides several complementary backup tools suited to different recovery scenarios: pg_dump for logical, per-database backups that can be restored selectively; pg_dumpall for capturing the entire cluster including global objects; and pg_basebackup for physical backups that form the foundation of point-in-time recovery. This guide covers all three tools, explains when to use each, demonstrates WAL archiving for continuous backup, and shows how to automate the process with cron on RHEL 7.

Prerequisites

  • RHEL 7 with PostgreSQL 9.6 installed and running
  • Root or sudo access
  • Sufficient disk space on backup destination (local or NFS mount)
  • The postgres OS user has write access to the backup directory
  • Familiarity with basic PostgreSQL administration

Step 1: Logical Backup with pg_dump — Custom Format

The pg_dump command exports a single database to a backup file. The custom format (-F c) is the recommended format for production use: it is compressed, supports parallel restore with multiple jobs, and allows selective table restoration.

# Create the backup directory
sudo mkdir -p /backups/postgresql
sudo chown postgres:postgres /backups/postgresql

# Dump the "appdb" database in custom format
sudo -u postgres pg_dump 
  -F c 
  -Z 9 
  -f /backups/postgresql/appdb_$(date +%Y%m%d_%H%M%S).dump 
  appdb

Options used:

  • -F c — custom format (compressed, supports parallel restore)
  • -Z 9 — maximum compression level (1-9)
  • -f — output file path
  • appdb — name of the database to back up

To back up a specific schema or table only:

# Dump a single schema
sudo -u postgres pg_dump -F c -n public -f /backups/postgresql/appdb_public.dump appdb

# Dump a single table
sudo -u postgres pg_dump -F c -t orders -f /backups/postgresql/appdb_orders.dump appdb

Step 2: Logical Backup with pg_dump — Plain SQL Format

The plain SQL format (-F p) produces a human-readable .sql file containing CREATE and INSERT statements. It is useful for migration, inspection, or loading into a different database system, but does not support parallel restore or selective table recovery.

sudo -u postgres pg_dump 
  -F p 
  -f /backups/postgresql/appdb_plain_$(date +%Y%m%d).sql 
  appdb

Compress the plain SQL output manually to save space:

sudo -u postgres pg_dump -F p appdb | gzip > /backups/postgresql/appdb_plain_$(date +%Y%m%d).sql.gz

Step 3: Restore with pg_restore

Use pg_restore to restore from a custom-format dump. First create the target database if it does not exist:

sudo -u postgres createdb appdb_restored

sudo -u postgres pg_restore 
  -d appdb_restored 
  -j 4 
  /backups/postgresql/appdb_20260517_120000.dump

The -j 4 flag enables parallel restore with 4 worker processes, which significantly reduces restore time for large databases by restoring indexes and data concurrently.

To restore only a specific table:

sudo -u postgres pg_restore 
  -d appdb 
  -t orders 
  /backups/postgresql/appdb_20260517_120000.dump

To restore a gzip-compressed plain SQL backup:

gunzip -c /backups/postgresql/appdb_plain_20260517.sql.gz | sudo -u postgres psql appdb

Step 4: Dump All Databases with pg_dumpall

pg_dumpall backs up the entire PostgreSQL cluster, including all databases, roles, tablespaces, and other global objects that pg_dump does not capture. It always produces plain SQL output.

sudo -u postgres pg_dumpall 
  -f /backups/postgresql/pgcluster_$(date +%Y%m%d_%H%M%S).sql

To back up only global objects (roles and tablespaces) without database data — useful for migrating users to a new cluster:

sudo -u postgres pg_dumpall --globals-only 
  -f /backups/postgresql/pgcluster_globals_$(date +%Y%m%d).sql

Restore a full cluster dump to a new PostgreSQL instance:

sudo -u postgres psql -f /backups/postgresql/pgcluster_20260517_120000.sql postgres

Step 5: Physical Backup with pg_basebackup

A physical backup captures the entire binary state of the PostgreSQL data directory and can be used as the starting point for streaming replication standbys or point-in-time recovery (PITR). It is the fastest way to clone a large database cluster because it copies data files directly rather than generating SQL.

sudo -u postgres pg_basebackup 
  -h localhost 
  -U postgres 
  -D /backups/postgresql/basebackup_$(date +%Y%m%d) 
  -F tar 
  -z 
  -P 
  --xlog-method=stream

Options explained:

  • -F tar — output as compressed tar archives (one per tablespace)
  • -z — gzip compress the tar output
  • -P — show progress
  • --xlog-method=stream — stream WAL during the backup for a consistent backup set

The resulting directory will contain base.tar.gz and pg_wal.tar.gz (or pg_xlog.tar.gz in older versions).

Step 6: Configure WAL Archiving

WAL archiving, combined with a base backup, enables point-in-time recovery (PITR) — the ability to restore the database to any moment in time, not just the moment of a backup. Edit postgresql.conf:

sudo vi /var/lib/pgsql/9.6/data/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'

Create the WAL archive directory:

sudo mkdir -p /backups/wal
sudo chown postgres:postgres /backups/wal

Restart PostgreSQL to apply archiving settings:

sudo systemctl restart postgresql-9.6

Force a WAL switch to confirm archiving is working:

sudo -u postgres psql -c "SELECT pg_switch_xlog();"
ls /backups/wal/

You should see WAL segment files (16 MB each) accumulating in the archive directory.

Step 7: Automate Backups with Cron

Create a backup script and schedule it via cron to run automatically. Create the script:

sudo vi /usr/local/bin/pg_backup.sh
#!/bin/bash
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
LOG="/var/log/pg_backup.log"

echo "[$(date)] Starting PostgreSQL backup" >> "$LOG"

# Dump each database individually
for DB in $(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;"); do
    sudo -u postgres pg_dump -F c -Z 6 
        -f "${BACKUP_DIR}/${DB}_${TIMESTAMP}.dump" 
        "$DB" >> "$LOG" 2>&1
    echo "[$(date)] Backed up $DB" >> "$LOG"
done

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete >> "$LOG" 2>&1

echo "[$(date)] Backup complete" >> "$LOG"
sudo chmod +x /usr/local/bin/pg_backup.sh

Schedule the script to run nightly at 2:00 AM using cron. Edit the crontab for the postgres user:

sudo crontab -u postgres -e
# Run PostgreSQL backup every day at 02:00 AM
0 2 * * * /usr/local/bin/pg_backup.sh

Test the script manually before relying on cron:

sudo bash /usr/local/bin/pg_backup.sh
ls -lh /backups/postgresql/

Step 8: Test Your Restore Process

A backup that has never been tested is not a backup you can rely on. At least quarterly, perform a full restore to a test server and verify data integrity:

# On the test server: create target database
sudo -u postgres createdb appdb_test

# Restore from the most recent dump
LATEST=$(ls -t /backups/postgresql/appdb_*.dump | head -1)
sudo -u postgres pg_restore -d appdb_test -j 4 "$LATEST"

# Verify row counts match expectations
sudo -u postgres psql -d appdb_test -c "dt"
sudo -u postgres psql -d appdb_test -c "SELECT COUNT(*) FROM orders;"

Conclusion

A complete PostgreSQL backup strategy on RHEL 7 combines logical backups with pg_dump and pg_dumpall for flexible, targeted recovery with physical backups via pg_basebackup and WAL archiving for point-in-time recovery. Logical backups are ideal for routine protection of individual databases and for migration; physical backups are essential for full cluster recovery and establishing streaming replication standbys. Schedule your backups with cron, monitor the log output, enforce a retention policy, and — most importantly — regularly test your restore procedures so that when a failure occurs, recovery is a practiced routine rather than an emergency improvisation.