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:

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" 

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

• 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
    KeepAliveInterval: 75000
    KeepMaxDataRetransmission: 10

    Note that the count parameter for Windows by default is set to 10 and cannot be changed.

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
    
    

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

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

    touch ~/.pg_service.conf

    and edit as follows
    

    [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