PostgreSQL 空闲会话超时插件 - pg_timeout (idle session timeout)

digoal 2020-07-24

1444

作者

digoal

日期

2020-07-24

背景

PostgreSQL内置的几种超时参数, 大多数时候可以防止雪崩.

语句执行时间超时: statement_timeout

空闲事务超时: idle_in_transaction_session_timeout

锁等待超时: lock_timeout

空闲会话超时: 暂无内置参数支持, 通过插件支持.

pg_timeout, 支持空闲会话超时

https://pgxn.org/dist/pg_timeout/1.0.0/

https://github.com/michaelpq/pg_plugins/tree/master/kill_idle

用法:

pg_timeout

PostgreSQL extension to manage database idle session timeout.

Installation

Compiling

This module can be built using the standard PGXS infrastructure. For this to work, the pg_config program must be available in your $PATH:

git clone https://github.com/pierreforstmann/pg_timeout.git

cd pg_timeout

make

make install

This extension has been validated with PostgresSQL 9.5, 9.6, 10, 11 and 12.

PostgreSQL setup

Extension can be loaded at server level with shared_preload_libraries parameter:

shared_preload_libraries = 'pg_timeout'

Usage

pg_timeout has 2 specific GUC:

- pg_timeout.naptime: number of seconds for the dedicated backgroud worker to sleep between idle session checks (default value is 10 seconds)

- pg_timeout.idle_session_timeout: database session idle timeout in seconds (default value is 60 seconds)

Note that pg_timeout only takes care of database session with idle status (idle in transaction is not taken into account).

Example

Add in postgresql.conf:

shared_preload_libraries = 'pg_timeout'

pg_timeout.naptime=30

pg_timeout.idle_session_timeout=30

Any database session with is idle for more than 30 seconds is killed. In database instance log you get messages similar to:

LOG: pg_timeout_worker: idle session PID=26546 user=pierre database=pierre application=psql hostname=NULL

LOG: pg_timeout_worker: idle session(s) since 30 seconds terminated

FATAL: terminating connection due to administrator command

If the database session was started by psql, you get:

FATAL: terminating connection due to administrator command

server closed the connection unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

The connection to the server was lost. Attempting reset: Succeeded.