Solved: Cannot update PostGIS to 3.4.0

g_mendes_gain

Following the official CloudSQL documentation: https://cloud.google.com/sql/docs/postgres/extensions#postgis , CloudSQL supports PostGIS 3.4.0 for Postgres version 15 (which is what I'm currently using).

While trying to upgrade the Postgres version to 16, I get the following error:

* Error waiting for Patch Instance: pre-upgrade check failed: errors:{type:INVALID_EXTENSION_VERSION detail:"extension \"postgis\" needs to be upgraded before major version upgrade for database \"redacted\""}

While trying to upgrade the postgis version, I get the following:

ALTER EXTENSION postgis UPDATE TO "3.4.0";
ERROR: Extension "postgis" and version "3.4.0" not supported by Cloud SQL.
DETAIL: Supported extensions are "address_standardizer:3.2.5, address_standardizer_data_us:3.2.5, amcheck:1.3, anon:1.0.0, autoinc:1.0, bloom:1.0, btree_gin:1.3, btree_gist:1.7, citext:1.6, cube:1.5, dblink:1.2, dict_int:1.0, dict_xsyn:1.0, earthdistance:1.1, fuzzystrmatch:1.1, google_ml_integration:1.2, hll:2.18, hstore:1.8, insert_username:1.0, intagg:1.1, intarray:1.5, ip4r:2.4, isn:1.2, lo:1.1, ltree:1.2, moddatetime:1.0, oracle_fdw:1.2, orafce:4.7, pageinspect:1.11, pg_background:1.2, pg_bigm:1.2, pg_buffercache:1.3, pg_cron:1.6, pg_freespacemap:1.2, pg_hint_plan:1.5, pg_partman:4.5.1:4.7.4, pg_prewarm:1.2, pg_proctab:0.0.10, pg_repack:1.5.0, pg_similarity:1.0, pg_squeeze:1.5, pg_stat_statements:1.10, pg_trgm:1.6, pg_visibility:1.2, pg_wait_sampling:1.1, pgaudit:1.7, pgcrypto:1.3, pgfincore:1.3.1, pglogical:2.4.3, pgrowlocks:1.2, pgstattuple:1.5, pgtap:1.3.0, pgrouting:3.3.0, pgtt:3.0.0, plpgsql:1.0, plproxy:2.11.0, plv8:3.2.0, postgis:3.2.5, postgis_raster:3.2.5, postgis_sfcgal:3.2.5, postgis_tiger_geocoder:3.2.5, postgis_topology:3.2.5, postgres_fdw:1.1, prefix:1.2.0, rdkit:4.3.0, refint:1.0, sslinfo:1.2, tablefunc:1.0, tcn:1.0, tsm_system_rows:1.0, tsm_system_time:1.0, unaccent:1.1, uuid-ossp:1.1, vector:0.5.1".

g_mendes_gain

Hello, thanks for your response!

We decided to wait for the CloudSQL Maintenance, and it worked.

After the monthly maintenance that happened during this weekend, the PostGIS version was automatically updated and then we were able to bump the postgres version to 16 without the need for creating a new instance and moving the backup to the new instance.

This would have introduced some downtime to our app that would not be ideal for us.

Thank you anyhow for the help.

Best regards!

View solution in original post

ms4446

The issue you're facing with upgrading PostGIS in Google Cloud SQL to version 3.4.0 while using PostgreSQL 15 stems from a known incompatibility within the Cloud SQL environment. Although PostGIS 3.4.0 is officially supported on PostgreSQL 15, Cloud SQL currently does not allow direct upgrades to this version. Additionally, upgrading to PostgreSQL 16 requires a compatible PostGIS version, which Cloud SQL currently does not support, creating a roadblock for those seeking both upgrades.

To overcome this issue, consider the following solutions:

New Instance

Create a new Cloud SQL instance running PostgreSQL 16 and PostGIS 3.4.0, then migrate your data. This is the most straightforward approach to ensure full compatibility. By setting up a fresh instance, you avoid the complications associated with in-place upgrades and ensure that both your PostgreSQL and PostGIS versions are fully compatible.

Downgrade & Upgrade

If your application's functionality isn't strictly dependent on PostGIS 3.4.0, you can try this workaround:

Downgrade PostGIS:Downgrade PostGIS to a version explicitly supported by Cloud SQL for your current PostgreSQL version (e.g., 3.2.5).
Upgrade PostgreSQL:Proceed with upgrading PostgreSQL to version 16.
Upgrade PostGIS:After upgrading PostgreSQL, attempt to upgrade PostGIS again, potentially to 3.4.0. This step might succeed once PostgreSQL is at version 16.

Dump and Restore

Create a dump of your existing database using pg_dump, set up a new instance with PostgreSQL 16 and PostGIS 3.4.0, and restore the dump using pg_restore. This method allows for a clean start, ensuring that your database version and PostGIS extension are compatible from the outset.

Key Considerations

Data Migration: Always test thoroughly on a non-production environment before migrating production data. This step is crucial to identify and resolve any issues that might arise during the migration process.
PostGIS Functionality: Verify that your application's critical features function as expected after any upgrade or migration. Ensuring the integrity and functionality of your application post-migration is essential to avoid any disruptions.
Official Documentation: Refer to the Google Cloud SQL documentation for the most up-to-date information. Keeping abreast of the latest documentation can provide insights into supported versions and potential changes.

Troubleshooting Tips

Check Cloud SQL Logs: Examine the logs for detailed error messages that might offer further clues. These logs can provide valuable information for diagnosing and resolving issues.
Contact Google Cloud Support: If you encounter persistent issues, reach out to Google Cloud Support for expert assistance tailored to your specific Cloud SQL configuration. They can offer guidance and support based on your unique setup, helping to resolve the problem effectively.

g_mendes_gain