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

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

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:

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