[postgis-users] Keep alive script

[Shaozhong SHI]

Hi, Stephen,

We are using PostgreSQL version 10.10 and postgis version 2.4.8.

Are these Linux commands ---  "df -h" and watch the process with "top"?

Regards,

Shao



On Tue, 14 Jan 2020 at 01:23, Stephen Woodbridge <
stephenwoodbridge37 at gmail.com> wrote:

> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200114/dfb7e417/attachment.html>