Search Postgresql Archives

Re: Handling psql lost connections

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux