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 <t...@sss.pgh.pa.us> wrote: > Amitabh Kant <amitabhk...@gmail.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 >