Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Bash script to kill active connections on a PostgreSQL database
#!/bin/bash
#
# A simple script to kill all connections to
# a postgresql database.
#
function usage() {
cat << EOF
usage: $0 options
Kill all connections to a PostgreSQL database.
OPTIONS:
-h display this message
-H database server or socket directory (default: "local socket")
-p database server port (default: "5432")
-U database user name (default: `whoami`)
-w no password
-d database name to kill connections
-C number of current connections (including this one).
EOF
}
PSQL=`which psql`
HOST=""
PORT=5432
USER=`whoami`
PASSWD=1
DBNAME=""
function construct_conn_args() {
# Constructs the connection arguments to use.
local CONN_ARGS="-U $USER -p $PORT"
if [[ -n $HOST ]]; then
# set host to connect to
CONN_ARGS+=" -h $HOST"
fi
if [[ -n $DBNAME ]]; then
# set database name
CONN_ARGS+=" -d $DBNAME"
fi
if [[ $PASSWD -eq 0 ]]; then
# don't ask for password
CONN_ARGS+=" -w"
fi
echo "$CONN_ARGS"
}
function get_server_version() {
# Retrieve the server version.
local SERVER_VERSION=`$PSQL $(construct_conn_args) -t -c "show SERVER_VERSION;"`
echo $SERVER_VERSION
}
function count_connections() {
local NUM_CONNS=`$PSQL $(construct_conn_args) -t -c "select count(*) from pg_stat_activity;"`
echo $NUM_CONNS
}
function killconns() {
# kill all connections on database
local SERVER_VERSION=$(get_server_version)
local SERVER_VERSION_NUMERIC=`echo $SERVER_VERSION|awk '{gsub(/\./,"",$0); print}'`
local SERVER_VERSION_NUMERIC=${SERVER_VERSION_NUMERIC:0:2}
if [[ $SERVER_VERSION_NUMERIC -ge 92 ]]; then
# if postgresql server version is greater than or
# equal to 9.2.x
local QUERY="select pg_terminate_backend(pg_stat_activity.pid)
from pg_stat_activity where pg_stat_activity.datname = '$DBNAME'
and pg_stat_activity.pid <> pg_backend_pid();"
else
local QUERY="select pg_terminate_backend(pg_stat_activity.procpid)
from pg_stat_activity where pg_stat_activity.datname = '$DBNAME'
and pg_stat_activity.procpid <> pg_backend_pid();"
fi
read -p "Are you sure you want to kill them? " -n 1 -r -e
if [[ $REPLY =~ ^[Yy]$ ]]; then
`$PSQL $(construct_conn_args) -c "$QUERY"`
else
echo -e "Quit without killing any connections."
exit 1
fi
}
while getopts "hd:H:p:U:wC" OPTION; do
case $OPTION in
h)
usage
exit 1
;;
H)
HOST=$OPTARG
;;
p)
PASSWD=$OPTARG
;;
U)
USER=$OPTARG
;;
w)
PASSWD=0
;;
d)
DBNAME=$OPTARG
;;
C)
echo "Current connections: "
count_connections
exit 1
;;
?)
usage
exit
;;
esac
done
if [[ -z $DBNAME ]]; then
usage
echo -e "\nYou must specify a database name to connect to.\n"
exit 1
fi
echo "Number of current connections (including this one): $(count_connections)"
echo $(killconns)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment