Re: Server stops responding in every week

Scott,

Thank you.

>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.

I can run scripts from windows scheduler for every 4 minutes or maybe from
pgadmin or maybe frequently accessed table triggers.

Whan command should be used to detect presence of hangup ( maybe query
running more than 60
seconds or too many idle transactions?)
should pg_stat_activity.query_start column used for this ?

How to log memory usage ?

Is it best way to use initially:

create table activity as   select * from pg_stat_activity limit 0;
create table locks as   select * from pg_locks limit 0;

and if script detects hangup it invokes

insert into activity select * from pg_stat_activity;
insert into locks select * from pg_locks;

How to add log timestamp column to activity and locks tables ?
How to log memory usage ?
Can users notice server perfomance degration due to this?
What else to log ?
How to create plpgsql procedure for this ?

There are few tables which are used frequently. Hangup occurs probably if
queries are invoked againt those tables.
Is it better to implement this as trigger for those tables?

Andrus.