Postgres pg_dumpall: Cluster-Level Backups Beyond Single-Database pg_dump

pg_dump is the tool most Postgres operators reach for when they want a logical backup, and for most application use cases it is the right tool. It produces a clean, restorable dump of a single database including schemas, tables, indexes, functions, and data. What it does not produce is anything that lives outside the database: roles, tablespace definitions, replication slots, cluster-level configuration, or other databases in the same cluster.

pg_dumpall is the complementary tool that handles cluster-level state. It is less commonly used and less commonly understood, which means that disaster recovery plans built around pg_dump alone often have a quiet gap. The cluster reconstruction step assumes that someone, somewhere, has a copy of the roles and tablespaces and global settings, and frequently nobody does.

What pg_dumpall captures that pg_dump does not

The pg_dumpall output is a SQL script that, when applied to an empty Postgres cluster, recreates every database in the source cluster plus the global objects that exist outside any single database. The global objects are the part that pg_dump cannot capture even in principle: roles (including their passwords as hashed strings), tablespaces, default privileges on tablespaces, and cluster-level configuration parameters set via ALTER SYSTEM.

The output structure is straightforward. pg_dumpall first writes the CREATE ROLE statements with their attributes, then CREATE TABLESPACE statements, then per-database CREATE DATABASE statements followed by the equivalent of pg_dump output for that database. The order matters because roles must exist before the per-database GRANT statements can reference them.

The --globals-only flag is the underused middle ground. It produces only the cluster-level objects without the per-database content, which is useful for keeping a continuously-updated record of cluster state without the storage cost of repeated full dumps. The pattern is to run pg_dumpall --globals-only as a separate fast-scheduled job and combine it with per-database pg_dump runs for the actual data.

The role-password question

pg_dumpall captures role passwords as their SCRAM-SHA-256 (or MD5 on older clusters) hashed forms, not as plaintext. This is correct behavior; the passwords cannot be recovered from the dump but can be restored, because the new cluster will accept the same hashed value and authenticate users with the same plaintext password.

The implication is that pg_dumpall output contains sensitive material that must be treated as a credential. Storing pg_dumpall output in object storage with default ACLs, or attaching it to a support ticket, or committing it to a repository, are all forms of credential leakage. Most teams that have been bitten by this learned through an unrelated incident response review rather than through proactive design.

The --no-role-passwords flag suppresses the password hashes for cases where the dump is being used for schema documentation or test-environment setup rather than actual disaster recovery. The trade-off is that the resulting dump cannot restore a working production cluster without separately provisioning passwords. For most disaster recovery use cases, the password hashes need to be in the dump and the dump needs to be treated like a credential.

Where pg_dumpall fits in a backup strategy

pg_dumpall is rarely the right tool for the main data backup, because the output is a single large text file that does not parallelize during restore and that gets unwieldy for clusters above a few gigabytes. The typical structure is per-database pg_dump for the actual data (in directory format with --jobs for parallelism), pg_dumpall --globals-only for the cluster-level state, and pg_basebackup or a continuous archiving tool for point-in-time recovery.

The three outputs together provide complete recovery coverage. pg_dump can restore any individual database to a new or empty cluster. pg_dumpall --globals-only can restore roles and tablespaces. pg_basebackup or continuous archiving can restore the cluster to a specific point in time, including all databases and global state, but at a cost in storage and complexity that pg_dump avoids.

The right cadence depends on the rate of change of the corresponding state. Per-database data changes constantly and needs frequent backups (typically daily for hot tier, with continuous archiving for PITR). Global state changes rarely (new roles added perhaps weekly) and a weekly pg_dumpall --globals-only is usually sufficient. Tablespace definitions change almost never and the same weekly cadence works for them.

The restore mechanics

Restoring from pg_dumpall output is conceptually simple: pipe the file into psql against an empty cluster. The practical complications include version compatibility (pg_dumpall output for Postgres 16 should be restored into Postgres 16 or later, with some caveats for cross-version restoration), the time cost (single-threaded SQL execution is much slower than parallel pg_restore), and the failure modes (a partial failure midway through a multi-database restore leaves the cluster in an ambiguous state).

The pattern most operators settle on is to use pg_dumpall --globals-only output for the cluster-level setup followed by per-database pg_restore in parallel for the data. This combines the cluster-level coverage of pg_dumpall with the parallelism and resumability of per-database restore. The combined recovery time is typically dominated by the largest database rather than the sum of all databases.

The verification step is the part most teams skip. A pg_dumpall output that cannot be restored is worse than no backup at all because it produces false confidence. The discipline is to actually run a restore drill against a separate cluster on a regular cadence (quarterly is the common interval for non-critical systems, monthly for production-critical) and to measure the recovery time as well as the success of the restore. The recovery time is part of the disaster recovery objective; a 24-hour restore that works is meaningfully different from a 4-hour restore that works.

What pg_dumpall does not do

The tool produces a logical dump, which means it does not preserve physical layout, bloat, statistics, or anything else that depends on the storage representation. A restored cluster has the same logical state as the source but different physical characteristics. This is usually fine but occasionally relevant: a heavily-clustered table that was carefully ordered will be in insertion order after restore unless explicitly clustered again, and accumulated statistics need to be regenerated via ANALYZE.

pg_dumpall does not handle replication slots. Slots are cluster-state that lives outside any database but is also tightly coupled to ongoing replication, and the dump-and-restore semantics do not apply cleanly. The right pattern is to recreate slots as part of the post-restore cluster setup, after replication is reconfigured.

pg_dumpall does not handle large objects (the BLOBs stored in pg_largeobject) in a way that scales beyond moderate sizes. The data is included but as part of the single-threaded SQL stream, so large-object-heavy databases hit the same performance ceiling as everything else. For these cases, per-database pg_dump in directory format is much more practical.

pg_dumpall does not handle anything outside Postgres. Configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf) live on the filesystem and need to be backed up separately. Operating-system-level settings (kernel parameters, ulimits, locale and timezone files) similarly need separate handling. The pg_dumpall output is a backup of the logical Postgres state, not of the host environment that supports it.

SQLite and our four products

Our four products (DocuMint, CronPing, FlagBit, WebhookVault) currently run on SQLite, which means there is no cluster-level state to back up separately. The entire database is a single file plus its WAL companion, and the backup story is correspondingly simpler: file copy with appropriate locking semantics, or sqlite3 .backup, or Litestream for continuous replication. The pg_dumpall analog does not have a SQLite equivalent because the cluster-vs-database distinction does not exist.

The migration to Postgres for any product will reintroduce the cluster-level concern. The planning artifact we use captures the four backup streams of per-database pg_dump (the data), pg_dumpall --globals-only (the roles and tablespaces), pg_basebackup or continuous archiving (the point-in-time recovery), and the host-environment backup (configuration files, certificates, operating system state). The pre-migration design discipline is to ensure all four streams are present from the first production deployment of Postgres rather than added piecemeal after the first close call.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) keep the lights on.