[postgis-users] Keep alive script

[Stephen Woodbridge]

On 1/13/2020 5:28 PM, Shaozhong SHI wrote:
> Psql may carry out large queries on big data sets.  These operations 
> may take a long time to complete.  It occurs that psql loses 
> connection to the database.
>
> When a do loop is created to limit queries to range of rows at a time, 
> engagement between psql and postures can be maintained.  However, this 
> could take a long time.
>
> Disconnection between psql and posters is understood due to timeout.

I am not aware of any timeouts between psql and the database. I have in 
fact run queries in the past that have run for 5-6 days. It is more 
likely that the query died because it ran out of memory, or disk space. 
You can monitor disk space with "df -h" and watch the process with "top" 
commands. If your query calls a plpgsql function that does a lot of 
looping be aware that these runs in a transaction and that long running 
transactions on logs of rows can eat up a lot of disk space. I have 
gotten around the problem by writing a perl/python/php/whatever script 
that runs the looping and make multiple smaller queries to the 
database.  In the newer versions of postgresql 10,11, 12 not sure which, 
I think you can now do begin and commit in plpgsql script to close out 
transactions but I'm not sure whether the that is just for 
sub-transactions or if the plpgsql function may still run as a transaction.

What version postgresql and postgis are you running?

I'm not sure I can help more than this other than to say I'm not aware 
of any 'keep alive' thing for this or that that is the real problem you 
are facing.

-Steve

>
> Is there a way to create a 'keep life script'?
>
> Regards,
> Shao
>
> On Monday, 13 January 2020, Stephen Woodbridge 
> <stephenwoodbridge37 at gmail.com <mailto:stephenwoodbridge37 at gmail.com>> 
> wrote:
>
>     On 1/13/2020 6:10 AM, Shaozhong SHI wrote:
>
>         With long running processes, time-out could be an issue.
>
>         Has anyone got experience in creating a "Keep alive script"
>         solution to share?
>
>
>     Need more information.
>     What is timing out? ssh, php, psql, etc?
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     https://lists.osgeo.org/mailman/listinfo/postgis-users
>     <https://lists.osgeo.org/mailman/listinfo/postgis-users>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users