pg_dumpall
TLDR
Dump all databases
$ pg_dumpall > [backup.sql]
Dump only roles and tablespaces$ pg_dumpall --globals-only > [globals.sql]
Dump specific host$ pg_dumpall -h [hostname] -U [username] > [backup.sql]
Compressed backup$ pg_dumpall | gzip > [backup.sql.gz]
SYNOPSIS
pg_dumpall [options]
DESCRIPTION
pg_dumpall extracts all PostgreSQL databases including cluster-wide objects like roles and tablespaces. Unlike pg_dump, it handles the entire cluster.
Output is a SQL script that can be restored with psql.
PARAMETERS
-h, --host host
Database server host.-U, --username name
Connect as user.-p, --port port
Server port.-g, --globals-only
Only roles and tablespaces.-r, --roles-only
Only roles.-t, --tablespaces-only
Only tablespaces.--clean
Add DROP statements.-f file
Output file.
EXAMPLES
$ # Full cluster backup
pg_dumpall -U postgres > all_databases.sql
# Backup globals for restore prep
pg_dumpall -g > globals.sql
# Restore from backup
psql -U postgres -f all_databases.sql
# Specific encoding
pg_dumpall -E UTF8 > backup.sql
pg_dumpall -U postgres > all_databases.sql
# Backup globals for restore prep
pg_dumpall -g > globals.sql
# Restore from backup
psql -U postgres -f all_databases.sql
# Specific encoding
pg_dumpall -E UTF8 > backup.sql
CAVEATS
Output is plain SQL only (no custom format). For large databases, consider pg_dump per database. Requires superuser for complete backup.
HISTORY
pg_dumpall is part of PostgreSQL, providing cluster-wide backup since early PostgreSQL versions.
SEE ALSO
pg_dump(1), pg_restore(1), psql(1), postgres(1)
