Pgsession.sh

Script pgsession.sh. És una utilitat de shell per llistar i finalitzar sessions de Postgres. El script el copio a el copio a /usr/lib/postgresql/8.1/bin, li dono permisos d'escriptura, i l'executo amb l'usuari postgres (l'usuari que executa postmaster)

	Script: pgsession
		pgsession : List/Kill database user sessions

	Usage:
		pgsession [OPTION]... [USER]

	Options:
		--h (help)	show this help, then exit
		-l  (list)	list database sessions
		-k  (kill)	kill/terminate database sessions
		-f  (force)	force kill (do not ask for confirmation,
				use in conjunction with -k option)
		-u  USER	specify database user name
		-p  PID	specify database user process id (pid)

Examples:
	  pgsession -l		list all sessions
	  pgsession -l -u 	list user sessions
	  pgsession -k		kill all sessions
	  pgsession -k -f	force kill all sessions
	  pgsession -k -u 	kill user sessions
	  pgsession -k -p 	kill user session with a specific pid

Note: that a listing of sessions and their queries is also available in psql using:

select * from pg_stat_activity; 

I el script:

#!/bin/bash
#
# Script: pgsession
# Author: Rao Kumar raokumar@netwolves.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