Re: 7.3.4 Table corruption

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

25 августа 2004 г. в 22:56:54

Andrew Farmer  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

7.3.4 Table corruption

От:

Andrew Farmer <mail@andrewfarmer.com>

Дата:

25 августа 2004 г. в 21:58:50

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

Re: 7.3.4 Table corruption

От:

Andrew Farmer <mail@andrewfarmer.com>

Дата:

27 августа 2004 г. в 20:14:01

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  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
>
>  
>