[postgis-devel] Upgrade PostGIS from 2.5.4 to 3.1.2

[Regina Obe]

Nikhil,

 

Your install looks fine.  The only thing I would recommend is if you don’t need raster support, you can drop it with:

 

DROP EXTENSION postgis_raster;

 

If the above fails with has dependencies, like a table column, then you are using raster.

 

Regarding this:

I have seen one blog which says that a direct upgrade from 2.4.6 to 3.1.2 has many dependencies on raster functions and we should upgrade to 3.1.1 and then to 3.1.2. They have mentioned postgis 2.4.6 but Is it valid in my case also? I am upgrading postgis from 2.5.4 to 3.1.2.

 

Blog - https://www.enterprisedb.com/docs/postgis/3.1.2/03_upgrading_postgis/#example-2

 

 

Going from 2.4.6 to 3.1.2 straight – the only reason I can think of is that there were some view issues with changes in PostgreSQL 12 I think, that we had to correct to accommodate upgrading to 12. Those were added after 2.4.6 (so doing a pg_upgrade from saying 2.4.6 to 12 would fail.  Using the latest 2.4 should work though.  

 

Now going from 2.5.4 to 3.1.2 – depends on the version.  It looks like you upgraded your 11 to 3.1.2 before pg_upgrading?  If that is the case, you are fine.

 

The only concern I had was how well repacking postgis_raster extension would work in PostgreSQL 13 since they removed the  “from unpackaged” feature.  If you can

 

DROP EXTENSION postgis_raster;

 

Then your setup is fine.  The only danger is having loose raster functions dangling around.

 

Sandro,

Can you remind me, I think you figured out a way around the raster unpackaged issue so it works in PG 13 too so it should be possible for someone to go straight from say PG 9.x 2.4 to PG 13 3.x

 

Thanks,

Regina

 

 

 

From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of Nikhil Shetty
Sent: Friday, January 28, 2022 12:12 AM
To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: Re: [postgis-devel] Upgrade PostGIS from 2.5.4 to 3.1.2

 

Hi Team,

 

I have followed the steps mentioned by Regina. Just to add a few more details. After database  upgrade you may see below message:

 

upgrade_testdb_dev=# SELECT postgis_full_version();

                                                                                                     postgis_full_version                                                                                       

               

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

---------------

 POSTGIS="3.1.2 cbe925d" [EXTENSION] PGSQL="110" (procs need upgrade for use with PostgreSQL "130") GEOS="3.9.1-CAPI-1.14.2" PROJ="7.2.1" GDAL="GDAL 3.2.3, released 2021/04/27" LIBXML="2.9.1" LIBJSON="0.11" T

OPOLOGY RASTER

(1 row)

 

 

Run the upgrade command again to resolve this on Pg13

 

upgrade_testdb_dev=# SELECT postgis_extensions_upgrade();

NOTICE:  Updating extension postgis 3.1.2

NOTICE:  Extension postgis_sfcgal is not available or not packagable for some reason

NOTICE:  Extension postgis_tiger_geocoder is not available or not packagable for some reason

                    postgis_extensions_upgrade                     

-------------------------------------------------------------------

 Upgrade completed, run SELECT postgis_full_version(); for details

 

upgrade_testdb_dev=# SELECT postgis_full_version();

                                                                            postgis_full_version                                                                            

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 POSTGIS="3.1.2 cbe925d" [EXTENSION] PGSQL="130" GEOS="3.9.1-CAPI-1.14.2" PROJ="7.2.1" GDAL="GDAL 3.2.3, released 2021/04/27" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER

(1 row)

 

 

I have seen one blog which says that a direct upgrade from 2.4.6 to 3.1.2 has many dependencies on raster functions and we should upgrade to 3.1.1 and then to 3.1.2. They have mentioned postgis 2.4.6 but Is it valid in my case also? I am upgrading postgis from 2.5.4 to 3.1.2.

 

Blog - https://www.enterprisedb.com/docs/postgis/3.1.2/03_upgrading_postgis/#example-2

 

On Thu, Jan 27, 2022 at 3:26 PM Sandro Santilli <strk at kbt.io <mailto:strk at kbt.io> > wrote:

On Wed, Jan 26, 2022 at 04:04:51PM -0500, Regina Obe wrote:

> In addition, having the lib not change, means you are not fully upgraded
> until you run the 
> 
> SELECT postgis_extensions_upgrade()  or ALTER EXTENSION ..

This is STILL the case. You are NOT fully upgraded until you run

  SELECT postgis_extensions_upgrade()

Let's make it very clear for users that upgrading files on the
operating system level is NOT enough for upgrading postgis databases.

> Which means for basic use, you can easily test with just the new lib, and
> back out of an upgrade by uninstalling the new version and reinstalling the
> old version.

You would really only be testing specific kind of changes (not any new
or changed signature, not any script-only speedup or refactoring or bugfix)
and could also incur in crashes due to scripts being incompatible with
underlying library. Let's remember we currently have NO automated test
coverage of these kind of upgrades.

--strk;
_______________________________________________
postgis-devel mailing list
postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-devel

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220128/e8c79eec/attachment.html>