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