Why does my backup randomly fail at 90% completion?

Usually network timeouts or disk space running out. The backup process streams data continuously, and if your network hiccups or the target disk fills up, pg_basebackup dies. Check network stability with `ping -i 0.2 db-server` during backup. For disk space, monitor with `watch -n 5 df -h /backup`. If you're backing up over WAN, add `--checkpoint=spread` to reduce I/O spikes that trigger timeouts.

How do I know if my backup is actually restorable without testing it?

**You don't.** Seriously. I've seen "successful" backups that were corrupted because WAL files got out of sync. The only way to know is to test restore to a different server. For PostgreSQL 13+, use `pg_verifybackup backup_directory` but understand this only checks file integrity, not logical consistency. A monthly restore test to a throwaway instance is the only real validation.

Why does pg_basebackup eat all my bandwidth even with rate limiting?

The rate limiting only applies to data files, not WAL streaming. If you use `-X stream`, WAL files transfer at full speed and can easily saturate a gigabit connection on busy databases. Monitor WAL generation with: ```sql SELECT pg_current_wal_lsn(), pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')/1024/1024 AS mb_generated; ``` For high-transaction databases, WAL can generate 100MB/hour+, which bypasses your rate limits.

My backup says it completed successfully but the directory is empty

Check your permissions and the exact error in the PostgreSQL logs. This usually happens when: 1. The backup user doesn't have REPLICATION privileges 2. The target directory doesn't exist or isn't writable 3. SELinux is blocking file creation (check `ausearch -m avc` on RHEL/CentOS) The exit code will be 0 even if the backup failed silently due to permission issues.

Why do incremental backups sometimes end up larger than full backups?

PostgreSQL 17's incremental backups are buggy as hell. They sometimes include unchanged files, and the metadata overhead can be significant for databases with many small files. Incremental backups also don't compress as well because they're storing block-level changes rather than full files. Just use pgBackRest if you need real incremental backups.

How do I recover from a corrupted backup manifest?

If the backup manifest gets corrupted, your backup is effectively useless for PostgreSQL 13+. You can try `pg_resetwal` on the backup directory, but you're probably better off taking a new backup. This is why I always use `--no-manifest` on production backups - one less thing to break.

pg_basebackup is maxing out my database server CPU, what gives?

You probably didn't rate limit it. pg_basebackup will read data as fast as your disks can provide it, which on NVMe drives can completely saturate your I/O subsystem. Always use `--max-rate=50M` or lower for production. Monitor with `iostat -x 1` during backup to see disk utilization.

Can I run multiple pg_basebackup processes simultaneously?

Technically yes, but each one consumes a `max_wal_senders` slot and competes for disk I/O. Running 2-3 parallel backups will usually just make everything slower. If you need parallel backup, use pgBackRest instead - it's designed for this.

The backup worked fine for months, now it randomly fails

Check if your database size crossed a threshold where the backup duration exceeds `wal_sender_timeout` (default 60 seconds). As databases grow, backups take longer, and if PostgreSQL doesn't see activity from the backup process, it kills the connection. Increase `wal_sender_timeout` to 300 seconds or more for large databases.

Why does restoring the backup take longer than creating it?

Because you're probably restoring to spinning disks while the original server has SSDs, or your restore target has different I/O characteristics. Backup is mostly sequential reads (fast), restore involves random writes during WAL replay (slow). Also, if you used compression, decompression during restore is single-threaded and CPU-bound.

Currently viewing the AI version

Switch to human version

PostgreSQL pg_basebackup: AI-Optimized Technical Reference

Core Function

pg_basebackup creates physical backups of PostgreSQL clusters by copying actual data files (not SQL dumps) while the database is running. Uses PostgreSQL's replication protocol to create consistent backups without downtime.

Critical Performance Specifications

Backup Time Comparison (500GB Database)

pg_basebackup: 2-4 hours with rate limiting
pg_dump: 8-12 hours (SQL dump approach)
pgBackRest: 45 minutes (enterprise tool)
Barman: 2 hours (backup manager)

Production Impact During Backup

Without rate limiting: Destroys production performance (100% CPU, API timeouts)
With 50MB/s limit: 30% CPU usage, no user impact, 2.5 hours backup time
With compression: 60% space savings, single CPU core maxed out, 3.5 hours total

