[Pgpool-hackers] Recovery and persistent connections...

[J. Carlos Muro]

I made a script some time ago to close (in fact kill) connections. Kinda
aggresive though..
I don't know if it will work with the last version, as I am not very
involved with databases right now.
You could use the script to kill every opened connection, even by user.
Hope it helps.
J. Carlos Muro

#!/bin/bash
# Last revision: 20/07/09

PGMASTER=postgres

usage()
{
cat << EOF
usage: $0 [-h] [-u <user>] [-b <database>]

This script closes connections to a PostgreSQL database.
If no option is specified then all connections are closed.

OPTIONS:
   -u      User accessing the database.
   -b      Name of the database. By default all databases
   -h      Shows this message.
EOF
}

USER=
DATABASE=".*"
DATABASE_LABEL="all databases"

while getopts "b:u:h" opt
do
  case $opt in
    b ) DATABASE=$OPTARG ; DATABASE_LABEL=$DATABASE ;;
    u ) USER=$OPTARG ;;
    h ) usage ; exit 0 ;;
    * ) echo "$opt - Unimplemented option." ; usage ; exit 1 ;;
  esac
done
shift $(($OPTIND - 1))

function close_connections {
  n=0
  IP_PATTERN="[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}"
  if [ "$USER" = "" ] ; then
    echo "* Purging connections to $DATABASE_LABEL"
    for conn in `ps x | grep -i -e
"${PGMASTER}:\s.*\s${DATABASE}\s${IP_PATTERN}(.*)\s" -e
"${PGMASTER}:\s.*\s${DATABASE}\s\[local\]\s"| grep -v grep | awk '
{print $1;}'`; do
      kill $conn
      n=$((${n} + 1))
    done
  else
    echo "* Purging connections of $USER to $DATABASE_LABEL"
    for conn in `ps x | grep -i -e
"${PGMASTER}:\s${USER}\s${DATABASE}\s${IP_PATTERN}(.*)\s" -e
"${PGMASTER}:\s${USER}\s${DATABASE}\s\[local\]\s"| grep -v gr
ep | awk '{print $1;}'`; do
      kill $conn
      n=$((${n} + 1))
    done
  fi
  echo "  ${n} connections closed!"
}

close_connections

exit 0


2010/6/11 Ivan Havlicek <ivan at modulix.org>

> Hi,
>
> I'm looking for a way to make a recovery with a 'force' option.
> As our databases are accessed via pgpool in persistent mode,
> actually the recovery always fail due some active transactions.
> I tried many different values of recovery_timeout and
> client_idle_limit_in_recovery without success...
> Do you know if there's an other/better solution than kill all postgres
> processes ?
>
> Thx for any idea.
> --
>                                                               Ivan Havlicek
>
> _______________________________________________
> Pgpool-hackers mailing list
> Pgpool-hackers at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-hackers
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-hackers/attachments/20100611/fc0122a4/attachment.html>