Re: Setting libpq TCP keepalive parameters from environment

On 8 May 2018 at 16:15, Oleksandr Shulgin <oleksandr(dot)shulgin(at)zalando(dot)de> wrote:
> Hi Hackers,
>
> I didn't find the original discussion which led to introduction of the
> client-side set of keepalive parameters back in [1].
>
> The issue I'm facing is that it doesn't seem to be possible to set these
> parameters from the environment variables. The block of option
> definitions[2] added for these parameters doesn't specify any corresponding
> env var names.
>
> I wonder if this is an oversight or was there a conscious decision not to
> allow it?
>
> To give a specific example, I have a (legacy) Python script which talks to
> the database in two ways: by directly using psycopg2.connect(host=..., ) and
> also by running some shell commands with psql's \copy + gzip, mainly for
> convenience.
>
> Now when I needed to tune the keepalives_idle value, I had to include it
> everywhere a database connection is made:
> - For psycopg2 it's straightforward: you just add an extra keyword parameter
> to connect().
> - For psql it's trickier, since the only way to achieve this seems to pack
> it together with -d as: -d 'dbname=mydb keepalives_idle=NNN'.
>
> In case of a binary that would amount to recompiling, I guess. I have no
> permission to tweak sysctl directly on the host, unfortunately.
>
> It would be much more convenient to just set the environment variable when
> running the script and let it affect the whole process and its children.
>
> Would a patch be welcome?

I can't really comment on that part, but:

PGOPTIONS="-c tcp_keepalives_count=5 -c tcp_keepalives_interval=60"
psql 'host=localhost'

should enable server-side keepalives. Unsure how much that helps you
if you need client-side keepalives too.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services