Re: db recovery after hd crash (could not open relation 1663/16385/16400: No such file or directory)

Roland Wells <roland(dot)wells(at)gmail(dot)com> writes:
> On new hardware, installed pg8.1 and restored data directory and
> attempted to start pg. After fixing some config differences, it
> complained of a missing pg_clog/xxx file. After searching the archives
> for hints, added a file of zero's and pg starts successfully. The db
> in question exists with what appears to be intact structure. When
> attempting to dump the db ran into the error: "could not open relation
> 1663/16385/16400: No such file or directory" and run into similar
> errors (with different relation numbers) trying to select * from
> certain tables.

> I'm stuck here. Assuming the damage to filesystem, what is the best a
> way forward? Does the relation error indicate damage to the data? or
> to an index??? Is there a process I can do to try and salvage partial
> data?

You can look for the third number in pg_class.relfilenode to see exactly
which table or index it's complaining about. If it's happening in
pg_dump then it's probably a table not an index, unfortunately.
Not sure there's much you can do except go back to your last backup
--- PG can't do anything to recover data that the filesystem has lost
track of, which is what this is sounding like.

But having said that, did you try fsck'ing the DD image to see if fsck
could recover any files?

regards, tom lane