Re: I/O error on data file, can't run backup

Leif Biberg Kristensen <leif(at)solumslekt(dot)org> writes:
> Running postgresql 9.0.5 on
> balapapa ~ # uname -a
> Linux balapapa 2.6.39-gentoo-r3 #1 SMP Sun Jul 17 11:22:15 CEST 2011 x86_64
> Intel(R) Core(TM) i7 CPU 930 @ 2.80GHz GenuineIntel GNU/Linux

> I'm trying to run pg_dump on my database, and get an error:

> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: could not read block 1 in file
> "base/612249/11658": Inn/ut-feil
> pg_dump: The command was: SELECT tableoid, oid, opfname, opfnamespace, (SELECT
> rolname FROM pg_catalog.pg_roles WHERE oid = opfowner) AS rolname FROM
> pg_opfamily

> I have tried to stop postgresql and take a filesystem backup of the data
> directory with a cp -ax, but it crashes on the same file.

You have a disk failure on some sector of that file, apparently. I'd be
thinking about replacing that disk drive if I were you. Once it starts
showing uncorrectable errors the MTTF is going to be short.

> The backup script overwrote my previous backup with a 40 byte file (yes silly
> me I know that's bloody stupid - I'm gonna fix that) and now I haven't got a
> recent backup anymore.

> Is this fixable?

Postgres can't magically resurrect data that your drive lost, if that's
what you were hoping for. However, you might be in luck, because that
file is probably just an index and not original data. Try this:

select relname from pg_class where relfilenode = 11658;

On my 9.0 installation I get "pg_opclass_am_name_nsp_index". If you get
the same (or any other index for that matter) just reindex that index
and you'll be all right ... or at least, you will be if that's the only
file your drive has lost.

regards, tom lane