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-13T11:57:38.86+00:00

Original post was not an aswer and is replaced by the comment above
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-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?
ShaktiSingh-MSFT 16,171 Reputation points

2025-01-20T05:32:09.2766667+00:00

Hi Richard Huesken •,

Thanks.

I followed as per your problem statement.

Created an Azure Database for PostgreSQL Flexible Server 14, upgraded to 16.

Enabled Server Parameters for POSTGIS, then Created Extension from Azure CLI command.

When executed query of pg_extension, I could see the latest version 3.3.3 after upgrade to 16 major version.

If you did the same, and still could not see the latest version, it seems strange, and I would request you to raise support case for deeper investigation.

Let me know if you have any query.

Thanks

2 answers

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
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.
Richard Huesken 1 Reputation point

2025-01-22T14:21:35.7166667+00:00

Postgres DB Product team has mitigated our Postgres Instance, after that the Major Version Upgrade is performed succesfully. However, this was an instance specific fix. User who run into this issue should create a Ticket and apply for this mitigation to be done on their instance.
Please sign in to rate this answer.
ShaktiSingh-MSFT 16,171 Reputation points

2025-01-24T04:26:29.28+00:00

Hi Richard Huesken •,

Glad to know that your issue is resolved and sorry to know about your experience with instance specific issue.

It seems like for specific servers this could a transient issue.

However, when I followed similar steps, I was able to create server, upgrade and enabled extension to the expected version.

We recommend you to share your feedback over Azure PostgreSQL Feeback Channel so that internal team can work on the item depending on the importance and up-votes.

Thanks
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

2 answers

Your answer