pg_dumpall - Back up entire PostgreSQL clusters

pg_dumpall is PostgreSQL's cluster-wide backup tool that creates logical dumps of everything in your PostgreSQL instance. While pg_dump handles single databases, pg_dumpall grabs the whole damn thing: databases, users, roles, tablespaces, and all the global objects that pg_dump can't touch.

Here's the brutal reality: pg_dumpall is slow. Painfully slow. A 100GB cluster can take 2-3 hours, and there's no progress indicator. You run it and pray nothing crashes halfway through. Unlike pg_dump which supports parallel processing with -j, pg_dumpall is single-threaded all the way.

The Password Prompt Hell: pg_dumpall connects to every database in your cluster separately. Without a .pgpass file, you'll be typing passwords every few minutes like some kind of authentication slave. Set up your password file or lose your sanity.

When You Actually Need This Tool: Despite the pain, pg_dumpall is your only option for:

• Migrating entire PostgreSQL clusters between servers
• Backing up all databases plus user accounts and permissions
• Cloning development environments that need identical user setups
• Version upgrades where you need everything preserved

Production Reality Check: Most teams end up using pg_dump for individual databases and only reach for pg_dumpall when they need to replicate the entire cluster setup. The restore process is also all-or-nothing - if something fails midway, you're starting over from scratch.

The tool requires superuser access because it needs to read everything, including system catalogs and role information. Try running this as a regular user and you'll get permission errors that'll make you question your life choices.

Basic Commands That Don't Suck

Simple cluster backup:

pg_dumpall > cluster_backup.sql

That basic command looks simple but you'll get password prompts for every database. Set up .pgpass or you'll be typing passwords for 20 minutes.

Better approach with compression:

pg_dumpall | gzip > cluster_backup.sql.gz

The gzip trick is nice until your backup process dies and you're stuck with a corrupted .gz file. Always test your compressed backups.

Production-ready command:

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

What Goes Wrong (And How to Fix It)

Memory Issues: Large clusters can exhaust memory during the dump. PostgreSQL 17's pg_dumpall doesn't support the memory-limiting options that pg_dump has. If you're hitting OOM errors, you're basically fucked - split the job or get more RAM.

Tablespace Headaches: If your cluster uses custom tablespaces, the restore will fail on a different server because the paths won't exist. The backup includes CREATE TABLESPACE commands with hardcoded paths like /data/pg_tablespace. You'll need to either:

• Create identical paths on the target server
• Edit the SQL dump to fix the paths (fun!)
• Use `--no-tablespaces` flag and lose the tablespace layout

The Password Prompt Nightmare: Without proper authentication setup, pg_dumpall becomes interactive hell:

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

Fix this with a .pgpass file or `PGPASSWORD` environment variable.

Version Compatibility: Unlike binary backups, pg_dumpall dumps work across PostgreSQL major versions. You can dump from PostgreSQL 13 and restore to 17. But here's the catch - always use the **newer** version's pg_dumpall. Dump from 13 using pg_dumpall from 17, not the other way around.

Restore Reality Check

Restoring is the same nightmare in reverse:

psql -U postgres -f cluster_backup.sql

If anything fails midway through (corrupted user, conflicting tablespace, whatever), you're starting over. No incremental restore, no "skip the broken part" - it's all or nothing.

Pro tip: Always test restores on a throwaway server first. The number of times I've seen teams discover their backups are trash during an actual emergency is depressing.