Method

pg_dumpall vs pg_dump: what a per-database dump leaves behind

A pg_dump file is a backup of one database. Everything that lives one level up — roles, tablespaces, the CREATE DATABASE line itself — is not in it, and you find that out at restore time.

The short version

pg_dump dumps a single database and can write a compressed archive you can restore selectively and in parallel. pg_dumpall dumps the whole cluster — every database plus the cluster-wide objects — but only as a plain SQL script, which throws away every one of those restore-time conveniences.

The answer for most setups is not one or the other. It is pg_dumpall --globals-only for the cluster-level objects, plus one pg_dump -Fc per database.

What each one covers

Under the hood pg_dumpall is not a different engine: it writes the cluster-wide objects itself, then calls pg_dump once per database and concatenates the results. That is exactly why the output is plain SQL — a single stream cannot be a custom-format archive.

The three things that bite

Roles

Roles are cluster objects. Your dump is full of ALTER TABLE ... OWNER TO app_user and GRANT statements naming them, and carries none of their definitions — which is the whole story behind role "…" does not exist on a fresh target.

The database itself

Without --create, a pg_dump archive assumes the target database already exists and says nothing about it. With --create, the output also reproduces the database's own properties: encoding, locale, owner, ACL, and any ALTER DATABASE ... SET settings attached to it. If you have ever set search_path, statement_timeout or an extension parameter at the database level, that is where it lives — and a plain pg_dump into a hand-made createdb silently drops it.

One gotcha with --create: the database named by -d is then only the one pg_restore connects to in order to issue CREATE DATABASE. The data goes into the database name recorded in the archive, not into -d.

Things neither tool touches

• postgresql.conf and pg_hba.conf — memory settings, and the rules that decide who may connect at all.
• Extension binaries. The dump contains CREATE EXTENSION vector; if the target server does not have that extension installed on disk, the statement fails.
• Replication slots, WAL archives, the physical layout — see pg_dump vs pg_basebackup vs PITR for what does carry those.

The combination that actually works

Dump the globals once, then each database into its own archive:

# 1. cluster-wide objects: roles, role memberships, tablespaces. No table data.
pg_dumpall --globals-only --no-role-passwords -d "$SOURCE_URL" -f globals.sql

# 2. one compressed, restorable archive per database
pg_dump -Fc -d "postgresql://user@host:5432/appdb" -f appdb.dump

To enumerate the databases instead of maintaining a list by hand:

psql -d "$SOURCE_URL" -Atc \
  "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate ORDER BY 1"

Restore in that order — globals first, because the OWNER TO statements in each archive need the roles to already exist:

psql -d postgres -f globals.sql
createdb appdb
pg_restore -j 4 -d appdb appdb.dump

Read the errors from globals.sql, do not suppress them. Replaying it on a target that already has some of these roles produces role "postgres" already exists, which is expected and harmless. role "app_user" already exists is also harmless. permission denied to create role is not — that one means the role never got created and every OWNER TO for it will fail a minute later.

Why --no-role-passwords is in that command

Role passwords live in pg_authid, which only superusers can read. On managed Postgres you are not a superuser, so a plain pg_dumpall --globals-only fails on that read. --no-role-passwords tells it to use pg_roles instead: the roles, memberships and attributes all come out, the password hashes come out null.

That is the right trade for a backup you intend to restore. It also means the restored roles cannot log in until you set passwords — worth knowing before a real recovery, not during one.

When a full pg_dumpall is the right tool

Cloning a small self-hosted cluster, or moving between two machines you control:

pg_dumpall -d "$SOURCE_URL" | gzip > cluster-$(date +%F).sql.gz

# restore
gunzip -c cluster-2026-09-21.sql.gz | psql -d postgres

Two constraints to know before you rely on it. The script switches database with psql's \connect between sections, so you cannot wrap it in --single-transaction; a failure halfway leaves the cluster half-restored. And because it is one plain stream, a large cluster restores in one long single-threaded psql run, with no -j to speed it up and no way to recover just the one table you actually lost.

That second point is the real cost. Most recoveries are not "the cluster is gone" — they are "someone truncated one table an hour ago". Custom format answers that in seconds; a 40 GB SQL script does not.

Verify the globals actually landed

Row counts will not tell you. Check the three things a per-database dump could not carry:

# roles present, with their attributes
psql -d appdb -c '\du'

# table privileges — empty ACLs here mean the GRANTs did not replay
psql -d appdb -c '\dp'

# database-level settings and ACL
psql -d appdb -c '\l appdb'

A restore that ends with every row in place, no roles, and empty ACLs is a restore your application cannot connect to. It is also the default outcome if you back up with pg_dump alone — which is why the drill checks permissions, not just counts.

Related fixes: pg_restore: role "…" does not exist pg_restore: relation "…" already exists pg_dump vs pg_basebackup vs PITR pg_dump formats: custom, plain, directory or tar? How to actually test a Postgres backup pg_dump: permission denied for table