Is it possible to configure PostgreSQL to automatically close idle connections?

1、use pgbouncer

As new connections/transactions/statements arrive, the pool will increase in size up to the
defined user maximums. Those connections will stay around for at most server_idle_timeout
before the pool releases those connections.
pgbouncer also releases sessions every server_lifetime . This allows the server to free
backends in rotation to avoid issues with very long-lived session connections.

可以利用pgbouncer的server_idle_timeout参数

server_idle_timeout:

;; Close server connection if its not been used in this time.
;; Allows to clean unnecessary connections from pool after peak.
;server_idle_timeout =
server_lifetime:
;; Close server connection if its been connected longer.
;server_lifetime = 1200

2、结合pg_stat_activity中的state和state_change字段

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'regress'
AND pid <> pg_backend_pid()
AND state = 'idle'
AND state_change < current_timestamp - INTERVAL '' MINUTE;

有的可能使用pg_stat_activity中的query_start字段,但有时这个字段是空的,即用户只是连接进来但没有执行操作,此时该字段显示为空,所以尽量使用state_change较稳妥,使用pgbouncer就更方便了。

参考:

http://*.com/questions/13236160/is-there-a-timeout-for-idle-postgresql-connections

http://www.postgresql.org/docs/9.3/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

http://2ndquadrant.com/en/books/postgresql-9-administration-cookbook/

上一篇:静态资源库CDN服务


下一篇:针对C程序员的 C++