[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: Segmentation fault restoring data to Azure VM
From: Jeffrey Durrence <jeffrey.durrence () mcleanengineering ! com>
Date: 2024-04-23 19:28:26
Message-ID: DS7PR10MB7298C4FE75A0007934A8000D97112 () DS7PR10MB7298 ! namprd10 ! prod ! outlook ! com
[Download RAW message or body]
Regina,
As you suggested, I dropped the postgis_raster extension, exported, and the imported.
No errors!
Hopefully a record of this will be helpful to others who find themselves in this \
situation.
Thanks again for your input!
Jeffrey Durrence
McLean Engineering Company
www.mcleanengineering.com<http://www.mcleanengineering.com/>
Office: 229-985-1148
Mobile: 229-798-0480
________________________________
From: Regina Obe <lr@pcorp.us>
Sent: Tuesday, April 23, 2024 2:05 PM
To: Jeffrey Durrence <jeffrey.durrence@mcleanengineering.com>; \
postgis-users@lists.osgeo.org <postgis-users@lists.osgeo.org>
Subject: RE: Segmentation fault restoring data to Azure VM
Yah dropping the raster extension if you are not using it would be my first \
suggestion.
If you do
DROP EXTENSION postgis_raster;
And it successfully drops, then you definitely don’t have data tied to it so safe to \
drop before you do your pg_dump and restore.
I’ve seen people complain about random crashes with for example a bad netcdf driver \
that gets loaded when gdal lib is loaded. So if you are running into one of those \
types of issues, not installing the raster extension will save you from that pain.
I do see you have Azure postgis compiled with GEOS 3.10.2, but you are running with \
3.11.1. In theory that shouldn’t be an issue since 3.10.2 is upward compatible with \
3.11.
Everything else at a glance looks to be in shape.
Hope that helps,
Regina
From: Jeffrey Durrence <jeffrey.durrence@mcleanengineering.com>
Sent: Tuesday, April 23, 2024 1:21 PM
To: Regina Obe <lr@pcorp.us>; postgis-users@lists.osgeo.org
Subject: Re: Segmentation fault restoring data to Azure VM
Regina,
Thank you so much for your quick response (and for your contributions to the rest of \
us in your writings)!
On the physical server I have:
POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="150" GEOS="3.10.2-CAPI-1.16.0" \
PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org \
USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" GDAL="GDAL \
3.4.3, released 2022/04/22" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" \
WAGYU="0.5.0 (Internal)" (core procs from "3.4.1 ca035b9" need upgrade) RASTER \
(raster procs from "3.4.1 ca035b9" need upgrade)
On the Azure server I have
POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="150" GEOS="3.11.1-CAPI-1.17.1" (compiled \
against GEOS 3.10.2) PROJ="8.2.1 NETWORK_ENABLED=OFF \
URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj \
DATABASE_PATH=/usr/share/proj/proj.db" GDAL="GDAL 3.4.3, released 2022/04/22" \
LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER
There are some differences, though I'm not sure of the best strategy to reconcile \
this. As for the raster extension, I don't use it for my workflows. I could drop \
the postgis_raster extension and see if that allows my export/import to succeed \
without error.
-Jeffrey
Jeffrey Durrence
McLean Engineering Company
www.mcleanengineering.com<http://www.mcleanengineering.com/>
Office: 229-985-1148
Mobile: 229-798-0480
________________________________
From: Regina Obe <lr@pcorp.us<mailto:lr@pcorp.us>>
Sent: Tuesday, April 23, 2024 1:09 PM
To: Jeffrey Durrence \
<jeffrey.durrence@mcleanengineering.com<mailto:jeffrey.durrence@mcleanengineering.com>>; \
postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> \
<postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>>
Subject: RE: Segmentation fault restoring data to Azure VM
What does
SELECT postgis_full_version();
Run on your current setup vs. your Azure VM setup.
Might thinking is it might be a dependent library crashing.
Usually the issues are with postgis_raster extension since that drags in GDAL which \
drags in several more libraries.
Could also be an issue with PROJ. As I recall at least on windows, I used to get \
crashes if PROJ_LIB path wasn’t set, but I think that issue has gone away with recent \
releases.
Not sure if it was ever an issue on Linux or Unix.
Hope that helps,
Regina
From: Jeffrey Durrence \
<jeffrey.durrence@mcleanengineering.com<mailto:jeffrey.durrence@mcleanengineering.com>>
Sent: Tuesday, April 23, 2024 11:42 AM
To: postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
Subject: Segmentation fault restoring data to Azure VM
Greetings,
I am migrating PostgreSQL/PostGIS databases from a physical server with these specs:
Host OS: Ubuntu 22.04.4 LTS x64 (32GB RAM, 8GB Swap, 1TB disk space, XEON E5-1660 \
CPU)
Postgres: 15.6.1-pgdg22.04+1
PostGIS: 3.4.2
To an Azure VM with
Host OS: Ubuntu 22.04.4 LTS x64 (32GB RAM, 8GB Swap, 512GB disk space, XEON 8171M) \
(again, this is in Azure VM)
Postgres: 15.6.1-pgdg22.04+1
PostGIS: 3.4.2
In the past, I have migrated the same data from on the same physical hardware for \
changes in host O/S, Postgres version, and/or PostGIS version. I've also migrated \
from one physical host to another. When doing so, I try to make sure that my \
software packages are up to date and that the packages in the destination environment \
match. I did this for this planned environment as well. Because I know that some of \
my dbs have several years of history in them, I export these dbs individually using \
pg_dump (using the custom format when possible but in some cases I use the plain text \
option) and pg_restore on the destination host.
During the process of migrating data in this manner, I observed that restoration of \
many of my dbs failed. I did lots of testing with this and have made these \
additional observations:
1. I am able to restore my export files to another physical host with the same \
software environment without error. (the errors occur when importing to virtual \
environment).
1. If I split my export/import into separate steps for the schema and data, it is \
the schema import that produces the error, not the data import.
1. When the error occurs, the db process terminates with a segmentation vault (see \
log output below), and the db process restarts.
1. With logging of all statements enabled, I can see that the segmentation vault \
occurs at the absolute end of the import, when it seems that all commands are \
completed. So long as I wait on the server process to recover, I have no errors when \
I import the data following the error.
1. The db imports that produce these errors seem to be those with more history \
(e.g., it's more likely that these were created with an older version of postGIS and \
subsequently upgraded to the versions they have now).
1. The errors do seem related to the installation of the PostGIS extension. If I \
purposely drop the extension from the source database before I export, the resulting \
file can be imported in the target environment without error. This is obviously \
undesirable as it results in my losing all of the geometry info from tables, views, \
etc. in the db. I only did this to experiment.
My typical restore command looks like this:
pg_restore --verbose --format=custom --dbname=my_db /tmp/my_db_schema_only.backup
(plus some options to capture the output)
Here's a look at what the command line output looks like when this happens:
LOG: server process (PID 52476) was terminated by signal 11: Segmentation fault
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2024-04-23 02:16:49 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 5/51381960
WARNING: could not open directory "base/696372": No such file or directory
CONTEXT: WAL redo at 5/51381A48 for Database/DROP: dir 1663/696372
WARNING: some useless files may be left behind in old database directory \
"base/696372"
CONTEXT: WAL redo at 5/51381A48 for Database/DROP: dir 1663/696372
LOG: invalid record length at 5/52679558: expected at least 24, got 0
LOG: redo done at 5/526794E0 system usage: CPU: user: 0.07 s, system: 0.01 s, \
elapsed: 0.10 s
LOG: checkpoint starting: end-of-recovery immediate wait
LOG: checkpoint complete: wrote 2699 buffers (16.5%); 0 WAL file(s) added, 1 \
removed, 0 recycled;
B; lsn=5/52679558, redo lsn=5/52679558
LOG: database system is ready to accept connections
In the log file, the section written as the error occurs is something like this...
[60419] [6627bcbb.ec03] [2024-04-23 13:50:54.813 UTC] [0]: LOG: statement: GRANT ALL \
ON TABLE my_schema.my_table TO my_user_name;
[60419] [6627bcbb.ec03] [2024-04-23 13:50:54.815 UTC] [0]: LOG: statement: -- \
Completed on 2024-04-23 13:50:54 UTC
[60419] [6627bcbb.ec03] [2024-04-23 13:50:54.815 UTC] [0]: LOG: statement: --
-- PostgreSQL database dump complete
--
[59940] [6627b39f.ea24] [2024-04-23 13:50:54.943 UTC] [0]: LOG: server process (PID \
60419) was terminated by signal 11: Segmentation fault
[59940] [6627b39f.ea24] [2024-04-23 13:50:54.943 UTC] [0]: LOG: terminating any \
other active server processes
[59940] [6627b39f.ea24] [2024-04-23 13:50:54.945 UTC] [0]: LOG: all server processes \
terminated; reinitializing
[60423] [6627bcbf.ec07] [2024-04-23 13:50:55.007 UTC] [0]: LOG: database system was \
interrupted; last known up at 2024-04-23 13:50:48 UTC
[60423] [6627bcbf.ec07] [2024-04-23 13:51:05.511 UTC] [0]: LOG: syncing data \
directory (fsync), elapsed time: 10.00 s, current path: ./base/897556/1249
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.554 UTC] [0]: LOG: database system was \
not properly shut down; automatic recovery in progress
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.606 UTC] [0]: LOG: redo starts at \
E/3A8785A8
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.607 UTC] [0]: WARNING: could not open \
directory "base/562219": No such file or directory
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.607 UTC] [0]: CONTEXT: WAL redo at \
E/3A878690 for Database/DROP: dir 1663/562219
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.607 UTC] [0]: WARNING: some useless \
files may be left behind in old database directory "base/562219"
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.607 UTC] [0]: CONTEXT: WAL redo at \
E/3A878690 for Database/DROP: dir 1663/562219
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.704 UTC] [0]: LOG: invalid record \
length at E/3BACDAF8: expected at least 24, got 0
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.704 UTC] [0]: LOG: redo done at \
E/3BACDA80 system usage: CPU: user: 0.07 s, system: 0.01 s, elapsed: 0.09 s
[60424] [6627bcbf.ec08] [2024-04-23 13:51:12.804 UTC] [0]: LOG: checkpoint starting: \
end-of-recovery immediate wait
[60424] [6627bcbf.ec08] [2024-04-23 13:51:13.143 UTC] [0]: LOG: checkpoint complete: \
wrote 2592 buffers (15.8%); 0 WAL file(s) added, 1 removed, 0 recycled; writ
9 s, average=0.001 s; distance=18773 kB, estimate=18773 kB; lsn=E/3BACDAF8, redo \
lsn=E/3BACDAF8
[59940] [6627b39f.ea24] [2024-04-23 13:51:13.156 UTC] [0]: LOG: database system is \
ready to accept connections
I searched the mail archive and performed some additional web searches based on my \
observations. Unfortunately, I haven't found a similar-enough incident where I might \
get suggestions for further troubleshooting. In over 10 years of using \
PostgreSQL+PostGIS, I've never had to dig this deeply to determine the source of \
error. I'm looking for advice for further troubleshooting or for shared experiences \
in similar situations
Thank you for your time!
Jeffrey Durrence
McLean Engineering Company
www.mcleanengineering.com<http://www.mcleanengineering.com/>
Office: 229-985-1148
Mobile: 229-798-0480
[Attachment #3 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} \
</style> </head>
<body dir="ltr">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, \
Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, \
0, 0);"> Regina,</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, \
Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, \
0, 0);"> <br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, \
Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, \
0, 0);"> As you suggested, I dropped the postgis_raster extension, exported, and the \
imported.</div> <div class="elementToProof" style="font-family: Aptos, \
Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: \
12pt; color: rgb(0, 0, 0);"> <br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, \
Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, \
0, 0);"> No errors!</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, \
Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, \
0, 0);"> <br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, \
Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, \
0, 0);"> Hopefully a record of this will be helpful to others who find themselves in \
this situation.</div> <div class="elementToProof" style="font-family: Aptos, \
Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: \
12pt; color: rgb(0, 0, 0);"> <br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, \
Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, \
0, 0);"> Thanks again for your input!</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, \
Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, \
0, 0);"> <br>
</div>
<div id="Signature">
<p><span style="font-family: "Segoe UI", sans-serif; color: black;">Jeffrey \
Durrence</span></p> <p><span style="font-family: "Segoe UI", sans-serif; \
color: black;">McLean Engineering Company</span></p> <p><span style="font-family: \
"Segoe UI", sans-serif; color: black;"><a \
href="http://www.mcleanengineering.com/" style="margin-top: 0px; margin-bottom: \
0px;">www.mcleanengineering.com</a></span></p> <p><span style="font-family: \
"Segoe UI", sans-serif; color: black;">Office: 229-985-1148</span></p> \
<p><span style="font-family: "Segoe UI", sans-serif; color: black; \
background-color: white;">Mobile: 229-798-0480</span></p> <p> </p>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" \
style="font-size:11pt" color="#000000"><b>From:</b> Regina Obe \
<lr@pcorp.us><br> <b>Sent:</b> Tuesday, April 23, 2024 2:05 PM<br>
<b>To:</b> Jeffrey Durrence <jeffrey.durrence@mcleanengineering.com>; \
postgis-users@lists.osgeo.org <postgis-users@lists.osgeo.org><br> \
<b>Subject:</b> RE: Segmentation fault restoring data to Azure VM</font> \
<div> </div> </div>
<style>
<!--
@font-face
{font-family:"Cambria Math"}
@font-face
{font-family:Calibri}
@font-face
{font-family:Aptos}
@font-face
{font-family:"Segoe UI"}
p.x_MsoNormal, li.x_MsoNormal, div.x_MsoNormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif}
a:link, span.x_MsoHyperlink
{color:blue;
text-decoration:underline}
p.x_xmsonormal, li.x_xmsonormal, div.x_xmsonormal
{margin:0in;
font-size:12.0pt;
font-family:"Aptos",sans-serif}
span.x_EmailStyle24
{font-family:"Aptos",sans-serif;
color:windowtext}
.x_MsoChpDefault
{font-size:10.0pt}
@page WordSection1
{margin:1.0in 1.0in 1.0in 1.0in}
div.x_WordSection1
{}
ol
{margin-bottom:0in}
ul
{margin-bottom:0in}
-->
</style>
<div lang="EN-US" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="x_WordSection1">
<p class="x_MsoNormal"><span style="font-size:11.0pt">Yah dropping the raster \
extension if you are not using it would be my first suggestion.</span></p> <p \
class="x_MsoNormal"><span style="font-size:11.0pt"> </span></p> <p \
class="x_MsoNormal"><span style="font-size:11.0pt">If you do </span></p> <p \
class="x_MsoNormal"><span style="font-size:11.0pt"> </span></p> <p \
class="x_MsoNormal"><span style="font-size:11.0pt">DROP EXTENSION \
postgis_raster;</span></p> <p class="x_MsoNormal"><span \
style="font-size:11.0pt"> </span></p> <p class="x_MsoNormal"><span \
style="font-size:11.0pt">And it successfully drops, then you definitely don’t have \
data tied to it so safe to drop before you do your pg_dump and restore.</span></p> <p \
class="x_MsoNormal"><span style="font-size:11.0pt"> </span></p> <p \
class="x_MsoNormal"><span style="font-size:11.0pt">I’ve seen people complain about \
random crashes with for example a bad netcdf driver that gets loaded when gdal lib is \
loaded. So if you are running into one of those types of issues, not installing \
the raster extension will save you from that pain.</span></p>
<p class="x_MsoNormal"><span style="font-size:11.0pt"> </span></p>
<p class="x_MsoNormal"><span style="font-size:11.0pt">I do see you have Azure postgis \
compiled with GEOS 3.10.2, but you are running with 3.11.1. In theory that \
shouldn’t be an issue since 3.10.2 is upward compatible with 3.11.</span></p> <p \
class="x_MsoNormal"><span style="font-size:11.0pt"> </span></p> <p \
class="x_MsoNormal"><span style="font-size:11.0pt">Everything else at a glance looks \
to be in shape.</span></p> <p class="x_MsoNormal"><span \
style="font-size:11.0pt"> </span></p> <p class="x_MsoNormal"><span \
style="font-size:11.0pt">Hope that helps,</span></p> <p class="x_MsoNormal"><span \
style="font-size:11.0pt">Regina</span></p> <p class="x_MsoNormal"><span \
style="font-size:11.0pt"> </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="x_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"> Jeffrey \
Durrence <jeffrey.durrence@mcleanengineering.com> <br>
<b>Sent:</b> Tuesday, April 23, 2024 1:21 PM<br>
<b>To:</b> Regina Obe <lr@pcorp.us>; postgis-users@lists.osgeo.org<br>
<b>Subject:</b> Re: Segmentation fault restoring data to Azure VM</span></p>
</div>
</div>
<p class="x_MsoNormal"> </p>
<div>
<p class="x_MsoNormal"><span style="color:black">Regina,</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="color:black">Thank you so much for your quick \
response (and for your contributions to the rest of us in your writings)!<br> <br>
On the physical server I have:<br>
POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="150" \
GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1 NETWORK_ENABLED=OFF \
URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj \
DATABASE_PATH=/usr/share/proj/proj.db" GDAL="GDAL 3.4.3, released \
2022/04/22" LIBXML="2.9.13" LIBJSON="0.15" \
LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from \
"3.4.1 ca035b9" need upgrade) RASTER (raster procs from "3.4.1 \
ca035b9" need upgrade)</span></p> </div>
<div>
<p class="x_MsoNormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin-bottom:12.0pt"><span style="color:black">On the \
Azure server I have <br> POSTGIS="3.4.2 c19ce56" [EXTENSION] \
PGSQL="150" GEOS="3.11.1-CAPI-1.17.1" (compiled against GEOS \
3.10.2) PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org \
USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" \
GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.13" \
LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 \
(Internal)" RASTER</span></p> </div>
<div>
<p class="x_MsoNormal"><span style="color:black">There are some differences, though \
I'm not sure of the best strategy to reconcile this. As for the raster \
extension, I don't use it for my workflows. I could drop the postgis_raster \
extension and see if that allows my export/import to succeed without \
error.</span></p> </div>
<div>
<p class="x_MsoNormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="color:black">-Jeffrey</span></p>
</div>
<div>
<p class="x_MsoNormal"><span style="color:black"> </span></p>
</div>
<div id="x_Signature">
<p><span style="font-family:"Segoe UI",sans-serif; color:black">Jeffrey \
Durrence</span></p> <p><span style="font-family:"Segoe UI",sans-serif; \
color:black">McLean Engineering Company</span></p> <p><span \
style="font-family:"Segoe UI",sans-serif; color:black"><a \
href="http://www.mcleanengineering.com/">www.mcleanengineering.com</a></span></p> \
<p><span style="font-family:"Segoe UI",sans-serif; color:black">Office: \
229-985-1148</span></p> <p><span style="font-family:"Segoe UI",sans-serif; \
color:black; background:white">Mobile: 229-798-0480</span></p> <p> </p>
</div>
<div class="x_MsoNormal" align="center" style="text-align:center">
<hr size="2" width="98%" align="center">
</div>
<div id="x_divRplyFwdMsg">
<p class="x_MsoNormal"><b><span style="font-size:11.0pt; \
font-family:"Calibri",sans-serif; color:black">From:</span></b><span \
style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:black"> \
Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>><br> <b>Sent:</b> \
Tuesday, April 23, 2024 1:09 PM<br> <b>To:</b> Jeffrey Durrence <<a \
href="mailto:jeffrey.durrence@mcleanengineering.com">jeffrey.durrence@mcleanengineering.com</a>>;
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a> \
<<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>
<b>Subject:</b> RE: Segmentation fault restoring data to Azure VM</span> </p>
<div>
<p class="x_MsoNormal"> </p>
</div>
</div>
<div>
<div>
<p class="x_xmsonormal"><span style="font-size:11.0pt">What does </span></p>
<p class="x_xmsonormal"><span style="font-size:11.0pt"> </span></p>
<p class="x_xmsonormal"><span style="font-size:11.0pt">SELECT \
postgis_full_version(); </span></p>
<p class="x_xmsonormal"><span style="font-size:11.0pt"> </span></p>
<p class="x_xmsonormal"><span style="font-size:11.0pt">Run on your current setup vs. \
your Azure VM setup.</span></p> <p class="x_xmsonormal"><span \
style="font-size:11.0pt"> </span></p> <p class="x_xmsonormal"><span \
style="font-size:11.0pt">Might thinking is it might be a dependent library \
crashing.</span></p> <p class="x_xmsonormal"><span \
style="font-size:11.0pt"> </span></p> <p class="x_xmsonormal"><span \
style="font-size:11.0pt">Usually the issues are with postgis_raster extension since \
that drags in GDAL which drags in several more libraries.</span></p> <p \
class="x_xmsonormal"><span style="font-size:11.0pt"> </span></p> <p \
class="x_xmsonormal"><span style="font-size:11.0pt">Could also be an issue with \
PROJ. As I recall at least on windows, I used to get crashes if PROJ_LIB path \
wasn’t set, but I think that issue has gone away with recent releases.</span></p> <p \
class="x_xmsonormal"><span style="font-size:11.0pt"> </span></p> <p \
class="x_xmsonormal"><span style="font-size:11.0pt">Not sure if it was ever an issue \
on Linux or Unix.</span></p> <p class="x_xmsonormal"><span \
style="font-size:11.0pt"> </span></p> <p class="x_xmsonormal"><span \
style="font-size:11.0pt">Hope that helps,</span></p> <p class="x_xmsonormal"><span \
style="font-size:11.0pt">Regina</span></p> <p class="x_xmsonormal"><span \
style="font-size:11.0pt"> </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="x_xmsonormal"><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"> Jeffrey \
Durrence <<a href="mailto:jeffrey.durrence@mcleanengineering.com">jeffrey.durrence@mcleanengineering.com</a>>
<br>
<b>Sent:</b> Tuesday, April 23, 2024 11:42 AM<br>
<b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<b>Subject:</b> Segmentation fault restoring data to Azure VM</span></p>
</div>
</div>
<p class="x_xmsonormal"> </p>
<div>
<p class="x_xmsonormal"><span style="color:black">Greetings,</span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black">I am migrating PostgreSQL/PostGIS \
databases from a physical server with these specs:<br> Host OS: Ubuntu 22.04.4 LTS \
x64 (32GB RAM, 8GB Swap, 1TB disk space, XEON E5-1660 CPU)<br>
Postgres: 15.6.1-pgdg22.04+1<br>
PostGIS: 3.4.2</span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black">To an Azure VM \
with </span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">Host OS: Ubuntu 22.04.4 LTS x64 \
(32GB RAM, 8GB Swap, 512GB disk space, XEON 8171M) (again, this is in \
Azure VM)<br>
Postgres: 15.6.1-pgdg22.04+1<br>
PostGIS: 3.4.2</span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black">In the past, I have migrated the \
same data from on the same physical hardware for changes in host O/S, Postgres \
version, and/or PostGIS version. I've also migrated from one physical host to \
another. When doing so, I try to make sure that my software packages are up to \
date and that the packages in the destination environment match. I did this for \
this planned environment as well. Because I know that some of my dbs have \
several years of history in them, I export these dbs individually using pg_dump \
(using the custom format when possible but in some cases I use the plain text option) \
and pg_restore on the destination host.</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black">During the process of migrating \
data in this manner, I observed that restoration of many of my dbs failed. I \
did lots of testing with this and have made these additional observations:</span></p> \
</div> <div>
<ol start="1" type="1" style="margin-top:0in">
<li class="x_xmsonormal" style="color:black; list-style-type:"1. "">I am \
able to restore my export files to another physical host with the same software \
environment without error. (the errors occur when importing to virtual \
environment).</li></ol> </div>
<div>
<ol start="2" type="1" style="margin-top:0in">
<li class="x_xmsonormal" style="color:black; list-style-type:"2. "">If I \
split my export/import into separate steps for the schema and data, it is the schema \
import that produces the error, not the data import.</li></ol> </div>
<div>
<ol start="3" type="1" style="margin-top:0in">
<li class="x_xmsonormal" style="color:black; list-style-type:"3. "">When \
the error occurs, the db process terminates with a segmentation vault (see log output \
below), and the db process restarts.</li></ol> </div>
<div>
<ol start="4" type="1" style="margin-top:0in">
<li class="x_xmsonormal" style="color:black; list-style-type:"4. "">With \
logging of all statements enabled, I can see that the segmentation vault occurs at \
the absolute end of the import, when it seems that all commands are completed. \
So long as I wait on the server process to recover, I have no errors when I import \
the data following the error.</li></ol> </div>
<div>
<ol start="5" type="1" style="margin-top:0in">
<li class="x_xmsonormal" style="color:black; list-style-type:"5. "">The db \
imports that produce these errors seem to be those with more history (e.g., it's more \
likely that these were created with an older version of postGIS and subsequently \
upgraded to the versions they have now). </li></ol>
</div>
<div>
<ol start="6" type="1" style="margin-top:0in">
<li class="x_xmsonormal" style="color:black; list-style-type:"6. "">The \
errors do seem related to the installation of the PostGIS extension. If I \
purposely drop the extension from the source database before I export, the resulting \
file can be imported in the target environment without error. This is \
obviously undesirable as it results in my losing all of the geometry info from \
tables, views, etc. in the db. I only did this to experiment.</li></ol> </div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black">My typical restore command looks \
like this:<br> pg_restore --verbose --format=custom --dbname=my_db \
/tmp/my_db_schema_only.backup </span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">(plus some options to capture the \
output) </span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal" style="margin-bottom:12.0pt"><span style="color:black">Here's \
a look at what the command line output looks like when this happens:</span></p> \
</div> <div>
<p class="x_xmsonormal"><span style="color:black">LOG: server process (PID \
52476) was terminated by signal 11: Segmentation fault</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: terminating any other \
active server processes</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: all server processes \
terminated; reinitializing</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: database system was \
interrupted; last known up at 2024-04-23 02:16:49 UTC</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: database system was not \
properly shut down; automatic recovery in progress</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: redo starts at \
5/51381960</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">WARNING: could not open \
directory "base/696372": No such file or directory</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">CONTEXT: WAL redo at \
5/51381A48 for Database/DROP: dir 1663/696372</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">WARNING: some useless files \
may be left behind in old database directory "base/696372"</span></p> \
</div> <div>
<p class="x_xmsonormal"><span style="color:black">CONTEXT: WAL redo at \
5/51381A48 for Database/DROP: dir 1663/696372</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: invalid record length at \
5/52679558: expected at least 24, got 0</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: redo done at 5/526794E0 \
system usage: CPU: user: 0.07 s, system: 0.01 s, elapsed: 0.10 s</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: checkpoint starting: \
end-of-recovery immediate wait</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: checkpoint complete: \
wrote 2699 buffers (16.5%); 0 WAL file(s) added, 1 removed, 0 recycled;</span></p> \
</div> <div>
<p class="x_xmsonormal"><span style="color:black">B; lsn=5/52679558, redo \
lsn=5/52679558</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">LOG: database system is ready \
to accept connections</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black">In the log file, the section \
written as the error occurs is something like this...</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black"><br>
[60419] [6627bcbb.ec03] [2024-04-23 13:50:54.813 UTC] [0]: LOG: statement: \
GRANT ALL ON TABLE my_schema.my_table TO my_user_name;</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60419] [6627bcbb.ec03] [2024-04-23 \
13:50:54.815 UTC] [0]: LOG: statement: -- Completed on 2024-04-23 13:50:54 \
UTC</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60419] [6627bcbb.ec03] [2024-04-23 \
13:50:54.815 UTC] [0]: LOG: statement: --</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black"> -- \
PostgreSQL database dump complete</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black"> \
--</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[59940] [6627b39f.ea24] [2024-04-23 \
13:50:54.943 UTC] [0]: LOG: server process (PID 60419) was terminated by signal \
11: Segmentation fault</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[59940] [6627b39f.ea24] [2024-04-23 \
13:50:54.943 UTC] [0]: LOG: terminating any other active server \
processes</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[59940] [6627b39f.ea24] [2024-04-23 \
13:50:54.945 UTC] [0]: LOG: all server processes terminated; \
reinitializing</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:50:55.007 UTC] [0]: LOG: database system was interrupted; last known up at \
2024-04-23 13:50:48 UTC</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:05.511 UTC] [0]: LOG: syncing data directory (fsync), elapsed time: 10.00 \
s, current path: ./base/897556/1249</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:12.554 UTC] [0]: LOG: database system was not properly shut down; \
automatic recovery in progress</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:12.606 UTC] [0]: LOG: redo starts at E/3A8785A8</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:12.607 UTC] [0]: WARNING: could not open directory \
"base/562219": No such file or directory</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:12.607 UTC] [0]: CONTEXT: WAL redo at E/3A878690 for Database/DROP: dir \
1663/562219</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:12.607 UTC] [0]: WARNING: some useless files may be left behind in old \
database directory "base/562219"</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:12.607 UTC] [0]: CONTEXT: WAL redo at E/3A878690 for Database/DROP: dir \
1663/562219</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:12.704 UTC] [0]: LOG: invalid record length at E/3BACDAF8: expected at \
least 24, got 0</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60423] [6627bcbf.ec07] [2024-04-23 \
13:51:12.704 UTC] [0]: LOG: redo done at E/3BACDA80 system usage: CPU: user: \
0.07 s, system: 0.01 s, elapsed: 0.09 s</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60424] [6627bcbf.ec08] [2024-04-23 \
13:51:12.804 UTC] [0]: LOG: checkpoint starting: end-of-recovery immediate \
wait</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[60424] [6627bcbf.ec08] [2024-04-23 \
13:51:13.143 UTC] [0]: LOG: checkpoint complete: wrote 2592 buffers (15.8%); 0 \
WAL file(s) added, 1 removed, 0 recycled; writ</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">9 s, average=0.001 s; \
distance=18773 kB, estimate=18773 kB; lsn=E/3BACDAF8, redo lsn=E/3BACDAF8 \
\
\
</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black">[59940] [6627b39f.ea24] [2024-04-23 \
13:51:13.156 UTC] [0]: LOG: database system is ready to accept connections \
\
\
</span></p> </div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black">I searched the mail archive and \
performed some additional web searches based on my observations. Unfortunately, \
I haven't found a similar-enough incident where I might get suggestions for further \
troubleshooting. In over 10 years of using PostgreSQL+PostGIS, I've never had \
to dig this deeply to determine the source of error. I'm looking for advice for \
further troubleshooting or for shared experiences in similar situations</span></p> \
</div> <div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black">Thank you for your time!</span></p>
</div>
<div>
<p class="x_xmsonormal"><span style="color:black"> </span></p>
</div>
<div id="x_x_Signature">
<p><span style="font-family:"Segoe UI",sans-serif; color:black">Jeffrey \
Durrence</span></p> <p><span style="font-family:"Segoe UI",sans-serif; \
color:black">McLean Engineering Company</span></p> <p><span \
style="font-family:"Segoe UI",sans-serif; color:black"><a \
href="http://www.mcleanengineering.com/">www.mcleanengineering.com</a></span></p> \
<p><span style="font-family:"Segoe UI",sans-serif; color:black">Office: \
229-985-1148</span></p> <p><span style="font-family:"Segoe UI",sans-serif; \
color:black; background:white">Mobile: 229-798-0480</span></p> <p> </p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic