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

[Regina Obe]

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] On Behalf Of pham lan
Sent: Wednesday, March 15, 2023 5:00 PM
To: PostGIS Users Discussion <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

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