Handling psql lost connections
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?
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.
Cheers,
Steve
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.
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)?
Cheers,
Steve
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Not a response to OP but this also occurs with libpq connections.
The only thing I found at the moment is to lower the net.ipv4.tcp_retries2 value to 8 (instead of 15).
This will lower the TCP timeout from kernel to around 100 seconds instead of 15 minutes.
Sent from ProtonMail mobile
-------- Original Message --------
On 29 mars 2017 à 18:05, Adrian Klaver 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.
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)?
Cheers,
Steve
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Mar 29, 2017 at 9:05 AM, Adrian Klaver <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-SETTINGStcp_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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 2017-03-29 08:49:57 -0700, 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:
[...]
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.
One workaround could be to login to the server, start a screen session
and psql in the screen session. Then if your network connection drops
you can simply login again and resume the screen session. Of course this
only works if you have a shell login on the server which may not be the
case.
hp
--
_ | Peter J. Holzer | A coding theorist is someone who doesn't
|_|_) | | think Alice is crazy.
| | | hjp@hjp.at | -- John Gordon
__/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html