Storage Requirements

Uncompressed: 1x database size
With gzip: ~0.6x database size (built-in compression is single-threaded)
Need 2x database size free space for safe backup operations

Configuration Requirements

Essential PostgreSQL Settings

# CRITICAL: Insufficient wal_senders causes silent failures
max_wal_senders = 5  # Must exceed number of replicas + 1 for backup

# MUST be replica or higher (minimal breaks everything)
wal_level = replica

Authentication Setup

-- Backup user requires REPLICATION privilege
CREATE USER backup_user WITH REPLICATION LOGIN PASSWORD 'secure_password';

-- pg_hba.conf entry required
host replication backup_user 10.0.0.0/8 md5

Production-Safe Commands

Basic Backup (Rate Limited)

pg_basebackup -h db-server -D /backup/postgres -U backup_user \
  --max-rate=50M -P -v -X stream

Large Database Backup (>500GB)

pg_basebackup -h db-server -D /backup/postgres -U backup_user \
  --max-rate=100M -Ft -z -X stream -P -v

Emergency Backup (No Rate Limiting)

pg_basebackup -h db-server -D /emergency-backup -U backup_user \
  -P -v -X stream --no-sync

Critical Failure Modes

"Number of requested standby connections exceeds max_wal_senders"

Cause: max_wal_senders too low for existing replicas + backup process
Impact: Backup fails immediately
Solution: Increase max_wal_senders or stop non-essential replicas
Check: SELECT * FROM pg_stat_replication;

Backup Completes but is Corrupted

Cause: WAL files get out of sync during backup
Impact: Backup appears successful but is unusable
Solution: Always use -X stream (streams WAL during backup)
Avoid: -X fetch (fetches WAL after backup - race condition prone)

90% Completion Failures

Primary causes: Network timeouts, disk space exhaustion
Impact: Hours of backup time wasted
Mitigation: Monitor disk space with watch -n 5 df -h /backup
Network: Use --checkpoint=spread for WAN backups

Rate Limiting Bypassed

Issue: WAL streaming ignores rate limits
Impact: Full bandwidth saturation despite rate limiting
High-transaction DBs: Generate 100MB/hour+ WAL, bypassing limits
Monitor: SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')/1024/1024 AS mb_generated;

Version-Specific Issues

PostgreSQL 13

Breaking change: WAL file naming scheme changed
Impact: Backup scripts using old naming conventions fail
Workaround: Update scripts or use --no-manifest

PostgreSQL 14

New feature: Built-in compression options
Reality: Single-threaded compression slower than piping through gzip
Recommendation: Use external compression tools

PostgreSQL 17

New feature: Incremental backups
Critical issues: Buggy implementation, incremental manifests corrupt
Block tracking overhead: Significant performance impact
Recommendation: Use pgBackRest for reliable incrementals instead

Resource Requirements

Time Investment

Initial setup: 30 minutes (assuming no configuration issues)
First production backup: 2-4 hours for debugging and tuning
Monthly restore testing: 2 hours (mandatory for validation)

Expertise Requirements

Basic usage: Junior DBA level
Production troubleshooting: Senior DBA level (networking, PostgreSQL internals)
Performance optimization: Expert level (I/O tuning, replication protocol knowledge)

Infrastructure Requirements

Network: Dedicated backup network recommended for large databases
Storage: 2x database size for backup target
CPU: Minimal impact with proper rate limiting
Monitoring: Essential for large databases (backup can take hours)

Operational Intelligence

When pg_basebackup is Wrong Tool

Cross-version restores: Use pg_dump instead
Logical replication: pg_dump provides more flexibility
Cloud managed services: AWS RDS, Google Cloud SQL, Azure - use their backup systems
Need real incrementals: pgBackRest or Barman required

Hidden Costs

Network bandwidth: Can saturate links without rate limiting
I/O impact: Even rate-limited backups stress storage subsystem
Backup validation: Requires separate infrastructure for restore testing
Monitoring complexity: Large backups need progress tracking

Production Gotchas

