Thread: invalid page header
Hi, I'm in the unfortunate position of having "invalid page header(s) in block 58591 of relation "pg_toast_302599". I'm well aware that the hardware in question isn't the most reliable one. None the less, I'd like to restore as much of the data as possible. A pg_filedump analysis of the file in question spits out the following information (relation pg_toast_302599 has oid 302602): > # pg_filedump -R 58590 58592 base/296788/302602 > > ******************************************************************* > * PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1 > * > * File: base/296788/302602 > * Options used: -R 58590 58592 > * > * Dump created on: Fri Aug 3 10:34:23 2007 > ******************************************************************* > > Block 58590 ******************************************************** > <Header> ----- > Block Offset: 0x1c9bc000 Offsets: Lower 36 (0x0024) > Block: Size 8192 Version 3 Upper 48 (0x0030) > LSN: logid 4 recoff 0x24b6ee88 Special 8192 (0x2000) > Items: 4 Free Space: 12 > Length (including item array): 40 > > <Data> ------ > Item 1 -- Length: 2034 Offset: 6156 (0x180c) Flags: USED > Item 2 -- Length: 2034 Offset: 4120 (0x1018) Flags: USED > Item 3 -- Length: 2034 Offset: 2084 (0x0824) Flags: USED > Item 4 -- Length: 2034 Offset: 48 (0x0030) Flags: USED > > > Block 58591 ******************************************************** > <Header> ----- > Block Offset: 0x1c9be000 Offsets: Lower 12858 (0x323a) > Block: Size 28160 Version 73 Upper 14900 (0x3a34) > LSN: logid 627535472 recoff 0x3a693b22 Special 9506 (0x2522) > Items: 3209 Free Space: 2042 > Length (including item array): 8192 > > Error: Invalid header information. > > Error: End of block encountered within the header. Bytes read: 8192. > > > Block 58592 ******************************************************** > <Header> ----- > Block Offset: 0x1c9c0000 Offsets: Lower 36 (0x0024) > Block: Size 8192 Version 3 Upper 48 (0x0030) > LSN: logid 4 recoff 0x24b73110 Special 8192 (0x2000) > Items: 4 Free Space: 12 > Length (including item array): 40 > > <Data> ------ > Item 1 -- Length: 2034 Offset: 6156 (0x180c) Flags: USED > Item 2 -- Length: 2034 Offset: 4120 (0x1018) Flags: USED > Item 3 -- Length: 2034 Offset: 2084 (0x0824) Flags: USED > Item 4 -- Length: 2034 Offset: 48 (0x0030) Flags: USED > > > *** End of Requested Range Encountered. Last Block Read: 58592 *** It seems obvious, that the block in question is absolutely screwed, as the block size as well as the version don't match. What's the best cure? Can I just wipe out the block with something like: # dd if=/dev/zero of=base/296788/302602 seek=58591 bs=8192 count=1 This is PostgreSQL 8.1.9 (from Debian 8.1.9-0etch1), but it has been running earlier 8.1.x versions in the past. Regards Markus
Markus Schiltknecht <markus@bluegap.ch> writes:
>> Block 58591 ********************************************************
>> <Header> -----
>> Block Offset: 0x1c9be000 Offsets: Lower 12858 (0x323a)
>> Block: Size 28160 Version 73 Upper 14900 (0x3a34)
>> LSN: logid 627535472 recoff 0x3a693b22 Special 9506 (0x2522)
>> Items: 3209 Free Space: 2042
>> Length (including item array): 8192
>>
>> Error: Invalid header information.
Hm, looks suspiciously ASCII-like. If you examine the page as text,
is it recognizable? We've seen cases where, for instance, a page
of a system's mail spool had replaced a database page. It's hard to
be sure whether that sort of thing is a hardware error or an operating
system bug.
> What's the best cure? Can I just wipe out the block with something like:
> # dd if=/dev/zero of=base/296788/302602 seek=58591 bs=8192 count=1
Yeah, try that. I think the net effect will be that some wide (toasted)
values will be truncated.
regards, tom lane
Hi, Tom Lane wrote: > Hm, looks suspiciously ASCII-like. If you examine the page as text, > is it recognizable? Doh! Yup, is recognizable. It looks like some PHP serialized output: png%";i:84;s:24:"%InfoToolIconActive.png%";i:85;s:29:"%InfoToolIconHighlighted.png%";i:86;s:26:"%InfoToolIconInactive.png%";i:87;.... We do store serialized PHP objects like the above one in the database, so it's probably not a mail spool. >> What's the best cure? Can I just wipe out the block with something like: >> # dd if=/dev/zero of=base/296788/302602 seek=58591 bs=8192 count=1 > > Yeah, try that. I think the net effect will be that some wide (toasted) > values will be truncated. Thank you for you guidance. I've done that and hope the running pg_dump goes through fine. Regards Markus
Hi,
Markus Schiltknecht wrote:
> I've done that (zeroing out the pg_toast table page) and hope
> the running pg_dump goes through fine.
Unfortunately, pg_dump didn't go through. I already did some REINDEXing
and VACUUMing. Vacuum fixed something (sorry, don't I recall the
message), but SELECTing from the table still fails, as some fields of
course reference now deleted toast data:
ERROR: missing chunk number 0 for toast value 479667
Is there any way to find this toast value 479667? Something like the
following query:
SELECT id FROM my_corrupt_table
WHERE pg_is_toasted('column')
AND pg_toasted_value('column') == 479667;
Again, I'm fine loosing the data in the toasted fields in question. But
I'd rather like to save the remaining data. I've already tried UPDATing
a field I've found manually (by trial and error). That helped and I can
now select that row again (having lost only the field's content).
Is there some sort of error recovery tool? Or should VACUUM fix such
things on special request?
Regards
Markus