RDS Postgres major upgrade from 12 to 13 fails


Tried upgrading RDS Postgres from major version 12 to 13. Also tried upgrading related snapshot. These get stuck with an error:

(please note that some info has been obfuscated in the error message to not reveal sensitive information)

Snapshot test is in a state that cannot be upgraded: patchDb 155947 (engine upgrade): pg_restore: from TOC entry 4889; 1259 763225 INDEX tablename_columnname_idx database pg_restore: error: could not execute query: ERROR: permission denied for schema public... Command was: -- For binary upgrade, must preserve pg_class oids SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('763225'::pg_catalog.oid); CREATE INDEX "tablename_columnname_idx" ON "public..."."tablename" USING "gin" ("columnname" "public..."."gin_trgm_ops");

What might cause this? Master user seems to be owner of public schema, also it has CREATE and USAGE privileges to the schema. Master user also is member of rds_superuser. Not sure this matters though, as it seems from the log that pg_restore is run as a user rdsadmin.

From log, it also visible that pg_restore creates succesfully some indexes before the failing one? Is the pg_trgm extension the actual culprit here? weird stuff.

Ok, so removing all pg_trgm indexes kind of solved the problem!?! But would be nice to able to restore those indexes too as they are quite large and re-building those from scratch will take forever.

2 Answers

This bug has been fixed in Postgres 14.5

answered 10 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions