PostgreSQL Backup Guide 2026: pg_dump, pgBackRest & Point-in-Time Recovery

Key Takeaways

• --format=custom always: Compressed, parallel-restorable. Never use --format=plain for production backups over 1GB.
• pgBackRest for PITR: pg_dump gives you a point-in-time snapshot. pgBackRest + WAL archiving gives you recovery to any second.
• Test restores automatically: Untested backups are assumptions, not guarantees.
• Encrypt before offsite: Use pgbackrest --cipher-type=aes-256-cbc or openssl enc before storing backups externally.

---

Introduction

Direct Answer: How do I back up PostgreSQL on Ubuntu 24.04 in 2026?

For most databases under 100GB: pg_dump -U postgres --format=custom --compress=9 mydb > mydb-$(date +%Y%m%d).dump. Restore with pg_restore -U postgres -d mydb_restored mydb-20260502.dump. For continuous protection (WAL archiving + point-in-time recovery): install pgBackRest, configure archive_mode=on and archive_command in postgresql.conf, then pgbackrest --stanza=main backup for scheduled full/incremental backups. Automate daily backups with a cron job or systemd timer. Test restores weekly by restoring to a separate mydb_test database and running row count checks.

---

Part 1: pg_dump Logical Backups

# Backup a single database (custom format — compressed, parallel restore)
pg_dump \
    -U postgres \
    --format=custom \
    --compress=9 \
    --verbose \
    mydb > /var/backups/postgresql/mydb-$(date +%Y%m%d_%H%M).dump

