Re: Recovering database from crashed HD (bad sectors)

I am just experimenting with the crashed DB. Before I tried anything else,
I did make a complete copy of the recovered directory tree.

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;

relname | relnamespace | reltype | reloftype | relowner | relam |
relfilenode | reltablespace | relpages | reltuples | reltoastrelid |
reltoastidxid | relhasindex | relisshared | relpersistence | relkind |
relnatts | relchecks | relhasoids | relhaspkey | relhasrules |
relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions

----------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+--------------+------------+------------

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

(1 row)

From what I can understand, this is not a index as reltype is non-zero . Is
there anything else that I can try?

With regards

Amitabh

On Sat, Jul 18, 2015 at 7:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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