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

[pham lan]

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> 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] *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
> _______________________________________________
> postgis-users mailing list
> 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/af0ac5ce/attachment.htm>