PostgreSQL upgrade on Amazon RDS blocked by PostGIS version

Question

I'm trying to upgrade my RDS instance from 9.5.4 to 9.6.1, and I'm getting blocked by an error about the PostGIS version needing to be upgraded first.

Database instance is in a state that cannot be upgraded: PreUpgrade checks failed: The instance could not be upgraded because there are one or more databases with an older version of PostGIS installed. Please upgrade all installations of PostGIS and try again.

The highest version of PostGIS supported by RDS 9.5.4 is 2.2.2

alter extension postgis update

yields

NOTICE: version "2.2.2" of extension "postgis" is already installed

I tried upgrading explicitly to the version in 9.6.1

alter extension postgis update to '2.3.0'

Getting

[Err] ERROR: extension "postgis" has no update path from version "2.2.2" to version "2.3.0"

So my question is: how do I upgrade? I suspect I can delete the extension prior to the upgrade and install the new version after, but I don't know what that will do to my data, or if that will properly upgrade my spatial structures or functions.

I have already checked the documentation for any mention of this scenario

Answer 1

Make sure your instance is upgraded to 9.5.4 before upgrading to 9.6.1.

I had this same error on one of my DB instances, but it was on 9.5.2. First I upgraded it to 9.5.4 and then to 9.6.1 and everything worked as expected.

The next instance I tried it on was already on 9.5.4 and got the same message.

So first I did an:

ALTER EXTENSION postgis UPDATE;

Then upgraded the server

Answer 2

I was running into this exact issue trying to get a server upgraded from 9.3 to 9.6. Through a bit of trial and error I found a successful path. Just a detail about the upgrade process: after each upgrade, the RDS requires a manual reboot. I would think the upgrade would automatically handle that, but it does not.

Starting point:

SELECT version(); ->  PostgreSQL 9.3.20 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
SELECT postgis_full_version(); ->  POSTGIS="2.1.8 r13780" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.5, released 2016/07/01" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER
ALTER EXTENSION postgis UPDATE; ->  Returned success, but no change to PostGIS version

After upgrading to 9.4:

SELECT version(); ->  PostgreSQL 9.4.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
SELECT postgis_full_version(); ->  POSTGIS="2.1.8 r13780" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.5, released 2016/07/01" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER
ALTER EXTENSION postgis UPDATE; ->  Returned success, but no change to PostGIS version

After upgrading to 9.5:

SELECT version(); ->  PostgreSQL 9.5.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
SELECT postgis_full_version(); ->  POSTGIS="2.1.8 r13780" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.5, released 2016/07/01" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER
ALTER EXTENSION postgis UPDATE; ->  SQL Error [XX000]: ERROR: attempt to redefine parameter "postgis.backend"

So I was stuck here for a bit, couldn't seem to get PostGIS upgraded successfully. Then I saw this post describing that same error and decided to just wait for 24 hours to see if the RDS instance received a patch. It seems that it did, though I didn't notice anything in the logs. In any event, without any changes from myself, the next day I was able to successfully upgrade the PostGIS extension

ALTER EXTENSION postgis UPDATE; ->  Returned success
SELECT postgis_full_version(); ->  POSTGIS="2.2.5 r15298" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.0.3, released 2016/07/01" LIBXML="2.9.1" LIBJSON="0.12" RASTER

After that, I was able to upgrade the RDS instance to 9.6

SELECT version(); ->  PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
SELECT postgis_full_version(); ->  POSTGIS="2.2.5 r15298" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.0.3, released 2016/07/01" LIBXML="2.9.1" LIBJSON="0.12" RASTER
ALTER EXTENSION postgis UPDATE; ->  Returned success
SELECT postgis_full_version(); ->  POSTGIS="2.3.7 r16523" PGSQL="96" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.1.4, released 2017/06/23" LIBXML="2.9.1" LIBJSON="0.12" RASTER

I'd like to figure out how to trigger that apparent patch to the extensions immediately rather than just waiting for it, but I haven't been able to figure that out yet.

Answer 3

I had same issue. the problem was, I had more than 1 database. I upgraded main database but forgot about others. You need to check all your databases in same server and upgrade each of them with following command.

ALTER EXTENSION postgis UPDATE;

Answer 4

The real reason for the ERROR: attempt to redefine parameter "postgis.backend" is because you have referenced the old postGIS libraries by invoking the PostGIS_full_version() function, as a result the library configuration cannot be updated to the new version since the session still has the old functions loaded.

You need to disconnect from the database and connect a new session then immediately invoke the ALTER EXTENSION postgis UPDATE; command before trying to use any gis functions.

Any existing connections will reference the old functions until they disconnect & reconnect at which point the new ones will be loaded.

Once the AWS RDS PostgreSQL upgrade step is completed you should perform a further REBOOT of the instance in order to complete the synchronisation of the parameter group. I would recommend this be done before you do the postGIS extension updates.

You can check what version of the libraries are present by using: select distinct probin from pg_proc where probin IS NOT null order by probin and probin like '%postgis%';