Thread: Corrupted database, how do I fix?

Corrupted database, how do I fix?

From
Roy Souther
Date:
First of all, I hope this is the write place to post this. If not please tell
me.

Some time ago the HDD that my database is on died. The backups as far back as
I can go are to be corrupted, this could be because of the drive dieing over
time. I can not afford to loose my data so I am trying to fix the tables.
Some are missing, others give me error when I try to read them or post to
them.

Many tables I was able to fix by creating a new table, coping the data into
it, drop the old corrupted table and rename the new table to the old name.
This is very slow but it works. Some of the old corrupted tables will not
drop.

When I do this for example... "DROP TABLE a_bad_table"
I get... "ERROR:  DeleteTypeTuple: a_bad_table type nonexistent"
But I can do "SELECT * FROM a_bad_table" and I see all the data, it is there.
But also when I do "SELECT * FROM pg_tables WHERE tablename = 'a_bad_table'"
I get nothing, it is not in the tables table.

I have been reading the docs for days and have not found a solution yet.
What can I do? I was thinking I could manually add an entry for the table
into pg_tables but I don't know if that is a good idea or even if it would
work. Is there another table that keeps track of what is in the database?

I you need to know
postgresql version 7.0.2

--
Roy Souther <roy@silicontao.com>

01100010 10101110 11000110 11010110 00000100 10110010 10010110 11000110
01001110 11110110 11001110 00010110 10010110 00101110 10000100 10000100


Re: Corrupted database, how do I fix?

From
Tom Lane
Date:
Roy Souther <roy@silicontao.com> writes:
> But I can do "SELECT * FROM a_bad_table" and I see all the data, it is there.
> But also when I do "SELECT * FROM pg_tables WHERE tablename = 'a_bad_table'"
> I get nothing, it is not in the tables table.

pg_tables is just a view, and it filters out stuff that it thinks is a
view or otherwise not a plain table.  What do you see if you look in
pg_class?

            regards, tom lane