Re: Recovering database from crashed HD (bad sectors)

On Sun, Jul 19, 2015 at 4:10 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> 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
>

There is no lost+found directory in the recovered files. Since the DB was
not critical, and can easily be replaced, we are not inclined towards
sending it for professional recovery. As Tom suggested, this would just be
the tip iceberg, as even during recovery, there were lots of read errors
from the disk. I will recreate the DB from the backups.

Thanks for the help and insights though.

With regards

Amitabh