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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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@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.klaver@aklaver.com