A practical, version-aware runbook for PostgreSQL backup and restore covering logical (pg_dump/pg_restore) and physical (pg_basebackup + WAL/PITR) methods with copy-pastable commands, verification steps, failure-mode diagnostics, and a repeatable operations checklist. Target audience: DBAs and engineers who must choose the right method for their RPO/RTO, execute safely on a test target first, validate at multiple layers, and recover confidently when things go wrong.

## Prerequisites & Assumptions

- PostgreSQL versions : Examples target PostgreSQL 14–16. Syntax differences for older versions are noted in the Version-Specific Notes Appendix.

- Operating system : Linux (systemd-based) with standard filesystem (ext4, xfs). Separate volumes for data and WAL are recommended.

- Disk space : At least 1.5×–2× the database size free on the backup destination; additional space for WAL archive and staging during restore.

- Time synchronization : NTP or chrony must be running on all nodes; PITR timestamps must be in UTC.

- Backup user privileges :

- Logical: least-privilege role with CONNECT and SELECT on target databases, USAGE on schemas.

- Physical: superuser or role with REPLICATION privilege for pg_basebackup .

- Client-tool parity : pg_dump , pg_restore , pg_basebackup , and psql versions must match or be newer than the server major version.

- Managed-service constraints ☁️ MANAGED SERVICE : RDS, Cloud SQL, and Azure Database for PostgreSQL restrict filesystem access, pg_basebackup , wal_level changes, and custom archive_command . Use provider-native automated backups and PITR via console/CLI (e.g., aws rds restore-db-instance-to-point-in-time ). Logical dumps remain portable.

## Architecture & Method Selection

Choose the method that matches your RPO, RTO, data size, and portability needs.

- Method — Primary Use Case — RPO/RTO Notes — Command Starter

- Logical dump — Migrations, selective restore, version upgrades — RPO = last dump; RTO slower on large DBs — pg_dump -Fc -f ... appdb

- Logical globals — Roles, tablespaces, config grants — Needed before data restore — pg_dumpall -g > globals.sql

- Physical + WAL — Large DBs, PITR, standby seed — Low RPO with continuous WAL; fast RTO — pg_basebackup -D ... -X stream

Decision factors :

- Data size > 500 GB → prefer physical.

- Cross-major-version migration → logical only.

- RPO < 5 minutes → physical with streaming WAL archive.

- Managed service → logical or provider PITR API.

- Need to restore single schema/table → logical directory format with pg_restore -n / -t .

💡 TIP : Pilot first. Start with a single database or small schema, validate end-to-end, then expand scope.

## Security Hardening

🔒 SECURITY : Protect backup data at rest, in transit, and during handling.

- Encryption at rest : Encrypt dump files and base backup directories with gpg or age before writing to long-term storage.

pg_dump -Fd -j 4 -f - appdb | gpg --encrypt --recipient backup-key > /backups/appdb_<DATE>.dmp.gpg 

- Encryption in transit : Use TLS for remote pg_dump / pg_basebackup .

 pg_dump "postgresql://user@host:5432/db?sslmode=verify-full&sslrootcert=/path/ca.crt" -Fc -f appdb.dump
 pg_basebackup -h host -D /backups/base -X stream --sslmode=verify-full --sslrootcert=/path/ca.crt 

- Credential handling : Use .pgpass (mode 0600) or environment variables ( PGPASSWORD , PGSSLMODE ). Never hard-code passwords in scripts. For managed services, prefer IAM authentication.

- File permissions : Backup destination directories 0700 , dump files 0600 , data directories 0700 owned by postgres .

- Secrets in globals.sql : pg_dumpall -g emits CREATE ROLE ... PASSWORD '...' . Encrypt the globals file immediately; rotate passwords after restore rehearsal.

## Logical Backup & Restore

### Globals Dump

 Capture roles, tablespaces, and grants first. ⚠️ WARNING : pg_dumpall -g does not capture ALTER DEFAULT PRIVILEGES or event triggers. Dump them separately if used:

pg_dumpall -g > globals.sql
# Capture default privileges
psql -d postgres -c "SELECT pg_catalog.pg_get_userbyid(d.defaclrole), n.nspname, d.defaclacl FROM pg_default_acl d JOIN pg_namespace n ON d.defaclnamespace = n.oid;" > default_privs.sql 

