Re: "invalid page header in block 597621 of relation..."error

Hi,

I just had this error in my database:

bugasbase2=# SELECT count(*) from mba_data_base;
ERROR: invalid page header in block 597621 of relation "mba_data_base"

Any ideas whats going on? Am a bit worried as this is my production
database.

Thanks for any assistance

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> bugasbase2=# SELECT count(*) from mba_data_base;
> ERROR: invalid page header in block 597621 of relation "mba_data_base"

Sounds like a data corruption problem :-(. Do you want to pull out that
page and see what's in it? Something like

dd bs=8k skip=73333 count=1 if=relationfile | od -x

where you need to use oid2name or something similar to determine which
file contains that relation, and then append ".4" because block 597621
would be in the fifth segment of the file. (I got 73333 from
"select 597621 % 131072")

regards, tom lane

On 23/11/05 8:55 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> bugasbase2=# SELECT count(*) from mba_data_base;
>> ERROR: invalid page header in block 597621 of relation "mba_data_base"
>
> Sounds like a data corruption problem :-(. Do you want to pull out that
> page and see what's in it? Something like
>
> dd bs=8k skip=73333 count=1 if=relationfile | od -x
>
> where you need to use oid2name or something similar to determine which
> file contains that relation, and then append ".4" because block 597621
> would be in the fifth segment of the file. (I got 73333 from
> "select 597621 % 131072")

Thanks for the help.... Here is the output:

adam(at)bugsdb:/opt$ dd bs=8k skip=73333 count=1 if=134401991.4 | od -x
0000000 0000 0000 0000 0000 0000 0000 0000 0000
*
1+0 records in
1+0 records out
0010000 1d9e 201c 0fa0 0000 0010 0000 0000 000b
0010020 0ca6 19fb 1797 0ab4 000a 0000 0000 0001
0010040 01af 0000 000a 0000 0000 0001 0ca7 0000
0010060 0012 0000 0000 0010 0002 1190 068f 0c9a
0010100 0391 0000 0012 0000 0000 000f 0002 10aa
0010120 0971 06f3 184c 0000 0010 0000 ffff 0010
0010140 0d14 0f78 1c5c 066d 0012 0000 0000 000f
0010160 0009 0ff5 26c1 077a 0c8a 0000 0012 0000
0010200 0000 000f 0009 0ff5 26c1 077a 0c8a 0000
0010220 0008 0000 0000 0000 0002 0000 0000 0000
0010240 0000 0000 0000 0000 0000 0000 0000 0000
0010260 0000 0000 0000 0000 0001 0000 0000 0000
0010300 0000 0000 0008 0000 7059 3376 0004 0000
0010320 1d53 003b 0021 0000 0000 0000 0009 1e75
0010340 0006 004d 0813 ff28 fedf fffb ff87 1803
0010360 0000 0000 221c 0ea7 e6dc 009c 1439 0000
0010400 0017 0000 4353 522d 7461 6f69 305f 2036
0010420 3228 4537 3031 0029 0012 0000 0000 000e
0010440 0056 0a6a 1a0a 1a0a 1a2c 0000 0012 0000
0010460 0000 000e 004e 1b26 0dda 03e0 238c 0000
0010500 000a 0000 0000 0001 004c 0000 000a 0000
0010520 0000 0001 004a 0000 0012 0000 0000 000e
0010540 002c 2208 12b6 2232 0ce4 0000 0012 0000
0010560 0000 000f 0027 2448 0aad 0001 1dec 0000
0010600 0019 0000 000b 0000 0007 0000 0001 0000
0010620 000c 0000 0000 0001 0199 1388 000c 0000
0010640 0000 0001 0ca7 1388 0012 0000 0000 000e
0010660 0073 16c9 0d05 0d05 0ce4 0000 0012 0000
0010700 0000 000e 0047 2404 1e70 081d 23f0 0000
0010720 000a 0000 0000 0001 003c 0000 000a 0000
0010740 0000 0001 011a 0000 000a 0000 0000 0001
0010760 1438 0000 000c 0000 0001 0001 0002 0890
0011000 000a 0000 ffff 0002 1d4c 0000 0008 0000
0011020 0000 0001 000a 0000 0000 0001 003c 0000
0011040 0010 0000 ffff 0010 2591 04b3 1a9d 07e3
0011060 0008 0000 0000 0001 000a 0000 0000 0001
0011100 0009 0000 0012 0000 0000 0010 0001 22c2
0011120 131e 07da 21f5 0000 0012 0000 0000 4010
0011140 0001 1c60 234b 2081 0363 0000 0010 0000
0011160 ffff 000f 1e06 0395 1980 164e 0012 0000
0011200 0000 000d 019b 08d8 234b 2081 03e8 0000
0011220 0012 0000 0000 000d 0ca6 1c91 237a 0d8f
0011240 0fa0 0000 000c 0000 0000 0001 0199 1388
0011260 000c 0000 0000 0001 0ca7 1388 0012 0000
0011300 0000 0010 0001 22c2 131e 07da 21f5 0000
0011320 0012 0000 0000 4010 0001 1c60 234b 2081
0011340 0363 0000 0010 0000 ffff 000f 1e06 0395
0011360 1980 164e 0012 0000 0000 000f 0008 2032
0011400 0390 1032 0b5e 0000 0012 0000 0000 000f
0011420 0008 2032 0390 1032 0b5e 0000 0008 0000
0011440 0000 0000 0002 0000 0000 0000 0000 0000
0011460 0000 0000 0000 0000 0000 0000 0000 0000
0011500 0000 0000 0001 0000 0000 0000 0000 0000
0011520 0008 0000 7059 3376 0004 0000 1d53 003b
0011540 0021 0000 0000 0000 0009 1e75 0005 004d
0011560 0813 ff28 fedf fffb ff87 1803 0000 0000
0011600 221b 0ea7 e6db 009c 1439 0000 0017 0000
0011620 4353 522d 7461 6f69 305f 2035 3228 4137
0011640 3031 0029 0012 0000 0000 000e 005b 229f
0011660 18db 18db 1900 0000 0012 0000 0000 000e
0011700 004f 07c6 01f1 1404 0ed8 0000 000a 0000
0011720 0000 0001 0057 0000 000a 0000 0000 0001
0011740 0045 0000 0012 0000 0000 000d 0033 1608
0011760 1186 1eec 0fa0 0000 0012 0000 0000 000e
0012000 002a 113e 066c 136d 2134 0000 0019 0000
0012020 000a 0000 0007 0000 0001 0000 000c 0000
0012040 0000 0001 018c 1388 000c 0000 0000 0001
0012060 0ca7 1388 000c 0000 0000 0001 0073 1388
0012100 0012 0000 0000 000f 0032 02a5 25bd 00a9
0012120 1356 0000 000a 0000 0000 0001 0058 0000
0012140 000a 0000 0000 0001 00c9 0000 000a 0000
0012160 0000 0001 1f96 0000 000c 0000 0001 0001
0012200 0001 171f 0010 0000 ffff 0010 1eb1 0594
0012220 1652 0b29 0008 0000 0000 0001 000a 0000
0012240 0000 0001 0058 0000 000a 0000 0000 0001
0012260 0001 0000 0008 0000 0000 0001 000a 0000
0012300 0000 0001 000b 0000 0012 0000 0000 000f
0012320 0002 0207 1d03 15de 18a6 0000 0012 0000
0012340 0000 0010 0001 2436 1511 1a71 1510 0000
0012360 0010 0000 ffff 0010 1b8a 0a20 0cd1 249b
0012400 0012 0000 0000 000e 018a 1661 11fe 0c9e
0012420 11f8 0000 0010 0000 0000 000c 0ca6 1f0d
0012440 1cef 1a3e 000c 0000 0000 0001 018c 1388
0012460 000c 0000 0000 0001 0ca7 1388 0012 0000
0012500 0000 000f 0002 0207 1d03 15de 18a6 0000
0012520 0012 0000 0000 0010 0001 2436 1511 1a71
0012540 1510 0000 0010 0000 ffff 0010 1b8a 0a20
0012560 0cd1 249b 0012 0000 0000 000f 000a 188f
0012600 07f6 09d3 0d20 0000 0012 0000 0000 000f
0012620 000a 188f 07f6 09d3 0d20 0000 0008 0000
0012640 0000 0000 0002 0000 0000 0000 0000 0000
0012660 0000 0000 0000 0000 0000 0000 0000 0000
0012700 0000 0000 0001 0000 0000 0000 0000 0000
0012720 0008 0000 7059 3376 0004 0000 1d53 003b
0012740 0021 0000 0000 0000 0009 1e75 0004 004d
0012760 0813 ff28 fedf fffb ff87 1803 0000 0000
0013000 221a 0ea7 e6da 009c 1439 0000 0016 0000
0013020 4353 522d 7461 6f69 305f 2034 3228 4d37
0013040 2932 0000 0012 0000 0000 000e 0058 0339
0013060 1ae0 1cab 11f8 0000 0012 0000 0000 000e
0013100 0052 014b 1d6f 154a 0960 0000 000a 0000
0013120 0000 0001 0054 0000 000a 0000 0000 0001
0013140 004d 0000 0012 0000 0000 000e 002c 0f2c
0013160 0b57 0801 26ac 0000 0012 0000 0000 000f
0013200 0028 00a6 0b9a 13b0 01cc 0000 0019 0000
0013220 0009 0000 0007 0000 0001 0000 000a 0000
0013240 0000 0001 0179 0000 000a 0000 0000 0001
0013260 0ca5 0000 0012 0000 0000 000e 005e 0165
0013300 0594 1652 0b54 0000 0012 0000 0000 000d
0013320 0045 0487 106a 2319 1388 0000 000a 0000
0013340 0000 0001 006d 0000 000a 0000 0000 0001
0013360 00d3 0000 000a 0000 0000 0001 2581 0000
0013400 000c 0000 0001 0001 0001 1c8d 000a 0000
0013420 0000 0001 0001 0000 0008 0000 0000 0001
0013440 000a 0000 0000 0001 006d 0000 000a 0000
0013460 0000 0001 0001 0000 0008 0000 0000 0001
0013500 000a 0000 0000 0001 000c 0000 0012 0000
0013520 0000 0010 0001 25e3 1314 0a3e 0e3e 0000
0013540 0010 0000 ffff 4010 1965 0312 0555 0125
0013560 0012 0000 0000 400f 0001 2193 2165 115a
0013600 0f96 0000 0012 0000 0000 000e 0179 1965
0013620 0312 0555 012c 0000 0012 0000 0000 000d
0013640 0ca6 2193 2165 115a 0fa0 0000 000a 0000
0013660 0000 0001 0179 0000 000a 0000 0000 0001
0013700 0ca5 0000 0012 0000 0000 0010 0001 25e3
0013720 1314 0a3e 0e3e 0000 0010 0000 ffff 4010
0013740 1965 0312 0555 0125 0012 0000 0000 400f
0013760 0001 2193 2165 115a 0f96 0000 0012 0000
0014000 0000 000f 000b 20d4 0aaa 014b 118a 0000
0014020 0012 0000 0000 000f 000b 20d4 0aaa 014b
0014040 118a 0000 0008 0000 0000 0000 0002 0000
0014060 0000 0000 0000 0000 0000 0000 0000 0000
0014100 0000 0000 0000 0000 0000 0000 0001 0000
0014120 0000 0000 0000 0000 0008 0000 7059 3376
0014140 0004 0000 1d53 003b 0021 0000 0000 0000
0014160 0009 1e75 0003 004d 0813 ff28 fedf fffb
0014200 ff87 1803 0000 0000 2219 0ea7 e6d9 009c
0014220 1439 0000 0016 0000 4353 522d 7461 6f69
0014240 305f 2033 3228 4937 2932 0000 0012 0000
0014260 0000 000e 0061 12a4 1c68 1c68 1c84 0000
0014300 0012 0000 0000 000e 0050 1809 210d 1508
0014320 17d4 0000 000a 0000 0000 0001 0056 0000
0014340 000a 0000 0000 0001 004b 0000 0012 0000
0014360 0000 000e 002d 059a 147f 2017 1004 0000
0014400 0012 0000 0000 000e 0029 2326 1c5b 2064
0014420 206c 0000 0019 0000 0008 0000 0007 0000
0014440 0001 0000 000c 0000 0000 0001 0165 1388
0014460 000c 0000 0000 0001 0ca6 1388 0012 0000
0014500 0000 000e 0054 0341 0d05 0d05 0ce4 0000
0014520 0012 0000 0000 000f 003f 06c6 1b1a 1e4b
0014540 041a 0000 000a 0000 0000 0001 0058 0000
0014560 000a 0000 0000 0001 0111 0000 000a 0000
0014600 0000 0001 2182 0000 000c 0000 0001 0001
0014620 0002 07d8 0010 0000 ffff 0010 1eb1 0594
0014640 1652 0b29 0008 0000 0000 0001 000a 0000
0014660 0000 0001 0058 0000 000a 0000 0000 0001
0014700 0001 0000 0008 0000 0000 0001 000a 0000
0014720 0000 0001 000b 0000 0012 0000 0000 000f
0014740 0002 2696 1b71 19ea 1860 0000 0012 0000
0014760 0000 400f 0002 2566 162e 093e 055a 0000
0015000 0010 0000 ffff 4010 109c 0a91 1f8a 0590
0015020 0012 0000 0000 000e 0168 11de 162e 093e
0015040 0578 0000 0010 0000 0000 000c 0ca6 2424
0015060 0a91 1f8a 000c 0000 0000 0001 0165 1388
0015100 000c 0000 0000 0001 0ca6 1388 0012 0000
0015120 0000 000f 0002 2696 1b71 19ea 1860 0000
0015140 0012 0000 0000 400f 0002 2566 162e 093e
0015160 055a 0000 0010 0000 ffff 4010 109c 0a91
0015200 1f8a 0590 0012 0000 0000 000f 000a 188f
0015220 07f6 09d3 0d20 0000 0012 0000 0000 000f
0015240 000a 188f 07f6 09d3 0d20 0000 0008 0000
0015260 0000 0000 0002 0000 0000 0000 0000 0000
0015300 0000 0000 0000 0000 0000 0000 0000 0000
0015320 0000 0000 0001 0000 0000 0000 0000 0000
0015340 0008 0000 7059 3376 0004 0000 1d53 003b
0015360 0021 0000 0000 0000 0009 1e75 0002 004d
0015400 0813 ff28 fedf fffb ff87 1803 0000 0000
0015420 2218 0ea7 e6d8 009c 1439 0000 0016 0000
0015440 4353 522d 7461 6f69 305f 2032 3228 4537
0015460 2932 0000 000c 0000 0000 0001 0047 1b58
0015500 0012 0000 0000 000e 0050 19ce 025e 025e
0015520 0258 0000 000c 0000 0000 0001 0041 1388
0015540 000a 0000 0000 0001 004b 0000 0012 0000
0015560 0000 000e 0020 24e5 1349 085b 1130 0000
0015600 0012 0000 0000 000e 0028 0150 2612 1f93
0015620 0dac 0000 0019 0000 0007 0000 0007 0000
0015640 0001 0000 000c 0000 0000 0001 0154 1388
0015660 000c 0000 0000 0001 0ca3 1388 0012 0000
0015700 0000 000d 0041 2357 182e 1299 2328 0000
0015720 0012 0000 0000 000e 0031 0ec7 1e9d 20ba
0015740 0ed8 0000 000a 0000 0000 0001 003c 0000
0015760 000a 0000 0000 0001 00a5 0000 000a 0000
0016000 0000 0001 10ce 0000 000c 0000 0001 0001
0016020 0001 0ced 000a 0000 ffff 0002 1d4c 0000
0016040 0008 0000 0000 0001 000a 0000 0000 0001
0016060 003c 0000 0010 0000 ffff 0010 2591 04b3
0016100 1a9d 07e3 0008 0000 0000 0001 000a 0000
0016120 0000 0001 0009 0000 0012 0000 0000 000f
0016140 0004 04c4 0255 1e79 1c5c 0000 0012 0000
0016160 0000 400f 0002 075c 1b6a 0ccd 2562 0000
0016200 0012 0000 0000 4010 0003 1342 1afb 05ac
0016220 0b09 0000 0012 0000 0000 000e 0156 1ae4
0016240 1b6a 0ccd 2580 0000 0012 0000 0000 000d
0016260 0ca6 26ca 1afb 05ac 0bb8 0000 000c 0000
0016300 0000 0001 0154 1388 000c 0000 0000 0001
0016320 0ca3 1388 0012 0000 0000 000f 0004 04c4
0016340 0255 1e79 1c5c 0000 0012 0000 0000 400f
0016360 0002 075c 1b6a 0ccd 2562 0000 0012 0000
0016400 0000 4010 0003 1342 1afb 05ac 0b09 0000
0016420 0012 0000 0000 000f 0008 2032 0390 1032
0016440 0b5e 0000 0012 0000 0000 000f 0008 2032
0016460 0390 1032 0b5e 0000 0008 0000 0000 0000
0016500 0002 0000 0000 0000 0000 0000 0000 0000
0016520 0000 0000 0000 0000 0000 0000 0000 0000
0016540 0001 0000 0001 0000 0000 0000 0008 0000
0016560 7059 3376 0004 0000 1d53 003b 0021 0000
0016600 0000 0000 0009 1e75 0001 004d 0813 ff28
0016620 fedf fffb ff87 1803 0000 0000 2217 0ea7
0016640 e6d7 009c 1439 0000 0016 0000 4353 522d
0016660 7461 6f69 305f 2031 3228 4137 2932 0000
0016700 0012 0000 0000 000d 005a 0154 2382 2382
0016720 2328 0000 0012 0000 0000 000e 0049 12ae
0016740 17c6 255d 0898 0000 000a 0000 0000 0001
0016760 0052 0000 000c 0000 0000 0001 0040 1388
0017000 0012 0000 0000 000f 0021 1c70 024f 22b6
0017020 1f04 0000 0012 0000 0000 000e 002a 0a17
0017040 21f5 1c6d 0898 0000 0019 0000 0006 0000
0017060 0007 0000 0001 0000 000c 0000 0000 0001
0017100 0148 1388 000c 0000 0000 0001 0ca7 1388
0017120 0012 0000 0000 000e 0054 18db 18db 18db
0017140 1900 0000 0012 0000 0000 000e 0026 17e7
0017160 0fbd 213b 11f8 0000 000a 0000 0000 0001
0017200 0058 0000 000a 0000 0000 0001 00b8 0000
0017220 000a 0000 0000 0001 1ef3 0000 000c 0000
0017240 0001 0001 0001 0dc0 0010 0000 ffff 0010
0017260 1eb1 0594 1652 0b29 0008 0000 0000 0001
0017300 000a 0000 0000 0001 0058 0000 000a 0000
0017320 0000 0001 0001 0000 0008 0000 0000 0001
0017340 000a 0000 0000 0001 000b 0000 0012 0000
0017360 0000 000f 0001 11e8 2467 248b 0014 0000
0017400 0012 0000 0000 0010 0001 0f29 16a4 26a4
0017420 1f56 0000 0010 0000 ffff 0010 117a 172e
0017440 09c1 0198 0012 0000 0000 000d 0147 045e
0017460 106b 006b 07d0 0000 0010 0000 0000 000c
0017500 0ca7 020d 0fe1 1d4f 000c 0000 0000 0001
0017520 0148 1388 000c 0000 0000 0001 0ca7 1388
0017540 0012 0000 0000 000f 0001 11e8 2467 248b
0017560 0014 0000 0012 0000 0000 0010 0001 0f29
0017600 16a4 26a4 1f56 0000 0010 0000 ffff 0010
0017620 117a 172e 09c1 0198 0012 0000 0000 000f
0017640 000a 188f 07f6 09d3 0d20 0000 0012 0000
0017660 0000 000f 000a 188f 07f6 09d3 0d20 0000
0017700 0008 0000 0000 0000 0002 0000 0000 0000
0017720 0000 0000 0000 0000 0000 0000 0000 0000
0017740 0000 0000 0000 0000 0001 0000 0000 0000
0017760 0000 0000 0008 0000 7059 3376 0004 0000
0020000

Unfortunately I have no idea what any of that means!

Does it help?

Thanks again

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> Thanks for the help.... Here is the output:

> adam(at)bugsdb:/opt$ dd bs=8k skip=73333 count=1 if=134401991.4 | od -x
> 0000000 0000 0000 0000 0000 0000 0000 0000 0000
> *
> 0010000 1d9e 201c 0fa0 0000 0010 0000 0000 000b
> 0010020 0ca6 19fb 1797 0ab4 000a 0000 0000 0001
> 0010040 01af 0000 000a 0000 0000 0001 0ca7 0000
> 0010060 0012 0000 0000 0010 0002 1190 068f 0c9a
> ...

> Unfortunately I have no idea what any of that means!

The second half of the page looks reasonable, but the first half
is all zeroes :-(. (dd uses "*" to mean "same as above".)

It's unlikely that this is Postgres' fault; I can't think of any
plausible pathology within PG that would so carefully zero out just
half of a page. What seems more likely is that the block size on the
underlying filesystem is 4K, and that either a kernel bug or a disk
drive error has caused the system to drop the contents of one block.
If I had to bet with no additional info, I'd bet on kernel bug. What's
the platform exactly, and what filesystem are you using?

regards, tom lane

On 23/11/05 9:36 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> Thanks for the help.... Here is the output:
>
>> adam(at)bugsdb:/opt$ dd bs=8k skip=73333 count=1 if=134401991.4 | od -x
>> 0000000 0000 0000 0000 0000 0000 0000 0000 0000
>> *
>> 0010000 1d9e 201c 0fa0 0000 0010 0000 0000 000b
>> 0010020 0ca6 19fb 1797 0ab4 000a 0000 0000 0001
>> 0010040 01af 0000 000a 0000 0000 0001 0ca7 0000
>> 0010060 0012 0000 0000 0010 0002 1190 068f 0c9a
>> ...
>
>> Unfortunately I have no idea what any of that means!
>
> The second half of the page looks reasonable, but the first half
> is all zeroes :-(. (dd uses "*" to mean "same as above".)
>
> It's unlikely that this is Postgres' fault; I can't think of any
> plausible pathology within PG that would so carefully zero out just
> half of a page. What seems more likely is that the block size on the
> underlying filesystem is 4K, and that either a kernel bug or a disk
> drive error has caused the system to drop the contents of one block.
> If I had to bet with no additional info, I'd bet on kernel bug. What's
> the platform exactly, and what filesystem are you using?

Linux bugsdb 2.4.26 #1 SMP Wed May 5 12:08:48 BST 2004 i686 unknown

/dev/md2 on /pg_data type xfs (rw,noatime)

/dev/md2 is a software RAID5 device. Also

PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC 2.95.4

This table is only ever COPY'd to from data files, no updates or deletes, if
I could find out which data file this bit comes from I could just reupload
that file... Is it possible to tell what the data actually is from the data
I sent?

Thanks again

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> This table is only ever COPY'd to from data files, no updates or deletes, if
> I could find out which data file this bit comes from I could just reupload
> that file... Is it possible to tell what the data actually is from the data
> I sent?

You might try dumping the page with "od -c" instead of "-x" and looking
to see if there are any recognizable strings. Also try the same on the
preceding or following pages. Or try dumping what's on the preceding
and following pages, eg
select * from tab where ctid = '(597620,1)';
select * from tab where ctid = '(597620,2)';
...
(run the item number up high enough to be sure you've seen all the live
rows on the page).

To get the file back in a usable state before you add more data, you'll
want to zero the bad block out completely, and then do a VACUUM to see
if there are any other damaged pages.

regards, tom lane

On 23/11/05 9:55 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> This table is only ever COPY'd to from data files, no updates or deletes, if
>> I could find out which data file this bit comes from I could just reupload
>> that file... Is it possible to tell what the data actually is from the data
>> I sent?
>
> You might try dumping the page with "od -c" instead of "-x" and looking
> to see if there are any recognizable strings. Also try the same on the
> preceding or following pages. Or try dumping what's on the preceding
> and following pages, eg
> select * from tab where ctid = '(597620,1)';
> select * from tab where ctid = '(597620,2)';
> ...
> (run the item number up high enough to be sure you've seen all the live
> rows on the page).
>
> To get the file back in a usable state before you add more data, you'll
> want to zero the bad block out completely, and then do a VACUUM to see
> if there are any other damaged pages.

Aha, the select statements above made it easy to identify the correct file.

Whats the best way to zero the bad block? Could I not just delete that data
and reload it?

Thanks again

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> Whats the best way to zero the bad block?

Probably dd from /dev/zero, along the lines of

dd bs=8k seek=597621 count=1 conv=notrunc if=/dev/zero of=relation

(check this before you apply it ;-)). You probably should stop the
postmaster while doing this, in case it has a cached copy of the page.

regards, tom lane

On 23/11/05 10:20 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> Whats the best way to zero the bad block?
>
> Probably dd from /dev/zero, along the lines of
>
> dd bs=8k seek=597621 count=1 conv=notrunc if=/dev/zero of=relation
>
> (check this before you apply it ;-)). You probably should stop the
> postmaster while doing this, in case it has a cached copy of the page.

Just wanted to clarify, should this not be

dd bs=8k seek=73333 count=1 conv=notrunc if=/dev/zero of=134401991.4

I thought that the 597621 blocks were split up over the . .1 .2 .3 .4 files?

Thanks again

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> Just wanted to clarify, should this not be
> dd bs=8k seek=73333 count=1 conv=notrunc if=/dev/zero of=134401991.4

Looks reasonable.

regards, tom lane

On 24/11/05 2:48 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> Just wanted to clarify, should this not be
>> dd bs=8k seek=73333 count=1 conv=notrunc if=/dev/zero of=134401991.4
>
> Looks reasonable.
>
> regards, tom lane

Excellent thanks. I have run it and copied the changed file back into the
right place and run vacuum:

bugasbase2=# vacuum;
WARNING: relation "mba_data_base" page 597621 is uninitialized --- fixing
WARNING: relation "mba_data_base" page 640793 is uninitialized --- fixing
WARNING: relation "mba_data_base" page 640794 is uninitialized --- fixing
WARNING: relation "mba_data_base" page 640795 is uninitialized --- fixing
WARNING: relation "mba_data_base" page 640796 is uninitialized --- fixing
WARNING: relation "mba_data_base" page 640797 is uninitialized --- fixing
WARNING: relation "mba_data_base" page 640798 is uninitialized --- fixing
VACUUM

Does this look reasonable? Or should I be looking for other problems? There
are some different pages being fixed here from the ones that were shown
before (597621), they all belong to a different dataset so looks like I have
to reupload that one as well.

Thanks again for your help

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> bugasbase2=# vacuum;
> WARNING: relation "mba_data_base" page 597621 is uninitialized --- fixing

This is the expected result of what you did.

> WARNING: relation "mba_data_base" page 640793 is uninitialized --- fixing
> WARNING: relation "mba_data_base" page 640794 is uninitialized --- fixing
> WARNING: relation "mba_data_base" page 640795 is uninitialized --- fixing
> WARNING: relation "mba_data_base" page 640796 is uninitialized --- fixing
> WARNING: relation "mba_data_base" page 640797 is uninitialized --- fixing
> WARNING: relation "mba_data_base" page 640798 is uninitialized --- fixing

That's a bit odd. There are scenarios where all-zero pages can
legitimately appear in a PG file --- specifically, if PG extends
the table and the OS completes that task, but then there's a crash
before PG gets to write any data into the new page. Conceivably a
crash during a bulk data load process could result in half a dozen
such pages together, but it seems improbable. Try looking at the
data on the preceding and following pages --- does it look like there's
something missing?

regards, tom lane

On 24/11/05 3:52 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> bugasbase2=# vacuum;
>> WARNING: relation "mba_data_base" page 597621 is uninitialized --- fixing
>
> This is the expected result of what you did.
>
>> WARNING: relation "mba_data_base" page 640793 is uninitialized --- fixing
>> WARNING: relation "mba_data_base" page 640794 is uninitialized --- fixing
>> WARNING: relation "mba_data_base" page 640795 is uninitialized --- fixing
>> WARNING: relation "mba_data_base" page 640796 is uninitialized --- fixing
>> WARNING: relation "mba_data_base" page 640797 is uninitialized --- fixing
>> WARNING: relation "mba_data_base" page 640798 is uninitialized --- fixing
>
> That's a bit odd. There are scenarios where all-zero pages can
> legitimately appear in a PG file --- specifically, if PG extends
> the table and the OS completes that task, but then there's a crash
> before PG gets to write any data into the new page. Conceivably a
> crash during a bulk data load process could result in half a dozen
> such pages together, but it seems improbable. Try looking at the
> data on the preceding and following pages --- does it look like there's
> something missing?

If you mean by that, this:

select * from mba_data_base where ctid = '(640792,12)';
select * from mba_data_base where ctid = '(640799,1)';

Then the data looks normal... Of course everything in between that is now
blank.

Is that what you meant?

Thanks

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> If you mean by that, this:

> select * from mba_data_base where ctid = '(640792,12)';
> select * from mba_data_base where ctid = '(640799,1)';

> Then the data looks normal... Of course everything in between that is now
> blank.

The question is, can you tell whether any data is actually missing?
In the crash scenario I was describing, no committed data would be lost.
If these blocks went zero because of filesystem misfeasance, however,
you might have lost data ...

regards, tom lane

On 24/11/05 4:19 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> If you mean by that, this:
>
>> select * from mba_data_base where ctid = '(640792,12)';
>> select * from mba_data_base where ctid = '(640799,1)';
>
>> Then the data looks normal... Of course everything in between that is now
>> blank.
>
> The question is, can you tell whether any data is actually missing?
> In the crash scenario I was describing, no committed data would be lost.
> If these blocks went zero because of filesystem misfeasance, however,
> you might have lost data ...

Well each of these datasets are about 20,000 rows each... So I can tell
which one is in (640792,12) and in (640799,1), they have the same dataset id
value. Im assuming the missing ones in the middle will therefore be from the
same dataset as well then... So I know which files did not get uploaded
properly and I can re-upload them....

Is that what you were referring to?

Thanks again

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> On 24/11/05 4:19 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The question is, can you tell whether any data is actually missing?

> Well each of these datasets are about 20,000 rows each... So I can tell
> which one is in (640792,12) and in (640799,1), they have the same dataset id
> value. Im assuming the missing ones in the middle will therefore be from the
> same dataset as well then... So I know which files did not get uploaded
> properly and I can re-upload them....

But can you tell whether there *were* any in the middle? It might be
worth counting the rows currently present for that dataset, then
reloading and counting again.

regards, tom lane

On 24/11/05 4:42 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> On 24/11/05 4:19 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> The question is, can you tell whether any data is actually missing?
>
>> Well each of these datasets are about 20,000 rows each... So I can tell
>> which one is in (640792,12) and in (640799,1), they have the same dataset id
>> value. Im assuming the missing ones in the middle will therefore be from the
>> same dataset as well then... So I know which files did not get uploaded
>> properly and I can re-upload them....
>
> But can you tell whether there *were* any in the middle? It might be
> worth counting the rows currently present for that dataset, then
> reloading and counting again.

Ah yes I see what you mean. I have already done a count(*) on them:

bugasbase2=# select count(*) from mba_data_base where bioassay_id in (5176);
count
-------
20000
(1 row)

bugasbase2=# select count(*) from mba_data_base where bioassay_id in (5177);
count
-------
19988
(1 row)

bugasbase2=# select count(*) from mba_data_base where bioassay_id in (5203);
count
-------
19928

And the two affected datasets (5177, 5203) are short on rows compared to a
correct one (5176)

Does this help identifying what went wrong?

Thanks

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

On 24/11/05 5:28 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> Does this help identifying what went wrong?
>
> At this point I think there's no question that your filesystem is
> dropping blocks :-(. Might want to check for available kernel updates,
> or contemplate changing to a different filesystem.

Well I have been considering moving up to the 2.6.x kernels, this has made
my other machines more reliable (they are not db machines though)

Thanks again for all your help

adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> Does this help identifying what went wrong?

At this point I think there's no question that your filesystem is
dropping blocks :-(. Might want to check for available kernel updates,
or contemplate changing to a different filesystem.

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote
>
> At this point I think there's no question that your filesystem is
> dropping blocks :-(.

It is very interesting to follow this thread. But at this point, can you
explain more why "there is no question" is file system's fault?

Thanks,
Qingqing

On 24/11/05 5:27 pm, "Adam Witney" <awitney(at)sgul(dot)ac(dot)uk> wrote:

> On 24/11/05 5:28 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>>> Does this help identifying what went wrong?
>>
>> At this point I think there's no question that your filesystem is
>> dropping blocks :-(. Might want to check for available kernel updates,
>> or contemplate changing to a different filesystem.

Ok I think this problem is not entirely fixed, my backups are failing now
with this:

pg_dump: ERROR: unexpected chunk number 5153 (expected 21) for toast value
245334402
pg_dump: SQL command to dump the contents of table "measured_bioassay_base"
failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR: unexpected chunk number 5153
(expected 21) for toast value 245334402

measured_bioassay_base is always inserted at the same time as mba_data_base
(the table where I had the problem before) and it has a text field which is
very large.. I guess this is where the TOAST comes in.

Any ideas what is going on here?

Thanks again for any help

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Could it be faulty hardware?

Run memtest86? Test your drives?

At 10:49 AM 11/26/2005 +0000, Adam Witney wrote:

>Any ideas what is going on here?
>
>Thanks again for any help
>
>Adam

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> pg_dump: ERROR: unexpected chunk number 5153 (expected 21) for toast value
> 245334402

> measured_bioassay_base is always inserted at the same time as mba_data_base
> (the table where I had the problem before) and it has a text field which is
> very large..

How large is "very large" ... on the order of 10Mb? If so I'd say this
is the same problem as we saw in your table --- a lot of consecutive
rows have gone missing. You could examine the toast table to confirm
or deny this.

regards, tom lane

On 26/11/05 4:14 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> pg_dump: ERROR: unexpected chunk number 5153 (expected 21) for toast value
>> 245334402
>
>> measured_bioassay_base is always inserted at the same time as mba_data_base
>> (the table where I had the problem before) and it has a text field which is
>> very large..
>
> How large is "very large" ... on the order of 10Mb? If so I'd say this
> is the same problem as we saw in your table --- a lot of consecutive
> rows have gone missing. You could examine the toast table to confirm
> or deny this.

Yes, around 10Mb is about right.

I deleted the two datasets in mba_data_base that were affected by the empty
pages, I also deleted the relevant two rows in measured_bioassay_base... But
maybe it didn't do the right thing with the toast table for these two rows?
Where would I need to look to see what the problem is?

Thanks again for the help.

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
> I deleted the two datasets in mba_data_base that were affected by the empty
> pages, I also deleted the relevant two rows in measured_bioassay_base... But
> maybe it didn't do the right thing with the toast table for these two rows?

Evidently the missing data in the toast table is associated with yet a
different dataset.

I'd suggest first looking into the toast table to see if you can confirm
that the missing data corresponds to a swath of zeroed-out pages. If
that's the case then it gives even more urgency to the need to find out
what's going wrong with your filesystem (or possibly your disk drive,
but my gut feel is that this is a kernel filesystem problem).

The other thing you'd need to do is figure out which dataset you have to
reload. A tedious way to do this is something like
select sum(length(bigfield)) from maintable where dataset = 'xxx';
for various values of xxx until you see the error.

regards, tom lane

On 26/11/05 4:48 pm, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam Witney <awitney(at)sgul(dot)ac(dot)uk> writes:
>> I deleted the two datasets in mba_data_base that were affected by the empty
>> pages, I also deleted the relevant two rows in measured_bioassay_base... But
>> maybe it didn't do the right thing with the toast table for these two rows?
>
> Evidently the missing data in the toast table is associated with yet a
> different dataset.
>
> I'd suggest first looking into the toast table to see if you can confirm
> that the missing data corresponds to a swath of zeroed-out pages. If
> that's the case then it gives even more urgency to the need to find out
> what's going wrong with your filesystem (or possibly your disk drive,
> but my gut feel is that this is a kernel filesystem problem).
>
> The other thing you'd need to do is figure out which dataset you have to
> reload. A tedious way to do this is something like
> select sum(length(bigfield)) from maintable where dataset = 'xxx';
> for various values of xxx until you see the error.

Well I tracked down which row went wrong and deleted that dataset also, the
backups worked fine and it seems to be ok now.

Not really sure what caused all this, all these datasets (190 in total) went
in in one batch the other day, so for some reason 3 of them got screwed up.

Anyway I have upgraded the box to linux 2.6.14.3, so I will keep an eye on
it and see how things go.

Thanks again for your help Tom

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

On Thu, Nov 24, 2005 at 02:59:28PM -0500, Qingqing Zhou wrote:
>
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote
> >
> > At this point I think there's no question that your filesystem is
> > dropping blocks :-(.
>
> It is very interesting to follow this thread. But at this point, can you
> explain more why "there is no question" is file system's fault?

Not to put words in Tom's mouth, but as he said there's very few ways
that PostgreSQL will leave a blank page laying around, and the tests he
had the OP perform show that this almost certainly isn't one of those
cases. That means something other than PostgreSQL is dropping data.
Since it's apparently multiples of 4k it's reasonable to suspect the
kernel or the filesystem; it's pretty unlikely it's the drives.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461