Re: Problem with database connections timing out for long-running queries

You might check the stunnel settings. A quick search of "stunnel 12-hours"
indicates that this is the stunnel default for idle connections.

Cheers,
Steve

On Fri, Aug 21, 2015 at 11:16 AM, Rich Schaaf <rschaaf(at)commoninf(dot)com> wrote:

> I’m running into a problem where the connection between application its
> database is timing out after 12 hours.
>
>
>
> Here’s the context:
>
> · Two servers (one running the application and another hosting
> the database) in the Amazon AWS environment on different subnets within a
> virtual private cloud
>
>
>
> · The server hosting the application is running Ubuntu 14.04.2 LTS
>
>
>
> · The server hosting the database is running Ubuntu 14.04.2 LTS
> and PostgreSQL version 9.4.4
>
>
>
> · An stunnel connection is provisioned from the client to the
> database. The app server is running stunnel version 4.53 with the
> following stunnel.conf settings:
>
> ; PID is created inside the chroot jail
>
> pid = /stunnel4.pid
>
>
>
> [postgres-<host>]
>
> client = yes
>
> protocol = pgsql
>
> accept = 5432
>
> connect = <host>:5432
>
> options = NO_TICKET
>
> retry = yes
>
>
>
> · The database is provisioned to only accept “hostssl” type
> connections from the application server
>
>
>
> I’ve enabled the logging of PostgreSQL connections and disconnections and
> I see that for connections that terminate before the associated query
> completes, the session time shown in the disconnect message is 12 hours
> plus three or four seconds.
>
>
>
> I see this problem with both of the default tcp_keepalives settings of:
>
> #tcp_keepalives_idle = 0
>
> #tcp_keepalives_interval = 0
>
> #tcp_keepalives_count = 0
>
>
>
> And with the following settings:
>
> tcp_keepalives_idle = 120
>
> tcp_keepalives_interval = 120
>
> tcp_keepalives_count = 5
>
>
>
> I don’t see the disconnect problem for long-running queries that use a
> local database connection.
>
>
>
> Any advice on what might be causing remote database connections to drop
> after 12 hours (and how to work around the issue)?
>
>
>
> Kind regards,
> Rich
>
>
>