Bash script to kill active connections on a PostgreSQL database

pg_killcon.sh

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

Number of current connections (including this one): 8
Are you sure you want to kill them? y
ERROR: column pg_stat_activity.procpid does not exist
LINE 1: select pg_terminate_backend(pg_stat_activity.procpid)
^

Hey, this is an old script I wrote in 2013 (for PostgreSQL 9.2).
Things might have changed a lot since then. I think it shouldn't be harder than figuring out which table that contains the PIDs for active connections and change accordingly in the embedded SQL queries :)