Обсуждение: Recording vacuum/analyze/dump times
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
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
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 -------
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.
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 -------
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 > > >
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 -------