On Sat, 1 Mar 2008, Franck Routier wrote:
Well, am I just wrong, or the file system might also heavily rely on cache, especially as I use XFS ? So anyway Postgresql has no way to know if the data is really on the disk, and in case of a brutal outage, the system may definitely lose data, wether there is another level of caching (Raid controller) or not...
After PostgreSQL writes to the WAL, it calls fsync. If your filesystem doesn't then force a real write to disk at that point and clear whatever cache it might have, it's broken and unsuitable for database use. XFS is smart enough to understand that.
The only thing people typically run into that will hear fsync and lie about the data actually being written to disk are a) caching controllers with the write cache turned on and b) cheap hard drives. In case (a), having a battery backup for the cache is sufficient to survive most classes of outage without damage--if the system is without power for longer than the battery lasts you're in trouble, otherwise is shouldn't be a problem. In case (b), you have to turn the disk cache off to get reliable database operation.
I've put all the interesting trivia on this topic I've ever come across at http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm if you're looking for some really exciting reading.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- Do not post admin requests to the list. They will be ignored. pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) Help/Unsubscribe/Update your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
