Re: block error, but can't pg_dump

NOW Web Sites Manager <webperson(at)now(dot)org> writes:
> I have a table that reports one block error when I try to select
> everything, reindex, vacuum, etc. (invalid page header in block
> 413620 of relation "tablename") I've read on the list that the thing
> to do is use pg_dump and then restore it. However, pg_dump fails
> with the same error.

> I can't take down the whole server, let alone easily Postgresql, to
> debug this. Any ideas as to how I could do this? I wrote a Perl
> script to select the records one by one but it didn't find a bad
> record although maybe it's how I wrote the script (it just selected
> the records one by one.)

Hm, were you selecting the records by primary key? (or some other way
that would have produced an index search?) If so, the above suggests
that the corrupted block is one that happens not to contain any live
data, which would be a lucky break for you.

Personally I'd want to know what was going on before taking any drastic
measures, so I'd try to dump out the block with pg_filedump:
http://sources.redhat.com/rhdb/

However, if you're comfortable that your perl script was able to
retrieve everything you care about from the table, you could skip
that and go directly to zeroing out the block, which is the appropriate
recovery action when you don't care about the data in it. The usual
way to do that is with dd:
dd if=/dev/zero of=TARGETFILE bs=8k seek=413620 count=1
(Sorry, no idea how to do it if you're on Windows.)

The trick is to be sure you've got the right target file. You need the
OID of your database (directory name) and the relfilenode (NOT OID)
of the table, which you get from pg_database and pg_class respectively.
Then the file is $PGDATA/base/DBOID/RELFILENODE.

Hm, wait a second, block 413620 would be past the 1-GB mark, well
past it in fact. What you're actually gonna need is
dd if=/dev/zero of=TARGETFILE.3 bs=8k seek=20404 count=1
ie the filename you want is RELFILENODE.3 not just RELFILENODE.

You might want to read the "Database Physical Storage" chapter of
TFM to be sure you follow all this before you start zapping data.

regards, tom lane