auto disconnect idle connection with pgbouncer.

Products

• VMware Tanzu Greenplum

Issue/Introduction

The utility pgbouncer has the ability to automatically disconnect idle connections.

Environment

Product Version: 6.23

Resolution

Required setting:
1. "pool_mode" needs to be "transaction" or "statement".

;; When server connection is released back to pool:
;;   session      - after client disconnects (default)
;;   transaction  - after transaction finishes
;;   statement    - after statement finishes

2. There are two parameters that control the auto disconnect timeout. Both work for idle connections (the lower setting will be chosen).

server_lifetime
The pooler will close an unused (not currently linked to any client connection) server connection that has been connected longer than this. Setting it to 0 means the connection is to be used only once, then closed. [seconds]
Default: 3600.0

server_idle_timeout
If a server connection has been idle more than this many seconds it will be closed. If 0 then this timeout is disabled. [seconds]
Default: 600.0

Here the "server connection has been idle" means no related client connection is running any queries. If server connect A has two related client connection (C,D). If client C has been idle for some time, but client D is actively running queries. Then this server connection is considered active. In other words, a server connection is considered idle only when all it's related client connections are idle.