Re: Recovering database from crashed HD (bad sectors)

Tom Lane wrote:
> Amitabh Kant <amitabhkant@gmail.com> writes:
> > As for running the sql command as suggested by Tom, here is the result:
> > template1=# select * from pg_class where pg_relation_filenode(oid) = 11678;
>
> >  pg_class |           11 |      83 |         0 |       10 |     0 |
> >   0 |             0 |        8 |       281 |             0 |             0
> > | t           | f           | p              | r       |       26 |
> > 0 | t          | f          | f           | f              | f
> > |          662 | {=r/pgsql} |
>
> That's about the worst possible answer :-(.  Without pg_class, you have
> little hope of telling which is which among the other files; and there
> would be no real commonality with the contents of pg_class from other
> databases in the installation, so no way to jury-rig something.  Moreover,
> because pg_class is consulted *very* early in backend startup, it seems
> entirely likely that the failure you're seeing is only the tip of the
> iceberg; there very possibly are other files that are also missing or
> badly damaged.

I would look for the file in the partition's lost+found directory; with
luck, the file is there.  pg_filedump can identify the number of
attributes each tuple has in each file, if there are many such files;
you need to look for a file whose tuples have 26 attributes (relnatts in
the above row).

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services