I want to upgrade the major version of my prod Postgres RDS database, from 11.16 to 12.11. I restored a snapshot to do a dry run. When trying to modify the version on AWS, I have the following error:
pg_restore: from TOC entry 5375; 1259 180582003 INDEX my_index my_database
pg_restore: error: could not execute query: ERROR: permission denied for schema publicf2iwen5v79e7a7ugjf2zdrpvhvpmr9bc
Command was:
-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('180582003'::pg_catalog.oid);
CREATE INDEX "my_index" ON "publicf2iwen5v79e7a7ugjf2zdrpvhvpmr9bc"."my_table" USING "gin" ("upper"("my_column") "publicf2iwen5v79e7a7ugjf2zdrpvhvpmr9bc"."gin_trgm_ops");
When removing this index, and the other 3 trgm indexes, the upgrade works fine.
I've checked the owner of the extension, it's the same as the other ones, and I don't see any difference between those indexes and other ones, except they're from pg_trgm
Have you any idea on how to fix this? Obviously I would like to avoid deleting these indexes on my real prod DB before upgrading the version.
The same problem occurs when upgrading from 12 to 13
