Re: Handling psql lost connections

On Wed, Mar 29, 2017 at 9:05 AM, Adrian Klaver <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
>
> tcp_keepalives*
>
> I guess you could abuse \watch:
>
> 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.

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

Cheers,
Steve