Silent permission failures: Exit code 0 even with failed backups
Manifest corruption: PostgreSQL 13+ manifests can corrupt, invalidating entire backup
Timeout scaling: As databases grow, wal_sender_timeout must increase
Compression tradeoffs: Built-in compression is CPU-bound and slow

Tool Comparison Reality Check

Tool	Setup Time	Reliability	Performance	Feature Completeness
pg_basebackup	30 min	Good with caveats	Medium	Basic
pgBackRest	2-4 hours	Excellent	High	Enterprise
Barman	1-2 hours	Good	Medium	Good
pg_dump	5 min	Excellent	Low (large DBs)	Limited scope

Backup Validation Strategy

File integrity: pg_verifybackup backup_directory (PostgreSQL 13+)
Logical consistency: Only verifiable through actual restore
Mandatory testing: Monthly restore to throwaway instance
Automation: Use Testcontainers for CI/CD backup testing

Critical Warnings

What Documentation Doesn't Tell You

Rate limiting only applies to data files, not WAL streaming
Successful backup status doesn't guarantee usable backup
Version compatibility is strict - no cross-major-version restores
Managed cloud services block pg_basebackup access entirely

Breaking Points

Database size >1TB: Backup duration may exceed timeout windows
High transaction rate: WAL generation can overwhelm network
Network instability: Backup process is sensitive to connection drops
Storage I/O limits: Can bottleneck entire backup process

Migration Considerations

From pg_dump: Massive speed improvement but less flexibility
To pgBackRest: Complex setup but superior features and reliability
Cloud migration: pg_basebackup becomes unavailable, plan backup strategy change

Decision Criteria

Use pg_basebackup When:

Database size >100GB (where pg_dump becomes impractical)
Same major version backup/restore required
Simple backup requirements
Limited expertise/time for complex tool setup

Avoid pg_basebackup When:

Need cross-version compatibility
Require reliable incremental backups
Using managed cloud PostgreSQL services
Need enterprise backup features (encryption, deduplication)
Database has high transaction rate (WAL streaming issues)

Worth the Complexity Despite:

Confusing error messages that waste troubleshooting time
Version-specific quirks and breaking changes
Network bandwidth consumption
Need for separate restore testing infrastructure

The tool is reliable for basic physical backups when properly configured, but lacks enterprise features and has sharp edges that require operational expertise to navigate safely.

Useful Links for Further Investigation

Resources That Actually Help (And Which Ones Suck)

Link	Description
pg_basebackup Official Documentation	The actual command reference. Dry as hell but complete. Start here for flag definitions.
Continuous Archiving and PITR	How to set up point-in-time recovery. Essential reading if you want to sleep at night.
Streaming Replication Protocol	Technical details. Only read this if you're debugging connection issues or you enjoy pain.
PostgreSQL Wiki: Backup Ecosystem	Community-maintained and updated by people who actually use this stuff. Much more practical than official docs.
Pythian PITR Guide	One of the few guides that shows you how to recover from real failure scenarios.
pgBackRest	The backup tool you'll eventually switch to. Documentation is confusing but the tool is solid. Parallel backups, real incrementals, proper compression.
Barman	2ndQuadrant's backup manager. Has a web interface which is nice if you're not a command line person. Slower than pgBackRest but easier to set up.
WAL-G	If you're backing up to cloud storage and want something faster than the built-in tools. Written in Go, actually maintained.
AWS RDS PostgreSQL Backups	You can't use pg_basebackup with RDS. Use their snapshot system instead.
Google Cloud SQL PostgreSQL	Same deal - no pg_basebackup access. Their backup system works fine though.
Azure Database for PostgreSQL	Another managed service where pg_basebackup doesn't work. Use their built-in backups.
pg_stat_progress_basebackup	Query this view to see how much of your backup is actually done. Essential for large databases.
PostgreSQL Performance Blog	Cybertec's blog has actual performance testing of backup tools. Read their pgBackRest vs pg_basebackup comparisons.
PostgreSQL GitHub Issues	Search for "pg_basebackup" to find actual bugs and workarounds
pgBackRest Issues	Better maintained than you'd expect. Developers actually respond.
PostgreSQL Docker Official Images	Good for testing backup/restore procedures without affecting production. The Alpine images start faster.
Testcontainers	If you want to automate backup testing in CI/CD. Worth the complexity for critical systems.