7.3.4 Table corruption

[Andrew Farmer]

I'm running a 7.3.4 on a RedHat 7.2 i686 box. I take daily backups of
my application's database, and last week it failed on one of the
tables. After some checking I found that the rest of the data was ok,
just a large number of tuples on one table were corrupt. I'm pretty sure
it's table corruption, as a REINDEX does not solve the problem, and I
can't COPY data from the table.

I have backups I can use, but I only want to restore this one table,
which would not normally be a big problem, as I can just pull the
relevant commands out of the dump file. But there are a lot of
connections through rules and references from the broken table to other
tables, so I can't simply truncate this table and reload it, I would
have to reload a large number of other tables as well. Something I
don't really want to do, as I could potentially lose more data.

My question is, if I load the good dump into a clean database, and then
find the underlying file that represents the broken table and copy it
over the top of the broken table, am I likely to face any big problems?

Thanks
Andrew


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Post by Andrew Farmer
My question is, if I load the good dump into a clean database, and then
find the underlying file that represents the broken table and copy it
over the top of the broken table, am I likely to face any big problems?

This strikes me as a real good way to shoot yourself in the foot ;-).
Better take a backup and be prepared to restore from it. And I'd
suggest experimenting in a scratch installation before you try it for
real.

Having said that, I think it would work, if your "clean database" is
another DB in the same cluster (you could *not* copy from data prepared
under a different postmaster). And you'll need to copy all the indexes
on that table, and its toast table and toast table index if it has one.
And shut down the postmaster while you do the copying.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Andrew Farmer]

Thanks for the advice. I'll relax the constraints on the table, and
reload that table from the dump, might take a while to fix any problems,
but it should be safer.

Regards, Andrew

Post by Tom Lane

Post by Andrew Farmer
My question is, if I load the good dump into a clean database, and then
find the underlying file that represents the broken table and copy it
over the top of the broken table, am I likely to face any big problems?

This strikes me as a real good way to shoot yourself in the foot ;-).
Better take a backup and be prepared to restore from it. And I'd
suggest experimenting in a scratch installation before you try it for
real.
Having said that, I think it would work, if your "clean database" is
another DB in the same cluster (you could *not* copy from data prepared
under a different postmaster). And you'll need to copy all the indexes
on that table, and its toast table and toast table index if it has one.
And shut down the postmaster while you do the copying.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org