Thread: Corrpupt Data File
Hi,
I have a postgres data base that is probably about 2gigs in size
spread across a couple of tables. The orders table is about 500megs in
size and is mostly writes.
When running "select count(1) from orders" on the database this
evening, the DB responded with "ERROR: Invalid page header in block 57879
of orders". After doing a bunch of reading online, I downloaded
pg_filedump and ran it against the offending block with these results:
*****************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 1.1
*
* File: 309928249
* Options used: -R 57879
*
* Dump created on: Thu Feb 26 01:19:09 2004
*****************************************************************
Block 57879 ******************************************************
<Header> -----
Block Offset: 0x1c42e000 Offsets: Lower 3 (0x0003)
Block: Size 16384 Version 24 Upper 10679 (0x29b7)
LSN: logid 808988729 recoff 0x00000037 Special 79 (0x004f)
Items: -4 Free Space: 10676
Length (including item array): 24
Error: Invalid header information.
<Data> ------
Error: Item index corrupt on block. Offset: <-4>.
<Special Section> -----
Error: Invalid special section encountered.
*** End of Requested Range Encountered. Last Block Read: 57879 ***
The blocks both before and after this block are fine. (I'll include the
header from one at the end of this message). I should mention that I am
running postgres 7.3 on AMD-64 hardware with kernel 2.4.25.
deleting the indexes from the table did not help. Is there a tool or
method that that I could use to simply skip the bad data and at least
recover the good blocks?
Thanks
-Alex
*****************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 1.1
*
* File: 309928249
* Options used: -R 57880
*
* Dump created on: Thu Feb 26 01:24:00 2004
*****************************************************************
Block 57880 ******************************************************
<Header> -----
Block Offset: 0x1c430000 Offsets: Lower 336 (0x0150)
Block: Size 8192 Version 1 Upper 336 (0x0150)
LSN: logid 17 recoff 0x5e6c8704 Special 8192 (0x2000)
Items: 79 Free Space: 0
Length (including item array): 340
<Data> ------
Item 1 -- Length: 102 Offset: 8088 (0x1f98) Flags: USED
Item 2 -- Length: 94 Offset: 7992 (0x1f38) Flags: USED
Item 3 -- Length: 120 Offset: 7872 (0x1ec0) Flags: USED
Item 4 -- Length: 120 Offset: 7752 (0x1e48) Flags: USED
Item 5 -- Length: 120 Offset: 7632 (0x1dd0) Flags: USED
Item 6 -- Length: 102 Offset: 7528 (0x1d68) Flags: USED
Item 7 -- Length: 95 Offset: 7432 (0x1d08) Flags: USED
Item 8 -- Length: 120 Offset: 7312 (0x1c90) Flags: USED
Item 9 -- Length: 102 Offset: 7208 (0x1c28) Flags: USED
Item 10 -- Length: 102 Offset: 7104 (0x1bc0) Flags: USED
Item 11 -- Length: 104 Offset: 7000 (0x1b58) Flags: USED
Item 12 -- Length: 104 Offset: 6896 (0x1af0) Flags: USED
Item 13 -- Length: 120 Offset: 6776 (0x1a78) Flags: USED
Item 14 -- Length: 72 Offset: 6704 (0x1a30) Flags: USED
Item 15 -- Length: 72 Offset: 6632 (0x19e8) Flags: USED
Item 16 -- Length: 120 Offset: 6512 (0x1970) Flags: USED
Item 17 -- Length: 96 Offset: 6416 (0x1910) Flags: USED
Item 18 -- Length: 104 Offset: 6312 (0x18a8) Flags: USED
Item 19 -- Length: 104 Offset: 6208 (0x1840) Flags: USED
Item 20 -- Length: 72 Offset: 6136 (0x17f8) Flags: USED
Item 21 -- Length: 120 Offset: 6016 (0x1780) Flags: USED
Item 22 -- Length: 96 Offset: 5920 (0x1720) Flags: USED
Item 23 -- Length: 72 Offset: 5848 (0x16d8) Flags: USED
Item 24 -- Length: 72 Offset: 5776 (0x1690) Flags: USED
Item 25 -- Length: 72 Offset: 5704 (0x1648) Flags: USED
Item 26 -- Length: 120 Offset: 5584 (0x15d0) Flags: USED
Item 27 -- Length: 120 Offset: 5464 (0x1558) Flags: USED
Item 28 -- Length: 101 Offset: 5360 (0x14f0) Flags: USED
Item 29 -- Length: 72 Offset: 5288 (0x14a8) Flags: USED
Item 30 -- Length: 94 Offset: 5192 (0x1448) Flags: USED
Item 31 -- Length: 102 Offset: 5088 (0x13e0) Flags: USED
Item 32 -- Length: 72 Offset: 5016 (0x1398) Flags: USED
Item 33 -- Length: 120 Offset: 4896 (0x1320) Flags: USED
Item 34 -- Length: 93 Offset: 4800 (0x12c0) Flags: USED
Item 35 -- Length: 72 Offset: 4728 (0x1278) Flags: USED
Item 36 -- Length: 120 Offset: 4608 (0x1200) Flags: USED
Item 37 -- Length: 102 Offset: 4504 (0x1198) Flags: USED
Item 38 -- Length: 94 Offset: 4408 (0x1138) Flags: USED
Item 39 -- Length: 101 Offset: 4304 (0x10d0) Flags: USED
Item 40 -- Length: 120 Offset: 4184 (0x1058) Flags: USED
Item 41 -- Length: 120 Offset: 4064 (0x0fe0) Flags: USED
Item 42 -- Length: 103 Offset: 3960 (0x0f78) Flags: USED
Item 43 -- Length: 103 Offset: 3856 (0x0f10) Flags: USED
Item 44 -- Length: 96 Offset: 3760 (0x0eb0) Flags: USED
Item 45 -- Length: 102 Offset: 3656 (0x0e48) Flags: USED
Item 46 -- Length: 101 Offset: 3552 (0x0de0) Flags: USED
Item 47 -- Length: 72 Offset: 3480 (0x0d98) Flags: USED
Item 48 -- Length: 72 Offset: 3408 (0x0d50) Flags: USED
Item 49 -- Length: 72 Offset: 3336 (0x0d08) Flags: USED
Item 50 -- Length: 72 Offset: 3264 (0x0cc0) Flags: USED
Item 51 -- Length: 88 Offset: 3176 (0x0c68) Flags: USED
Item 52 -- Length: 72 Offset: 3104 (0x0c20) Flags: USED
Item 53 -- Length: 96 Offset: 3008 (0x0bc0) Flags: USED
Item 54 -- Length: 95 Offset: 2912 (0x0b60) Flags: USED
Item 55 -- Length: 72 Offset: 2840 (0x0b18) Flags: USED
Item 56 -- Length: 109 Offset: 2728 (0x0aa8) Flags: USED
Item 57 -- Length: 109 Offset: 2616 (0x0a38) Flags: USED
Item 58 -- Length: 120 Offset: 2496 (0x09c0) Flags: USED
Item 59 -- Length: 120 Offset: 2376 (0x0948) Flags: USED
Item 60 -- Length: 117 Offset: 2256 (0x08d0) Flags: USED
Item 61 -- Length: 94 Offset: 2160 (0x0870) Flags: USED
Item 62 -- Length: 95 Offset: 2064 (0x0810) Flags: USED
Item 63 -- Length: 72 Offset: 1992 (0x07c8) Flags: USED
Item 64 -- Length: 95 Offset: 1896 (0x0768) Flags: USED
Item 65 -- Length: 120 Offset: 1776 (0x06f0) Flags: USED
Item 66 -- Length: 102 Offset: 1672 (0x0688) Flags: USED
Item 67 -- Length: 101 Offset: 1568 (0x0620) Flags: USED
Item 68 -- Length: 94 Offset: 1472 (0x05c0) Flags: USED
Item 69 -- Length: 120 Offset: 1352 (0x0548) Flags: USED
Item 70 -- Length: 95 Offset: 1256 (0x04e8) Flags: USED
Item 71 -- Length: 120 Offset: 1136 (0x0470) Flags: USED
Item 72 -- Length: 120 Offset: 1016 (0x03f8) Flags: USED
Item 73 -- Length: 120 Offset: 896 (0x0380) Flags: USED
Item 74 -- Length: 110 Offset: 784 (0x0310) Flags: USED
Item 75 -- Length: 94 Offset: 688 (0x02b0) Flags: USED
Item 76 -- Length: 102 Offset: 584 (0x0248) Flags: USED
Item 77 -- Length: 72 Offset: 512 (0x0200) Flags: USED
Item 78 -- Length: 72 Offset: 440 (0x01b8) Flags: USED
Item 79 -- Length: 101 Offset: 336 (0x0150) Flags: USED
*** End of Requested Range Encountered. Last Block Read: 57880 ***
Alex Derbes <acd@brillig.org> writes:
> deleting the indexes from the table did not help. Is there a tool or
> method that that I could use to simply skip the bad data and at least
> recover the good blocks?
Just zero out the broken page, eg with dd from /dev/zero. Note it
might be a good idea to shut down the postmaster while you do so,
else there could be a copy of that page in Postgres shared buffers,
which'd mask your attempted fix.
regards, tom lane
for the annals of postgres maintenance I got my DB running by using DD to copy the blocks before the corrupt block, then add block from /dev/zero, then add the remaining blocks from the file. I discovered the block size by looking at he output of pg_filedump. To find the offending file select rel_filename from pg_class where relname = TABLE_NAME. My original error was : "ERROR: Invalid page header in block 57879 of orders" -- thus the bad block is #57879 cp 309928249 orders.bak dd bs=8192 count=57879 if=309928249 of=out dd bs=8192 count=1 seek=57879 if=/dev/zero of=out dd bs=8192 skip=57880 seek=57880 if=309928249 of=out mv out 309928249 Many thanks to Tom for his quick reply & Good luck to all others. Thanks, -Alex On Thu, 26 Feb 2004, Tom Lane wrote: > Alex Derbes <acd@brillig.org> writes: > > deleting the indexes from the table did not help. Is there a tool or > > method that that I could use to simply skip the bad data and at least > > recover the good blocks? > > Just zero out the broken page, eg with dd from /dev/zero. Note it > might be a good idea to shut down the postmaster while you do so, > else there could be a copy of that page in Postgres shared buffers, > which'd mask your attempted fix. > > regards, tom lane > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-= acd@brillig.org - acd@cwru.edu f720.920.1148 - c415.572.1669 http://www.brillig.org/~acd 01 1010 100