Keepalive the Idle postgres connections

[gopal-abbineni]

In our scenario there are router/switches that will reset the connection if the connection is idle for more than 5 min. This throws the ECONNRESET error and crashing up the server. Is there any way to keep the connection idle from pg cliient.

[boromisp]

You could use the undocumented keepAlive and keepAliveInitialDelayMillis connection parameters, to set up appropriate TCP keepalives.

[brianc]

oof I need to document that!

[gopal-abbineni]

But with out passing those attributes I tried enabling the keepalive attributes( tcs_keepalive_idle, tcs_keepalive_interval, tcs_keepalive_count) on the DB side(postgresql.conf),

After that I am able to see the keepAlive packets are being passed in the tcp connection.

Is this expected behaviour?

[hammady]

@gopal-abbineni what you see is the keepalive packets sent by the server to the client. More about that here: https://www.postgresql.org/docs/9.6/runtime-config-connection.html. What @boromisp is referring to is the client generated keepalives which is equivalent to the native libpq: https://www.postgresql.org/docs/9.6/libpq-connect.html#LIBPQ-KEEPALIVES. I am not sure though if they are supported in the node implementation.

[jodem]

Hi all, what's the purpose of keepAliveInitialDelayMillis ?

[rynop]

I'm struggling to find clear documentation on the implications of setting keepAlive: true.

node-postgres seems to be using libpq. Libpq docs, nor node-postgres docs are clear on what happens when the connection to server is considered dead. Will it auto re-connect? Or does it require nodejs process restart.

[rynop]

Hi all, what's the purpose of keepAliveInitialDelayMillis ?

@jodem pretty sure it is just used to set keepalives_idle. See code here, see docs on keepalives_idle here.

[lukas1994]

I'm using Google Cloud functions and my connections are killed every 2min. Currently, this causes an exception. How can I make it reconnect automatically? KeepAlive=true doesn't seem to help.

[PierBover]

Is there any reason keepAlive is not documented?

Is it a finished feature? 😅

[tasdflkjweio]

After some lengthy periods of inactivity in our stage environment (overnight, every night), the first attempt to write to pg consistently fails. The second and all other subsequent attempts succeed. It seems like the connections are being closed or are dying but are not removed from the connection pool. I'm left wondering if I should be using keepAlive, and what the implications are.

[gajus]

@brianc Could you comment on whether keepAlive should be used? and if yes, why it is not enabled by default.

In particular, I am debugging why each query starts with tcp.connect. I wouldn't expect this to be necessary.

[gajus]

I believe this documents implications of keepAlive setting

https://stackoverflow.com/a/46884708/368691

keepalives is a client-side setting.

If you set it to 0, the TCP socket on the client machine will have the SO_KEEPALIVE socket option set to 0 (the default setting (on Linux) is 1, meaning that keepalive is enabled).

Then it won't send keepalive messages on idle connections to a database server to check if the other side is still alive.

The effect is that the client will not detect if the database server terminates unexpectedly, that is, without closing the TCP connection.

Usually it is not necessary to enable keepalive on the client side: The client will notice when the server has dies anyway when it next tries to talk to it.

The other use for keepalive messages would be to keep a firewall or proxy from closing an idle connection. But since the PostgreSQL server enables keepalive on the server side anyway, that should be taken care of.