PostgreSQL Backup and Restore Strategies for Production Databases

TL;DR — Quick Summary

PostgreSQL backup and restore strategies: pg_dump, pg_dumpall, pg_basebackup, WAL archiving, PITR, and automated scripts with rotation and S3 upload.

PostgreSQL is the backbone of countless production applications — and losing data without a tested restore plan is catastrophic. This guide covers every major PostgreSQL backup and restore strategy: logical backups with pg_dump and pg_dumpall, physical backups with pg_basebackup, continuous WAL archiving for point-in-time recovery (PITR), automated scripts, encryption, and cloud upload. Each approach has different trade-offs; the best production setup uses a combination.

Prerequisites

• PostgreSQL 14 or newer (commands work for 12+, some flags differ).
• The postgres system user or a role with pg_read_all_data / SUPERUSER privileges.
• Sufficient disk space for backup files (at least 1.5× the size of your databases).
• aws-cli or rclone installed if using cloud storage.
• gpg installed for encrypted backups.

---

Logical Backups with pg_dump

pg_dump exports a single database into SQL or a custom binary format. It is the most portable backup method — you can restore into a different PostgreSQL version or a different OS.

Full Database Backup

# Custom format (recommended) — compressed, supports selective restore
pg_dump -U postgres -Fc -d mydb -f /backup/mydb_$(date +%Y%m%d).pgdump

# Plain SQL — human-readable but larger, restore with psql
pg_dump -U postgres -Fp -d mydb -f /backup/mydb_$(date +%Y%m%d).sql

Single Table Backup

# Dump only one table
pg_dump -U postgres -Fc -d mydb -t public.orders -f /backup/orders_$(date +%Y%m%d).pgdump

# Dump multiple tables with pattern
pg_dump -U postgres -Fc -d mydb -t 'public.order*' -f /backup/orders_all.pgdump

Custom Format vs Plain SQL

Feature	Custom Format (-Fc)	Plain SQL (-Fp)
Compression	Built-in (zlib)	No (use gzip separately)
Selective restore	Yes (-t, -n, -T)	No
Parallel restore	Yes (pg_restore -j)	No
Human-readable	No	Yes
Restore tool	pg_restore	psql

Use custom format for production backups. Plain SQL is useful for small databases you want to inspect or edit manually.

Schema-Only and Data-Only

# Schema only (structure, no data)
pg_dump -U postgres -s -d mydb -f /backup/schema_$(date +%Y%m%d).sql

# Data only (no CREATE TABLE statements)
pg_dump -U postgres -a -d mydb -f /backup/data_$(date +%Y%m%d).sql

---

Cluster-Wide Backups with pg_dumpall

pg_dumpall backs up the entire PostgreSQL cluster: all databases, global objects, roles (users), and tablespaces. It always produces plain SQL.

# Full cluster backup
pg_dumpall -U postgres -f /backup/cluster_$(date +%Y%m%d).sql

# Roles and tablespaces only (no database data)
pg_dumpall -U postgres --globals-only -f /backup/globals_$(date +%Y%m%d).sql

Restoring from pg_dumpall requires psql:

psql -U postgres -f /backup/cluster_20260322.sql

Use pg_dumpall to capture roles and permissions even if you back up individual databases with pg_dump. A common pattern is to run pg_dumpall --globals-only daily alongside per-database pg_dump custom-format dumps.

---

Physical Backups with pg_basebackup

pg_basebackup creates a binary copy of the PostgreSQL data directory — a physical backup. It is the foundation for standby servers and point-in-time recovery.

# Compressed tar format (recommended for backup storage)
pg_basebackup -U replicator -h localhost -D /backup/base \
  -Ft -z -P --wal-method=stream

# Plain directory format (ready to use as a data directory)
pg_basebackup -U replicator -h localhost -D /backup/base_dir \
  -Fp -P --wal-method=stream

Requirements for pg_basebackup:

• A PostgreSQL role with REPLICATION privilege: CREATE ROLE replicator REPLICATION LOGIN PASSWORD '...';
• max_wal_senders >= 2 in postgresql.conf.
• A matching entry in pg_hba.conf: host replication replicator 127.0.0.1/32 md5

The -Ft -z flags produce base.tar.gz and pg_wal.tar.gz in the output directory. The --wal-method=stream flag streams WAL during the backup, making the result immediately consistent — no separate WAL copy needed.

---

WAL Archiving and Point-in-Time Recovery

WAL (Write-Ahead Log) archiving, combined with a base backup, enables PITR — restoring your database to any past timestamp, not just the moment of the last backup.

Enable WAL Archiving

Edit /etc/postgresql/16/main/postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal_archive/%f'
archive_timeout = 300      # Force WAL switch every 5 minutes

For S3:

archive_command = 'aws s3 cp %p s3://my-pg-backups/wal/%f'

Reload PostgreSQL:

sudo systemctl reload postgresql

Verify Archiving is Working

SELECT last_archived_wal, last_archived_time, last_failed_wal
FROM pg_stat_archiver;

If last_failed_wal is not null, the archive command is failing — check permissions and disk space immediately.

Recovery to a Specific Point in Time

1. Stop PostgreSQL and replace the data directory with your base backup.
2. Create recovery.signal in the data directory (PostgreSQL 12+).
3. Set recovery parameters in postgresql.conf:

restore_command = 'cp /backup/wal_archive/%f %p'
recovery_target_time = '2026-03-22 14:30:00'
recovery_target_action = 'promote'

4. Start PostgreSQL — it will replay WAL up to the specified time and then promote.

---

Restoring from Backups

Restore with pg_restore (Custom Format)

# Full restore to an existing (empty) database
createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored /backup/mydb_20260322.pgdump

# Parallel restore (much faster on multi-core servers)
pg_restore -U postgres -j 4 -d mydb_restored /backup/mydb_20260322.pgdump

# Restore only one table
pg_restore -U postgres -d mydb_restored -t public.orders /backup/mydb_20260322.pgdump

Restore with psql (Plain SQL)

# Restore a plain SQL dump
psql -U postgres -d mydb_restored -f /backup/mydb_20260322.sql

# Restore the full cluster dump
psql -U postgres -f /backup/cluster_20260322.sql

Preview What is in a Backup

# List all objects in a custom-format backup (no actual restore)
pg_restore --list /backup/mydb_20260322.pgdump

# Filter the list to only tables
pg_restore --list /backup/mydb_20260322.pgdump | grep TABLE

---

Automated Backup Scripts

Daily Backup Script with Rotation

#!/bin/bash
# /usr/local/bin/pg-backup.sh

set -euo pipefail

BACKUP_DIR="/backup/postgresql"
DAYS_TO_KEEP=7
PG_USER="postgres"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR/daily"

