Problem with database connections timing out for long-running queries

From: "Rich Schaaf" <rschaaf(at)commoninf(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Problem with database connections timing out for long-running queries
Date: 2015-08-21 18:16:52
Message-ID: 002a01d0dc3d$8ea97390$abfc5ab0$@commoninf.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andy Colson 2015-08-21 20:27:33 9.5 beta pg_upgrade documentation
Previous Message Igor Sosa Mayor 2015-08-21 15:38:48 Re: Problem with pl/python procedure connecting to the internet