Thread: Recording vacuum/analyze/dump times

Recording vacuum/analyze/dump times

From
"Jim Buttafuoco"
Date:
Its there a reason postgresql doesn't record vacuum/analyze and dump times in pg_class (or another table).  This seems
like it would be a very helpful feature.

for pg_dump I would add an option --record=YES|NO 
for vacuum and analyze I would add a NORECORD|RECORD option

Jim



Re: Recording vacuum/analyze/dump times

From
Heikki Linnakangas
Date:
On Mon, 7 Mar 2005, Jim Buttafuoco wrote:

> Its there a reason postgresql doesn't record vacuum/analyze and dump times in pg_class (or another table).  This
seems
> like it would be a very helpful feature.
>
> for pg_dump I would add an option --record=YES|NO
> for vacuum and analyze I would add a NORECORD|RECORD option

You could easily do this in application level:

CREATE TABLE vacuums (relname name, last_vacuum timestamp);

Every time you vacuum, do:

VACUUM foobar; UPDATE dumps set last_dump = now() WHERE relname = 'foobar';

Same for pg_dump.

- Heikki


Re: Recording vacuum/analyze/dump times

From
"Jim Buttafuoco"
Date:
This is what I was thinking about doing.  It would be nicer if the system "just did it" for me.  I have 100+'s of
databases with 100+'s of tables in each and run pg_autovacuum on them all.  I also do nightly dumps and any database
that has been modified (my application keeps track).  I was just thinking of using these dates as a check that the
automated processes are working.

Jim

---------- Original Message -----------
From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Jim Buttafuoco <jim@contactbda.com>
Cc: pgsql-hackers@postgresql.org
Sent: Mon, 7 Mar 2005 20:35:21 +0200 (EET)
Subject: Re: [HACKERS] Recording vacuum/analyze/dump times

> On Mon, 7 Mar 2005, Jim Buttafuoco wrote:
> 
> > Its there a reason postgresql doesn't record vacuum/analyze and dump times in pg_class (or another table).  This
seems
> > like it would be a very helpful feature.
> >
> > for pg_dump I would add an option --record=YES|NO
> > for vacuum and analyze I would add a NORECORD|RECORD option
> 
> You could easily do this in application level:
> 
> CREATE TABLE vacuums (relname name, last_vacuum timestamp);
> 
> Every time you vacuum, do:
> 
> VACUUM foobar; UPDATE dumps set last_dump = now() WHERE relname = 'foobar';
> 
> Same for pg_dump.
> 
> - Heikki
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
------- End of Original Message -------



Re: Recording vacuum/analyze/dump times

From
"Matthew T. O'Connor"
Date:
Jim Buttafuoco wrote:

>Its there a reason postgresql doesn't record vacuum/analyze and dump times in pg_class (or another table).  This
seems
>like it would be a very helpful feature.
>
>for pg_dump I would add an option --record=YES|NO 
>for vacuum and analyze I would add a NORECORD|RECORD option
>  
>

For what it's worth, integrated pg_autovacuum will have something like 
this.  At least my initial design does, since the autovacuum daemon 
needs to know remember when the last time a table was vacuumed.



Re: Recording vacuum/analyze/dump times

From
"Jim Buttafuoco"
Date:
But what happens if I go in and manually vacuum a table (either because I just deleted a bunch of records or
whatever).Thisis why I think the backend should record the date in pg_class.  
 


---------- Original Message -----------
From: "Matthew T. O'Connor" <matthew@zeut.net>
To: jim@contactbda.com
Cc: pgsql-hackers@postgresql.org
Sent: Mon, 07 Mar 2005 13:56:04 -0500
Subject: Re: [HACKERS] Recording vacuum/analyze/dump times

