On 03/29/2017 11:48 AM, Steve Crawford wrote:
On Wed, Mar 29, 2017 at 9:05 AM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto: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
<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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
