<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<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);">
Greetings,</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);">
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</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);">
To an Azure VM with </div>
<div class="elementToProof" style="margin: 0px; font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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</div>
<div class="elementToProof" style="margin: 0px; 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="margin: 0px; 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="margin: 0px; font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
<div class="elementToProof" style="margin: 0px; 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="margin: 0px; font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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:</div>
<ol start="1" data-editing-info="{"orderedStyleType":1,"unorderedStyleType":1}" style="margin-top: 0px; margin-bottom: 0px; list-style-type: decimal;">
<li style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); list-style-type: "1. ";">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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).</div>
</li><li style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); list-style-type: "2. ";">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
</li><li style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); list-style-type: "3. ";">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
When the error occurs, the db process terminates with a segmentation vault (see log output below), and the db process restarts.</div>
</li><li style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); list-style-type: "4. ";">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
</li><li style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); list-style-type: "5. ";">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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). </div>
</li><li style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); list-style-type: "6. ";">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
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.</div>
</li></ol>
<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);">
My typical restore command looks like this:<br>
pg_restore --verbose --format=custom --dbname=my_db /tmp/my_db_schema_only.backup </div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
(plus some options to capture the output) </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);">
Here's a look at what the command line output looks like when this happens:<br>
<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);">
LOG: server process (PID 52476) was terminated by signal 11: Segmentation fault</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: terminating any other active server processes</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: all server processes terminated; reinitializing</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: database system was interrupted; last known up at 2024-04-23 02:16:49 UTC</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: database system was not properly shut down; automatic recovery in progress</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: redo starts at 5/51381960</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
WARNING: could not open directory "base/696372": No such file or directory</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
CONTEXT: WAL redo at 5/51381A48 for Database/DROP: dir 1663/696372</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
WARNING: some useless files may be left behind in old database directory "base/696372"</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
CONTEXT: WAL redo at 5/51381A48 for Database/DROP: dir 1663/696372</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: invalid record length at 5/52679558: expected at least 24, got 0</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: redo done at 5/526794E0 system usage: CPU: user: 0.07 s, system: 0.01 s, elapsed: 0.10 s</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: checkpoint starting: end-of-recovery immediate wait</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: checkpoint complete: wrote 2699 buffers (16.5%); 0 WAL file(s) added, 1 removed, 0 recycled;</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
B; lsn=5/52679558, redo lsn=5/52679558</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
LOG: database system is ready to accept connections</div>
<div class="elementToProof" style="margin: 0px; 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);">
In the log file, the section written as the error occurs is something like this...</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>
[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;</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[60419] [6627bcbb.ec03] [2024-04-23 13:50:54.815 UTC] [0]: LOG: statement: -- Completed on 2024-04-23 13:50:54 UTC</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[60419] [6627bcbb.ec03] [2024-04-23 13:50:54.815 UTC] [0]: LOG: statement: --</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
-- PostgreSQL database dump complete</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
--</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[59940] [6627b39f.ea24] [2024-04-23 13:50:54.943 UTC] [0]: LOG: server process (PID 60419) was terminated by signal 11: Segmentation fault</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[59940] [6627b39f.ea24] [2024-04-23 13:50:54.943 UTC] [0]: LOG: terminating any other active server processes</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[59940] [6627b39f.ea24] [2024-04-23 13:50:54.945 UTC] [0]: LOG: all server processes terminated; reinitializing</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[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</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[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</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.554 UTC] [0]: LOG: database system was not properly shut down; automatic recovery in progress</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.606 UTC] [0]: LOG: redo starts at E/3A8785A8</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.607 UTC] [0]: WARNING: could not open directory "base/562219": No such file or directory</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.607 UTC] [0]: CONTEXT: WAL redo at E/3A878690 for Database/DROP: dir 1663/562219</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[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"</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[60423] [6627bcbf.ec07] [2024-04-23 13:51:12.607 UTC] [0]: CONTEXT: WAL redo at E/3A878690 for Database/DROP: dir 1663/562219</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[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</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 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</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[60424] [6627bcbf.ec08] [2024-04-23 13:51:12.804 UTC] [0]: LOG: checkpoint starting: end-of-recovery immediate wait</div>
<div style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[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</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
9 s, average=0.001 s; distance=18773 kB, estimate=18773 kB; lsn=E/3BACDAF8, redo lsn=E/3BACDAF8 </div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
[59940] [6627b39f.ea24] [2024-04-23 13:51:13.156 UTC] [0]: LOG: database system is ready to accept connections </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);">
<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);">
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</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);">
Thank you for your time!</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/" id="OWA78b4d233-99c2-a814-dcbc-073cd03e6c4e" class="OWAAutoLink" 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>
</body>
</html>