recovery from xid wraparound

From: "Shane Wright" <shane(dot)wright(at)edigitalresearch(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: recovery from xid wraparound
Date: 2006-10-24 06:43:15
Message-ID: 64F50E3BAAE32A4FA0686CF651E0D4765E9D@exchange11.ad.edigitalresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I'm running 7.4 on RHAS 4, and I think I've had a transaction id
wraparound issue in a stats database we have. Running the command below gives the suitably
worrying negative number:

[dbname]=# SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
------------------+-------------
[maindbname] | -2081610471
[otherdbname] | 1075601025
[otherdbname] | 1257289757
[otherdbname] | 1074582099
[otherdbname] | 1257289757

Which is weird - because I have vacuumed the database quite a lot -
both individual tables and I thought a vacuum of the whole database a
month or so ago.

Anyway - not noticed any data loss yet and was hoping it would be such
that if all tables had been vacuumed recently (including system catalog
tables), that there would be no remaining rows that would appear to
have a future xid and so the database should be ok?

Obviously I'm now doing the write thing with a vacuumdb -a - however
this has been running 9 hours now and looks like at least 7 hours to
go just on this one monstrous table

in the interests of risk reduction I've just knocked up a script to run
ahead and quickly vacuum all the other tables.

But my questions are thus...

a) is my assumption about the database being ok correct - assuming all
tables have been vacuumed recently, including catalog tables?

b) is it possible to safely abort my whole table vacuum now so I can
run it at the weekend when there's less traffic?

c) if I have experienced data loss, on the assumption all the table
structure remains (looks like it does), and I have a working backup
from before the xid wraparound (I do), can I just reinsert any
detected-missing data at the application level without needing a
dump/reload?

Any help appreciated in this really not-fun time,

thanks

S


Responses

Browse pgsql-general by date

  From Date Subject
Next Message johnber 2006-10-24 06:56:23 How to improve ODBC performance
Previous Message Shane Wright 2006-10-24 06:06:32 recovery from xid wraparound