Re: Please HELP - URGENT - transaction wraparound error

Martijn

Thanks for the answers/thoughts...

Vacuumuming the databases hammers the server so the vacuums are spread
out at different times during the night/morning. Plus template1 is
vacuumed once a week.

I guess I was unlucky to have missed the vacuum on that unused database
(due to my misunderstanding), and not to have been scanning the
serverlog more frequently (if at all recently!).

My solution is to create a nagios script that scans entries in serverlog
loking for WARN or ERROR messages in the past xx minutes.

With this in place, I would have caught this error weeks ago before it
bit me in the ass!

Stressful day, but learnt a lot...

Thanks for everyone for their input - great product and great support!

John

Martijn van Oosterhout wrote:
> 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,