Обсуждение: Fwd: Suspected Postgres Datacorruption
Hi All ,
Can you please help me out with the following questions .
Our application is running on Postgres 7.4.X . I agree that this is a very old version of Postgres and we should have upgraded . The issue that we faced is that
1 . There was a system crash due to a hardware failure .
2 . When the system came up , we tried to insert a few records into the database . However at this point in time we saw that Postgres was taking a lot of CPU & memory .
Around 42% CPU consumption . This was a cause of concern .
3 . We re-indexed the database and it helped reduce the cpu & memory consumption .
My question is
A ) Isn’t Postgres database resilient enough to handle hardware system failure ? or it sometime results in a corrupt index for its tables ? I read on the Postgres site that hardware failure can cause corrupt indexes . Besides this are there any other scenario which may result in such corruption .
B) If there has been improvement / enhancements done by Postgres regarding the way it handles corrupt indexes can you please pass me more information about the bug Id or some documentation on it ? Our application does not do any REINDEXING . I am in a dilemma if we should seriously incorporate it in our application .
I ideally want to push to a higher version of Postgres . If I can prove that there will be significant performance benefits and that crashes won’t occur then I will be able to present a strong case .
Since my question is related to Performance & Data corruption i saw on the Postgres site that i should provide the following information
Addition Info :
CPU manufacturer and model : Intel's Itanium Processor
Do you use a RAID controller? yes
PCIe SAS SmartArray P410i RAID Controller
PCIe SAS SmartArray P411 RAID Controller
Is is Write back caching enabled ?
Total Cache Size (MB)............... 144
Read Cache........................ N/A
Write Cache....................... N/A
No of disks : 4
Have you ever set fsync=off in the postgresql config file?
#fsync = true # turns forced synchronization on or off
I never changed it .
Have you had any unexpected power loss lately? Replaced a failed RAID disk? Had an operating system crash? Yes system crashed had occured .
Hope this information helps .
Regards,
Sumeet
On 4/08/2011 1:14 AM, Sumeet Jauhar wrote: > 1 . There was a system crash due to a hardware failure . > A ) Isn’t Postgres database resilient enough to handle hardware system > failure ? or it sometime results in a corrupt index for its tables ? I You should *always* be able to pull the plug out of a box running Pg at any point and have it come up just fine. If you can't, it's a bug. They do turn up, but quite rarely and usually relating to odd corner cases in newly introduced features. If you've done something like turn off fsync, of course, you've just told PostgreSQL "I don't care about my data, don't bother keeping it crash safe" and you get garbage if there's a system crash. But that's your choice to enable if your use case doesn't require data durability. You haven't done that, so this isn't the cause of your issue. The only other known case (in a current version) where index corruption is expected after a crash is if you are using hash indexes. Hash indexes are NOT CRASH SAFE, as per the documentation, and WILL need to be reindexed after a crash. Don't use them unless you really know you need them (you don't). Of course, if you're using 7.4.2 or something ancient, you're missing a lot of bug fixes, and some of them DID relate to data durability issues. -- Craig Ringer