Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

pg_restore prints role does not exist for owners and grants after a move to a new server

Errorcould not execute query: ERROR: role "xxx" does not exist

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;"
  2. 2Match what you see, then jump to its fix

    1. 1Command was: ALTER ... OWNER TO <name>; followed by the role does not exist errorObject owners from the old server do not exist hereFix
    2. 2Command was: GRANT ... TO <name>; and the object itself was created fineGRANT or REVOKE lines name a role that is missingFix
    3. 3Many 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

What does the error look like?

  • could not execute query: ERROR: role "xxx" does not exist
  • Command 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.dump

3Verify

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

Did this fix it?

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

Did this fix it?

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.dump

3Verify

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

Did this fix it?

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

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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.