pg_dumpall: AI-Optimized Technical Reference

Core Function

PostgreSQL cluster-wide backup tool that creates logical dumps of entire PostgreSQL instances including all databases, users, roles, tablespaces, and global objects.

Critical Performance Characteristics

Speed and Resource Requirements

• Performance: 100GB cluster = 2-3 hours backup time
• Threading: Single-threaded only (no parallel processing unlike pg_dump)
• Memory: Can exhaust system memory on large clusters with no memory limiting options
• Progress: No progress indicator - runs blindly until completion or failure
• Disk Space: Requires 2-3x database size for uncompressed dump

Failure Modes and Consequences

• All-or-nothing: If backup fails halfway, restart from beginning (no resume capability)
• Authentication hell: Prompts for password per database without .pgpass file
• Memory exhaustion: OOM errors on large clusters (solution: more RAM or split job)
• Tablespace path dependency: Hardcoded paths cause restore failures on different servers

Configuration That Actually Works

Production-Ready Commands

# Basic with compression and timestamping
pg_dumpall -h localhost -U postgres --no-password | gzip > "cluster_$(date +%Y%m%d_%H%M%S).sql.gz"

# With database exclusion
pg_dumpall --exclude-database=test_db | gzip > cluster_backup.sql.gz

Authentication Setup (Critical)

# .pgpass file format: hostname:port:database:username:password
echo "localhost:5432:*:postgres:yourpassword" > ~/.pgpass
chmod 600 ~/.pgpass

Alternative: Environment variable

export PGPASSWORD=yourpassword
pg_dumpall | gzip > cluster_backup.sql.gz

Decision Matrix: pg_dumpall vs Alternatives

Capability	pg_dumpall	pg_dump	pg_basebackup
Speed	Slowest (single-thread)	Fast (parallel -j)	Fastest
Scope	Entire cluster	Single database	Entire cluster
Global objects	✅ Users/roles/tablespaces	❌ Database only	✅ Everything
Parallel processing	❌ None	✅ -j workers	✅ Inherent
Progress tracking	❌ None	✅ Verbose mode	✅ Progress shown
Partial restore	❌ All-or-nothing	✅ Selective	❌ All-or-nothing
Cross-version	✅ Works	✅ Works	❌ Same major only

When pg_dumpall is Required (vs Better Alternatives)

Use pg_dumpall When:

• Migrating entire PostgreSQL clusters between servers
• Cloning development environments needing identical user setups
• Version upgrades requiring all global objects preserved
• Backing up user accounts and permissions alongside data

Use pg_dump Instead When:

• Daily database backups (faster, parallel processing)
• Individual database restoration needed
• Large databases (parallel workers reduce time significantly)
• Progress monitoring required

Use pg_basebackup When:

• Hot standby setup required
• Fastest full cluster backup needed
• Binary-level cluster replication

Critical Warnings and Gotchas

Version Compatibility Rule

Always use NEWER version's pg_dumpall for cross-version migration

• Correct: Use PostgreSQL 17's pg_dumpall to backup from PostgreSQL 13
• Wrong: Use PostgreSQL 13's pg_dumpall for PostgreSQL 17 target

Tablespace Path Issues

• Backup includes hardcoded CREATE TABLESPACE paths
• Restore fails if paths don't exist on target server
• Solutions: Create identical paths OR use --no-tablespaces flag OR manually edit SQL

System Database Limitations

• Cannot exclude template0, template1 databases
• If system databases have issues, entire dump fails
• No partial recovery options

Restore Process Reality

Basic Restore

psql -U postgres -f cluster_backup.sql

Restore Failure Handling

• No incremental restore capability
• No "skip broken parts" option
• Failure midway = restart from beginning
• Always test restores on throwaway servers first

Resource Requirements

Prerequisites

• Superuser access (needs system catalogs and role information)
• .pgpass file or PGPASSWORD environment variable
• Disk space: 2-3x database size (uncompressed)
• Memory: Proportional to largest database + metadata

Time Investment

• Setup: 15-30 minutes (authentication configuration)
• Execution: 1-3 hours per 100GB
• Testing: Required for each backup strategy change
• Recovery planning: Critical for production use

Common Failure Scenarios

Password Authentication Loops

Password for user postgres: 
Password for user postgres: 
Password for user postgres: 
[continues for every database...]

Solution: Configure .pgpass file (mandatory for production)

Memory Exhaustion

Symptoms: OOM killer terminates pg_dumpall process
Solutions:

• Increase system RAM
• Split job into individual database dumps
• No memory limiting options available

Corrupted Compressed Backups

Risk: Gzip pipeline failures create corrupted .gz files
Mitigation: Always test compressed backups before relying on them

Alternative Tools Comparison

For Large-Scale Production

• pgBackRest: Parallel backup/restore, incremental backups, enterprise features
• Barman: Disaster recovery focus, continuous archiving, point-in-time recovery
• WAL-G: Cloud storage archiving, fast restoration, multi-database support

Performance Benchmarks (Approximate)

• pg_dumpall: 100GB = 2-3 hours (single-thread)
• pg_dump with -j 4: 100GB = 45-90 minutes (4 workers)
• pg_basebackup: 100GB = 15-30 minutes (binary copy)

Operational Intelligence Summary

pg_dumpall is the "nuclear option" - use only when you need everything or have no other choice. For most production scenarios, pg_dump with parallel workers or pg_basebackup provides better performance and flexibility. The tool's single-threaded, all-or-nothing nature makes it unsuitable for large production databases where backup time and recovery flexibility matter.

Success criteria: Working .pgpass file, sufficient disk space, tested restore procedures, and realistic time expectations. Failure to meet any of these results in painful backup experiences and potential data recovery failures.