Problem description
When connected to BigAnimal Postgres clusters you may receive the error "Connection reset by peer" and get disconnected
This is happening because our load balancers may have a networking rule which closes connections that are not sending a keep alive signal for longer than a certain amount of time.
The following table shows the current load balancer network timeouts in our BigAnimal solutions:
BigAnimal Cluster Type | Timeout |
Azure publicly access | 30 minutes |
Azure privately access | 30 minutes (4 minutes for private link) |
AWS (publicly and privately access) | 350 seconds |
The rule is needed to clean-up pending connections not more in use.
So if it is running a very long query, lasting for more than 30 minutes, it possible to face this issue.
A specific tcp networking signal called "keep alive" is used in this cases to confirm the connection still should be considered active.
If the load balancer receive that signal before the timeout is reach will keep the connection established.
To overcome it, some solutions are available
Solutions
Operating system settings
It is possible to change the default keep alive operating system setting in the machine where your client is running.
The objective is sending a keep alive signal with an interval shorter than the timeout, let's say, in our case, 25 minutes.
Before modifying any configuration file or operating system registry, please, take a backup and be sure you can restore it !!
-
Linux
- To change the keep alive linux settings execute the following command
sysctl -w net.ipv4.tcp_keepalive_time=1500 net.ipv4.tcp_keepalive_intvl=75 net.ipv4.tcp_keepalive_probes=10
- To persist the new settings create or modify the file /etc/sysctl.conf with the following values
net.ipv4.tcp_keepalive_time=1500
net.ipv4.tcp_keepalive_intvl=75
net.ipv4.tcp_keepalive_probes=10 - Restart the machine and check if the settings have been properly saved
sysctl -A | grep -E "keepidle|keepintvl|keepcnt"
- To change the keep alive linux settings execute the following command
-
MAC
- To change the keep alive MAC settings execute the following command
sudo sysctl net.inet.tcp.keepidle=1500000
sudo sysctl net.inet.tcp.keepintvl=75000
sudo sysctl net.inet.tcp.keepcnt=10 - To persist the new settings create or modify the file /etc/sysctl.conf with the following values
net.inet.tcp.keepidle=1500000
net.inet.tcp.keepintvl=75000
net.inet.tcp.keepcnt=10 - Restart the machine and check if the settings have been properly saved
sysctl net.inet.tcp | grep -E "keepidle|keepintvl|keepcnt"
- To change the keep alive MAC settings execute the following command
-
Windows
- Set the following values in windows registry under, if they do not exist you can create it
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\:KeepAliveTime: 1500000
Note that the count parameter for Windows by default is set to 10 and cannot be changed.
KeepAliveInterval: 75000
KeepMaxDataRetransmission: 10
- Set the following values in windows registry under, if they do not exist you can create it
Application settings
Some applications, when running, may override the operating system tcp keep alive settings.
When possible we advise to follow this latter approach.
-
JDBC
- JDBC only has a parameter regarding the keep alive signal
It is possible to add it in the jdbc connection string.
The parameter name is tcpKeepAlive and by default its value is false.
If that parameter is set to true will let jdbc using the Operating System settings for the keep alive signal (see below section on how to set-up them).
tcpKeepAlive=true
For further information on jdbc parameters see the official documentation
https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
- JDBC only has a parameter regarding the keep alive signal
-
psql
- psql can set the keep alive parameters directly in the connection string.
for example in the following way
psql "postgres://user@hostname:port/dbname?keepalives=1&keepalives_idle=1500&keepalives_interval=75&keepalives_count=10"
- psql can set the keep alive parameters directly in the connection string.
-
PgAdmin
- It is possible to use a service in pgadmin to set specific values for the keep alives parameters.
To find further information about Postgres service and pg_service.conf
read this postgreSQL documentation page
https://www.postgresql.org/docs/current/libpq-pgservice.html
For example create in Linux or MAC the following pg_service.conf file in the home directorytouch
and edit as follows~/.pg_service.conf
[mypgdatabase]
keepalives=1
keepalives_idle=1500
keepalives_interval=75
keepalives_count=10
Then add the service name in the PgAdmin create connection tab as in the picture below
Now it is possible to run long queries from PgAdmin without risking to have the connection reset
- It is possible to use a service in pgadmin to set specific values for the keep alives parameters.