PostGIS error by upgrading with PostGIS_Extensions_Upgrade()

Question

I have a PostgreSQL database with PostGIS installed version 2.5.5 We use RDS and our PostgreSQL version will soon be deprecated, to upgrade our PostgreSQL version it's necessary to upgrade PostGIS. When I try to do

SELECT PostGIS_Extensions_Upgrade();

I got this error:

SQL Error [42501]: ERROR: must be owner of extension postgis Where: SQL statement "ALTER EXTENSION postgis UPDATE TO "3.4.2";" PL/pgSQL function postgis_extensions_upgrade() line 22 at EXECUTE

I've also tried ALTER EXTENSION postgis UPDATE;

Which gave me the following:

QL Error [42501]: ERROR: must be owner of extension postgis

I've checked the current owner which is rdsadmin

An important fact: I have db1 which has an upgraded PostGIS 3.4, and the mentioned database (db2), they're both in the same RDS instance but are different databases.

How can I solve this issue?

• HeikkiVesanto: Is this AWS RDS? What version of PostgreSQL is it?
• Goncalves: Yes, it's an AWS RDS, the PostgreSQL version is 12.19
• HeikkiVesanto: What does select postgis_version() currently show? I don't think you can upgrade PostGIS in RDS, I thought each PostgreSQL version has a set PostGIS version: docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/…
• Goncalves: By typing select postgis_version() I got 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 When I check the available extensions with SELECT * FROM pg_available_extension_versions; I have available 2.5.5 and 3.4.2 (the version 3 is the desired one) I have tried also to drop the extension and re-install it, but I got the error SQL Error [42501]: ERROR: must be owner of extension PostGIS, which is the same as mentioned above.
• user30184: Doesn't if feel rather clear? By the message only the owner can upgrade. You have checked that the owner is rdsadmin. By repost.aws/questions/QUViv96PqWSdKMn_FAdRcw4A/… "rdsadmin" is a role with login permissions (also known as a user) that is used by our internal automation mechanisms. It seems that only AWS can upgrade PostGIS for you.

Answer 1

I made some checks and got into this article https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Roles.html

Investigating, I found out that I already had an user that had superior privileges when you start the RDS instance, the way I solved was using psql using this user and typing the command to upgrade PostGIS, which solved the problem

psql -h myhost.amazonaws.com -p 5432 -U user_with_privileges -d the_database_upgraded -c "SELECT PostGIS_Extensions_Upgrade();"

What I learned and wanted to share is that you always will have an user with the necessary privileges and have to use it, even in a different database, which was my case.

Thanks to all people that tried to help me!