Unable to upgrade PostgreSQL 9.6 with postgis 3.0.3 because of permissions

[mdurand]

Hello,

I'm trying to upgrade my PostgreSQL database from 9.6 to 10, but the Postgis extension is blocking me.

I tried the managed migration but I got an error caused by the Postgis extension:

"pre-upgrade check failed: errors:{type:INVALID_EXTENSION_VERSION detail:"extension \"postgis\" needs to be upgraded before major version upgrade for database"

Then I tried to manually upgrade Postgis but I got this error:

After looking on the internet, I found a few people with the same issue and no clear answer (except contact support). Actually, the only user that can have the right to do the update of the extension is the "cloudsqladmin", which is only available for Google's internals uses.

The official Google page for database upgrade says to perform self-service maintenance , but the documentation is unclear, and when I run the "gcloud sql instances describe data-base-ID" command, I get no "availableMaintenanceVersions".

Even logging in as the owner of the database with the "cloudsqlsuperuser" doesn't resolve the permissions issues.

I also tried to manually drop the Postgis extension but I got the same error as before (must be owner of the extension).

My case is very similar to this post, but there is no clear solution to the issue in this thread.

Is there anyone that can help ?

Thanks in advance !

[ms4446 · Accepted solution]

The issue you're facing is a common one encountered during PostgreSQL upgrades on Google Cloud SQL when PostGIS is involved. The core of the problem is that the cloudsqladmin user, who owns the PostGIS extension, is not directly accessible to users. This makes it difficult to upgrade PostGIS to a compatible version before the PostgreSQL upgrade.

• The PostGIS extension is owned by the cloudsqladmin user, which has elevated privileges and isn't meant for direct user interaction.
• Major version upgrades of PostgreSQL often require compatible versions of extensions like PostGIS.
• Regular database users and even the cloudsqlsuperuser lack the permissions to alter or update the PostGIS extension.

Given the permission issues, contacting Google Cloud Support is the most reliable way to resolve this issue. They can perform the necessary steps to upgrade the PostGIS extension and ensure your database upgrade proceeds smoothly.

[mdurand]

Okay, thanks for the quick answer!