I know you're running windows, but if you can get bash working on it, here's a simple bash script I wrote that when it detects too many people connected creates a table called pg_stat_bk_20110120140634 (i.e. date and time) so I can then look over what was in pg_stat_activity when things were acting up.
#!/bin/bash
threshold=50;
dt=`date +%Y%m%d%H%M%S`;
active=`/usr/bin/psql www -Atc "select count(*) from pg_stat_activity
where current_query not ilike '%idle%';"`
if [[ active -gt threshold ]]; then
echo "there are "$active" backends";
echo "creating backup for pg_stat as pg_stat_bk_$dt"
psql www -c "select * into monitoring.pg_stat_bk_$dt from
pg_stat_activity where current_query not ilike '%idle%';"
fi
note that you should probably change what triggers it to things like
if there are any queries waiting or idle in transaction, etc.
check.sh
Description: Bourne shell script
-- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
