Thread: 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.Ghiurea@nrc-cnrc.gc.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

#!/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

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Isabella Ghiurea
Sent: Tuesday, June 30, 2009 1:23 PM
To: pgsql-admin@postgresql.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.Ghiurea@nrc-cnrc.gc.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


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Plugge, Joe R. wrote:
> #!/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)
>
[............]

Is it safe to kill pg sessions with Postgresql versions < 8.4?

This can be read in the 8.4 release:
"Add pg_terminate_backend() to safely terminate a backend (the SIGTERM
signal works also) (Tom, Bruce)

While it's always been possible to SIGTERM a single backend, this was
previously considered unsupported; and testing of the case found some
bugs that are now fixed."

I don't know what "some bugs that are now fixed" means, but I think I
read somewhere that killing a session with the command kill was not a
god idea.

regards
--
 Rafael Martinez, <r.m.guerrero@usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

To kill the process and session you can do like follows:

(1) Edit  " pg_hba.conf  " file which is in your data directory and  at the bottom of the file add a line as follows:

 host all all <client IP address/32>  reject

(2)And reload the database.

pg_ctl –D <your data directory path> reload

(3)After that run the following command:

$ pg_ctl -D <your data directory path> kill TERM <procpid>  

Thanks & Regards,
Raghu