How to terminate idle connections in Postgres 11.x and above

Products

• VMware Tanzu Greenplum

Issue/Introduction

Users may remain connected to the database after running queries.

These connections will consume connection slots and may cause the "max_connections" limit to be reached.

This will then block other users from connecting to the database.
This KB describes how to find and terminate connections that have bee idle for some time in VMWare Postgres.

See KB "Scripts to terminate all idle connections in Pivotal Greenplum Database cluster " for instructions for doing this in VMWARE Greenplum database.

Environment

Product Version: 11.10

Resolution

The examples below will terminate connections that have been idle for 15 minutes or longer.
The "15min" specification in the examples can be changed to requirement.

Option 1

Connect to the database and run the following query

SELECT pg_terminate_backend(pid) 
FROM   pg_stat_activity 
WHERE  state = 'idle' 
       AND state_change < now() - '15min'::interval; 

Option 2

Run the following script via crontab (or similar feature) periodically to kill idle connections

#!/bin/sh

export PGDATABASE='template1'
export PGPORT='5432'

psql <<-EOF
SELECT pg_terminate_backend(pid) 
FROM   pg_stat_activity 
WHERE  state = 'idle' 
       AND state_change < now() - '15min'::interval;
EOF