0

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

TheEnglishMan_
  • 33
  • 10

1 Answers1

0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Must have been corrupted by my repeated attempts at upgrades along the way, luckily this was a local test before doing so on a live installation. However this solved my issue so thank you! – TheEnglishMan_ Jun 13 '22 at 18:59