# Back up each database
for DB in $(psql -U "$PG_USER" -t -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
    DB=$(echo "$DB" | xargs)
    pg_dump -U "$PG_USER" -Fc -d "$DB" \
        -f "$BACKUP_DIR/daily/${DB}_${DATE}.pgdump"
    echo "Backed up: $DB"
done

# Back up globals (roles, tablespaces)
pg_dumpall -U "$PG_USER" --globals-only \
    -f "$BACKUP_DIR/daily/globals_${DATE}.sql"

# Rotate old backups
find "$BACKUP_DIR/daily" -type f -mtime +"$DAYS_TO_KEEP" -delete

echo "Backup complete: $DATE"

Cron Schedule

# Edit crontab as root or postgres user
crontab -e

# Daily at 02:00, log output
0 2 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Weekly pg_basebackup

#!/bin/bash
# /usr/local/bin/pg-basebackup.sh

BACKUP_DIR="/backup/postgresql/base"
WEEKS_TO_KEEP=4

pg_basebackup -U replicator -D "$BACKUP_DIR/$(date +%Y%m%d)" \
    -Ft -z -P --wal-method=stream

# Keep only last N weeks
ls -dt "$BACKUP_DIR"/20* | tail -n +"$((WEEKS_TO_KEEP + 1))" | xargs rm -rf

# Run every Sunday at 01:00
0 1 * * 0 /usr/local/bin/pg-basebackup.sh >> /var/log/pg-basebackup.log 2>&1

---

Backup Security — Compression, Encryption, S3 Upload

Gzip Compression for Plain SQL

pg_dump -U postgres -Fp -d mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

# Restore from gzip
gunzip -c /backup/mydb_20260322.sql.gz | psql -U postgres -d mydb_restored

GPG Encryption

# Encrypt with a recipient public key
pg_dump -U postgres -Fc -d mydb | \
    gpg --encrypt --recipient admin@example.com \
    -o /backup/mydb_$(date +%Y%m%d).pgdump.gpg

# Decrypt and restore
gpg --decrypt /backup/mydb_20260322.pgdump.gpg | \
    pg_restore -U postgres -d mydb_restored

Upload to S3

# AWS CLI
aws s3 cp /backup/mydb_20260322.pgdump \
    s3://my-pg-backups/daily/ \
    --storage-class STANDARD_IA

# Sync the entire backup directory
aws s3 sync /backup/postgresql/ s3://my-pg-backups/ \
    --exclude "*.tmp" \
    --storage-class STANDARD_IA

# Rclone (works with any S3-compatible provider)
rclone copy /backup/postgresql/ remote:my-pg-backups/

Add lifecycle rules in S3 to automatically expire old backups — no need to manage deletion manually for cloud-stored files.

---

Comparison: PostgreSQL Backup Methods

Method	Speed	Backup Size	Selective Restore	PITR Support	Use Case
pg_dump (custom)	Medium	Small (compressed)	Yes	No	Daily per-database backup
pg_dump (plain SQL)	Medium	Large	No	No	Small DBs, portability
pg_dumpall	Slow	Large	No	No	Cluster + roles backup
pg_basebackup	Fast	Full data dir	No	Yes (with WAL)	PITR base, standby setup
WAL archiving	Continuous	Variable	No	Yes	Continuous protection

For most production PostgreSQL deployments, the recommended combination is:

1. Continuous WAL archiving — for zero data loss between backups.
2. Weekly pg_basebackup — fast full restore baseline.
3. Daily pg_dump (custom format) — for table-level and logical restores.
4. Daily pg_dumpall —globals-only — to capture role changes.

---

Real-World Scenario: Disaster Recovery Walkthrough

Situation: Your production PostgreSQL 16 server crashes at 15:47 on a Tuesday. The last pg_basebackup ran Sunday at 01:00. WAL archiving to S3 has been running continuously.

Goal: Restore to the state at 15:45 (2 minutes before the crash, after the last committed transaction you trust).

Step 1 — Provision a New Server

Install the same PostgreSQL version. Stop the service before configuring.

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

Step 2 — Restore the Base Backup

cd /var/lib/postgresql/16/main
sudo -u postgres tar -xzf /backup/base/20260320/base.tar.gz .
sudo -u postgres tar -xzf /backup/base/20260320/pg_wal.tar.gz pg_wal/

Step 3 — Configure Recovery

Edit /etc/postgresql/16/main/postgresql.conf:

restore_command = 'aws s3 cp s3://my-pg-backups/wal/%f %p'
recovery_target_time = '2026-03-22 15:45:00'
recovery_target_action = 'promote'

Create the recovery signal:

sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

Step 4 — Start PostgreSQL and Monitor Recovery

sudo systemctl start postgresql
sudo tail -f /var/log/postgresql/postgresql-16-main.log

You will see log lines like LOG: restored log file "0000000100000001" from archive as WAL segments are replayed. When recovery reaches the target time, PostgreSQL logs LOG: recovery stopping before commit and promotes to a normal read-write state.

Step 5 — Verify Data

SELECT COUNT(*) FROM orders WHERE created_at < '2026-03-22 15:46:00';
-- Confirm count matches expectations

Step 6 — Resume Operations

Update your application connection strings to point to the new server. Re-enable WAL archiving and start a fresh pg_basebackup immediately.

---

Gotchas and Edge Cases

• Large databases (>100 GB): Use parallel pg_dump with -j workers and pg_restore -j for significantly faster backup and restore. Avoid plain SQL format at this scale.
• Locks during backup: pg_dump takes an ACCESS SHARE lock on tables. Long-running transactions can block it. Run backups during low-traffic windows.
• Encoding mismatches: Always create the target database with the same encoding and locale as the source: createdb -E UTF8 --locale=en_US.UTF-8 mydb_restored.
• Sequences: Custom-format pg_dump captures sequence values. Plain SQL does too, but only the value at dump time — replay of inserts may reset sequences.
• Extensions: pg_dump includes extension creation (CREATE EXTENSION). Ensure the same extensions are available on the restore server.
• Tablespaces: If your database uses custom tablespaces, they must exist (with correct paths) on the restore server before running pg_restore.
• pg_basebackup and streaming replication: Do not confuse --wal-method=fetch (copies WAL at end) with --wal-method=stream (streams WAL continuously). Use stream for production to avoid WAL recycling during long backups.

---

Troubleshooting

Problem	Likely Cause	Solution
pg_dump: error: query failed: ERROR: permission denied	Role lacks read privileges	Grant pg_read_all_data or use postgres superuser
pg_restore: error: could not execute query: ERROR: relation already exists	Restoring into a non-empty database	Drop and recreate the target DB, or use --clean flag
archive_command failing silently	Wrong path or permissions	Check pg_stat_archiver.last_failed_wal and test the command manually
PITR stuck replaying WAL	recovery_target_time in the future or wrong timezone	Use UTC timestamps; check show timezone in psql
pg_basebackup: error: could not connect to server	No replication slot or pg_hba entry	Add host replication replicator 127.0.0.1/32 md5 to pg_hba.conf
Restore much slower than expected	Single-threaded restore of large dump	Use pg_restore -j N where N = number of CPU cores

Summary

• Use pg_dump (custom format) daily for per-database logical backups with selective restore capability.
• Use pg_dumpall —globals-only daily to capture roles and tablespace definitions.
• Use pg_basebackup weekly as a binary base for fast full restoration.
• Enable WAL archiving for continuous protection and PITR.
• Encrypt backups with GPG and store offsite on S3 or compatible storage.
• Test restores regularly — an untested backup is not a backup.
• Use cron + rotation scripts to automate and prune old backups.
• Monitor pg_stat_archiver and backup log files for silent failures.

Related Articles

• pgBackRest PostgreSQL Backup and PITR
• BorgBackup for Linux Server Backups
• Restic Encrypted Cloud Backups
• PostgreSQL Replication Setup
• MinIO Self-Hosted S3 Storage