### Database Dump (Directory Format, Parallel)

 pg_dump -Fd -j 4 -f /backups/appdb_<DATE>_dir appdb 
 Expected: directory /backups/appdb_<DATE>_dir with toc.dat and numbered segment files, non-zero total size.

### Selective/Schema-Only Options

- Schema only: pg_dump -s -Fd -j 4 -f /backups/appdb_schema_<DATE>_dir appdb

- Data only: pg_dump -a -Fd -j 4 -f /backups/appdb_data_<DATE>_dir appdb

- Exclude tables: pg_dump -T 'temp_*' -Fd -j 4 -f /backups/appdb_<DATE>_dir appdb

### Restore Globals

Run on target cluster before data restore:

psql -f globals.sql postgres
psql -f default_privs.sql postgres # if captured 
 Expected: CREATE ROLE / CREATE TABLESPACE messages; notices if objects exist.

### Create Target Database

createdb -T template0 -E UTF8 -O appuser appdb
psql -d postgres -c "\l+ appdb" 
 Verify encoding, collation, owner match source.

### Parallel Restore from Directory Format

pg_restore -d appdb -j 4 --clean --if-exists -v /backups/appdb_<DATE>_dir 
 Expected: SQL statements replay; zero errors at completion.

### Post-Restore Fixes

- Extensions : Install binaries on target, then CREATE EXTENSION IF NOT EXISTS name VERSION 'x.y'; to match source versions.

- Sequences : Fix off-by-one with is_called=false :

SELECT setval(pg_get_serial_sequence('public.orders','id'),
 (SELECT COALESCE(MAX(id),0)+1 FROM public.orders), false); 
 📌 VERSION NOTE : is_called=false sets the next nextval() to the given value; true would set the last returned value, causing the next nextval() to return value+1.

- Search path / default privileges : Re-apply from default_privs.sql if captured.

## Physical Backup & PITR

### WAL Archiving Configuration (Production)

🔒 SECURITY : The cp example below is for local demo only. Production must use a durable, off-host destination (pgBackRest, Barman, WAL-G, or cloud object storage). test ! -f is not sufficient for production safety (race conditions, no retry, no integrity verification).

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p' # or wal-g/barman equivalent
max_wal_senders = 10
wal_keep_size = 2GB # or use replication slots 
 mkdir -p /var/lib/pgsql/wal_archive
chown postgres:postgres /var/lib/pgsql/wal_archive
chmod 700 /var/lib/pgsql/wal_archive
systemctl reload postgresql
psql -d postgres -c "SELECT pg_switch_wal();"
ls -l /var/lib/pgsql/wal_archive 
 Expected: new WAL files appear in archive within seconds.

📌 VERSION NOTE : wal_keep_size (PG13+); older versions use wal_keep_segments . wal_level = replica is correct for PG10+.

### Base Backup with Throttling and Replication Slot

pg_basebackup -D /backups/base/<DATE> \
 --max-rate=100M \
 --wal-method=stream \
 --slot=backup_slot \
 --create-slot \
 -Fp -v -P -X stream 
 Expected: data files copied to destination; size roughly equals source data directory. --max-rate throttles I/O to avoid impacting primary.

📌 VERSION NOTE : --create-slot requires PG12+. pg_basebackup -X stream requires max_wal_senders and either a replication slot or sufficient wal_keep_size to prevent WAL removal during backup.

### PITR Restore to New Data Directory (Alternate Port)

# Stop any instance using target directory
systemctl stop postgresql

# Prepare clean data directory
rm -rf /var/lib/pgsql/data_restore
mkdir -p /var/lib/pgsql/data_restore
chown postgres:postgres /var/lib/pgsql/data_restore
chmod 700 /var/lib/pgsql/data_restore

# Stage base backup
rsync -a --delete /backups/base/<DATE>/ /var/lib/pgsql/data_restore/
chown -R postgres:postgres /var/lib/pgsql/data_restore

# Configure recovery (PG12+)
cat >> /var/lib/pgsql/data_restore/postgresql.conf <<'EOF'
restore_command = 'pgbackrest --stanza=main archive-get %f %p'
recovery_target_time = '<DATE> 14:25:00 UTC'
recovery_target_action = 'pause'
# For standby promotion path after PITR:
primary_conninfo = 'host=primary_host port=5432 user=replicator password=secret'
EOF

touch /var/lib/pgsql/data_restore/recovery.signal

