[postgis-users] Stuck with upgrade from postgresql 9.6 to postgresql 14 due to postgis2.3

Regina Obe lr at pcorp.us
Wed Aug 30 06:36:32 PDT 2023


Marcelius,

 

So you installed PostGIS from scripts right? 

I think your best bet is to upgrade your PostgreSQL 9.6 using scripts

 

1) Get a PostGIS 3.0  or higher for PostgreSQL 9.6 (it can be from a yum repo or compiled yourself).  Preferable a PostGIS 3.1 (I think 3.1 is the last version supported for PostgreSQL 9.6) if you can get it.

2) Run the postgis_upgrade.sql  script, which should be in the contrib/postgis-<version>  folder of your install 

As detailed here: https://postgis.net/docs/postgis_administration.html#soft_upgrade_sql_script

 

You can call pg_config --sharedir

To figure out the path of the shared folder if you don’t know it.

 

You may need to run the raster and other upgrade scripts too. To confirm do something like

 

SELECT * FROM raster_columns;

 

If that doesn’t error out, you have raster support in there too that needs to be upgraded

 

3) Once you’ve upgraded your PostgreSQL 9.6 using the scripts, run

 

SELECT postgis_extensions_upgrade();

 

That should if you are running PostGIS 3+ or higher to an extension based install.

 

Sandro, remember which version of this we started flipping everyone to extension based install?  I think 3.1 was after that time

 

Hope that helps,

Regina

 

From: postgis-users <postgis-users-bounces at lists.osgeo.org> On Behalf Of atanga MARCELIUS via postgis-users
Sent: Wednesday, August 30, 2023 5:51 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>; Sandro Santilli <strk at kbt.io>
Cc: atanga MARCELIUS <marx407 at yahoo.ca>
Subject: Re: [postgis-users] Stuck with upgrade from postgresql 9.6 to postgresql 14 due to postgis2.3

 

Hello Sandro, Thanks for your support.

 Please here are the out put to the queries you suggested.

I have also attached the output in text document in a clearer format.

My main concern here is how to  manage the upgrade path from postgis 2.3 in postgresql 9.6  to  a compactible version of postgis  in postgresql14  given that postgis 2.3 was hard install(using source code) and given my environmental challenges I do not want to do a dump and restore?

 


1.

postgres=# SELECT postgis_full_version();

                                                                            postgis_full_version                                                           

                 

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

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

 POSTGIS="2.3.7 r16523" PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJS

ON="0.11" RASTER

(1 row)

 

2.

postgres=# SELECT oid::regprocedure,probin

  FROM pg_catalog.pg_proc

  WHERE proname LIKE 'postgis_%version';

             oid              |        probin         

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

 postgis_version()            | $libdir/postgis-2.3

 postgis_liblwgeom_version()  | $libdir/postgis-2.3

 postgis_proj_version()       | $libdir/postgis-2.3

 postgis_lib_version()        | $libdir/postgis-2.3

 postgis_geos_version()       | $libdir/postgis-2.3

 postgis_svn_version()        | $libdir/postgis-2.3

 postgis_libxml_version()     | $libdir/postgis-2.3

 postgis_full_version()       | 

 postgis_libjson_version()    | $libdir/postgis-2.3

 postgis_raster_lib_version() | $libdir/rtpostgis-2.3

 postgis_gdal_version()       | $libdir/rtpostgis-2.3

(11 rows)

 

postgres=# 

 

 

 

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

Marcelius Atanga

Tel: +13025384841


                       

Call

Send SMS

Call from mobile

Add to Skype

You'll need Skype CreditFree via Skype

 

 

On Tuesday, August 29, 2023 at 02:55:37 AM EDT, atanga MARCELIUS <marx407 at yahoo.ca <mailto:marx407 at yahoo.ca> > wrote: 

 

 

Hello Sandro, Thanks for your support.

 Please here are the out put to the queries you suggested.

I have also attached the output in text document in a clearer format.

My main concern here is how to  manage the upgrade path from postgis 2.3 in postgresql 9.6  to  a compactible version of postgis  in postgresql14  given that postgis 2.3 was hard install(using source code) and given my environmental challenges I do not want to do a dump and restore?

 


1.

postgres=# SELECT postgis_full_version();

                                                                            postgis_full_version                                                           

                 

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

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

 POSTGIS="2.3.7 r16523" PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJS

ON="0.11" RASTER

(1 row)

 

2.

postgres=# SELECT oid::regprocedure,probin

  FROM pg_catalog.pg_proc

  WHERE proname LIKE 'postgis_%version';

             oid              |        probin         

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

 postgis_version()            | $libdir/postgis-2.3

 postgis_liblwgeom_version()  | $libdir/postgis-2.3

 postgis_proj_version()       | $libdir/postgis-2.3

 postgis_lib_version()        | $libdir/postgis-2.3

 postgis_geos_version()       | $libdir/postgis-2.3

 postgis_svn_version()        | $libdir/postgis-2.3

 postgis_libxml_version()     | $libdir/postgis-2.3

 postgis_full_version()       | 

 postgis_libjson_version()    | $libdir/postgis-2.3

 postgis_raster_lib_version() | $libdir/rtpostgis-2.3

 postgis_gdal_version()       | $libdir/rtpostgis-2.3

(11 rows)

 

postgres=# 

 

 

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

Marcelius Atanga

Tel: +13025384841


                       

Call

Send SMS

Call from mobile

Add to Skype

You'll need Skype CreditFree via Skype

 

 

On Wednesday, August 23, 2023 at 11:31:11 AM EDT, Sandro Santilli <strk at kbt.io <mailto:strk at kbt.io> > wrote: 

 

 

On Wed, Aug 23, 2023 at 10:08:23AM +0000, atanga MARCELIUS wrote:

> xxxxx=# ALTER EXTENSION postgis UPDATE TO '2.5.5';
> ERROR:  extension "postgis" does not exist

The above message means that database "xxxxx" does not contain
the "postgis extension". This does not necessarely mean it doesn't
contain postgis objects, if you're coming from a very old install
as you seem to be coming from.

What does this query return?



  SELECT postgis_full_version();



And this one ?

  SELECT oid::regprocedure,probin
  FROM pg_catalog.pg_proc
  WHERE proname LIKE 'postgis_%version';

--strk;

  Libre GIS consultant/developer
  https://strk.kbt.io/services.html

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230830/d6f35df0/attachment.htm>


More information about the postgis-users mailing list