Re: Recovering database from crashed HD (bad sectors)

Tom Lane wrote:
> Amitabh Kant <amitabhkant(at)gmail(dot)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