<div dir="ltr"><div>Hi Regina,</div><div><br></div><div>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?<br><br># SELECT postgis_full_version(), version();<br> postgis_full_version<br> | version<br>----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>-----------------------+----------------------------------------------------------------------------------------------------------<br> 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="<br>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<br><br></div><div>Thanks,<br></div><div>Lan<br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Mar 16, 2023 at 2:15 PM Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div class="msg5789891816908826319"><div lang="EN-US"><div class="m_5789891816908826319WordSection1"><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">It depends on what platform you are on.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Can you output the following from your postgis enabled databases:<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">SELECT postgis_full_version(), version();<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">And send us the output of what each says. That will give a clue of at least what platform you are running.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Thanks,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Regina<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><div style="border-color:currentcolor currentcolor currentcolor blue;border-style:none none none solid;border-width:medium medium medium 1.5pt;padding:0in 0in 0in 4pt"><div><div style="border-color:rgb(225,225,225) currentcolor currentcolor;border-style:solid none none;border-width:1pt medium medium;padding:3pt 0in 0in"><p class="MsoNormal"><b><span style="font-size:11pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11pt;font-family:"Calibri",sans-serif"> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>pham lan<br><b>Sent:</b> Wednesday, March 15, 2023 5:00 PM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> [postgis-users] Help to upgrade postgresql10 with postgis 2.5<u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">Hello,<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">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?<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Thanks in advance.<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Best regards<u></u><u></u></p></div><div><p class="MsoNormal">Lan Pham<u></u><u></u></p></div></div></div></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</div></blockquote></div>