Subject: Re: no universally correct setting for fsync
From: "Joshua D. Drake" <jd@commandprompt.com>
Reply-To: jd@commandprompt.com
To: Greg Stark <gsstark@mit.edu>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Robert Haas <robertmhaas@gmail.com>, Bruce Momjian <bruce@momjian.us>,
	Michael Tharp <gxti@partiallystapled.com>, pgsql-hackers@postgresql.org,
	Craig Ringer <craig@postnewspapers.com.au>
In-Reply-To: <u2n407d949e1005101046weac382a6g99a5c932d4c5b736@mail.gmail.com>
References: <4BE81CA1.4080201@partiallystapled.com>
	<201005101512.o4AFCZS27089@momjian.us>
	<AANLkTiktz9l00oZEjxvaMljYLebCnJQm-S9Vr4XVF071@mail.gmail.com>
	<4BE7E62C02000025000314B7@gw.wicourts.gov>
	<u2n407d949e1005101046weac382a6g99a5c932d4c5b736@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Organization: Command Prompt, Inc.
Date: Mon, 10 May 2010 11:42:57 -0700
Message-ID: <1273516977.8624.18.camel@jd-desktop.unknown.charter.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Mon, 2010-05-10 at 18:46 +0100, Greg Stark wrote:
> On Mon, May 10, 2010 at 4:55 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
> > Robert Haas <robertmhaas@gmail.com> wrote:
> >
> >> "It might be safe" is a bit of a waffle.  It would be nice if we
> >> could provide some more clear guidance as to whether it is or is
> >> not, or how someone could go about testing their hardware to find
> >> out.
> >
> > I think that the issue is that you could have corruption if some,
> > but not all, disk sectors from a page were written from OS cache to
> > controller cache when a failure occurred.  The window would be small
> > for a RAM-to-RAM write, but it wouldn't be entirely *safe* unless
> > there's some OS/driver environment where you could count on all the
> > sectors making it or none of them making it for every single page.
> > Does such an environment exist?
> 
> The reason for the waffle is that the following sentence describes a
> whole set of environments based the following description:
> 
> > > ? ? ? ?if you have hardware (such as a battery-backed
> > > ? ? ? ?disk controller) or file-system software that reduces the risk
> > > ? ? ? ?of partial page writes to an acceptably low level
> 
> Depending on which set of hardware and how low the risk is it might be safe.
> 
> I think with WAFL or ZFS it's entirely safe. There may be other
> filesystems with similar guarantees. With a BBU the risk might be very
> low -- but it might not, it would be hard to determine without a
> detailed analysis of the entire stack from the buffer cache,
> filesystem, lvm, hardware drivers, BBU design, etc.
> 

The answer to this is:

PostgreSQL.org recommends that this setting be left on at all times.
Turning it off, may lead to data corruption.

Anything else is circumstantial and based on knowledge and facts we
don't have about environmental factors. 

Joshua D. Drake


> -- 
> greg
> 


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering