[postgis-users] Stuck with upgrade from postgresql 9.6 to postgresql 14 due to postgis2.3
atanga MARCELIUS
marx407 at yahoo.ca
Thu Aug 31 04:14:21 PDT 2023
Thank you Regina,
As mentioned earlier I tried the soft upgrade and it failed. Also the hard upgrade is not an option for me since it involves taking a dump of the DB and restoring(because of several environmental variables :huge database sizes, limited downtime window, etc).
--------------------------------------------- Marcelius AtangaTel: +13025384841
CallSend SMSCall from mobileAdd to SkypeYou'll need Skype CreditFree via Skype
On Wednesday, August 30, 2023 at 09:36:33 AM EDT, Regina Obe <lr at pcorp.us> wrote:
<!--#yiv6746310694 filtered {}#yiv6746310694 filtered {}#yiv6746310694 filtered {}#yiv6746310694 filtered {}#yiv6746310694 p.yiv6746310694MsoNormal, #yiv6746310694 li.yiv6746310694MsoNormal, #yiv6746310694 div.yiv6746310694MsoNormal {margin:0in;font-size:11.0pt;font-family:"Calibri", sans-serif;}#yiv6746310694 a:link, #yiv6746310694 span.yiv6746310694MsoHyperlink {color:blue;text-decoration:underline;}#yiv6746310694 p.yiv6746310694MsoPlainText, #yiv6746310694 li.yiv6746310694MsoPlainText, #yiv6746310694 div.yiv6746310694MsoPlainText {margin:0in;font-size:11.0pt;font-family:"Calibri", sans-serif;}#yiv6746310694 span.yiv6746310694ydp4fbbcc0skypec2cmenutollcallcredit {}#yiv6746310694 span.yiv6746310694ydp4fbbcc0skypec2cmenutollfree {}#yiv6746310694 span.yiv6746310694EmailStyle21 {font-family:"Calibri", sans-serif;color:windowtext;}#yiv6746310694 span.yiv6746310694PlainTextChar {font-family:"Calibri", sans-serif;}#yiv6746310694 .yiv6746310694MsoChpDefault {font-size:10.0pt;}#yiv6746310694 filtered {}#yiv6746310694 div.yiv6746310694WordSection1 {}-->
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> 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> 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/20230831/a195f2ef/attachment.htm>
More information about the postgis-users
mailing list