Обсуждение: Handling psql lost connections

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

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 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

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