# Backup all databases + globals (roles, tablespaces)
pg_dumpall -U postgres --globals-only > /var/backups/postgresql/globals.sql
for db in $(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0','template1','postgres')"); do
    pg_dump -U postgres --format=custom "$db" > "/var/backups/postgresql/${db}-$(date +%Y%m%d).dump"
    echo "Backed up: $db"
done

Expected output:

pg_dump: dumping contents of table "public.users"
pg_dump: dumping contents of table "public.orders"
...
Backed up: mydb

# Restore
createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored --verbose /var/backups/postgresql/mydb-20260502.dump

# Parallel restore (faster for large databases)
pg_restore -U postgres -d mydb_restored --jobs=4 /var/backups/postgresql/mydb-20260502.dump

---

Part 2: pgBackRest for Continuous Archiving

# Install pgBackRest
sudo apt-get install -y pgbackrest

# Configure PostgreSQL for WAL archiving
sudo tee /etc/postgresql/17/main/conf.d/archiving.conf << 'EOF'
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica
max_wal_senders = 3
EOF

# pgBackRest configuration
sudo tee /etc/pgbackrest/pgbackrest.conf << 'EOF'
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=change_this_to_a_strong_passphrase

log-level-console=info
log-level-file=debug

[main]
pg1-path=/var/lib/postgresql/17/main
pg1-user=postgres
EOF

sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf

# Create repository and take first full backup
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo systemctl restart postgresql
sudo -u postgres pgbackrest --stanza=main check

sudo -u postgres pgbackrest --stanza=main backup --type=full

Expected output:

P00   INFO: backup command begin 2.53.0
P00   INFO: execute non-exclusive backup begin: backup begins after the next regular checkpoint
P00   INFO: backup start archive = 000000010000000000000001, lsn = 0/1000028
P00   INFO: full backup size = 3.8GB
P00   INFO: backup command end: completed successfully

# View backup info
sudo -u postgres pgbackrest --stanza=main info

Expected output:

stanza: main
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (17): 000000010000000000000001/000000010000000000000047

        full backup: 20260502-080000F
            timestamp start/stop: 2026-05-02 08:00:00+00 / 2026-05-02 08:08:23+00
            wal start/stop: 000000010000000000000001 / 000000010000000000000003
            database size: 3.8GB

---

Part 3: Point-in-Time Recovery

# Scenario: accidental DELETE at 14:30:00 — restore to 14:29:59

# Stop PostgreSQL
sudo systemctl stop postgresql

# Restore to specific timestamp
sudo -u postgres pgbackrest --stanza=main restore \
    --target="2026-05-02 14:29:59+00" \
    --target-action=promote \
    --delta

sudo systemctl start postgresql

# Verify recovery worked
psql -U postgres -c "SELECT now(), COUNT(*) FROM orders;"

---

Part 4: Automated Backup Schedule

# /etc/pgbackrest/backup-schedule.sh
cat > /usr/local/bin/postgres-backup.sh << 'SCRIPT'
#!/bin/bash
set -euo pipefail
LOG="/var/log/postgresql/backup.log"
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG"; }

DAY=$(date +%u)

if [ "$DAY" = "7" ]; then
    log "Running FULL backup"
    sudo -u postgres pgbackrest --stanza=main backup --type=full
elif [ "$DAY" = "4" ]; then
    log "Running DIFFERENTIAL backup"
    sudo -u postgres pgbackrest --stanza=main backup --type=diff
else
    log "Running INCREMENTAL backup"
    sudo -u postgres pgbackrest --stanza=main backup --type=incr
fi

log "Backup complete"
log "$(sudo -u postgres pgbackrest --stanza=main info | grep 'full backup' | tail -1)"
SCRIPT

chmod +x /usr/local/bin/postgres-backup.sh

# Run at 2 AM daily
(crontab -l 2>/dev/null; echo "0 2 * * * /usr/local/bin/postgres-backup.sh") | crontab -

---

Part 5: Automated Restore Testing

cat > /usr/local/bin/postgres-restore-test.sh << 'SCRIPT'
#!/bin/bash
set -euo pipefail
LOG="/var/log/postgresql/restore-test.log"
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG"; }

TEST_DB="mydb_restore_test"
log "Starting weekly restore test"

# Drop and recreate test database
psql -U postgres -c "DROP DATABASE IF EXISTS ${TEST_DB};"
createdb -U postgres "$TEST_DB"

# Restore latest backup
pg_restore -U postgres -d "$TEST_DB" --jobs=2 \
    "$(ls -t /var/backups/postgresql/*.dump | head -1)"

# Verify row counts match production
PROD_COUNTS=$(psql -U postgres -d mydb -t -c "
    SELECT tablename, n_live_tup FROM pg_stat_user_tables ORDER BY tablename;")

TEST_COUNTS=$(psql -U postgres -d "$TEST_DB" -t -c "
    SELECT tablename, n_live_tup FROM pg_stat_user_tables ORDER BY tablename;")

if [ "$PROD_COUNTS" = "$TEST_COUNTS" ]; then
    log "RESTORE TEST PASSED — row counts match production"
else
    log "RESTORE TEST FAILED — row count mismatch!"
    log "Production: $PROD_COUNTS"
    log "Restored: $TEST_COUNTS"
    exit 1
fi

psql -U postgres -c "DROP DATABASE ${TEST_DB};"
log "Test database cleaned up"
SCRIPT

chmod +x /usr/local/bin/postgres-restore-test.sh
# Run every Sunday at 3 AM
(crontab -l 2>/dev/null; echo "0 3 * * 0 /usr/local/bin/postgres-restore-test.sh") | crontab -

---

Conclusion

PostgreSQL is backed up with daily pg_dump for logical portability plus pgBackRest for continuous WAL archiving enabling point-in-time recovery. The automated restore test runs weekly — when it passes, you have proof the backups work, not just hope.

See How to Install PostgreSQL 17 on Ubuntu 24.04 and PostgreSQL Performance Tuning 2026 for the database this backup strategy protects.

---

People Also Ask

What is the difference between pg_dump and pgBackRest?

pg_dump creates a logical backup (SQL statements or custom binary format) that captures a consistent snapshot. It’s portable, works across PostgreSQL versions, and is the right tool for database migrations and smaller databases. pgBackRest creates physical backups (copies of the actual data files) plus continuous WAL archiving. Physical backups restore much faster for large databases (TB+) and enable point-in-time recovery. For most teams: use pg_dump for daily backups under 100GB, pgBackRest for continuous protection and PITR.

---

Part 5: Physical Backup vs Logical Backup

PostgreSQL supports both physical and logical backup strategies. Understanding the difference is critical for sovereign deployments.

• pg_dump (logical backup) exports schema and data in a database-agnostic format. It is portable across minor and major versions and ideal for migrations or smaller databases.
• pgBackRest physical backups copy the actual PostgreSQL data files and WAL segments. They restore faster for large databases and support point-in-time recovery (PITR).

For most production systems, use both: pgDump for portable snapshots and pgBackRest for efficient recovery and PITR. This two-layer strategy gives you migration flexibility and fast disaster recovery.

---

Part 6: Replication and Standby Backups

A standby replica is both a high availability and backup asset. With streaming replication, you can offload backups from the primary and verify consistency on the replica.

Configure a standby with primary_conninfo and restore_command:

primary_conninfo = 'host=primary.example.com port=5432 user=replicator password=secret'
restore_command = 'pgbackrest --stanza=main archive-get %f %p'

A standby can run read-only queries, report status, and act as a failover target. Use pgBackRest to keep WAL archived and available for recovery if the standby needs to rebuild.

---

Part 7: Incremental Backups and Delta Restore

For large datasets, full backups can be expensive. pgBackRest supports incremental and differential backups that only capture changed blocks.

Example command:

sudo -u postgres pgbackrest --stanza=main backup --type=incr

On restore, pgBackRest stitches full and incremental backups together automatically. This reduces backup time and storage while preserving restore capability.

Use a schedule such as weekly full, daily differential, and hourly incremental backups depending on your RPO.

---

Part 8: Backup Encryption and Integrity

Protect PostgreSQL backups with encryption and checksums. pgBackRest supports built-in AES-256 encryption:

repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=change_this_to_a_strong_passphrase

For pg_dump files, encrypt with OpenSSL or GPG before moving them offsite.

gpg --symmetric --cipher-algo AES256 --output /backup/mydb.dump.gpg /backup/mydb.dump

Always verify checksums after transfer:

sha256sum /backup/mydb.dump.gpg > /backup/mydb.dump.gpg.sha256
sha256sum -c /backup/mydb.dump.gpg.sha256

Encryption and integrity checks protect your backup repository from tampering and unauthorized access.

---

Part 9: Recovery Targets and Recovery Options

PostgreSQL recovery supports multiple target modes:

• recovery_target_time for a specific timestamp
• recovery_target_xid for a transaction boundary
• recovery_target_name for a named restore point
• recovery_target_inclusive to include or exclude the target event

Example recovery configuration:

recovery_target_time = '2026-05-02 14:29:59'
recovery_target_action = promote

Use named restore points before risky operations:

SELECT pg_create_restore_point('pre_migration');

If an incident occurs, restore to that named point instead of guessing a timestamp.

---

Part 10: Monitoring Backup Health and WAL Archiving

A backup strategy is only reliable when it is monitored. Track the following metrics:

• age of the latest full backup
• number of WAL files archived in the retention window
• success / failure of the last backup command
• restore test results

Use pgBackRest status commands and schedule a daily health check:

sudo -u postgres pgbackrest --stanza=main info
sudo -u postgres pgbackrest --stanza=main check

If WAL archiving stops, the backup chain can break. Configure alerts for archive_command failures and archive directory permissions issues.

---

Part 11: Cross-Version Restore and Portability

Logical backups are valuable because they can be restored across PostgreSQL versions. Use pg_dump when you need migration flexibility.

When restoring to a newer version:

pg_restore -U postgres -d mydb_restored --no-owner --no-privileges /backup/mydb.dump

For schema changes, run pg_upgrade only after you have both a physical backup and a logical backup. The physical backup allows fast failover back to the older cluster if the upgrade fails.

---

Part 12: Automated Restore Testing

A valid backup must be test-restorable. Automate weekly restore tests into a separate database or VM.

Example restore test script:

#!/usr/bin/env bash
set -euo pipefail
TEST_DB=restore_test_$(date +%Y%m%d_%H%M)
sudo -u postgres createdb "$TEST_DB"
pg_restore -U postgres -d "$TEST_DB" --jobs=4 /var/backups/postgresql/mydb-$(date +%Y%m%d).dump
EXPECTED=$(psql -U postgres -d mydb -t -c "SELECT COUNT(*) FROM users;")
ACTUAL=$(psql -U postgres -d "$TEST_DB" -t -c "SELECT COUNT(*) FROM users;")
if [ "$EXPECTED" != "$ACTUAL" ]; then
  echo "RESTORE TEST FAILED"
  exit 1
fi
sudo -u postgres dropdb "$TEST_DB"
echo "Restore test passed"

Run this script on a schedule and keep the logs in a local archive. If the restore test fails, investigate immediately.

---

Part 13: Recovery Documentation and Operational Playbooks

Document the restore path for each environment. A recovery playbook should include:

• expected backup names and storage paths
• commands to restore full and incremental backups
• commands to recover to a timestamp or restore point
• verification queries and success criteria
• rollback procedures if the restore fails

Keep the playbook in a secure, version-controlled location. Operators should be able to follow it without improvisation.

---

Part 14: Storage and Repository Design

Store backups in a well-organized repository with clearly labeled stanzas and timestamps. For pgBackRest, use a dedicated repository path such as /var/lib/pgbackrest.

When you archive backups offsite, preserve the directory layout and metadata. Include a manifest with each backup set:

backup-set: main
backup-type: full
timestamp: 2026-05-02T08:00:00Z
checksum: abc123...

This metadata makes recovery faster and reduces the risk of restoring the wrong backup.

---

Part 15: Common PostgreSQL Backup Failures and Fixes

Failure: archive_command returns non-zero

Check the command, repository permissions, and disk space. The archive command must succeed on every WAL segment.

Failure: pgBackRest stale link or missing WAL files

Verify that WAL files are available in the repository and that retention policy has not removed needed segments.

Failure: restore hangs on pg_restore

Use --jobs that match your CPU and I/O capacity. If the restore is still slow, verify that the backup file is not corrupted and that the destination disk has enough throughput.

---

Part 16: Backup Governance Checklist

• archive_mode is enabled and WAL is archived successfully.
• pgBackRest stanzas are configured and checked daily.
• Full backups and incremental backups are both available.
• Restore tests run on an isolated system weekly.
• Backup metadata includes timestamps, checksums, and encryption status.
• Recovery targets and restore points are documented.
• Retention policy matches RPO/RTO requirements.
• Backup users have minimal privileges.
• Offsite/air-gapped copies exist.
• Backup logs are monitored and alert on failures.

Part 17: Standby Rebuild and Cluster Recovery

In a cluster or standby architecture, recovery may require rebuilding replicas and promoting a standby. Document the full sequence strictly.

When a primary fails, the steps are:

1. Promote the most up-to-date standby.
2. Rebuild other replicas from the promoted node or from the backup repository.
3. Reconfigure connection endpoints.
4. Verify replication lag and query correctness.

For a standby rebuild using pgBackRest:

sudo -u postgres pgbackrest --stanza=main restore --delta

--delta rewrites only changed files, which speeds rebuilds. Use this on a standby host after a primary loss.

---

Part 18: Incremental Recovery and WAL Replay

Incremental recovery is the strength of WAL archiving. With PostgreSQL, you can apply WAL segments from the last backup to the target recovery point.

A recovery configuration may look like:

recovery_target_time = '2026-05-02 14:29:59'
recovery_target_action = promote
recovery_target_inclusive = false

If you need to recover to a transaction boundary, use recovery_target_xid:

recovery_target_xid = '0A/1BCD1234'

This is useful when you want to stop immediately before a problematic commit.

---

Part 19: Object Store and Remote Repository Targets

A sovereign PostgreSQL backup strategy can still use private object stores. Use a self-hosted S3-compatible service such as MinIO, or a secure on-premises object repository.

Configure pgBackRest with an S3 target:

[global]
repo1-type=s3
repo1-path=/backups
repo1-s3-bucket=postgresql-backups
repo1-s3-endpoint=http://minio.local:9000
repo1-s3-region=us-east-1
repo1-s3-key=myaccesskey
repo1-s3-key-secret=mysecretkey

Encrypt backups at the repository level if the object store is not fully isolated. A private object store still preserves sovereignty if it is under your control.

---

Part 20: Retention Modeling for PostgreSQL Backups

Retention should match business needs. A common model is:

• daily full backups for 14 days
• weekly full backups for 8 weeks
• monthly full backups for 12 months
• WAL segments retained for at least the current recovery window

pgBackRest retention settings help automate this. Monitor the repository size and adjust policies to balance storage and recovery capability.

---

Part 21: Restore to Alternative Environments

Sometimes you need to restore a PostgreSQL backup to a different host, different OS, or a test environment. Physical backups are faster but may depend on the same major PostgreSQL version. Logical backups are more portable.

For cross-host restores, use pg_restore with custom-format dumps and test the restored schema before promoting it.

createdb -U postgres mydb_test
pg_restore -U postgres -d mydb_test /var/backups/postgresql/mydb-20260502.dump

For physical backups, ensure the target node has compatible filesystem paths and PostgreSQL configuration.

---

Part 22: Backup Compliance and Audit Readiness

A sovereign PostgreSQL backup program should support audits.

Record:

• what was backed up
• when the backup completed
• who initiated it
• which encryption key was used
• the restore test results

Store these logs separately from the backup data. For audited environments, produce a secure bundle of backup evidence that includes metadata, logs, and verification outputs.

---

Part 23: Troubleshooting PostgreSQL Restores

Issue: pg_restore fails with permission errors

Confirm the target user owns the restored objects and that pg_restore is run with a user who has CREATEDB privileges.

Issue: pgBackRest cannot find WAL files

Check the archive path and make sure the repository contains the requested WAL segments. pgBackRest --stanza=main info helps identify gaps.

Issue: recovery stops at restore_command error

Inspect postgresql.log and verify the command returns 0. The restore_command must be able to fetch WAL segments from the repository reliably.

---

Part 24: Daily Backup Health Checks

A simple daily check script should verify:

• the latest full backup exists
• WAL archiving is current
• the repository is not read-only
• restore tests have passed within the expected window

Example command:

sudo -u postgres pgbackrest --stanza=main info
sudo -u postgres pgbackrest --stanza=main check

If these checks fail, alert immediately and investigate. A failed backup check is a higher priority than many routine operations.

---

Part 25: Final PostgreSQL Backup Governance Checklist

• Full backups and WAL archives are created regularly.
• Restore tests are performed weekly.
• Backup repositories are encrypted or stored on encrypted media.
• Recovery targets are documented and tested.
• Retention policies are enforced and audited.
• Standby rebuild procedures are documented.
• Backup health checks run daily.
• Backup logs are retained and reviewed.
• Compression and encryption metadata are preserved.
• Recovery runbooks are accessible and up to date.

Part 26: Restore Point Best Practices

Create named restore points before risky operations:

SELECT pg_create_restore_point('before_upgrade');

A named restore point gives you a reliable recovery target that is easier to use than a timestamp. It also makes post-mortem analysis more precise.

---

Part 27: Cross-Cluster Restore and Data Mobility

A portable backup strategy includes the ability to restore to a different cluster or environment. Use logical dumps for migration and physical backups for fast restores within a compatible cluster.

When moving data between clusters, validate the destination PostgreSQL version and extensions. Not all extensions or custom types are portable with a physical backup.

---

Part 28: Stanza Management and Multi-Service Backups

If you manage multiple databases or clusters, use separate pgBackRest stanzas for each environment. Each stanza has its own configuration and repository path.

Example:

[main]
pg1-path=/var/lib/postgresql/17/main

[analytics]
pg1-path=/var/lib/postgresql/17/analytics

This avoids accidental restore confusion and keeps backup metadata clearly separated.

---

Part 29: Restore Verification Metrics

After a restore, verify not just that the database starts, but that the data is correct. Use checks like:

• row counts on critical tables
• checksums of sample data
• application smoke tests

A simple verification query set ensures the restore is not only successful technically, but also correct for the expected workload.

---

Part 30: Backup Alert Triage and Response

When a backup failure occurs, triage it quickly:

1. identify the failed stage (backup, archive, check, restore)
2. inspect the log output
3. verify disk and repository availability
4. rerun the failed step if safe
5. document the incident

A fast response minimizes the window where the backup program is unreliable.

---

Part 31: Backups for Compliance and Sovereign Data Protection

PostgreSQL backups may contain regulated data. Apply the same controls to backups as you do to production data:

• encrypt backups at rest
• limit access to backup stores
• audit all restore operations
• retain backups according to policy

Treat backup media as sensitive data and protect it accordingly.

---

Part 32: Final PostgreSQL Recovery Readiness Checklist

• WAL archiving is configured and healthy.
• Backup info reports successful full and incremental backups.
• Restore point creation is documented and tested.
• Standby rebuild procedures are available.
• Restore verification metrics are recorded.
• Alert triage steps are defined.
• Backup stanzas are separated by environment.
• Compliance controls apply to backup media.
• Recovery runbooks are exercised regularly.
• The backup system supports restoration to a new cluster if needed.

Part 33: Local Restore Confirmation Process

After any restore, run a local confirmation process that checks application-critical tables and validates the recovered dataset. A simple check list should include:

• row counts for primary tables
• ability to run core application queries
• schema consistency checks
• WAL replay completion status

This confirmation process should be part of every recovery drill so you are confident the restored PostgreSQL database is actually usable.

Part 34: Operational Lessons from Restore Drills

After each restore drill, capture at least three lessons learned. Common observations include:

• missing WAL segments due to retention gaps
• environment-specific restore command variations
• performance bottlenecks during parallel restore

Add these lessons to your recovery runbook so the next drill is smoother and more reliable.

Part 35: Restore Triage Checklist

When a restore is needed, use a triage checklist to avoid wasted effort:

• identify whether the issue is data corruption, accidental deletion, or server failure
• choose the correct backup date and restore type
• confirm the target restore environment is available and isolated
• restore the backup to a test database first
• run validation queries and application smoke tests
• if successful, promote the restored environment or redirect traffic

A disciplined checklist reduces the chance of restoring the wrong data or extending downtime.

Part 36: Recovery Communication Protocol

Define a communication protocol for recovery events: who is notified, what status updates are needed, and when to escalate to leadership. This keeps restoration efforts coordinated and transparent.

Part 37: Recovery Team Handoff Notes

When handing a recovery to another operator, include the backup path, target restore time, and verification queries. These handoff notes make the process reliable even when the original operator is unavailable.

Part 38: Final Recovery Readiness Reminder

A restore is only as reliable as the documentation and communication supporting it, so keep both up to date every time the backup policy changes.

Part 39: Final Backup Confirmation Note

Treat restore practice as a regular part of the backup cycle. This keeps your team ready and the process dependable.

Further Reading

• How to Install PostgreSQL 17 on Ubuntu 24.04 — database installation
• PostgreSQL Performance Tuning 2026 — tune the database these backups protect
• MySQL Backup Guide 2026 — equivalent guide for MySQL/MariaDB

Tested on: Ubuntu 24.04 LTS (Hetzner CX32). PostgreSQL 17.4, pgBackRest 2.53. Last verified: May 2, 2026.