Skip site navigation (1) Skip section navigation (2)

Re: Please HELP - URGENT - transaction wraparound error

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: John Sidney-Woollett <johnsw(at)wardbrook(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org
Subject: Re: Please HELP - URGENT - transaction wraparound error
Date: 2005-10-30 19:05:09
Message-ID: 20051030190500.GC19496@svana.org (view raw or flat)
Thread:
Lists: pgsql-general
On Sun, Oct 30, 2005 at 06:41:45PM +0000, John Sidney-Woollett wrote:
> Hmm. I'm pretty sure that database mail_lxtreme was unused (no 
> connections/activity) - I didn't think that it would need to be vacuumed 
> at all...

A database that is never used still needs to be vacuumed. The only
exception is if you VACUUM FREEZE which puts the entire database in a
frozen state which will never need vacuuming. This is how template0 is
configured. Ofcourse, once you make changes...

> Just out of curiousity would the wraparound error (for mail_lxtreme) 
> actually have affected data in bp_live?

I doubt it but (thinking shared tables) I'll have to defer to someone
more knowledgable.

> Could I just have deleted mail_lxtreme and then continued to use bp_live 
> as though nothing had happened?
> 
> Or had database bp_live already been damaged by the wraparound?

Everything would probably have been fine.

BTW, I would have thought this message would have been appearing the
last billion transactions or so, didn't anyone notice?

To solve this forever, setup a cronjob for once a month:

vacuumdb -a

This will vacuum every database, even if you don't know the names or
where they came from. AIUI when you vacuum a database whose
transactions are over billion transactions old it automatically puts it
in "frozen" state. If someone had happened to run "vacuumdb -a" anytime
in the last few months, you might never have noticed the wraparound...

Hope this helps,
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Responses

pgsql-general by date

Next:From: Matthew PeterDate: 2005-10-30 19:09:16
Subject: Re: function example?
Previous:From: Martijn van OosterhoutDate: 2005-10-30 18:56:44
Subject: Re: create table in transaction fails

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group