pg_dumpall and Globals

Globals

PostgreSQL objects that exist at the cluster level rather than inside any single database: roles (with all their attributes and memberships) and tablespaces. They are visible from every database on the cluster simultaneously, which is exactly why pg_dump — scoped to one database — never captures them.

pg_dumpall --globals-only

Dumps every role and tablespace on the cluster as a plain SQL script, with no reference to any specific database's tables or data. Meant to be replayed with psql (not pg_restore — the output is always plain SQL) on a target cluster before any pg_dump/pg_restore of an individual database.

🔎 See What the Dump Missed

Dump the reporting schema and check the file for any mention of report_owner — the role that owns everything in it.

grep -c "report_owner" /tmp/reporting.dump

student@lab:~$ grep -c "report_owner" /tmp/reporting.dump grep: /tmp/reporting.dump: binary file matches 1

📤 Dump Globals Only

Dump every role and tablespace on this cluster with pg_dumpall --globals-only.

pg_dumpall -U postgres --globals-only -f /tmp/globals.sql

student@lab:~$ pg_dumpall -U postgres --globals-only -f /tmp/globals.sql

📖 Inspect the Globals File

Confirm report_owner's CREATE ROLE statement is actually in the globals dump.

grep 'report_owner' /tmp/globals.sql

student@lab:~$ grep 'report_owner' /tmp/globals.sql CREATE ROLE report_owner; ALTER ROLE report_owner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;

🏗️ Bootstrap a Genuinely Separate Cluster

Initialize a brand-new, empty PostgreSQL cluster on this VM to stand in for the "new server" in the migration scenario.

initdb -D /tmp/freshcluster -U postgres

student@lab:~$ initdb -D /tmp/freshcluster -U postgres The files belonging to this database system will be owned by user "student". This user must also own the server process. The database cluster will be initialized with locale "C". The default database encoding has accordingly been set to "SQL_ASCII". The default text search configuration will be set to "english". Data page checksums are enabled. creating directory /tmp/freshcluster ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... sysv selecting default "max_connections" ... 100 selecting default "shared_buffers" ... 128MB selecting default time zone ... UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... sh: locale: not found ok syncing data to disk ... ok initdb: warning: enabling "trust" authentication for local connections initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /tmp/freshcluster -l logfile start

▶️ Start the Fresh Cluster on Port 5433

Start the new cluster on an alternate port so it can run alongside the primary cluster on this VM.

pg_ctl start -D /tmp/freshcluster -o "-p 5433" -l /tmp/freshcluster/startup.log

student@lab:~$ pg_ctl start -D /tmp/freshcluster -o "-p 5433" -l /tmp/freshcluster/startup.log waiting for server to start.... done server started

❌ Attempt the Restore Without Globals First

Restore the reporting schema dump into the fresh cluster before its roles exist, and watch ownership fail.

createdb -p 5433 -h /tmp -U postgres migrated_db

pg_restore -p 5433 -h /tmp -U postgres -d migrated_db /tmp/reporting.dump

student@lab:~$ createdb -p 5433 -h /tmp -U postgres migrated_db student@lab:~$ pg_restore -p 5433 -h /tmp -U postgres -d migrated_db /tmp/reporting.dump pg_restore: warning: errors ignored on restore: 3 pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 6; SCHEMA reporting postgres pg_restore: error: could not execute query: ERROR: role "report_owner" does not exist Command was: ALTER SCHEMA reporting OWNER TO report_owner;

📥 Restore Globals to the Fresh Cluster

Replay the globals dump against the fresh cluster before touching any database dump again.

psql -p 5433 -h /tmp -U postgres -f /tmp/globals.sql

student@lab:~$ psql -p 5433 -h /tmp -U postgres -f /tmp/globals.sql SET SET SET SET CREATE ROLE ALTER ROLE psql:/tmp/globals.sql:18: ERROR: role "postgres" already exists ALTER ROLE CREATE ROLE ALTER ROLE CREATE ROLE ALTER ROLE CREATE ROLE ALTER ROLE CREATE ROLE ALTER ROLE

✅ Verify Roles Transferred

List roles on the fresh cluster and confirm report_owner is now present.

psql -p 5433 -h /tmp -U postgres -c "\du report_owner"

student@lab:~$ psql -p 5433 -h /tmp -U postgres -c "\du report_owner" SET List of roles Role name | Attributes --------------+-------------- report_owner | Cannot login

🔁 Retry the Database Restore — Cleanly

Drop the half-restored database, recreate it, and restore the reporting dump again now that the role it depends on actually exists.

dropdb -p 5433 -h /tmp -U postgres migrated_db

createdb -p 5433 -h /tmp -U postgres migrated_db

pg_restore -p 5433 -h /tmp -U postgres -d migrated_db /tmp/reporting.dump

student@lab:~$ dropdb -p 5433 -h /tmp -U postgres migrated_db student@lab:~$ createdb -p 5433 -h /tmp -U postgres migrated_db student@lab:~$ pg_restore -p 5433 -h /tmp -U postgres -d migrated_db /tmp/reporting.dump student@lab:~$