Unable to upgrade Postgres sql 9.6 to 15

[srinathjamboju9]

I'm trying to upgrade my Cloud SQL instance running Postgres version 9.6 to 15 and I got the below error.

I then looked around the docs to upgrade the postgis version and found this link. It says 
"Note: If you've enabled the PostGIS extension, then you can't upgrade to PostgreSQL 15. Remove the extension, perform the upgrade, and then reinstall the extension."

So, I've tried deleting the extension to see if that works, and it results with the below error

And then I found this link, and it says that I need to use self-service maintenance to roll out the latest systems manager (SSM) image. That took me to this page

Now when I use the command to grep for the availableMaintenanceVersions, nothing comes up and there is no such attribute at all in the output. And there is no single explanation anywhere as to why that attribute is missing.

Now I cannot figure out how to delete the extension to unblock me to proceed with upgrade. I logged into the instance as the owner of the database in which this extension is enabled, yet I cannot delete the extension. The user is part of the role cloudsqlsuperuser, still cannot delete the extension.

The documentation is not clear on some of the above options.

Is anyone here who can help?

[ms4446]

Here are some steps and considerations to help you navigate through the process:

Pre-Upgrade Preparation

• Identify the Appropriate Owner: Determine the most suitable role in your Cloud SQL PostgreSQL instance to have ownership of the PostGIS extensions. Carefully consider your security requirements and operational needs.
• Backup Your Database: Creating a complete backup (both data and schema) of your Cloud SQL instance before implementing changes is essential. Cloud SQL has features for automated and on-demand backups, ensuring recovery options in case of issues.

Managing Extensions and Permissions

• Potential Ownership Change Restrictions: Cloud SQL's managed environment may prevent you from directly using the standard ALTER EXTENSION ... OWNER TO ... command.

  • Contact Google Cloud Support: The recommended approach to change extension ownership is to contact Google Cloud Support for assistance to execute this on your behalf.
  • Consider Alternatives: If immediate ownership change isn't feasible, consult Cloud SQL documentation and support team to explore alternative ownership models or dropping and recreating extensions (if your use case allows),understanding the potential implications.

• Drop Extensions if Necessary: For compatibility reasons or if specifically guided by the upgrade instructions, you might need to drop PostGIS extensions. Proceed with this step only if fully aware of the impact on your data and schema.

  DROP EXTENSION postgis; 
  DROP EXTENSION postgis_tiger_geocoder; 
  DROP EXTENSION postgis_topology;
  

Upgrading PostgreSQL

• Upgrade PostgreSQL: Use the Cloud Console or gcloud CLI to upgrade your Cloud SQL instance to PostgreSQL 15, adhering to Google Cloud SQL's instructions.

Reinstalling PostGIS

• Reinstall PostGIS Extensions: After the PostgreSQL upgrade, reinstall compatible versions of the PostGIS extensions to restore all spatial features.
  

• CREATE EXTENSION postgis; 
  CREATE EXTENSION postgis_tiger_geocoder; 
  CREATE EXTENSION postgis_topology;
  

Post-Upgrade Actions

• Evaluate Ownership: Revisit the PostGIS extension ownership model (created as part of the installation or in previous configurations) to determine if your security and operational needs are still met. Contact Google Cloud Support if ownership modifications are necessary.
• Comprehensive Testing: Test all database and application functions thoroughly in a staging environment to ensure compatibility with PostgreSQL 15 and the new PostGIS versions. Only move the changes to production when the testing is successful.

Support and Documentation

• Consult Google Cloud Support and Documentation: For help related to permissions, extension management, or general guidance on the upgrade process,utilize Google Cloud Support and always refer to the latest Cloud SQL documentation.

Important Notes

• Understand SQL Commands: Be sure you fully grasp the potential consequences of SQL commands that change ownership or drop database objects.
• Managed Service Considerations: Direct use of certain superuser-level commands might be restricted in Cloud SQL. Remember to follow documented methods and commands, referring to the latest Google Cloud documentation when facing potential limitations.

[srinathjamboju9]

Thank you for the response.

I've tried the DROP command already and it complains about the ownership error.

[srinathjamboju9]

I've in fact tried to follow the instructions from one of your responses here https://www.googlecloudcommunity.com/gc/Databases/Unable-to-DROP-EXTENSION-in-Cloud-SQL-for-Postgres...

And the below command returns nothing

SELECT extname FROM pg_extension WHERE extowner NOT IN (SELECT oid FROM pg_roles);

[ms4446]

Your observation that the query SELECT extname FROM pg_extension WHERE extowner NOT IN (SELECT oid FROM pg_roles); returns no results is insightful. It indicates that all PostGIS extensions currently have valid owners, which are recognized within the PostgreSQL system. This result suggests the challenge isn't due to orphaned extensions but rather stems from the restricted permissions typical in a managed environment like Cloud SQL.

In this scenario, the most straightforward and advisable course of action is to reach out to Google Cloud Support. 

To expedite the support process, it's beneficial to prepare the following information:

• Project ID: Have your Google Cloud project ID ready, as this will help the support team quickly locate your Cloud SQL instance within their system.
• Cloud SQL Instance ID: Know the exact name of the Cloud SQL instance you're experiencing issues with, ensuring clarity and specificity in your request.
• Detailed Explanation: Provide a concise yet comprehensive description of your attempt to drop the PostGIS extensions, including the exact error message ("must be the owner of extension PostGIS") and a note that you've confirmed all extensions have valid owners. Additionally, include any specific steps or commands you've already tried, as this can help the support team understand your situation better and tailor their assistance accordingly.

[srinathjamboju9]

that absolutely makes no sense to me and it flags a security issue. How can GCP support access a database that is owned by customer? That's not even a systems database, and I'm concerned about the security of the database.

[ms4446]

Google Cloud Support may temporarily need to access customer-owned databases within Cloud SQL for tasks like extension management, complex troubleshooting, or applying critical updates. Google Cloud maintains stringent security protocols, including role-based access control (RBAC), comprehensive auditing, robust data encryption, and compliance with leading industry standards, to ensure the highest levels of data security and privacy. If you have concerns about Cloud Support accessing your database:

• Engage Directly with Google Cloud Support: Inquire about the specific reasons for required access and any security measures in place during the process.
• Assess Data Sensitivity: Review your database for sensitive information. Consider data anonymization or masking strategies as additional precautions where feasible.
• Review Your Service Agreement: Ensure your contract with Google Cloud meets your data protection and security expectations.

It's important to recognize that managed database services like Cloud SQL can offer enhanced security capabilities, thanks to the provider's dedicated resources, specialized expertise, and continuous investment in security infrastructure. These services are designed to maintain, if not improve, your data's security posture.