<div dir="ltr"><div>Hi again, I found the solution myself, hope this can be of any use.</div><div><br></div><div>It happens that there is a PostgreSQL config parameter I was missing. With this entries at the postgresql.conf the connection last enough to handle all the data imports, without compromising in the long run the integrity of the connections to the server with a connection leak:</div><div><br></div><div>tcp_keepalives_idle=60<br>tcp_keepalives_count=20000<br>tcp_keepalives_interval=30<br>idle_in_transaction_session_timeout=86400000</div><div><br></div><div>The TCP entries tries to maintain the connection alive, while the "idle_in..." param, which is the one I was missing, waits for 24h before dropping a connection that seems to be idle. However, I think the TCP ones can keep the connection alive even longer.</div><div><br></div><div>Hope this helps if anyone comes across such a case. I'm glad it's not a Docker problem.</div><div><br></div><div>Best regards,<br></div><div><div><div dir="ltr" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr">
<p>---<br></p><p><span>Juan Pedro Pérez Alcántara</span></p>
<p><span><a href="mailto:jp.perez.alcantara@gmail.com" target="_blank">jp.perez.alcantara@gmail.com</a></span></p></div></div></div></div></div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 19 Oct 2021 at 09:55, Juan Pedro Pérez Alcántara <<a href="mailto:jp.perez.alcantara@gmail.com" target="_blank">jp.perez.alcantara@gmail.com</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 dir="ltr"><div>Hello there,</div><div><br></div><div>this is my first post to this mailing list, so first I'd like to thank the community for supporting this marvelous piece of software. I perform most of my research and data processing in PostGIS since I work mostly with vector data in visualize-in-web use cases for most of my projects, but I rely on GRASS GIS to perform such powerful operations like topology cleaning and building.</div><div><br></div><div>And regarding this, I'm trying to import a large table from PostGIS. It is 2GB in size. I use this:</div><div><br></div><div><div style="color:rgb(0,0,0);background-color:rgb(255,255,255);font-family:"Droid Sans Mono","monospace",monospace,"Droid Sans Fallback";font-weight:normal;font-size:11px;line-height:15px;white-space:pre-wrap"><div><span style="color:rgb(0,0,0)">grass </span><span style="color:rgb(0,16,128)">$GRASS_DB</span><span style="color:rgb(0,0,0)">/PERMANENT --exec \</span></div><div><span style="color:rgb(0,0,0)"> v.in.ogr --verbose \</span></div><div><span style="color:rgb(0,0,0)"> input=</span><span style="color:rgb(163,21,21)">"PG:host=</span><span style="color:rgb(0,16,128)">$HOST</span><span style="color:rgb(163,21,21)"> dbname=cell_raw_data user=</span><span style="color:rgb(0,16,128)">$USER</span><span style="color:rgb(163,21,21)"> port=</span><span style="color:rgb(0,16,128)">$PORT</span><span style="color:rgb(163,21,21)"> password=</span><span style="color:rgb(0,16,128)">$PASS</span><span style="color:rgb(163,21,21)">"</span><span style="color:rgb(0,0,0)"> \</span></div><div><span style="color:rgb(0,0,0)"> layer=cat2020.buildingpart output=buildingpart</span></div><div><span style="color:rgb(0,0,0)"><br></span></div><div><span style="color:rgb(0,0,0)">everything goes well until it reaches the "Finding centroid for OGR layer" step:</span></div><div><span style="color:rgb(0,0,0)"><br></span></div><div><span style="color:rgb(0,0,0)">Finding centroids for OGR layer <cat2020.buildingpart>...<br>ERROR 1: server closed the connection unexpectedly<br> This probably means the server terminated abnormally<br> before or while processing the request.<br><br>ERROR 1: no connection to the server<br><br>ERROR 1: no connection to the server<br><br>ERROR 1: no connection to the server<br><br>ERROR 1: no connection to the server<br><br> 100%</span></div><div><span style="color:rgb(0,0,0)"><br></span></div><div><span style="color:rgb(0,0,0)">The connection to PostGIS seems to be closed. I've gone through the PostgreSQL </span></div><div><span style="color:rgb(0,0,0)">configuration and tried to set up generous values for the tcp_keepalives_*</span></div><div><span style="color:rgb(0,0,0)">parameters, but to no avail. It seems to me that the PostgreSQL server drops</span></div><div><span style="color:rgb(0,0,0)">the connection due to being idle too much time. Aside from that, both PostgreSQL</span></div><div><span style="color:rgb(0,0,0)">and GRASS run in Docker containers.</span></div><div><span style="color:rgb(0,0,0)"><br></span></div><div><span style="color:rgb(0,0,0)">GRASS version is 7.8.5.<br></span></div><div><span style="color:rgb(0,0,0)"><br></span></div><div><span style="color:rgb(0,0,0)">Does anybody have issues importing large PostGIS tables? Any advice / ideas I can</span></div><div><span style="color:rgb(0,0,0)">research on? I've already tried to solve it by reconfiguring PostgreSQL, as said,</span></div><div><span style="color:rgb(0,0,0)">and also have done some research into Docker handling TCP, but to no avail. Any</span></div><div><span style="color:rgb(0,0,0)">idea or hint will be most welcome.<br></span></div><div><span style="color:rgb(0,0,0)"><br></span></div><div><span style="color:rgb(0,0,0)">Best regards,<br></span></div></div></div><div><div><div dir="ltr"><div dir="ltr"><div><div dir="ltr">
<p>---<br></p><p><span>Juan Pedro Pérez Alcántara</span></p>
<p><span><a href="mailto:jp.perez.alcantara@gmail.com" target="_blank">jp.perez.alcantara@gmail.com</a></span></p></div></div></div></div></div></div></div>
</blockquote></div>