How do I fix 'pg_basebackup fails: WAL streaming error'?

Likely cause: replication permission missing or max_wal_senders too low. Fix: grant REPLICATION and raise max_wal_senders, then retry.

How do I fix 'restore is slower than the RTO allows'?

Likely cause: single-threaded restore of a huge dump. Fix: use pg_restore -j for parallel jobs on custom-format dumps.

How do I fix 'WAL archive filled the disk'?

Likely cause: archive_command succeeded but old segments were never cleaned. Fix: set up WAL retention with archive_cleanup_command or a standby consuming them.

How do I fix 'dump restores with owner errors'?

Likely cause: roles from production do not exist in the test cluster. Fix: restore with --no-owner or create the roles first.

How do I fix 'PITR misses the target time'?

Likely cause: the needed WAL segment was already recycled. Fix: keep wal_keep_size adequate or use a replication slot so segments are retained.

Database Backup & Recovery: pg_dump, PITR

I implemented both logical backups with pg_dump and physical point-in-time recovery via WAL archiving, then tested restores against defined RPO and RTO targets. A backup is only real once its restore is verified, so every backup type was exercised end to end.

Objective & Context

Backups exist to be restored. This lab pairs logical dumps (portable, slow) with WAL-based PITR (granular, fast recovery to a timestamp), defining recovery point and recovery time objectives and proving them with actual restores.

Learning Objectives

Take logical backups with pg_dump in custom format.
Take base backups with pg_basebackup for PITR.
Configure WAL archiving for point-in-time recovery.
Restore to a test database and verify row counts.
Validate restores against RPO and RTO targets.

Environment & Prerequisites

Prerequisites

PostgreSQL 16 with WAL archiving configured
Backup storage separate from the data volume
Defined RPO/RTO targets to validate against
Assumed skill: psql basics and Linux file management

flowchart LR B[Base backup] --> W[WAL archive stream] W --> R[Restore base] R --> P[Replay WAL to timestamp] P --> V[Verified database]

Step-by-Step Execution

1. Logical backup with pg_dump

pg_dump -Fc appdb -f /backups/appdb-$(date +%F).dump

Expected output:

(dump file written to /backups/)

What this teaches: Custom format (-Fc) compresses and allows selective restore; the date-stamped filename makes retention and rotation trivial.

2. Base backup for PITR

pg_basebackup -D /backups/base -Fp -Xs -P

Expected output:

(base backup streamed to /backups/base)

What this teaches: pg_basebackup copies the data directory while -X stream includes the WAL needed for consistency; together with archived WAL this enables PITR. Streaming mode opens two WAL sender connections (one for the backup, one for the WAL stream), so set max_wal_senders to at least 2 and run the command as a superuser or a role with the REPLICATION attribute.

3. Restore and verify

createdb appdb_test && pg_restore -d appdb_test /backups/appdb-2026-06-17.dump && psql appdb_test -c "SELECT count(*) FROM orders;"

 count
-------
 81234

Expected output:

 count
-------
 81234

What this teaches: Restoring into appdb_test (never production) and counting rows proves the backup is restorable; a backup without a tested restore is a hope, not a plan. pg_restore -d connects to an existing database, so createdb runs first; the -C flag is the alternative when you want the dump to create the database itself.

4. Configure WAL archiving

ALTER SYSTEM SET archive_mode = 'on'; ALTER SYSTEM SET archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f';

Expected output:

ALTER SYSTEM
ALTER SYSTEM

What this teaches: archive_mode only takes effect at server start, so a restart is required after setting it; archive_command then runs once per completed WAL segment. Verify with SELECT * FROM pg_stat_archiver; and confirm last_failed_time stays empty after the next checkpoint.

How It Works

Logical backups (pg_dump) export SQL or an archive of the database contents; they are portable across versions and good for single-database restores. Physical backups (pg_basebackup) copy the data files and are faster for full-cluster recovery.

Point-in-time recovery combines a base backup with the continuous WAL stream. Because WAL records every change, replaying it from the base backup's checkpoint lets you restore to any moment, which is how you recover from a 2pm accidental DROP TABLE using the 2am base backup.

RPO (how much data you can lose) and RTO (how fast you must recover) turn backups into engineering targets. WAL archiving gives a small RPO, tested restore runbooks give a predictable RTO, and only rehearsed restores prove either number is real.

flowchart LR D["production DB"] --> L["pg_dump
logical backup"] D --> B["pg_basebackup
+ WAL archive"] B --> PITR["replay WAL to
any point in time"] L --> R["restore to
appdb_test"] PITR --> V["verify: row counts,
RPO/RTO met"]

Validation & Testing

Restore to a scratch database and to a specific point in time, then compare row counts and checksums against the source. Pass criteria: restores complete within the RTO, data loss stays within the RPO, and verification queries match expected values.

Advanced: Troubleshooting

Untested backups: schedule periodic restore drills; an unverified backup is a guess.
WAL gap: ensure continuous archiving; a missing segment breaks PITR.
Slow restore: parallelize with pg_restore -j on multi-core hosts.

Troubleshooting

Symptom	Likely cause	Fix
pg_basebackup fails: WAL streaming error	replication permission missing or max_wal_senders too low	grant REPLICATION and raise max_wal_senders, then retry
restore is slower than the RTO allows	single-threaded restore of a huge dump	use pg_restore -j for parallel jobs on custom-format dumps
WAL archive filled the disk	archive_command succeeded but old segments were never cleaned	set up WAL retention with archive_cleanup_command or a standby consuming them
dump restores with owner errors	roles from production do not exist in the test cluster	restore with --no-owner or create the roles first
PITR misses the target time	the needed WAL segment was already recycled	keep wal_keep_size adequate or use a replication slot so segments are retained

Key Results

Verified restores for both logical dumps and WAL-based PITR.
Met the defined RTO on test restores from cold backup.
Kept potential data loss within the RPO via continuous WAL archiving.
Validated restored data with row-count and checksum checks.

Key Takeaways

Logical backups are portable; base backups plus WAL enable point-in-time recovery.
WAL archiving is what makes RPO small: every change is recorded.
Always restore into a test database and verify row counts.
Measure restore time against your RTO; tune with parallel pg_restore.
An untested backup is a hope, not a plan: rehearse restores regularly.

Practice Challenges

Challenge 1: PITR drill

Goal: Create a table, note the time, drop it, then recover it to a minute before the drop using WAL replay.

Hint: Restore the base backup to a new cluster and set recovery_target_time just before the DROP.

Challenge 2: RTO measurement

Goal: Time a full pg_restore of your dump with -j4 vs -j1 and record the speedup.

Hint: Custom format (-Fc) is required for parallel restore; directory format (-Fd) parallelizes too.

Database Backup and Recovery with pg_dump and Point-in-Time Recovery

Objective & Context

Learning Objectives

Environment & Prerequisites

Prerequisites

Step-by-Step Execution

1. Logical backup with pg_dump

2. Base backup for PITR

3. Restore and verify

4. Configure WAL archiving

How It Works

How It Works

Validation & Testing

Troubleshooting

Key Results

Key Takeaways

Practice Challenges

Challenge 1: PITR drill

Challenge 2: RTO measurement

Related Labs