[postgis-users] Help to upgrade postgresql10 with postgis 2.5

[Regina Obe]

You’d probably want to upgrade PostgreSQL too since PostgreSQL 10 is EOL’d.

 

Below are some instructions I had written for Centos.  Should be more or less the same for Redhat.

 

https://www.bostongis.com/blog/index.php?/archives/278-Using-pg_upgrade-to-upgrade-PostgreSQL-9.6-PostGIS-2.4-to-PostgreSQL-15-3.3-on-Yum.html

 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of pham lan
Sent: Friday, March 17, 2023 3:56 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Help to upgrade postgresql10 with postgis 2.5

 

Hi Regina,

 

Thank you for your reply. Below is the output of the command. Do you know a good way to migrate all the definitions of materialized views because we have a lot of them and they are quite complex?

# SELECT postgis_full_version(), version();
                                                                                                      postgis_full_version
                       |                                                 version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------+----------------------------------------------------------------------------------------------------------
 POSTGIS="2.5.5" [EXTENSION] PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" SFCGAL="1.4.1" PROJ="Rel. 7.2.1, January 1st, 2021" GDAL="GDAL 3.2.3, released 2021/04/27" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="
1.3.0" TOPOLOGY RASTER | PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

Thanks,

Lan

 

 

On Thu, Mar 16, 2023 at 2:15 PM Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:

It depends on what platform you are on.

 

Can you output the following from your postgis enabled databases:

 

SELECT postgis_full_version(), version();

 

And send us the output of what each says.  That will give a clue of at least what platform you are running.

 

You’ll need to run this on each database you have with postgis installed on, as it is possible to have 2 versions of postgis installed in separate databases.

 

As to whether you’d need to drop and recreate any materialized views, it would depend on what functions they are using.  If they are using deprecated or removed functions, then eventually you will need to drop and recreate, but you could do that at a later time.  The PostGIS 3+ upgrade will notify you of those issues, but generally can just rename the functions in use, so you can drop and recreate at a more convenient time.

 

For materialized views that take a long time to build, I generally build them under a new name, and do a swap after the new one has been built.  That would reduce the downtime from hours to 1-2 minutes.

 

Thanks,

Regina

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> ] On Behalf Of pham lan
Sent: Wednesday, March 15, 2023 5:00 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: [postgis-users] Help to upgrade postgresql10 with postgis 2.5

 

Hello,

 

I have very less experience with postgres and postgis. However i receive a task to upgrade an old database which has postgresql10 with postgis 2.5. The database has some extensions: postgis, postgis_sfcgal, postgis_topology, raster and have a lot of materialized views which depends on functions on postgis and sfcgal libraries. Could someone please instruct me to upgrade my postgres DB to a newer postgres and postgis version without having to drop all those materialized views? Idealy to postgis 3.x?

 

Thanks in advance.

 

Best regards

Lan Pham

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

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