# Start on alternate port
pg_ctl -D /var/lib/pgsql/data_restore -o "-p 5444" -l /var/lib/pgsql/data_restore.log start

# Monitor recovery
tail -f /var/lib/pgsql/data_restore.log
psql -p 5444 -d postgres -c "SELECT pg_is_in_recovery();" # true while paused 

### Inspect and Promote

 psql -p 5444 -d appdb -c "SELECT count(*) FROM public.orders;"
psql -p 5444 -d postgres -c "SELECT current_timestamp;"
psql -p 5444 -d postgres -c "SELECT pg_promote();"
psql -p 5444 -d postgres -c "SELECT pg_is_in_recovery();" # false after promote 
 📌 VERSION NOTE : pg_promote() exists since PG12. For PG11 and older, use pg_ctl promote -D ... or create promote.signal file.

## Verification & Validation

Validate at five layers. Automate these checks in rehearsal scripts.

### 1. File Existence & Catalog

# Logical
ls -lh /backups/appdb_<DATE>_dir/
pg_restore -l /backups/appdb_<DATE>_dir/toc.dat | head -30

# Physical
ls -la /var/lib/pgsql/data_restore/
test -f /var/lib/pgsql/data_restore/recovery.signal && echo "recovery.signal present" 

### 2. Disposable Restore & Data Checks

 # Logical: restore to throwaway DB
createdb -T template0 verify_db
pg_restore -d verify_db -j 4 /backups/appdb_<DATE>_dir 

### 3. Validation Queries Pack (Reusable SQL Block)

 Save as validation_queries.sql and run against restored instance:

-- Table counts vs baseline (replace with your key tables)
SELECT 'orders' AS tbl, count(*) FROM public.orders
UNION ALL SELECT 'customers', count(*) FROM public.customers
UNION ALL SELECT 'products', count(*) FROM public.products;

-- Sequence health: nextval will not collide
SELECT seq.relname AS sequence,
 seq_last_value(seq.oid) AS last_value,
 (SELECT COALESCE(MAX(id),0) FROM public.orders) AS table_max
FROM pg_class seq
JOIN pg_depend d ON d.objid = seq.oid AND d.deptype = 'a'
JOIN pg_class tbl ON d.refobjid = tbl.oid
WHERE seq.relkind = 'S' AND tbl.relname = 'orders';

-- TOAST size check
SELECT pg_size_pretty(pg_total_relation_size('public.orders')) AS total,
 pg_size_pretty(pg_relation_size('public.orders')) AS main,
 pg_size_pretty(pg_total_relation_size('public.orders') - pg_relation_size('public.orders')) AS toast;

-- Index validity
SELECT indexrelid::regclass AS index, indisvalid FROM pg_index WHERE NOT indisvalid;

-- Statistics refresh
SELECT pg_stat_clear_snapshot();
VACUUM (ANALYZE);

-- Application-specific checksum (example)
SELECT md5(string_agg(id::text || ':' || email, ',' ORDER BY id)) FROM public.customers; 

### 4. Application Smoke Test

- Connect app read-only user to restored instance.

- Execute read-only workflows (list, search, detail views).

- Verify expected data freshness matches recovery target.

### 5. PITR-Specific Checks

 # Confirm pause at target
psql -p 5444 -d postgres -c "SELECT pg_is_in_recovery();" # true
# Check last replayed LSN
psql -p 5444 -d postgres -c "SELECT pg_last_wal_replay_lsn();"
# After promote
psql -p 5444 -d postgres -c "SELECT pg_is_in_recovery();" # false
psql -p 5444 -d postgres -c "SELECT pg_control_checkpoint();" # new timeline 

## Failure Modes & Troubleshooting Decision Tree

 Text-form flowchart: Restore fails → Check logs → Identify class → Apply fix → Re-verify

- Symptom / Log Indicator — Class — Resolution Steps

- pg_restore: error: could not execute query: ERROR: syntax error at or near "..." — Version mismatch — Use pg_dump from client ≥ source major version. Restore with tools matching target version. For major upgrades, logical only.

- ERROR: could not access file "postgis": No such file or directory — Missing extension — Install extension packages on target ( postgresql-16-postgis-3 ). CREATE EXTENSION postgis VERSION '3.4.0'; before restore.

- ERROR: encoding "UTF8" does not match locale "en_US.ISO8859-1" — Encoding/collation — Create DB with template0 -E UTF8 --locale=en_US.UTF-8 . Match source SHOW lc_collate; .

- permission denied for table orders — Ownership/privileges — Restore globals first. REASSIGN OWNED BY old_role TO new_role; GRANT ALL ON ALL TABLES IN SCHEMA public TO appuser; .

- archive command failed with exit code 1 / WAL files missing — Archive command failure — Test archive_command manually as postgres user. Verify destination writable, network reachable, credentials valid. Switch to pgBackRest/WAL-G.

- could not open file "base/16384/12345": No such file or directory — Incomplete base backup — Re-run pg_basebackup -X stream with sufficient disk. Do not interrupt. Verify backup_label and tablespace_map exist.

- Recovery stops early/late; data includes/excludes recent txns — PITR over/under-shoot — Use recovery_target_action = 'pause' . Inspect pg_last_wal_replay_lsn() . Adjust recovery_target_time or use recovery_target_lsn from pg_create_restore_point() .

- FATAL: data directory "/var/lib/pgsql/data" has wrong ownership — Filesystem/permissions — chown -R postgres:postgres /var/lib/pgsql/data && chmod 700 /var/lib/pgsql/data .

- pg_restore: [archiver (db)] connection to database failed: timeout — Large DB timeout — Increase statement_timeout / idle_in_transaction_session_timeout . Use pg_restore -j N (directory format). Prefer physical for >500 GB.

- connection reset by peer during remote dump — Network issues — Run dumps locally on DB host. Use directory format ( -Fd ) for resumability. Enable TCP keepalives.

Log locations : Primary: /var/log/postgresql/postgresql-<VER>-main.log or journalctl -u postgresql . Restore instance: custom log path via pg_ctl -l .

## Rollback & Cutover Procedures

### Logical Method (Rename/Swap)

# On target cluster after successful verify_db validation
psql -d postgres -c "ALTER DATABASE appdb RENAME TO appdb_old_<DATE>;"
psql -d postgres -c "ALTER DATABASE verify_db RENAME TO appdb;"
# Update connection pooler (pgbouncer) or DNS to point to new cluster
# Keep appdb_old_<DATE> for TTL (e.g., 24h) then drop 

### Physical Method (Promote + Pooler Update)

 # After pg_promote() on restored instance (port 5444)
# Update pgbouncer/haproxy config to direct traffic to new primary:5444
# Reload pooler
systemctl reload pgbouncer
# Keep old data directory /var/lib/pgsql/data until TTL expires 
 ⚠️ WARNING : Never overwrite live data directory in-place without a verified backout. Restore to new directory/port, validate, then cut over.

## Automation, Scheduling & Retention

### Systemd Timer for Nightly Logical Backup

/etc/systemd/system/pg-backup-logical.service :

[Unit]
Description=PostgreSQL Logical Backup
Requires=flock-pg-backup.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/pg_backup_logical.sh
User=postgres 
 /etc/systemd/system/pg-backup-logical.timer :

[Unit]
Description=Nightly Logical Backup Timer

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true
RandomizedDelaySec=15m

[Install]
WantedBy=timers.target 
 /usr/local/bin/pg_backup_logical.sh (skeleton):

#!/bin/bash
set -euo pipefail
LOCKFILE="/var/lock/pg_backup_logical.lock"
exec 9>"$LOCKFILE"
flock -n 9 || { echo "Backup already running"; exit 1; }

DATE=$(date -u +%F)
DEST="/backups/logical/appdb_${DATE}_dir"
pg_dump -Fd -j 4 -f "$DEST" appdb
pg_dumpall -g | gpg --encrypt --recipient backup-key > "/backups/logical/globals_${DATE}.sql.gpg"

# Alerting: success/failure webhook or Prometheus pushgateway
curl -X POST "https://alerts.example.com/webhook" -d "status=success&job=pg_backup_logical&date=${DATE}" 

### Retention Script Skeleton (Bash)

 Keeps last 3 full base backups + required WAL; verifies continuity with pg_controldata / pg_waldump .

#!/bin/bash
set -euo pipefail
BASE_DIR="/backups/base"
WAL_DIR="/var/lib/pgsql/wal_archive"
KEEP_FULL=3

