Re: Recovering database from crashed HD (bad sectors) - Mailing list pgsql-general
| From | Amitabh Kant |
|---|---|
| Subject | Re: Recovering database from crashed HD (bad sectors) |
| Date | |
| Msg-id | CAPTAQBL=_98ryA0m59+QQ-qsJqnccnKmAk2Ui83NNFjQ8RnrGg@mail.gmail.com Whole thread Raw |
| In response to | Re: Recovering database from crashed HD (bad sectors) (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Recovering database from crashed HD (bad sectors)
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-general |
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
Amitabh Kant <amitabhkant@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
pgsql-general by date: