<div dir="auto"><div>Yes, you are right. This seems to be a mess. I will clean up the target server to have similar config like the original one and run the pg_basebackup again. Unfortunately, I am not allowed to touch the original server, but creating a backup of that, then pg_ upgrade from that backup.<div dir="auto">But if proj7.2 and 8.2.1 are different, i might get the same error again when i upgrade to newer postgres with postgis 3.x, right? What can I do in this case?</div><br><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, 27 Mar 2023, 23:53 Regina Obe, <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="EN-US" link="blue" vlink="purple"><div class="m_-5263940556686900234WordSection1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Did you run <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">SELECT postgis_extensions_upgrade();<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">SELECT postgis_extensions_upgrade();<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Twice on your 3.0?<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">You should actually do the same on your 2.5 as well, as they are both at unstable states. The PostGIS 3.0 more so is unstable, cause it looks to have a mix of using the 2.5 and 3.0 libraries.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I don’t think the 2.5 even supported Proj 8.2 or at least not a combination we’ve tested.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Probably the issue is with difference in Proj 7.2 vs Proj 8.2.1<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Do you have these running on the same server or different servers?<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I think the proj.db files changed between 7.2 and 8.2 and so if the 8.2 is using a proj.db from 7.2 that could be a mess.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p><div style="border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt"><div><div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0in 0in 0in"><p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>pham lan<br><b>Sent:</b> Monday, March 27, 2023 4:59 PM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-users] ERROR: transform: Point outside of projection domain (2050)<u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><div><div><p class="MsoNormal">Sorry, my bad. I just realized that the information about the postgis version I gave you was wrong. So the original one is 2.5 and the backup one is 3.0. Can it be that the st_transform function caused the problem in this case for different postgis functions?<u></u><u></u></p></div><p class="MsoNormal" style="margin-bottom:12.0pt">So the output of select postgis_full_version() are following:<u></u><u></u></p><div><p class="MsoNormal">#This is the output from the original database<u></u><u></u></p></div><p class="MsoNormal" style="margin-bottom:12.0pt"><span class="m_-5263940556686900234gmail-im">geodm=# SELECT postgis_full_version();</span><br><br><span class="m_-5263940556686900234gmail-im"> postgis_full_version</span><br><br><span class="m_-5263940556686900234gmail-im">--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</span><br>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>---------------------------<br> POSTGIS="2.5.5" [EXTENSION] PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" SFCGAL="1.3.1" PROJ="Rel. 7.2.1, January 1st, 2021" GDAL="GDAL 3.2.3, released 2021/04/27" LIBXML="2.9.1" LIBJSON="0.11" LIBPROT<br>OBUF="1.0.2" (core procs from "2.5.2 r17328" need upgrade) TOPOLOGY (topology procs from "2.5.2 r17328" need upgrade) RASTER (raster procs from "2.5.2 r17328" need upgrade) (sfcgal procs from "2<br>.5.2 r17328" need upgrade) <br><br><u></u><u></u></p></div><p class="MsoNormal">#This is output of the target database:<br>geodm=# select postgis_full_version();<br><br> postgis_full_version<br><br>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>--------------------------------------------------------<br> POSTGIS="3.0.7 r487b488" [EXTENSION] PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" SFCGAL="1.4.1" PROJ="8.2.1" GDAL="GDAL 3.2.3, released 2021/04/27" LIBXML="2.9.7" LIBJSON="0.13.1" LIBPROTOBUF="1.3.0"<br>(core procs from "2.5.2 r17328" need upgrade) TOPOLOGY (topology procs from "2.5.2 r17328" need upgrade) RASTER (raster lib from "2.5.5 r0" need upgrade) (raster procs from "2.5.2 r17328" need u<br>pgrade) (sfcgal procs from "2.5.2 r17328" need upgrade)<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">On Mon, Mar 27, 2023 at 10:41 PM pham lan <<a href="mailto:phamlankt@gmail.com" target="_blank" rel="noreferrer">phamlankt@gmail.com</a>> wrote:<u></u><u></u></p></div><blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in"><div><div><div><p class="MsoNormal">you are right, if i dropped the aforementioned index, analyze works without issue. As long as I tried to create the index again, it does not let me creating the index but throw the same error.<u></u><u></u></p></div><p class="MsoNormal">But I wonder why it is working in the original database. What happened during the backup/restore that changed this?<u></u><u></u></p></div><p class="MsoNormal">Btw, I see some small difference in the output of the original database. May be it sounds something to you?<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">#This is the output from the original database<u></u><u></u></p></div><p class="MsoNormal" style="margin-bottom:12.0pt">geodm=# SELECT postgis_full_version();<br><br> postgis_full_version<br><br>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>---------------------------<br> POSTGIS="2.5.5" [EXTENSION] PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" SFCGAL="1.3.1" PROJ="Rel. 7.2.1, January 1st, 2021" GDAL="GDAL 3.2.3, released 2021/04/27" LIBXML="2.9.1" LIBJSON="0.11" LIBPROT<br>OBUF="1.0.2" (core procs from "2.5.2 r17328" need upgrade) TOPOLOGY (topology procs from "2.5.2 r17328" need upgrade) RASTER (raster procs from "2.5.2 r17328" need upgrade) (sfcgal procs from "2<br>.5.2 r17328" need upgrade)<br>(1 row)<br><br><u></u><u></u></p></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">On Mon, Mar 27, 2023 at 10:21 PM Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank" rel="noreferrer">lr@pcorp.us</a>> wrote:<u></u><u></u></p></div><blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in"><p class="MsoNormal">Could be your ST_Transform index triggering the issue and you have at least one geometry out of bounds in there.<br><br>I’d try dropping your geobordervtc_geom_25832_idx and then rerunning analyze on the table.<br>If that does fix it then try to gigure out which geometry is at fault.<br><br>The 25632 is only good for utm zone 32N<br><br><a href="https://epsg.io/25832" target="_blank" rel="noreferrer">https://epsg.io/25832</a><br><br>So I suspect you have a geometry way off from there causing the issue. <br><br>Or it could be missing grid datum shift files at fault too.<br><br>You can update your grid files by running at your command line:<br><br>projsync --all<br><br>As detailed here - <a href="https://proj.org/apps/projsync.html#index-0" target="_blank" rel="noreferrer">https://proj.org/apps/projsync.html#index-0</a><br><br>--------------------------------------<br><br>From: postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users-bounces@lists.osgeo.org</a>] On Behalf Of pham lan<br>Sent: Monday, March 27, 2023 4:02 PM<br>To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a>><br>Subject: Re: [postgis-users] ERROR: transform: Point outside of projection domain (2050)<br><br>Hi Regina,<br>Here are more information:<br><br>geodm=# SELECT postgis_full_version();<br> postgis_full_version<br><br>--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>----------------<br> POSTGIS="2.5.5" [EXTENSION] PGSQL="100" GEOS="3.9.2-CAPI-1.14.3" 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"<br>TOPOLOGY RASTER<br>(1 row)<br><br><br><br>On Mon, Mar 27, 2023 at 9:43 PM Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank" rel="noreferrer">lr@pcorp.us</a>> wrote:<br>What’s the structure of the table in question?<br><br>\d+ name_of_table<br><br>Should give needed details.<br><br>Also output of <br><br>SELECT postgis_full_version();<br><br>From: postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users-bounces@lists.osgeo.org</a>] On Behalf Of pham lan<br>Sent: Monday, March 27, 2023 3:27 PM<br>To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a>><br>Subject: [postgis-users] ERROR: transform: Point outside of projection domain (2050)<br><br>Hi everyone,<br><br>I need your help to figure this out. I have a DB in postgresql 10 with postgis 2.5. I know postgresql 10 is not supported anymore and we have plan to upgrade it soon but it is a different story.<br>The thing is, I want to make a copy of this DB, so I tried a pg_basebackup to another postgres10+postgis 2.5 VM. After that, a vacuum went well but the analyzer throws an error on 1 specific table:<br>ERROR: transform: Point outside of projection domain (2050)<br>i tried also pg_dump/pg_restore but get the same error. Does anyone know what is the problem?<br><br>Thanks for your help!<br>Regards,<br>Lan<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank" rel="noreferrer">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank" rel="noreferrer">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><u></u><u></u></p></blockquote></div></blockquote></div></div></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div></div></div>