Restoring with pg_restore and pg_dumpall
It is restore time for real: Pedal's staging server must be rebuilt from production's backups. The team runs pg_restore, and the log fills with role "pedal_owner" does not exist. Every command was the right one; the order was wrong.
The rule
Globals first, then the database, then its contents.
A cluster's backup comes in two parts:
pg_dumpall --globals-onlywrites the cluster-wide objects: roles (with their password hashes, attributes and memberships) and tablespaces. It is plain SQL, restored withpsql.pg_dump -Fcper database, restored withpg_restore.
pg_dumpall without the flag dumps globals and every database into one plain SQL file. That is simple for a small cluster but gives up parallel and selective restore, so many teams run pg_dumpall -g plus one pg_dump -Fc per database.
# backup, nightly
pg_dumpall -h db1 -U postgres --globals-only -f globals.sql
pg_dump -h db1 -U postgres -d pedal -Fc -f pedal.dump
# restore, on the new server
psql -U postgres -f globals.sql # 1. roles and tablespaces
pg_restore -U postgres -d postgres --create -j 4 pedal.dump # 2 and 3--create tells pg_restore to create the pedal database and then connect to it; the -d postgres is only where it starts. Useful pg_restore options:
-j 4: restore with four parallel jobs (custom or directory format).-l: list the archive's table of contents without restoring anything.-t tariffsor-n billing: restore only one table or one schema.--clean --if-exists: drop existing objects before re-creating them.--no-owner: skip ownership, so everything is owned by the role that runs the restore.-f out.sql: write the SQL instead of running it, to inspect it.
The shortcut
Before any restore, write the three steps as a checklist — globals, database, contents — and for each, name the file and the tool. If any line is blank, you have found the gap before it bites.
Globals
globals.sqlwithpsql. Roles such aspedal_ownerandanalystsnow exist.
The trap is restoring production's globals onto a server that already has roles with the same names, such as a shared staging box. The existing roles cause errors, and production password hashes arrive with the others. For staging, consider --no-owner and creating a small set of staging roles by hand.
Drills
Answer fast — trust the rule.
Which command backs up roles and tablespaces only?
How do you restore a plain-SQL file made by pg_dump or pg_dumpall?
You need only the tariffs table from pedal.dump. What do you run?
A restore prints role "analysts" does not exist for every GRANT. What was skipped?
Remember
- Globals first, then the database, then its contents.
pg_dumpall -gwith psql for roles;pg_dump -Fcwith pg_restore for each database.-llists,-tpicks,-jspeeds up,--createmakes the database.