PostgreSQL Backup & Restore: Logical & Physical Methods with PITR

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_<DATE>_dir appdb completes without error.
[ ] pg_restore -l /backups/appdb_<DATE>_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/<DATE> --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.