Joan a 22:41, 1 des 2008

2008-12-01T22:41:58Z

Pàgina nova

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)

<pre>
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
</pre>

Note: that a listing of sessions and their queries is also available in psql using:
select * from pg_stat_activity;

I el script:
<pre>
#!/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
</pre>

Pgsession.sh - Historial de revisió

Joan a 22:41, 1 des 2008