Monitor the number of PostgreSQL connections

gistfile1.txt

#! /bin/sh

# Configurarion
DBHOST="127.0.0.1"
DBPORT="5432"
DBNAME="as_3_7_5"
PGUSER="postgres"
PGPASSWORD="a"
PGSQL_CMD="/usr/bin/psql"
AS_SERVER="127.0.0.1"
#Not DB pool set to PQM_PROCESS_COUNT, DB pool set to INITIAL_SIZE
SESSION_LIMIT="0"
PROC_LIMIT="3"
REFRESH_INTERVAL="2"
LOG_FILE="db_connections-$(date +%F).log"
QUERY="SELECT current_timestamp, procpid, backend_start, query_start, current_query FROM pg_stat_activity WHERE ( 
  datname='$DBNAME' AND client_addr='$AS_SERVER' AND  		
	(SELECT
		(SELECT COUNT(procpid) FROM pg_stat_activity WHERE (datname='$DBNAME' AND client_addr='$AS_SERVER')  ) > '$SESSION_LIMIT' )
	)"


# Execution
export PGPASSWORD="$PGPASSWORD"


if [ ! -f $LOG_FILE ];
then
 echo "  timestamp  | procpid |    backend_start        |   query_start          |   current_query" > $LOG_FILE
fi

while true; do
  PROC_NUM=$(ps aux | grep "postgres: $PGUSER $DBNAME" |wc -l)
  if [ $PROC_NUM -gt $PROC_LIMIT ];
  then
      $PGSQL_CMD -t -w -U $PGUSER -h $DBHOST -p $DBPORT -c "$QUERY" | tee -a $LOG_FILE
  fi
  sleep $REFRESH_INTERVAL 
done  

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