Обсуждение: tracking last_autovacuum

Hi All,
in  pg_stat_all_tables, the fields last_autovacuum, last_autoanalyze are
updated any time autovacuum vacuums a table.
I would like to keep an history of these activities in order to see when
and how many times any single table is vacuumed.

Is there any features that can help me ? (I'm using postgres 8.2.9)

What about creating a trigger on pg_stat_all_tables and storing the
required info in another table (like autovacuum_history) ?

Cheers,
ste

Stefano Nichele escribió:
> Hi All,
> in  pg_stat_all_tables, the fields last_autovacuum, last_autoanalyze are
> updated any time autovacuum vacuums a table.
> I would like to keep an history of these activities in order to see when
> and how many times any single table is vacuumed.
>
> Is there any features that can help me ? (I'm using postgres 8.2.9)
>
> What about creating a trigger on pg_stat_all_tables and storing the
> required info in another table (like autovacuum_history) ?

I guess you could run a query once every autovacuum_naptime and have it
save aside all those entries from pg_stat_all_tables that have recent
dates into a history table.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Yes, i could do that but it seems simpler (to me) with a trigger than
looking at the recent changes records and store them somewhere.

ste

Alvaro Herrera wrote:
> Stefano Nichele escribió:
>
>> Hi All,
>> in  pg_stat_all_tables, the fields last_autovacuum, last_autoanalyze are
>> updated any time autovacuum vacuums a table.
>> I would like to keep an history of these activities in order to see when
>> and how many times any single table is vacuumed.
>>
>> Is there any features that can help me ? (I'm using postgres 8.2.9)
>>
>> What about creating a trigger on pg_stat_all_tables and storing the
>> required info in another table (like autovacuum_history) ?
>>
>
> I guess you could run a query once every autovacuum_naptime and have it
> save aside all those entries from pg_stat_all_tables that have recent
> dates into a history table.
>
>

Stefano Nichele escribió:
> Yes, i could do that but it seems simpler (to me) with a trigger than
> looking at the recent changes records and store them somewhere.

You can't have a trigger there, sorry.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.