pg_dump vs pg_dumpall: globals, roles, and what you're missing

pg_dump skips roles, tablespaces, and cluster settings. What pg_dumpall --globals-only covers and the combined backup recipe.

What pg_dump leaves out

pg_dump captures ONE database: schema + data. It does not capture roles/passwords, role memberships, tablespaces, or cluster-wide settings (ALTER SYSTEM). Restore onto a blank server and you'll meet a wall of role "app_user" does not exist.

The combined recipe

pg_dumpall --globals-only --file=globals.sql   # roles + tablespaces
for db in app analytics; do
  pg_dump --format=custom --file="$db.dump" "$db"
done

Restore order: globals.sql first (via psql), then each dump. On managed platforms (RDS, Supabase, Neon) you often CAN'T restore globals — the platform owns roles — which is why --no-owner --no-privileges restores that re-point ownership at your app role are the pragmatic standard.

Passwords and secrets

pg_dumpall exports password hashes (SCRAM) — treat globals.sql as a secret. Better: manage roles declaratively (migrations/IaC) so a restore recreates them from code, and let backups carry only data.