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 <[email protected]> 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:[email protected]] *On > Behalf Of *pham lan > *Sent:* Friday, March 17, 2023 3:56 AM > *To:* PostGIS Users Discussion <[email protected]> > *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 <[email protected]> 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:[email protected]] *On > Behalf Of *pham lan > *Sent:* Wednesday, March 15, 2023 5:00 PM > *To:* PostGIS Users Discussion <[email protected]> > *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 > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
