Search code examples
databasepostgresqlrestorepostgresql-11postgresql-13

PostgreSQL pg_upgrade error on pg_restore. "role '29648' does not exist"

When using pg_upgrade to upgrade PostgreSQL from 11 to 13 I receive the below error in step "Restoring database schemas in the new cluster":

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3801; 0 0 ACL FUNCTION "pg_stat_statements_reset"() postgres
pg_restore: error: could not execute query: ERROR:  role "29648" does not exist

I can see pg_restore has already successfully restored other databases and all custom tables and constraints.

After researching online I can see that other suggest using the pg_restore option "-x, --no-privileges" however I do not see a way of applying this to the pg_upgrade command.

I've tried to locate this role in origin to no avail using SELECT * FROM pg_roles; but I see no role with rolname or oid as "29648".

Solution

  • You somehow managed to corrupt your database: there are permissions on the function pg_stat_statements_reset() for a user that doesn't exist. You'll have to search your conscience or statement history for the cause.

    The solution for this problem is simple, since the function belongs to an extension:

    DROP EXTENSION pg_stat_statements;
CREATE EXTENSION pg_stat_statements;

    Now the function will have the default permissions, and the upgrade should work without problems.