Why does the major version update fail when postgis is installed

Richard Huesken 1

The serverlogs show the following error when doing a major version upgrade from postgres version 14 to version 16. Installed postgis, postgis_raster and postgis_sfcgal.

ERROR

0A000

SELECT public.postgis_extensions_upgrade()

UTC-6783af44.35b-azuresu-msg_dev-ERROR: 0A000: updating an extension to a specific version is not supported

ShaktiSingh-MSFT 16,171 Reputation points

2025-01-13T08:37:23.62+00:00

Hi Richard Huesken •,

Welcome to Microsoft Q&A forum.

As I understand, in your Azure PostgreSQL server, major version update fail when postgis is installed.

Could you please make sure extension compatibility on the new PostgreSQL version and perform upgrade.

Please check if the extension postgis is enabled in Server Parameters list in Azure Portal for Flexible Server.

Refer for more details:

https://www.percona.com/blog/issues-upgrading-postgis-and-postgresql/

Awaiting your reply.

Thanks
Richard Huesken 1 Reputation point

2025-01-13T12:04:56.81+00:00

Hi,

Thanks for your response.

Azure docs (https://learn.microsoft.com/en-us/azure/postgresql/extensions/concepts-extensions-versions#postgis) state that Postgres 14 comes with Postgis 3.2.3, and Postgres 16 comes with Postgis 3.3.3

The link you included shows a compatibility matrix, which explains that Postgis 3.2.3 is no longer supported with Postgres 16. However, Postgis 3.2.3 is still included in my Postgres 14 instance.

I tried to manually upgrade postgis in the Postgres 14 instance, yet I do not have the permissions to do so (I'm do not have azuresu priviliges....).

Please provide further instructions on how to proceed.
Richard Huesken 1 Reputation point

2025-01-13T15:28:41.68+00:00

I used another account to update the extensions. The commands succeed now, however the versions of the extensions are not updated. I also restarted the postgres instance, without success.

Please advise: what step am I missing?

ALTER EXTENSION postgis update;

ALTER EXTENSION postgis_raster UPDATE;

ALTER EXTENSION postgis_sfcgal UPDATE;

SELECT * FROM pg_extension;

oid |extname |extowner|extnamespace|extrelocatable|extversion|extconfig

------+--------------+--------+------------+--------------+----------+---------

14328|plpgsql | 10| 11|false |1.0 |NULL

26441|postgis | 24806| 2200|false |3.2.3 |{26751}

28029|postgis_sfcgal| 24806| 2200|true |3.2.3 |NULL

27472|postgis_raster| 24806| 2200|false |3.2.3 |NULL

475037|hypopg | 10| 2200|true |1.4.0 |NULL
ShaktiSingh-MSFT 16,171 Reputation points

2025-01-15T08:23:51.6766667+00:00

Hi Richard Huesken •,

Thanks for providing the details.

Let me try to reproduce the scenario and get back to you.

In the meantime, if need is urgent, I request you to raise support case for quick addressal.

Thanks!
ShaktiSingh-MSFT 16,171 Reputation points

2025-01-16T08:17:05.4133333+00:00

Hi Richard Huesken •,

Thanks for your patience.

Only members of "azure_pg_admin" are allowed to use CREATE EXTENSION.

Could you please check if under azure.extensions Server Parameter, Postgis shows as selected?

and shows as below in Azure CLI:

Awaiting your reply.

Thanks
ShaktiSingh-MSFT 16,171 Reputation points

2025-01-17T07:47:49.72+00:00

Hi Richard Huesken •,

Just checking if you got a chance to look at the above reply.

Thanks
Richard Huesken 1 Reputation point

2025-01-17T10:06:57.8366667+00:00

Hi,

Thanks for your reply.

In my instance, the server parameter is set as displayed in your screenshot. I already executed the query "SELECT * FROM pg_extension;". This returns a postgis 3.2.3 (see my comment above, Jan 13th 2025).

How to proceed?

1 answer

Richard Huesken 1 Reputation point

2025-01-13T11:57:38.86+00:00

Original post was not an aswer and is replaced by the comment above
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Why does the major version update fail when postgis is installed

1 answer

Your answer