Back Up All PostgreSQL Databases with pg_dumpall

calendar Jun 9, 2026 / · 6 min read · pg_dumpall backup and recovery cluster backup global objects administration  ·
Share on: linkedin copy

Back Up All PostgreSQL Databases with pg_dumpall

Before a major-version upgrade or a disaster-recovery rehearsal, a DBA needs a single artifact that captures the whole cluster: every database, plus the global objects that live outside any one database. pg_dumpall produces exactly that — one plain-SQL script that recreates all databases together with the roles, tablespaces, and grants that pg_dump on its own leaves behind.

Purpose and Overview

pg_dump backs up a single database. It does a thorough job inside that database, but it does not capture cluster-wide objects. Roles (users and groups), role memberships, tablespace definitions, and the privileges attached to them are stored once per cluster, not once per database. Restore a pg_dump file into a fresh server and the owners and grant statements refer to roles that do not exist yet.

pg_dumpall solves that gap. It connects to the cluster, enumerates every database, and writes a single script that first recreates the global objects and then recreates each database in turn. The output is plain SQL text. You replay it with psql, not with pg_restore.

Because the output is one ordered script, the roles and tablespaces are created before any database that depends on them. That ordering is the main reason pg_dumpall exists: it gives you a self-contained, replayable snapshot of an entire PostgreSQL instance.

Sample Code

1# Full cluster dump: every database plus global objects (roles, tablespaces)
2pg_dumpall -U postgres -h localhost -f /backup/cluster_$(date +%F).sql
3
4# Globals only — roles and tablespaces; pair with per-database pg_dump for parallelism
5pg_dumpall --globals-only -U postgres -f /backup/globals.sql

Notes: Works on all supported PostgreSQL versions (9.x through 17). Run as a superuser so every database and role is visible. Adjust the host, port, and output path for your environment. The output is plain SQL, so the file can grow large on busy clusters — compress it or pipe it through gzip.

Code Breakdown

The two commands above cover the two ways DBAs use this tool: one captures everything, the other captures only the cluster-global objects.

Connection Flags

-U postgres selects the connecting role. pg_dumpall reads from every database, so the role must be a superuser or have read access across the cluster. -h localhost sets the host; add -p if the server listens on a non-default port. These flags follow the same conventions as psql and pg_dump, so existing connection habits carry over.

The Output File

-f /backup/cluster_$(date +%F).sql writes the dump to a file instead of standard output. The $(date +%F) shell substitution stamps the filename with the current date (for example cluster_2026-06-09.sql), which keeps successive dumps from overwriting each other and makes retention policies easy to script.

The Globals-Only Variant

--globals-only restricts the dump to roles and tablespaces — no database contents. This is the half of pg_dumpall that most production backup strategies actually use. Per-database pg_dump jobs can run in parallel and in the efficient custom format, while a single small --globals-only dump preserves the roles and grants those databases depend on.

Key pg_dumpall Options

--globals-only

Dumps only cluster-wide objects: roles, role memberships, and tablespaces. No database data is included. Use this to complement parallel pg_dump jobs.

--roles-only

Dumps only role definitions and memberships. Narrower than --globals-only because it omits tablespaces. Useful when you only need to migrate the user and group structure between clusters.

--tablespaces-only

Dumps only tablespace definitions. Pairs with --roles-only when you want to script the two global object classes separately.

--no-role-passwords

Dumps roles without their passwords. The resulting script creates the roles with no password set. This matters when the dump will be read by people who should not see password hashes, or when authentication is handled externally (LDAP, or SCRAM managed elsewhere). It also avoids a permission error on some managed platforms where password hashes are not readable.

-c / --clean and --if-exists

-c adds DROP statements before each CREATE, so a restore starts from a clean slate. --if-exists softens those drops to DROP ... IF EXISTS, which prevents errors when an object is already absent. Use them together when you replay a dump onto a server that may hold stale objects.

--exclude-database

Skips databases whose name matches a pattern. On a cluster with several large or disposable databases, --exclude-database='*_temp' keeps the dump focused on what matters. The flag accepts shell-style wildcards and can be given more than once to exclude several patterns in a single run.

Practical Applications

Cluster-wide backups show up at the moments when a single-database dump is not enough.

Major-Version Upgrade Migration

When moving from one major version to another by dump and reload, pg_dumpall captures the full source cluster in one script. Replaying it on the new server recreates roles, tablespaces, and every database in dependency order, so ownership and grants survive the jump.

Disaster-Recovery Rehearsal

A DR rehearsal is only meaningful if the rebuilt server is complete. A pg_dumpall snapshot lets you stand up an empty PostgreSQL instance and replay the entire cluster, then confirm that applications connect with the same roles and permissions they expect.

Preserving Roles Alongside Per-Database pg_dump

Large clusters often back up each database separately with pg_dump in custom format for speed and selective restore. Those dumps omit global objects. A nightly pg_dumpall --globals-only fills the gap, so the role and grant structure is always recoverable next to the per-database files.

Restoring with psql

Because the output is plain SQL, you restore by feeding it to psql: psql -U postgres -f /backup/cluster_2026-06-09.sql postgres. The script connects to and creates each database itself, so you point psql at an existing maintenance database (commonly postgres) and let the script do the rest.

Version Compatibility

pg_dumpall has been part of PostgreSQL for its entire modern history, but several options arrived later. --if-exists was added in PostgreSQL 9.4. --no-role-passwords was added in PostgreSQL 10 and is widely used on managed services where password hashes are not readable by ordinary superusers. The behavior of dumping globals first and databases second has been stable across PostgreSQL 12, 13, 14, 15, 16, and 17.

One limitation has not changed: pg_dumpall only writes the plain SQL format. It does not support the custom or directory formats that pg_dump offers, so there is no parallel restore and no selective object restore from a pg_dumpall file. For those features, dump individual databases with pg_dump and use pg_dumpall --globals-only for the cluster-wide pieces.

Best Practices

  • Restore with psql, not pg_restore — the output is plain SQL. pg_restore only reads the custom and directory formats that pg_dump produces.
  • Split globals from data on large clusters — run pg_dumpall --globals-only for roles and tablespaces, and parallel pg_dump custom-format jobs per database for the data. This is faster and gives selective restore.
  • Secure the dump file — a full pg_dumpall contains role definitions and, unless you pass --no-role-passwords, password hashes. Treat it as sensitive and restrict file permissions.
  • Stamp and rotate — date-stamp the output filename and apply a retention policy so dumps do not silently fill the backup volume.
  • Pair with physical backups for PITR — a logical dump is a point-in-time snapshot with no continuous recovery. For point-in-time recovery, combine pg_basebackup and WAL archiving; use pg_dumpall for portable, version-independent restores.

References

Posts in this series