On Wed, Mar 29, 2017 at 9:05 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@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