> Jim Buttafuoco wrote:
> 
> >Its there a reason postgresql doesn't record vacuum/analyze and dump times in pg_class (or another table).  This
seems
> >like it would be a very helpful feature.
> >
> >for pg_dump I would add an option --record=YES|NO 
> >for vacuum and analyze I would add a NORECORD|RECORD option
> >  
> >
> 
> For what it's worth, integrated pg_autovacuum will have something like 
> this.  At least my initial design does, since the autovacuum daemon 
> needs to know remember when the last time a table was vacuumed.
------- End of Original Message -------



Re: Recording vacuum/analyze/dump times

From
"Matthew T. O'Connor"
Date:
Right, once autovacuum is integrated, then I think vacuum and analyze 
should update the autovacuum table this way autovacuum won't redundantly 
vacuum tables that were just vacuumed manually.

Jim Buttafuoco wrote:

>But what happens if I go in and manually vacuum a table (either because I just deleted a bunch of records or
whatever).
> This is why I think the backend should record the date in pg_class.  
>
>
>---------- Original Message -----------
>From: "Matthew T. O'Connor" <matthew@zeut.net>
>To: jim@contactbda.com
>Cc: pgsql-hackers@postgresql.org
>Sent: Mon, 07 Mar 2005 13:56:04 -0500
>Subject: Re: [HACKERS] Recording vacuum/analyze/dump times
>
>  
>
>>Jim Buttafuoco wrote:
>>
>>    
>>
>>>Its there a reason postgresql doesn't record vacuum/analyze and dump times in pg_class (or another table).  This
seems
>>>like it would be a very helpful feature.
>>>
>>>for pg_dump I would add an option --record=YES|NO 
>>>for vacuum and analyze I would add a NORECORD|RECORD option
>>> 
>>>
>>>      
>>>
>>For what it's worth, integrated pg_autovacuum will have something like 
>>this.  At least my initial design does, since the autovacuum daemon 
>>needs to know remember when the last time a table was vacuumed.
>>    
>>
>------- End of Original Message -------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>
>  
>



Re: Recording vacuum/analyze/dump times

From
"Jim Buttafuoco"
Date:
I agree 100%. 


---------- Original Message -----------
From: "Matthew T. O'Connor" <matthew@zeut.net>
To: jim@contactbda.com
Cc: pgsql-hackers@postgresql.org
Sent: Mon, 07 Mar 2005 16:15:45 -0500
Subject: Re: [HACKERS] Recording vacuum/analyze/dump times

> Right, once autovacuum is integrated, then I think vacuum and analyze 
> should update the autovacuum table this way autovacuum won't redundantly 
> vacuum tables that were just vacuumed manually.
> 
> Jim Buttafuoco wrote:
> 
> >But what happens if I go in and manually vacuum a table (either because I just deleted a bunch of records or 
whatever).
> > This is why I think the backend should record the date in pg_class.  
> >
> >
> >---------- Original Message -----------
> >From: "Matthew T. O'Connor" <matthew@zeut.net>
> >To: jim@contactbda.com
> >Cc: pgsql-hackers@postgresql.org
> >Sent: Mon, 07 Mar 2005 13:56:04 -0500
> >Subject: Re: [HACKERS] Recording vacuum/analyze/dump times
> >
> >  
> >
> >>Jim Buttafuoco wrote:
> >>
> >>    
> >>
> >>>Its there a reason postgresql doesn't record vacuum/analyze and dump times in pg_class (or another table).  This 
seems
> >>>like it would be a very helpful feature.
> >>>
> >>>for pg_dump I would add an option --record=YES|NO 
> >>>for vacuum and analyze I would add a NORECORD|RECORD option
> >>> 
> >>>
> >>>      
> >>>
> >>For what it's worth, integrated pg_autovacuum will have something like 
> >>this.  At least my initial design does, since the autovacuum daemon 
> >>needs to know remember when the last time a table was vacuumed.
> >>    
> >>
> >------- End of Original Message -------
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: Have you searched our list archives?
> >
> >               http://archives.postgresql.org
> >
> >  
> >
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
------- End of Original Message -------