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

[pham lan]

Yes, actually I followed your instructions.
After drop extension postgis_sfcgal whichs required me to drop cascade 13
materialized views, the upgrade to postgres15 went well.
Afterward, I wanted to update the postgis extension:

ALTER EXTENSION postgis UPDATE;

 it requests me to drop cascade around 200 materialized views. For the test
DB, I did that, the update to postgis 3.3 went well but afterward, the
vacuum step failed.
In the end, I talked to the teams and they can not afford to have those
materialized views deleted and then being created again by them.
That's why I wanna ask if there is a simple way of migrating the
materialized views? I am not familiar with this.

Thanks,
Lan

On Fri, Mar 17, 2023 at 9:26 AM Regina Obe <lr at pcorp.us> wrote:

> 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> 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
>
> _______________________________________________
> 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/63706b11/attachment.htm>