Why are the `keepalives` params in `psycopg2.connect(...)` required to run long running postgres que-CodePudding

We just transitioned to using Docker for development and are using the ubuntu:18.04 image. We noticed that queries using psycopg2 failed after a few minutes. This answer solved the problem using the following keepalives params:

self.db = pg.connect(
    dbname=config.db_name,
    user=config.db_user,
    password=config.db_password,
    host=config.db_host,
    port=config.db_port,
    keepalives=1,
    keepalives_idle=30,
    keepalives_interval=10,
    keepalives_count=5
)

This works for us as well, but why does this work? The psycopg2 docs do not give insight into what the params do, however, this third party documentation does, and this postgres documentation does.

The question is, what is different in the docker environment vs the host environment which makes these non-default settings required? They work in a standard Ubuntu 18.04 environment too, but not in docker. I am hoping we could reconfigure our docker image so that these non-standard parameters aren't necessary in the first place.

Postgres version: PostgreSQL 13.4 (Ubuntu 13.4-1.pgdg20.04 1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit

psycopg2 version: 2.8.5

Host OS: Windows 10

Docker Image OS: Ubuntu 18:04

CodePudding user response：

You are probably using Dockers Overlay Network feature (or Ingress network for loadbalanced services), which is based on Linux IP Virtual Server (IPVS), a.k.a.Linux Virtual Server. This uses a default 900 second (15 minutes) timeout for idle TCP connections.

See: https://github.com/moby/moby/issues/31208

Default Linux TCP Keep-Alive settings only start sending packets much later (if enabled at all) and thus you are left with the options of:

change the TCP Keep-Alive settings on the server or client
change the Docker networking to use the host network directly
change your software to avoid idle TCP connections, e.g. configure connection pools for databases to remove idle connections or check health more often
change the Kernel IPVS defaults or TCP defaults