Postgres Pro
Downloads
Home   >   mailing lists

Thread: tuples gone?

tuples gone?

From
Daniel Kalchev
Date:
Hi,

I got an corrupted table,,, unfortunately with pretty important data :(

VACUUM tells me:

NOTICE:  Rel relx: TID 2344/5704: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel relx: TID 2344/5736: OID IS INVALID. TUPGONE 1.
NOTICE:  Rel relx: TID 2344/5768: OID IS INVALID. TUPGONE 1.

(this, many times, then)
pqReadData() -- backend closed the channel unexpectedly.       This probably means the backend terminated abnormally
  before or while processing the request.
 

I can read part (beginning?) of the relation with select or copy, but anything 
that touches this area dies badly :(

Is there any way to recover this relation? Or at least as much data as 
possible?

Oh, an this is 7.1.3 and I am probably running with too large oids :)

DEBUG:  NextTransactionId: 708172974; NextOid: 3480073772

Daniel

Re: tuples gone?

From
Tom Lane
Date:
Daniel Kalchev <daniel@digsys.bg> writes:
> VACUUM tells me:

> NOTICE:  Rel relx: TID 2344/5704: OID IS INVALID. TUPGONE 1.

It's physically impossible to get 2344 tuples on a page.  (If you're
using 8k pages then the most you could have per page is less than 200.)
So the above TID is obviously bogus, implying that you have pages
with corrupted page headers --- probably pd_lower is much larger than
it should be.

You could try dumping out the contents of page 5704, eg
dd bs=8k skip=5704 count=1 <tablefile | od -x

just to see what's there, but I bet you will find that it looks like
it's been trashed.

> Is there any way to recover this relation? Or at least as much data as 
> possible?

If you can figure out what pd_lower should be on each of the trashed
pages, you might be able to reset it to the correct value and recover
the tuples, if there are any un-trashed.  Otherwise zero out the trashed
page(s).  You should not expect to get everything back --- what you want
is to make the table readable so that you can dump the contents of the
undamaged pages.
        regards, tom lane

Re: tuples gone?

From
Tom Lane
Date:
I said:
> Daniel Kalchev <daniel@digsys.bg> writes:
>> NOTICE:  Rel relx: TID 2344/5704: OID IS INVALID. TUPGONE 1.

> You could try dumping out the contents of page 5704, eg

BTW, I got the ordering backwards: VACUUM prints TIDs as page number
and then tuple number.  So actually all these complaints are referencing
a single page, 2344, suggesting that you've got just one trashed page
header.
        regards, tom lane