On Wed, 3 Mar 2004, satish satish wrote:
> Hi,
>
> I am trying to do some reliability tests on postgre SQL. I have
> use-case where the power can go off abruptly. I initiated 10,000 insert
> operations and pulled out the cable in the middle. I had auto-commit
> option turned on. I observed 2 out of 5 times the tables were totally
> corrupted and could not read any data whereas 3 times I was able to read
> the data which was inserted.
>
> Is there any way that I could avoid that data corruption and ensure
> that atleast the records inserted till that point are available in the
> database. Or are there any tools through which I can recover the data in
> case the database gets corrupted?
There are a few steps to making sure your data can survive a power failure
when writing.
1: Make sure fsync is enabled in postgresql.conf / postmaster startup.
2: Use a journaling file system. Meta data only is fine.
3: Use hardware that fsyncs and doesn't lie about it.
4: Ditto # 3 for your OS. Most the OSes pgsql runs on are fine.
#3 above is the big sticker most of the time. write caching raid
controllers without battery backup or write caching hard drives that lie
about their fsync. IDEs drives are known to generally do this. SCSI
drives generally don't. No one on the list has done a lot of testing with
SATA, but if someone wants to send me a drive and a controller card I'd be
quite happy to pull the power plug on my box to test it. :-0
Anyway, someone else on the list has reported that the Escalade IDE RAID
controller passes the power pull test. I have personally tested the LSI /
MegaRAID controllers (the U160 one) with battery backed cache and found
they survive the power off test well. Someone else has tested the
adaptec SCSI RAID controllers with battery backed cache and reported that
they worked as well. Turning off the cache on IDE drives will drop your
average performance to about 1/3 that of what you get with caching on.
But it should make them reliable for power loss recovery. The command in
linux is: hdparm -W0 /dev/hda. Replace hda with the drive you would like
to disable the write caching for.
