pg_restore prints role does not exist for owners and grants after a move to a new server
could not execute query: ERROR: role "xxx" does not existShort answer
The dump names roles, both as object owners and in GRANT lines, and the server you restore into has none of them. pg_dump never saves roles. Restore with --no-owner and --no-privileges, or create the roles first, for example by exporting them from the old cluster with pg_dumpall --roles-only.
Fix it fast
1Find out which cause you have
Run this on the server you restore into. Compare the list with the role names in the error. Any name that is in the error and not in the list is a missing role.
psql -d postgres -c "SELECT rolname, rolcanlogin FROM pg_roles ORDER BY rolname;"2Match what you see, then jump to its fix
- 1
Command was: ALTER ... OWNER TO <name>; followed by the role does not exist errorObject owners from the old server do not exist hereFix - 2
Command was: GRANT ... TO <name>; and the object itself was created fineGRANT or REVOKE lines name a role that is missingFix - 3
Many different role names fail, or every application user is missing on the new serverpg_dump never saved the roles, so none of them were movedFix
- 1
What does the error look like?
could not execute query: ERROR: role "xxx" does not existCommand was: ALTER TABLE public.orders OWNER TO xxx;Command was: GRANT SELECT ON TABLE public.orders TO reporting;
Cause 1 of 3
Object owners from the old server do not exist here
1Check
By default pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION statements to give every object its original owner. The name after OWNER TO in the failing command is a role the new server lacks.
pg_restore -d mydb mydump.dump 2>&1 | grep -B1 -A1 "does not exist"2Fix
Choose one. Either skip ownership, so the user you connect as owns everything, or create the missing role and restore again. Create the role with LOGIN if an application will sign in as it, because CREATE ROLE alone gives NOLOGIN.
# option 1: restore without original owners
pg_restore -d mydb --no-owner mydump.dump
# option 2: create the role, then restore as before
psql -d postgres -c "CREATE ROLE appuser LOGIN;"
pg_restore -d mydb mydump.dump3Verify
Restore into a fresh, empty database. The command prints no role does not exist errors, and the tables are listed.
psql -d mydb -c "\dt"Confirmed by postgresql.org
Cause 2 of 3
GRANT or REVOKE lines name a role that is missing
1Check
Privileges are restored from the archive too. A GRANT to a role that does not exist fails even when every owner is in place. Read the role name at the end of the failing command.
pg_restore -d mydb --no-owner mydump.dump 2>&1 | grep -A1 "GRANT"2Fix
Create the role, or restore without access privileges and grant them again by hand. Option -x (also spelled --no-privileges) skips every GRANT and REVOKE command.
pg_restore -d mydb --no-owner --no-privileges mydump.dump
psql -d mydb -c "GRANT SELECT ON TABLE public.orders TO reporting;"3Verify
Restore into a fresh, empty database with the same options. No role errors appear. Then sign in as the application role and run one query.
psql -d mydb -c "\dt"Confirmed by postgresql.org
Cause 3 of 3
pg_dump never saved the roles, so none of them were moved
1Check
pg_dump dumps one database. Roles and tablespaces are global objects, and only pg_dumpall writes them. List the roles on the old server to see what has to travel.
# on the OLD server
psql -d postgres -c "SELECT rolname FROM pg_roles ORDER BY rolname;"2Fix
On the old server write the roles to a file with pg_dumpall --roles-only, read it, and run it on the new server as a superuser before pg_restore. Expect a harmless role already exists error for the bootstrap superuser. Add --no-role-passwords if you want to set passwords yourself: the roles then have a null password.
# on the OLD server
pg_dumpall --roles-only -f roles.sql
# on the NEW server, as a superuser, then restore the database
psql -X -d postgres -f roles.sql
pg_restore -d mydb mydump.dump3Verify
Run the first-step query on the new server. The roles from the old server are listed.
psql -d postgres -c "SELECT rolname FROM pg_roles ORDER BY rolname;"Confirmed by postgresql.org
How do I stop it coming back?
- Move roles together with data: run pg_dumpall --roles-only on the old cluster whenever you plan a migration.
- If the new environment will use different role names, restore with --no-owner and grant privileges again deliberately.
- Keep the role list (CREATE ROLE statements) in version control, so a new server can be built the same way every time.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
psql says the role does not exist, even for postgres or your own user
FATAL: role "username" does not exist
The server found no role with the name the client sent.
3 causes, each with a fixDatabases
A PostgreSQL restore fails on foreign key constraints
data-only restore into a schema that already has foreign keys fails
Foreign keys fail on restore when rows load before the rows they point to, or when the dump never contained the parent tables.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fix