Re: Handling psql lost connections

On 03/29/2017 11:48 AM, Steve Crawford wrote:
> On Wed, Mar 29, 2017 at 9:05 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 03/29/2017 08:49 AM, Steve Crawford wrote:
>
> When firewalls/VPNs stand between my psql client and a remote
> PostgreSQL
> server the connection will on occasion time out and drop. This
> results
> in the following scenario:
>
> -Leave for lunch mid project - leave psql open.
>
> -Return from lunch, complete and submit large query.
>
> -Notice query is taking too long. cancel it.
>
> -Cancel doesn't return - realize that connection has dropped.
>
> -Kill psql - history is not written out. Start query from scratch.
>
> Is there:
>
> 1) A way to set psql to send keepalives?
>
>
> >From server side:
> https://www.postgresql.org/docs/9.6/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS
> <https://www.postgresql.org/docs/9.6/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS>
>
> tcp_keepalives*
>
> I guess you could abuse \watch:
>
> https://www.postgresql.org/docs/9.6/static/app-psql.html
> <https://www.postgresql.org/docs/9.6/static/app-psql.html>
>
> \watch [ seconds ]
>
> Repeatedly execute the current query buffer (as \g does) until
> interrupted or the query fails. Wait the specified number of seconds
> (default 2) between executions. Each query result is displayed with
> a header that includes the \pset title string (if any), the time as
> of query start, and the delay interval.
>
> aklaver(at)test=> \watch 2
> Watch every 2s Wed Mar 29 08:59:55 2017
>
> ?column?
> ----------
> 1
> (1 row)
>
> Watch every 2s Wed Mar 29 08:59:57 2017
>
> ?column?
> ----------
> 1
> (1 row)
>
> With a larger value of seconds.
>
>
>
> If I could remember to do that I would remember that I had psql running
> in one or more terminals on one of my virtual screens and just close it.
> As it is, I try to remember to close psql and restart if it has been
> sitting for more than a few minutes.
>
>
>
>
> 2) A way to gracefully kill psql ensuring that the history is saved?
>
> Yes, I know I and my coworkers could spend brain cycles trying to
> unerringly remember to close and restart connections, write all
> queries
> in an external editor and then submit them, etc. but I'm looking for
> more user friendly options.
>
>
> Use the internal editor(\e)?
>
>
> That is actually the typical *cause* of the problems. I usually do use
> \e to fire up the external $EDITOR for anything more than a trivial
> query and if I need to stop or I step away mid-edit then finish and
> write/quit, the query is not visible on the screen where I could scroll
> back to it. If the connection has dropped, I have to kill psql and the
> history is lost as well.

Save it to a file from inside the editor before you run it and then if
you have to kill psql, pull it back in from the file:

test=# \e
select.sql

?column?

----------

1

Where the content of select.sql is

SELECT 1;

OR

Look for the most recent /tmp/psql.edit.NNNN.sql file.
That is the path on my machine, yours might be different. It will be
shown at the bottom of buffer when you do \e.

>
> I think for now that I'll just add some tcp settings to sysctl.conf to
> deal with the firewalls.
>
> Cheers,
> Steve

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com