Problem with database connections timing out for long-running queries

On 08/21/2015 11:16 AM, Rich Schaaf 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.

Is there anything else in the log at this point in time that looks like
it might be related?

What is the actual disconnect message?

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

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@commoninf.com> wrote:

I am using the default TIMEOUTidle setting in stunnel so that seems plausible.

Thanks very much for pointing me in what sounds like the right direction!

Kind regards,

Rich

From: Steve Crawford [mailto:scrawford@pinpointresearch.com]
Sent: Friday, August 21, 2015 6:25 PM
To: Rich Schaaf
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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@commoninf.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