Re: Recovering database from crashed HD (bad sectors)

Amitabh Kant <amitabhkant(at)gmail(dot)com> writes:
> A development box hard disk failed which was running a PG instance with
> multiple databases on it. I got the data recovered with some bad sector
> errors. Ran another instance of PG (same version), and was to able to take
> dump (using pg_dump) of all but one database. For one database I am getting
> the following error:

> pg_dump -Fc alpha_45 > alpha_45.dump
> pg_dump: [archiver (db)] connection to database "alpha_45" failed: FATAL:
> could not open file "base/525035/11678": No such file or directory

> These are the only two files in the directory similar to the one above:
> /usr/local/pgsql/data/base/525035/11678_fsm
> /usr/local/pgsql/data/base/525035/11678_vm

> Is there any hope of recovering this DB, or should I start looking into
> restoring from old backups?

Well, as others have said, if the data is worth money to you then it
would be worthwhile hiring a pro. If you just want to experiment,
the first thing to do is to find out which system catalog or index
that is, which you should be able to do by connecting to any of the
non-broken databases in the same installation and issuing
select * from pg_class where pg_relation_filenode(oid) = 11678;

If you're lucky and it's only an index, you could try connecting to the
busted database with PGOPTIONS=-P (ignore_system_indexes) and reindexing
the broken index. If it's a catalog, whether recovery is possible would
depend on which one.

In any case, it would be prudent to make a tar-style copy of the whole
$PGDATA tree (with the postmaster shut down) before experimenting,
so that you can undo any catastrophic mistakes.

regards, tom lane