<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body>
<p>Hi Brian,</p>
<p>Yes, I may need to resolve to posting this on the Virtualbox
forums, still, there might be someone around here having a similar
setup.</p>
<p>Yes, I have realised it is actually the VirtualBox "Bridged
adapter" network adapter that is failing, the one that uses the
host's device driver for the host's physical network card
according to the VirtualBox manual. The second "NAT" based network
adapter, that regulates the internet connection, apparently stays
up when this happens. So it might be something with the device
driver of the network card, although I am unsure if that is a
driver installed by VirtualBox itself, or that it uses the
Window's one (I actually have no idea if there can be more than
one device driver in an OS, likely not...).<br>
</p>
<p>Marco<br>
</p>
<div class="moz-cite-prefix">Op 21-8-2020 om 15:44 schreef Brent
Fraser:<br>
</div>
<blockquote type="cite"
cite="mid:8b45d79af3d441f09d1e3d436224e577@geoanalytic.com">
<meta http-equiv="content-type" content="text/html;
charset=windows-1252">
<div style="font-family: arial; font-size: 14px;">
<div>Hi Marco,</div>
<div><br>
</div>
<div> Based on the fact that after the failure, the guest and
host machines cannot communicate (but the guest can access the
internet), I suggest looking into VirtualBox and the network
"adapter" settings of the guest container. </div>
<div><br>
</div>
<div>The VirtualBox community may have some insights:</div>
<div><a href="https://www.virtualbox.org/wiki/Community"
target="_blank" moz-do-not-send="true">https://www.virtualbox.org/wiki/Community</a></div>
<div><br>
</div>
<div>Best Regards,</div>
<div>Brent Fraser</div>
<div><br>
</div>
<div><br>
</div>
<hr id="previousmessagehr">
<div><span><strong>From</strong>: Marco Boeringa
<a class="moz-txt-link-rfc2396E" href="mailto:marco@boeringa.demon.nl"><marco@boeringa.demon.nl></a><br>
<strong>Sent</strong>: 8/21/20 3:24 AM<br>
<strong>To</strong>: <a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<strong>Subject</strong>: [postgis-users] ODBC connection
problem, connection lost</span></div>
<div><br>
</div>
<div>Hi all,</div>
<div><br>
</div>
<div>I am running into an elusive issue that I have great
difficulty to</div>
<div>explain. Please note that, although I can find my way
through it, I am</div>
<div>not an Ubuntu nor network savvy person, so I may be
overlooking</div>
<div>something obvious.</div>
<div><br>
</div>
<div>What is my setup:</div>
<div><br>
</div>
<div>Ubuntu 20.04 with PostgreSQL 12 + PostGIS 3.0.2 running on
an Oracle</div>
<div>VirtualBox machine, so the Ubuntu OS being the guest
operating system.</div>
<div>This runs on a Windows 10 host.</div>
<div><br>
</div>
<div>Both guest and host have fixed, manually set, IP addresses
set in their</div>
<div>network connections. Ubuntu runs the UFW firewall with the
proper ports</div>
<div>set to allow traffic, Windows has the same set in the
firewall software</div>
<div>I use.</div>
<div><br>
</div>
<div>This all works fine, I can connect and do lots of stuff
without issues.</div>
<div><br>
</div>
<div>*The problem*:</div>
<div><br>
</div>
<div>I have some code that does a multi-threaded set of INSERTs
into my</div>
<div>database, via Python multi-threading using 'pyodbc'. This
needs to</div>
<div>insert some 175M spatial records into a table. I have
tested this with</div>
<div>smaller tables (+/-15M), and it works fine. However, as
soon as I try to</div>
<div>insert the 175M records, at some point, probably close to
10-15% of</div>
<div>records, I suddenly loose my network connection with a
generic HY000</div>
<div>error, that as far as I can tell is an ODBC level error,
not returned by</div>
<div>PostgreSQL. I have tried this multiple times, and it
consistently</div>
<div>happens. Note that the code commits in batches of about
10.000 records each.</div>
<div><br>
</div>
<div>At this point, I have verified the following:</div>
<div><br>
</div>
<div>- The guest Ubuntu OS runs on a 32GB RAM, quad core test
machine. Memory</div>
<div>usage is not at its limits when this happens, neither is
swap space</div>
<div>running out as far as I can tell (although I haven't been
able to</div>
<div>monitor this at the exact moment it fails, this is just an
observation</div>
<div>from logging into the guest after the fact). Note though
that I set 72</div>
<div>GB swap in Ubuntu, and I have never seen Ubuntu use more
than a few GB</div>
<div>of swap in its System Monitor, so the 72 GB is probably
totally overkill.</div>
<div><br>
</div>
<div>- No problem with disk space, there is 2x 1.4TB SSD
available to the</div>
<div>guest, and only about half of total available disk space is
used, so no</div>
<div>disk space problem.</div>
<div><br>
</div>
<div>- The UFW firewall is up and running when I check the
status with 'sudo</div>
<div>ufw status', nothing else to be seen there.</div>
<div><br>
</div>
<div>- Not unsurprisingly, when I try to 'ping' the host from
the guest or</div>
<div>the other way around, the network connection is indeed
down, and host /</div>
<div>guest unreachable at this point.</div>
<div><br>
</div>
<div>- However, I *can* open Firefox on the Ubuntu guest, and
connect to the</div>
<div>internet and browse the internet no problem</div>
<div><br>
</div>
<div>- I have two Ubuntu network connections, both of them
report status</div>
<div>"Connected" at this point, so no error despite not being
able to ping</div>
<div>the host.</div>
<div><br>
</div>
<div>- If I open 'pgAdmin 4' on the 'host', it *cannot* connect
to the</div>
<div>database. When I open pgAdmin 4 on the guest, it connects
without</div>
<div>issues, and the database is shown to be up and running. I
can open</div>
<div>tables, view records, and even open the table receiving the
INSERTs and</div>
<div>view its contents. So PostgreSQL seems to be fine.</div>
<div><br>
</div>
<div>- I have had a look at the Ubuntu logs, but to be honest,
it is like a</div>
<div>foreign language to me, and I don't see anything obvious.</div>
<div><br>
</div>
<div>- When I restart the Ubuntu guest, the issue is resolved
and I can</div>
<div>connect and successfully ping host and guest again.</div>
<div><br>
</div>
<div>- As to pyodbc, I have set the connections 'maxwrite' to
the recommended</div>
<div>'1024 * 1024 * 1024' value according to the pyodbc
documentation here:</div>
<div><a
href="https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-PostgreSQL"
target="_blank" moz-do-not-send="true">https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-PostgreSQL</a>,
but</div>
<div>I think this setting is wholly unrelated to this issue, but
the</div>
<div>documentation is in fact unclear as to what this setting
really means.</div>
<div><br>
</div>
<div>Does anyone have a clue of what may be going on,
suggestions how to</div>
<div>tackle such issues, or ever encountered similar issues and
been able to</div>
<div>deal with it somehow? I am kind of lost at this point.</div>
<div><br>
</div>
<div>Marco</div>
<div><br>
</div>
<div>_______________________________________________</div>
<div>postgis-users mailing list</div>
<div><a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a></div>
<div><a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
</body>
</html>