PostGIS 3.0.3 incompatible with PostgreSQL 12.17.

Question

Hi all, we recently had an incident where our PostgreSQL DB server in RDS was upgraded to 12.17 from 12.14 (through routine RDS maintenance). At the time it was running the PostGIS extension version 3.0.3. A consequence of this was that any call to the PostGIS function `ST_ClusterKMeans` caused a Sig 11 fault that put the DB into recovery mode and terminated all existing connections.  We were able to resolve the issue once we'd identified the cause by upgrading the PostGIS extension to 3.4, but it caused over 24 hours of downtime.

Answer

`PostGIS 3.0.3` is recommended for use with `PostgreSQL 13.1` and `GEOS 3.8.1`. Meanwhile, `PostGIS 3.4` is compatible with `PostgreSQL versions 12 through 16`, and requires `GEOS 3.6` or newer. It is advisable to keep this compatibility in mind, especially since others may encounter similar challenges during routine RDS maintenance.

Resources:
* [PostGIS-3.0.3 compartibility](https://postgis.net/2020/11/PostGIS-3.0.3/)
* [PostGIS-3.4.0 compartibility](https://postgis.net/2023/08/PostGIS-3.4.0/)

Answer

The challenge we faced was that when we originally created the PostgreSQL instance (12.7), it shipped with PostGIS 3.0.3 (https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-12x).

Unfortunately, RDS eventually migrated PostgreSQL to 12.17, but PostGIS extension migrations were not automatic, so it wasn't until it reached 12.17 that it failed.

Lesson learned, keep extensions up to date, but it was still an unexpected failure.

PostGIS 3.0.3 incompatible with PostgreSQL 12.17.

相關內容