Re: terminate PG connections

#!/bin//bash
#
# Script: pgsession
# Author: Rao Kumar raokumar(at)netwolves(dot)com
# Purpose: Utility to list/kill postgres database user sessions.
# Comments: Execute this script as "postgres" user (user who runs postmaster)

#
# INITIALIZE ENVIRONMENT
# Set up the environmental variables
#
KILL="kill -TERM"
BASENAME=`basename "$0"`
PSQLC="psql -U postgres -d template1 -c "
PSQLTC="psql -U postgres -t -A -d template1 -c "

while [ "$#" -gt 0 ]
do
case "$1" in
--help|-\?)
usage=t
break
;;
-l)
OPT="list"
;;
-k)
OPT="kill"
;;
-f)
force=t
;;
-u)
if [ -z "$2" ]; then
echo "ERROR: Please specify user name"
exit 1
else
user="$2"
fi
shift;;
-p)
if [ -z $2 ]; then
echo "ERROR: Please specify pid"
exit 1
else
pid="$2"
fi
shift;;
*)
if [ "$#" -eq "0" ]; then
echo "$BASENAME: invalid option: $2" 1>&2
echo "Try '$BASENAME --help' for more information." 1>&2
exit 1
fi
;;
esac
shift;
done

if [ "$usage" ]; then
echo "$BASENAME : List/Kill database user sessions"
echo
echo "Usage:"
echo " $BASENAME [OPTION]... [USER]"
echo
echo "Options:"
echo " --h (help) show this help, then exit"
echo " -l (list) list database sessions"
echo " -k (kill) kill/terminate database sessions"
echo " -f (force) force kill (do not ask for confirmation,"
echo " use in conjunction with -k option)"
echo " -u USER specify database user name"
echo " -p PID specify database user process id (pid)"
echo
echo "Examples: "
echo " $BASENAME -l list all sessions"
echo " $BASENAME -l -u <user> list user sessions "
echo " $BASENAME -k kill all sessions"
echo " $BASENAME -k -f force kill all sessions"
echo " $BASENAME -k -u <user> kill user sessions"
echo " $BASENAME -k -p <pid> kill user session with a specific pid"
echo
exit 0
fi

if [ "$OPT" = "list" ]; then
UCTR=`$PSQLTC "select count(*) from pg_stat_activity" `
echo; echo "Database Sessions (all users): $UCTR"
SQL="select procpid as "PID", datname as "Database", "
SQL="$SQL usename as "User" from pg_stat_activity"
if [ ! -z "$user" ]; then
SQL="$SQL where usename = '$user'"
echo "Session List ($user)"
fi
echo "----------------------------------"
$PSQLC "$SQL"
elif [ "$OPT" = "kill" ]; then
SQL="select procpid from pg_stat_activity "
if [ ! -z "$user" ]; then
SQL="$SQL where usename = '$user'"
elif [ ! -z "$pid" ]; then
SQL="$SQL where procpid = '$pid'"
fi
for pid in `$PSQLTC "$SQL" `; do
if [ "$force" ]; then
echo "Killing session (PID:$pid)"
$KILL $pid
else
echo -n "Kill database session (PID:$pid) [y/n] ?:"
read confirm
if [ "$confirm" = "y" ]; then
echo "Killing session (PID:$pid)"
$KILL $pid
fi
fi
done
else
echo "$BASENAME: invalid option: $2" 1>&2
echo "Try '$BASENAME --help' for more information." 1>&2
exit 1
fi

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Isabella Ghiurea
Sent: Tuesday, June 30, 2009 1:23 PM
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] terminate PG connections

Hello PG Users,
I would like to know if there is a "clean" way to terminate running
and idle connections inside PG db ?
( I see : pg_cancel_backend (pid) will send the connections to idle
stage but not disconnecting from db , if you have > 100 idle
connections to db and don't want to use the OS " kill " )

Thank you
Isabella

--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella(dot)Ghiurea(at)nrc-cnrc(dot)gc(dot)ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045