PostgreSQL provides two complementary tools for database backups: pg_dump and pg_basebackup. pg_dump takes logical backups of individual databases or selected objects and can restore to a different PostgreSQL version or host. pg_basebackup takes a physical, byte-for-byte copy of the entire data directory, which is the foundation of streaming replication and point-in-time recovery. This tutorial covers both approaches on RHEL 8, including WAL archiving and a point-in-time recovery overview.

Prerequisites

  • RHEL 8 server with PostgreSQL installed and running
  • Sufficient disk space in the backup destination (at least equal to the database size for logical backups, and equal to the data directory for physical backups)
  • The postgres OS user and superuser role in PostgreSQL
  • An NFS mount, external disk, or object-storage-bound path available at /backup (or adjust paths to suit)

Step 1 — Logical Backup with pg_dump (Custom Format)

The custom format (-F c) produces a compressed, single-file dump that pg_restore can restore selectively — by schema, table, or object. It is the most versatile format for routine database backups.

sudo mkdir -p /backup/pgdumps
sudo chown postgres:postgres /backup/pgdumps

# Dump a single database in custom format
sudo -u postgres pg_dump 
  -U postgres 
  -d mydb 
  -F c 
  -f /backup/pgdumps/mydb_$(date +%F).dump

# List the dump contents without restoring
sudo -u postgres pg_restore --list /backup/pgdumps/mydb_$(date +%F).dump | head -30

Step 2 — Dump All Databases with pg_dumpall

pg_dumpall writes a plain-text SQL script that recreates every database, global object (roles, tablespaces), and their data. It is the only built-in way to capture global roles that individual pg_dump calls do not include.

# Full cluster dump including global objects
sudo -u postgres pg_dumpall 
  -U postgres 
  -f /backup/pgdumps/cluster_$(date +%F).sql

# Dump only global objects (roles and tablespaces, no data)
sudo -u postgres pg_dumpall -g 
  -f /backup/pgdumps/globals_$(date +%F).sql

Step 3 — Restore from a Custom-Format Dump

Use pg_restore to load the custom-format dump back into PostgreSQL. Create the target database first, then restore. The -j flag enables parallel restore using multiple worker processes, which speeds up large restores significantly.

# Create the target database
sudo -u postgres createdb mydb_restored

# Restore using 4 parallel jobs
sudo -u postgres pg_restore 
  -U postgres 
  -d mydb_restored 
  -j 4 
  /backup/pgdumps/mydb_$(date +%F).dump

# Restore only a specific table
sudo -u postgres pg_restore 
  -U postgres 
  -d mydb_restored 
  -t orders 
  /backup/pgdumps/mydb_$(date +%F).dump

Step 4 — Physical Backup with pg_basebackup

pg_basebackup copies the entire PostgreSQL data directory while the server is running. The -Ft flag writes the backup as a tar archive, -z applies gzip compression, and -P prints progress. This backup can be used to seed a replica or as the base for point-in-time recovery.

sudo mkdir -p /backup/pgbase
sudo chown postgres:postgres /backup/pgbase

sudo -u postgres pg_basebackup 
  -U postgres 
  -D /backup/pgbase 
  -Ft 
  -z 
  -P 
  -Xs

# Verify the archive
ls -lh /backup/pgbase/

The backup directory will contain base.tar.gz (data directory) and pg_wal.tar.gz (WAL segments collected during the backup).

Step 5 — Enable WAL Archiving for Point-in-Time Recovery

WAL archiving copies completed WAL segment files to an archive directory as PostgreSQL finishes writing them. Combined with a base backup, archived WAL segments allow you to recover the database to any point in time, not just the moment the backup was taken. Edit postgresql.conf to enable archiving.

sudo mkdir -p /backup/pgwal_archive
sudo chown postgres:postgres /backup/pgwal_archive

sudo nano /var/lib/pgsql/data/postgresql.conf

Set the following directives:

wal_level        = replica
archive_mode     = on
archive_command  = 'cp %p /backup/pgwal_archive/%f'
archive_timeout  = 300   # force a new segment after 5 minutes of inactivity
sudo systemctl reload postgresql

# Confirm archiving is working by checking the archive directory after activity
ls /backup/pgwal_archive/ | head

Step 6 — Point-in-Time Recovery Overview

To perform a point-in-time recovery (PITR), stop PostgreSQL, replace the data directory with the base backup, and create a recovery.signal file alongside a postgresql.auto.conf entry specifying the target time. PostgreSQL will apply archived WAL until the target is reached and then pause in a consistent state.

# Stop the server
sudo systemctl stop postgresql

# Replace the data directory with the base backup (adjust paths)
sudo rm -rf /var/lib/pgsql/data/*
sudo -u postgres tar -xzf /backup/pgbase/base.tar.gz -C /var/lib/pgsql/data/

# Signal PITR mode
sudo -u postgres touch /var/lib/pgsql/data/recovery.signal

# Set the target time in postgresql.auto.conf
sudo -u postgres tee -a /var/lib/pgsql/data/postgresql.auto.conf <<'EOF'
restore_command  = 'cp /backup/pgwal_archive/%f %p'
recovery_target_time = '2026-05-17 03:00:00'
recovery_target_action = 'promote'
EOF

sudo systemctl start postgresql

Conclusion

You have learned to take logical backups with pg_dump and pg_dumpall, restore selectively with pg_restore, capture physical backups with pg_basebackup, archive WAL segments continuously, and perform a point-in-time recovery. Together these tools cover every common PostgreSQL backup scenario on RHEL 8 from single-table restores to full-cluster disaster recovery.

Next steps: How to Set Up PostgreSQL Streaming Replication on RHEL 8, How to Automate PostgreSQL Backups with Cron on RHEL 8, and How to Configure PostgreSQL Remote Access and SSL on RHEL 8.