Hi all, I just had the following error on one of our data bases: ERROR: could not access status of transaction 1038286848 DETAIL: could not open file "pg_clog/03DE": No such file or directory I researched on the mailing list and it looks like the usual suspect is disk page corruption. There are few previous discussions about how to dump the suspect disk page, e.g.: http://archives.postgresql.org/pgsql-general/2006-10/msg01372.php http://groups.google.com/group/comp.databases.postgresql.hackers/browse_frm/thread/4988752a6939f45a/fd9f12468e86dd3?hl=en&lr=&ie=UTF-8&rnum=8&prev=/groups%3Fq%3Dpg_filedump%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D20030922162322.E12708%2540quartz.newn.cam.ac.uk%26rnum%3D8#fd9f12468e86dd3 You can probably find more searching for "ERROR: could not access status of transaction" or "pg_filedump". What I could not find was a simple and fast way to find the bad block. The error message itself is not useful in this regard, and the "binary search" method is anything but fast on a big table. So I wrote the following plpgsql function: create or replace function find_bad_block(p_TableName text) returns tid as $find_bad_block$ declare result tid; crtRow record; count bigint := 0; begin for crtRow in execute 'select ctid from ' || p_TableName loop result = crtRow.ctid; count := count + 1; if count % 500000 = 0 then raise notice 'rows processed: %', count; end if; end loop; return result; exception when OTHERS then raise notice '%: %', SQLSTATE, SQLERRM; return result; end; $find_bad_block$ language plpgsql; This will spit out the error + the last valid block visited. If there's no bad block, you won't get the notice with the error, just the last block of the table... worked fine for me, resulting in: NOTICE: 58P01: could not access status of transaction 1038286848 find_bad_block ---------------- (657780,157) (1 row) Now to finding the file I should dump: select oid from pg_database where datname = 'dbname'; oid ------- 16389 (1 row) select relfilenode from pg_class where relname='table_name'; relfilenode ------------- 20418 (1 row) The file is then 'db_path/base/16389/20418'. Or a collection of '20418.x' files if the table's data is more than 1 GB, each file being a 1GB chunk of the table... so which one to dump ? First calculate the block count in one chunk: 1024 * 1024 / block_size, where block_size is the block size in KB with which postgres was compiled. That's normally 8, but we have systems where it is set to 32. If you didn't change that yourself, it is likely 8. So in my case the block count per chunk was: 1024 * 1024 / 8 = 131072. So the chunk number will be: blockId / blocks_per_chunk = 657780 / 131072 = 5 So I should look in the file 'db_path/base/16389/20418.5'... but wait, the block id has to be made relative to the chunk file's start: chunk_block_id = block_id % blocks_per_chunk So the block id to use with pg_filedump is: 657780 % 131072 = 2420 So my final command line was: pg_filedump -if -R 2420 db_path/base/16389/20418.5 resulting in something like: [snip] <Data> ------ Item 1 -- Length: 44 Offset: 8148 (0x1fd4) Flags: USED XMIN: 1048587 CMIN: 90130188 XMAX: 0 CMAX|XVAC: 1036845056 Block Id: 655376 linp Index: 18451 Attributes: 1375 Size: 0 infomask: 0x0200 (XMIN_INVALID) Error: Computed header length not equal to header size. Computed <28> Header: <0> 1fd4: 0b001000 0c475f05 00000000 0000cd3d .....G_........= 1fe4: 0a001000 13485f05 00020000 2b030000 .....H_.....+... 1ff4: 2d030000 00000000 01000000 -........... [snip] So I found my bad block, and the previous and following looked OK. Now I want to fix just that one block even if the rows on it are lost, as the table data is not detail-critical (massive amounts of lost data would be critical, small loss is tolerable). Now to the actual fixing: from my searches it looks like zeroing out the complete block + reindex the table seems to be the recommended solution if it is not possible to downright drop the table and restore from backup (in my case that is not possible - this error is there from last year's October, and all our backups failed from then to now - and nobody checked the logs till now when I tried to upgrade postgres via slony and failed to do it because of this error - rather telling for the importance of this DB). So, how to zero out the page ? The following message shows a way to do it: http://www.webservertalk.com/archive142-2004-7-319751.html Basically set the 'zero_damaged_pages' setting to 'on': http://www.postgresql.org/docs/8.1/interactive/runtime-config-developer.html ... and select that page again. Unfortunately this did not work for me... looks like if the page header is not corrupt but only individual tuples are a problem, the 'zero_damaged_pages' thing doesn't work. Next try: http://usr-share-man.org/man1/dd.html The start of my bad block is at: 8 * 2420 = 19360K, so I first dump the block for cross checking that I got the arguments right: dd ibs=1024 if=db_path/base/16389/20418.5 count=8 skip=19360 of=cross_check_file Then I used: pg_filedump -if -R 0 cross_check_file and diffed against the original dump, to make it absolutely sure it's that right block... call me paranoid, but I'm not a dd expert and I had to be sure. It matched. So now to the destructive part of the action: dd ibs=1024 obs=1024 if=/dev/zero of=db_path/base/16389/20418.5 count=8 seek=19360 conv=notrunc Ok, I admit that on first try I didn't put the 'conv=notrunc' there... so with all the former paranoia I still ended up with a truncated chunk. Luckily I remembered I have a WAL logging based replica, so I recovered the rest of the truncated file from the replica's same file... this being an insert only table I was lucky I guess that this was an option. To my surprise, the same block on the replica was not mangled... I say to my surprise, because on other occasions the bad blocks readily replicated over. In any case if you have a WAL logged replica you might be lucky to recover the corrupt block(s) from there (or just switch over, but that is risky too, you can't know for sure in what state the replica is, and that is actually harder to investigate than the master, as you can execute no SQL on the replica). Anyway, that's it... it looks the problem is gone, and the DB will be moved to another box to postgres 8.2.4 via slony. I'm not sure how much of that table I mangled with my dd experiments, hope that others can learn from my mistakes... Cheers, Csaba.
