Search Postgresql Archives

Re: Recovering database from crashed HD (bad sectors)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
Amitabh Kant <amitabhkant@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux