Re: Server stops responding in every week

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.