Configure pg_basebackup for physical backups, enable WAL archiving for continuous protection, deploy pgBackRest for automated retention and verification, and write a restore test script that proves your backups actually work. This guide covers every layer of PostgreSQL backup strategy from single-server setups to Kubernetes-native solutions.
The most dangerous backup is the one you have never tested. A 2024 Percona survey found that 34% of organizations have never tested a PostgreSQL backup restore, and among those who have, 12% discovered their backups were corrupt or incomplete. Point-in-Time Recovery (PITR) lets you restore to any second in time, but only if WAL archiving is correctly configured. This guide makes sure your backups work when you need them most.
---
1. Understand PostgreSQL Backup Types
PostgreSQL offers three backup approaches, each with different trade-offs. Understanding when to use each is critical for designing a backup strategy that balances recovery time, storage cost, and operational complexity.
Backup type comparison
| Backup Type | Method | PITR Support | Speed | Storage Cost | Complexity |
| Logical (pg_dump) | SQL export | No | Slow for large DBs | Medium | Low |
| Physical (pg_basebackup) | File copy | Yes (with WAL) | Fast | High | Medium |
| Continuous (pgBackRest/Barman) | Incremental + WAL | Yes | Fastest restore | Optimized | Higher setup |
- Logical backups (pg_dump) export SQL statements. Good for small databases, schema migrations, and cross-version upgrades. Cannot do PITR.
- Physical backups (pg_basebackup) copy the data directory. Required for PITR when combined with WAL archiving. Fast restores because there is no SQL replay.
- Continuous backups (pgBackRest, Barman) build on physical backups with incremental support, parallel compression, and automated WAL management.
For production databases, always use physical backups with WAL archiving. Logical backups are a supplement, not a replacement.
---
2. Configure pg_basebackup
pg_basebackup creates a binary copy of the entire PostgreSQL data directory. It is the foundation of any PITR strategy. The backup runs while the database is online and does not block reads or writes.
Basic pg_basebackup command
<h1>Create a compressed base backup with progress reporting</h1>
pg_basebackup \
--host=localhost \
--port=5432 \
--username=replicator \
--pgdata=/backups/base/$(date +%Y%m%d_%H%M%S) \
--format=tar \
--gzip \
--compress=6 \
--checkpoint=fast \
--progress \
--verbose \
--wal-method=stream
Create the replication user
-- Create a dedicated replication user for backups
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure-password-here';
-- Grant necessary permissions
GRANT USAGE ON SCHEMA pg_catalog TO replicator;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO replicator;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean) TO replicator;
Configure pg_hba.conf for replication access
<h1>pg_hba.conf - Add replication entry</h1>
host replication replicator 127.0.0.1/32 scram-sha-256
host replication replicator 10.0.0.0/8 scram-sha-256
Automated daily backup script
#!/bin/bash
<h1>daily-pg-backup.sh</h1>
set -euo pipefail
BACKUP_DIR="/backups/base"
RETENTION_DAYS=7
LOG_FILE="/var/log/pg-backup.log"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
echo "[$(date -u +%Y-%m-%dT%H:%M:%SZ)] Starting backup" >> "$LOG_FILE"
<h1>Create backup</h1>
pg_basebackup \
--host=localhost \
--username=replicator \
--pgdata="$BACKUP_DIR/$TIMESTAMP" \
--format=tar \
--gzip \
--checkpoint=fast \
--wal-method=stream 2>> "$LOG_FILE"
<h1>Verify backup exists and is not empty</h1>
BACKUP_SIZE=$(du -sh "$BACKUP_DIR/$TIMESTAMP" | cut -f1)
echo "[$(date -u +%Y-%m-%dT%H:%M:%SZ)] Backup complete: $BACKUP_SIZE" >> "$LOG_FILE"
<h1>Clean up old backups</h1>
find "$BACKUP_DIR" -maxdepth 1 -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;
echo "[$(date -u +%Y-%m-%dT%H:%M:%SZ)] Cleanup complete" >> "$LOG_FILE"
---
3. Enable WAL Archiving for PITR
Write-Ahead Logging (WAL) records every change made to the database. By archiving WAL files, you can replay changes on top of a base backup to restore to any point in time. Without WAL archiving, you can only restore to the exact moment the base backup was taken.
Configure postgresql.conf for WAL archiving
<h1>postgresql.conf - WAL archiving settings</h1>
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backups/wal/%f'
archive_timeout = 300
<h1>WAL retention for streaming replication</h1>
max_wal_senders = 5
wal_keep_size = 2GB
Create WAL archive directory with proper permissions
<h1>Create the WAL archive directory</h1>
mkdir -p /backups/wal
chown postgres:postgres /backups/wal
chmod 700 /backups/wal
<h1>Reload PostgreSQL to apply changes</h1>
pg_ctl reload -D /var/lib/postgresql/data
<h1>Verify archiving is active</h1>
psql -c "SELECT * FROM pg_stat_archiver;"
WAL archiving health checks
| Check | Query | Healthy Value |
| Archiving enabled | `SHOW archive_mode;` | `on` |
| Last archived WAL | `SELECT last_archived_wal FROM pg_stat_archiver;` | Recent filename |
| Archive failures | `SELECT failed_count FROM pg_stat_archiver;` | 0 |
| WAL size on disk | `SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir();` | Below wal_keep_size |
| Replication lag | `SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) FROM pg_stat_replication;` | Below 100MB |
---
4. Deploy pgBackRest for Production Backups
pgBackRest is the industry-standard backup tool for PostgreSQL. It supports incremental and differential backups, parallel compression and transfer, backup verification, and S3/GCS/Azure storage backends. The Percona 2024 survey found that pgBackRest is used by 52% of PostgreSQL deployments with more than 100GB of data.
pgBackRest configuration
<h1>/etc/pgbackrest/pgbackrest.conf</h1>
[global]
repo1-type=posix
repo1-path=/backups/pgbackrest
repo1-retention-full=4
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=your-encryption-passphrase
process-max=4
compress-type=zst
compress-level=6
log-level-console=info
log-level-file=detail
[main]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
pg1-user=postgres
For S3 storage backend
<h1>Replace repo1 settings with S3</h1>
[global]
repo1-type=s3
repo1-s3-bucket=mycompany-pg-backups
repo1-s3-region=us-east-1
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-key=AKIAIOSFODNN7EXAMPLE
repo1-s3-key-secret=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
repo1-path=/pgbackrest
repo1-retention-full=4
repo1-retention-diff=14
pgBackRest commands
<h1>Initialize the backup repository</h1>
pgbackrest --stanza=main stanza-create
<h1>Run a full backup</h1>
pgbackrest --stanza=main --type=full backup
<h1>Run an incremental backup (daily)</h1>
pgbackrest --stanza=main --type=incr backup
<h1>Run a differential backup (weekly)</h1>
pgbackrest --stanza=main --type=diff backup
<h1>Verify backup integrity</h1>
pgbackrest --stanza=main verify
<h1>List all backups</h1>
pgbackrest --stanza=main info
<h1>Check WAL archiving status</h1>
pgbackrest --stanza=main check
Recommended backup schedule
| Backup Type | Frequency | Retention | Typical Size |
| Full | Weekly (Sunday 2am) | 4 weeks | 100% of DB |
| Differential | Daily (2am) | 14 days | 10-30% of DB |
| Incremental | Every 6 hours | 7 days | 1-5% of DB |
| WAL archive | Continuous | 14 days | Varies |
---
5. Point-in-Time Recovery (PITR)
PITR lets you restore the database to any specific second. This is essential for recovering from accidental data deletion (DROP TABLE, bad UPDATE) where you need to go back to the moment just before the mistake.
Restore with pgBackRest to a specific time
<h1>Stop PostgreSQL</h1>
systemctl stop postgresql
<h1>Restore to a specific point in time</h1>
pgbackrest --stanza=main \
--type=time \
--target="2026-03-20 14:30:00+00" \
--target-action=promote \
restore
<h1>Start PostgreSQL</h1>
systemctl start postgresql
<h1>Verify the restore</h1>
psql -c "SELECT pg_postmaster_start_time();"
psql -c "SELECT count(*) FROM your_critical_table;"
Manual PITR with pg_basebackup and WAL replay
<h1>Stop PostgreSQL</h1>
systemctl stop postgresql
<h1>Clear the data directory</h1>
rm -rf /var/lib/postgresql/16/main/*
<h1>Restore the base backup</h1>
tar -xzf /backups/base/20260320_020000/base.tar.gz \
-C /var/lib/postgresql/16/main/
<h1>Create recovery configuration</h1>
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << EOF
restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2026-03-20 14:30:00+00'
recovery_target_action = 'promote'
EOF
<h1>Create the recovery signal file</h1>
touch /var/lib/postgresql/16/main/recovery.signal
<h1>Set proper ownership</h1>
chown -R postgres:postgres /var/lib/postgresql/16/main/
<h1>Start PostgreSQL (it will replay WAL files)</h1>
systemctl start postgresql
<h1>Monitor recovery progress</h1>
tail -f /var/log/postgresql/postgresql-16-main.log
On Arxentra's managed PostgreSQL, PITR is enabled by default with a 14-day recovery window, and restores can be triggered from the dashboard.
---
6. Monthly Restore Test Script
A backup that has never been tested is not a backup. Run this script monthly to verify your backup integrity. According to the 2024 Veeam Data Protection Trends Report, organizations that test restores monthly are 4x more likely to achieve their recovery time objectives during actual incidents.
#!/bin/bash
<h1>monthly-restore-test.sh</h1>
<h1>Run this on a test server, NEVER on production</h1>
set -euo pipefail
TEST_PORT=5433
TEST_DIR="/tmp/pg-restore-test"
LOG="/var/log/pg-restore-test.log"
EXPECTED_TABLES=50 # Adjust to your schema
echo "[$(date -u +%Y-%m-%dT%H:%M:%SZ)] Starting restore test" > "$LOG"
<h1>Clean up any previous test</h1>
pg_ctl -D "$TEST_DIR" stop 2>/dev/null || true
rm -rf "$TEST_DIR"
<h1>Restore from latest backup</h1>
pgbackrest --stanza=main \
--pg1-path="$TEST_DIR" \
--type=immediate \
--target-action=promote \
restore 2>> "$LOG"
<h1>Start on a test port</h1>
pg_ctl -D "$TEST_DIR" -o "-p $TEST_PORT" -l "$TEST_DIR/logfile" start
sleep 5
<h1>Run validation queries</h1>
TABLE_COUNT=$(psql -p $TEST_PORT -t -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';")
echo "Tables found: $TABLE_COUNT" >> "$LOG"
if [ "$TABLE_COUNT" -lt "$EXPECTED_TABLES" ]; then
echo "FAIL: Expected $EXPECTED_TABLES tables, found $TABLE_COUNT" >> "$LOG"
RESULT="FAIL"
else
echo "PASS: Table count matches" >> "$LOG"
RESULT="PASS"
fi
<h1>Test a known query</h1>
USER_COUNT=$(psql -p $TEST_PORT -t -c "SELECT count(*) FROM users;" 2>/dev/null || echo "0")
echo "Users found: $USER_COUNT" >> "$LOG"
<h1>Clean up</h1>
pg_ctl -D "$TEST_DIR" stop
rm -rf "$TEST_DIR"
echo "[$(date -u +%Y-%m-%dT%H:%M:%SZ)] Restore test $RESULT" >> "$LOG"
echo "$RESULT"
---
CNPG (CloudNativePG) Backups on Kubernetes
For Kubernetes-native PostgreSQL, CNPG handles backups declaratively. This is the approach used on Arxentra's infrastructure and recommended for any Kubernetes-based deployment. Read our PostgreSQL HA guide for the full CNPG cluster setup.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: app-db
spec:
instances: 3
storage:
size: 50Gi
backup:
barmanObjectStore:
destinationPath: s3://my-backups/cnpg/
s3Credentials:
accessKeyId:
name: s3-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: s3-creds
key: SECRET_ACCESS_KEY
wal:
compression: gzip
data:
compression: gzip
retentionPolicy: "30d"
---
Frequently Asked Questions
How much storage do I need for backups?
For pgBackRest with weekly full + daily differential + 6-hourly incremental: plan for 3-4x your database size with 4-week retention. Compression (zstd level 6) typically achieves 3-5x compression on PostgreSQL data, so a 100GB database needs roughly 80-130GB of backup storage. WAL archiving adds 5-20GB per day depending on write volume.
Can I use pg_dump for PITR?
No. pg_dump creates a logical snapshot at a single point in time. It does not capture WAL changes, so you cannot replay transactions to an arbitrary point. Use pg_dump for schema exports, cross-version migrations, and small database copies. Use pg_basebackup + WAL archiving (or pgBackRest) for PITR.
How do I know if my WAL archiving is working?
Run `SELECT * FROM pg_stat_archiver;` and check that `last_archived_wal` is recent and `failed_count` is 0. Also verify that WAL files are appearing in your archive directory. Set up an alert that fires if no WAL file has been archived in the last `archive_timeout` seconds (default 300s).
What is the difference between pgBackRest and Barman?
Both are excellent. pgBackRest is written in C and tends to be faster for large databases. Barman is written in Python and has a slightly simpler configuration. pgBackRest has better incremental backup support and parallel operations. The Arxentra documentation uses pgBackRest for all examples because of its performance advantages with databases over 100GB.
How long does a PITR restore take?
Two phases: (1) restoring the base backup (speed depends on storage I/O, typically 100-500 MB/s), and (2) replaying WAL files. For a 100GB database with 24 hours of WAL replay, expect 20-60 minutes total. pgBackRest parallel restore can significantly reduce phase 1. The WAL replay phase speed depends on the number of write operations in the recovery window.
Should I encrypt my backups?
Yes, always. pgBackRest supports AES-256-CBC encryption natively. For S3 storage, enable both pgBackRest encryption and S3 server-side encryption (SSE-S3 or SSE-KMS) for defense in depth. Store encryption passphrases in a secrets manager, not in configuration files. If you lose the encryption passphrase, your backups are unrecoverable.