# List base backups by date (newest first)
mapfile -t BACKUPS < <(ls -1dt "$BASE_DIR"/*/ 2>/dev/null | head -n "$KEEP_FULL")
# Determine oldest kept backup's start LSN
OLDEST_KEPT="${BACKUPS[-1]}"
START_LSN=$(grep 'START WAL LOCATION' "$OLDEST_KEPT/backup_label" | awk '{print $3}')

# Verify WAL continuity from START_LSN to latest
pg_waldump --start="$START_LSN" "$WAL_DIR"/*.partial 2>/dev/null | head -5

# Prune base backups older than KEEP_FULL
ls -1dt "$BASE_DIR"/*/ | tail -n +$((KEEP_FULL+1)) | xargs -r rm -rf

# Prune WAL older than START_LSN (requires WAL-G/pgBackRest for safe execution)
# pgbackrest --stanza=main expire --repo1-retention-full=3 

### Rehearsal Cadence

- Logical restore rehearsal: weekly (automated in CI/staging).

- Physical PITR rehearsal: monthly (restore to alternate port, validate, promote, drop).

- Record duration, issues, and lessons learned in runbook.

## Operations Checklist

### Pre-Flight

- [ ] Disk space ≥ 1.5× database size free on backup volume.

- [ ] pg_dump --version , pg_basebackup --version match server major version.

- [ ] Backup user credentials valid ( .pgpass or IAM).

- [ ] RPO/RTO targets documented for this run.

- [ ] Time sync verified ( chronyc tracking ).

### Logical Backup & Restore Rehearsal

- [ ] pg_dumpall -g > globals.sql → encrypt → store off-host.

- [ ] pg_dump -Fd -j 4 -f /backups/appdb_ _dir appdb completes without error.

- [ ] pg_restore -l /backups/appdb_ _dir/toc.dat lists expected objects.

- [ ] Restore to disposable DB → run Validation Queries Pack → app smoke test passes.

- [ ] VACUUM (ANALYZE) completes.

### Physical Backup & PITR Rehearsal

- [ ] wal_level=replica , archive_mode=on , archive_command tested manually.

- [ ] SELECT pg_switch_wal(); → new WAL file appears in archive.

- [ ] pg_basebackup -D /backups/base/ --max-rate=100M --wal-method=stream --slot=backup_slot --create-slot -X stream succeeds.

- [ ] Restore to /var/lib/pgsql/data_restore with restore_command , recovery_target_time , recovery.signal .

- [ ] Start on port 5444 → pg_is_in_recovery()=true → validate data → pg_promote() → pg_is_in_recovery()=false .

- [ ] Cutover procedure documented and tested (pooler config update).

### Monitoring & Retention

- [ ] Backup duration, size, success/failure logged to monitoring (Prometheus postgres_exporter + node_exporter or webhook).

- [ ] Retention script runs after each backup cycle; keeps 3 full cycles + required WAL.

- [ ] Quarterly full disaster recovery drill (restore to isolated environment, validate app).

## Version-Specific Notes Appendix

- Feature — PG12 — PG13 — PG14 — PG15 — PG16

- recovery.signal + postgresql.conf — ✅ — ✅ — ✅ — ✅ — ✅

- recovery.conf (deprecated) — ❌ removed — — — — — — — —

- pg_promote() — ✅ — ✅ — ✅ — ✅ — ✅

- wal_keep_size — ❌ (use wal_keep_segments ) — ✅ — ✅ — ✅ — ✅

- pg_basebackup --create-slot — ✅ — ✅ — ✅ — ✅ — ✅

- pg_basebackup --max-rate — ✅ — ✅ — ✅ — ✅ — ✅

- wal_level values — replica — replica — replica — replica — replica

- -b (large objects) default — included — included — included — included — included

- pg_dumpall -g default privileges — ❌ — ❌ — ❌ — ❌ — ❌

 Glossary : RPO (Recovery Point Objective), RTO (Recovery Time Objective), PITR (Point-In-Time Recovery), WAL (Write-Ahead Log), LSN (Log Sequence Number).

## Conclusion

You now have a complete, practical path to back up and restore PostgreSQL using both logical dumps and physical base backups with WAL. Start with a narrow rehearsal you can verify locally, then broaden scope to meet your RPO/RTO targets. Keep your restore procedure separate, documented, and exercised so you can act with confidence during incidents. The most important metric is not backup success but restore success: validate early and often, record lessons learned, and automate only after you trust the steps. This runbook gives you the commands, verification layers, failure-mode diagnostics, and rollback procedures to achieve that confidence.