"schema with OID does not exist" error during Postgres 12.8 to 13.6 upgrade

Question

I was trying to perform an upgrade to a snapshot-restored instance (dry run) of a client's RDS Postgres 12.8 database, using the RDS console. The upgrade process failed, during `pg_dumpall`, with the following error:
```
error: schema with OID 12245845 does not exist
```
I did some investigation -- first I ran `select oid, nspname from pg_namespace where oid= 12245845` and got 0 rows returned. Then I started going through the list of pg_catalog tables, finally getting a hit when I ran `select * from pg_proc where pronamespace = 12245845`. So there's a row in `pg_catalog.pg_proc` referencing a non-existent namespace.

If this was a non-RDS instance, I could delete the row in `pg_catalog.pg_pg_proc`, but I can't seem to do that in RDS using the master user. I tried manually dumping the database (using `pg_dumpall` and `pg_dump`) but I can't seem to get around this non-existing schema error. Is there any hope, or am I looking at a complete re-install and restoration of data from another source?

Many thanks for any ideas or pointers,

--Gary

Answer

Hello Gary, 
This error message indicates the orphaned functions or missing schemas which is preventing you from upgrading RDS Postgres instance. In this case normally you would need to delete those records if it's community PostgreSQL, however it's not possible here since RDS is a managed service. To resolve the issue you will have to create support case with AWS Support. They will perform necessary modifications to resolve this error.

They can change the owner of that schema in question to RDS master user. You can then drop both the schema and the function, but i would suggest to discuss this further with RDS support engineer.

"schema with OID does not exist" error during Postgres 12.8 to 13.6 upgrade

Relevant content