Обсуждение: 7.3.4 Table corruption

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

Andrew Farmer <mail@andrewfarmer.com> writes:
> 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

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

Tom Lane wrote:

>Andrew Farmer <mail@andrewfarmer.com> writes:
>
>
>>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
>
>
>