Vacuum Vs Vacuum Full

On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote:

Hi,

I've been trying to get to the bottom of the differences between a vacuum
and a vacuum full, it seems to me that the difference is that a vacuum full
also recovers disk space(and locks things making it less than useful on
production servers). But I believe that both will fix the transaction
ID(example message below).

"WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
"mydb"."Which is reason I ask the question, is full vacuum backup useful
for anything other than reclaiming disk space.

On a side note, we doubled our page slots, but they ran out much faster(of
course) than we thought, is there a good sql statement that can tell you
what your current transaction ID is?

Thanks in advance.

Cheers,
Rob

Actually its not asking for a VACUUM FULL but a VACUUM of the full database,
instead of selected tables.

See below for complete details
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver wrote:

On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote:

"WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
"mydb"."Which is reason I ask the question, is full vacuum backup useful
for anything other than reclaiming disk space.

Actually its not asking for a VACUUM FULL but a VACUUM of the full database,
instead of selected tables.

See below for complete details
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

BTW, what version of PostgreSQL is this? Database-wide vacuum is no
longer required for XID wraparound issues. I think this was an 8.3
change but might have happened in 8.2, I don't remember.

Matt

Its 8.1 and I'm doing a Vacuum using the vacuumdb program.

Thanks Matt, might be time for an upgrade.

Date: Tue, 5 Aug 2008 11:21:44 -0400
From: matthew@zeut.net
To: aklaver@comcast.net
CC: pgsql-general@postgresql.org; redsmurfau@msn.com
Subject: Re: [GENERAL] Vacuum Vs Vacuum Full

Adrian Klaver wrote:

On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote:

"WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
"mydb"."Which is reason I ask the question, is full vacuum backup useful
for anything other than reclaiming disk space.

Actually its not asking for a VACUUM FULL but a VACUUM of the full database,
instead of selected tables.

See below for complete details
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

BTW, what version of PostgreSQL is this? Database-wide vacuum is no
longer required for XID wraparound issues. I think this was an 8.3
change but might have happened in 8.2, I don't remember.

Matt

_________________________________________________________________
It's simple! Sell your car for just $40 at CarPoint.com.au
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641&_t=762955845&_r=tig_OCT07&_m=EXT

"Matthew T. O'Connor" <matthew@zeut.net> writes:

On Monday 04 August 2008 11:04:00 pm Robert Shaw wrote:

"WARNING: database "mydb" must be vacuumed within 177009986 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in
"mydb".

BTW, what version of PostgreSQL is this? Database-wide vacuum is no
longer required for XID wraparound issues. I think this was an 8.3
change but might have happened in 8.2, I don't remember.

8.2. But you could still get that message, even in CVS HEAD, if
autovacuum was failing to complete for some reason (and had been
failing for quite a long time).

regards, tom lane

Tom Lane wrote:

"Matthew T. O'Connor" <matthew@zeut.net> writes:

BTW, what version of PostgreSQL is this? Database-wide vacuum is no
longer required for XID wraparound issues. I think this was an 8.3
change but might have happened in 8.2, I don't remember.

8.2. But you could still get that message, even in CVS HEAD, if
autovacuum was failing to complete for some reason (and had been
failing for quite a long time).

Should that message to updated since a database-wide vacuum is no longer
required, or are you saying that the message is still relevant is some
corner cases?

"Matthew T. O'Connor" <matthew@zeut.net> writes:

Tom Lane wrote:

8.2. But you could still get that message, even in CVS HEAD, if
autovacuum was failing to complete for some reason (and had been
failing for quite a long time).

Should that message to updated since a database-wide vacuum is no longer
required, or are you saying that the message is still relevant is some
corner cases?

I think the message is okay as-is, or at least that the code doesn't
have the information available to do better --- it knows which database
is the most problematic, but not which table(s) within that DB most need
vacuuming. So the easiest manual fix is still a DB-wide vacuum.
Besides which, if you've got one problem table then you've probably got
more than one.

The odds of anyone seeing this message in the field in 8.2 or later seem
pretty remote anyway, so I'm not feeling like we should expend
tremendous effort to make it better.

regards, tom lane