Re: no universally correct setting for fsync

Josh Berkus wrote:
> All,
> 
> Updated docs based on tracking this discussion.  fsync through full page
> writes recorded below.

I have applied this doc update with the attached patch.

I added the change from "every night" to "frequently", and reworded it
slightly so it was clear it affects the entire cluster, not just a
single database.

-- 
  Bruce Momjian          http://momjian.us
  EnterpriseDB                             http://enterprisedb.com
Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.279
diff -c -c -r1.279 config.sgml
*** doc/src/sgml/config.sgml	26 May 2010 23:49:18 -0000	1.279
--- doc/src/sgml/config.sgml	31 May 2010 15:44:36 -0000
***************
*** 1413,1446 ****
         
  
         
!         However, using fsync results in a
!         performance penalty: when a transaction is committed,
!         PostgreSQL must wait for the
!         operating system to flush the write-ahead log to disk.  When
!         fsync is disabled, the operating system is
!         allowed to do its best in buffering, ordering, and delaying
!         writes. This can result in significantly improved performance.
!         However, if the system crashes, the results of the last few
!         committed transactions might be completely lost, or worse,
!         might appear partially committed, leaving the database in an
!         inconsistent state. In the
!         worst case, unrecoverable data corruption might occur.
!         (Crashes of the database software itself are not</>
!         a risk factor here.  Only an operating-system-level crash
!         creates a risk of corruption.)
         
  
         
!         Due to the risks involved, there is no universally correct
!         setting for fsync. Some administrators
!         always disable fsync, while others only
!         turn it off during initial bulk data loads, where there is a clear
!         restart point if something goes wrong.  Others
!         always leave fsync enabled. The default is
!         to enable fsync, for maximum reliability.
!         If you trust your operating system, your hardware, and your
!         utility company (or your battery backup), you can consider
!         disabling fsync.
         
  
         
--- 1413,1435 ----
         
  
         
!         While turning off fsync is often a performance
!         benefit, this can result in unrecoverable data corruption in
!         the event of an unexpected system shutdown or crash.  Thus it
!         is only advisable to turn off  fsync if
!         you can easily recreate your entire database from external
!         data.
         
  
         
!         Examples of safe circumstances for turning off
!         fsync include the initial loading a new
!         database cluster from a backup file, using a database cluster
!         for processing statistics on an hourly basis which is then
!         recreated, or for a reporting read-only database clone which
!         gets recreated frequently and is not used for failover.  High
!         quality hardware alone is not a sufficient justification for
!         turning off fsync.
         
  
         
***************
*** 1572,1583 ****
  
         
          Turning this parameter off speeds normal operation, but
!         might lead to a corrupt database after an operating system crash
!         or power failure. The risks are similar to turning off
!         fsync</>, though smaller.  It might be safe to turn off
!         this parameter 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 (e.g., ZFS).
         
  
         
--- 1561,1570 ----
  
         
          Turning this parameter off speeds normal operation, but
!         might lead to either unrecoverable data corruption, or silent
!         data corruption, after a system failure. The risks are similar to turning off
!         fsync, though smaller, and it should be turned off
!         only based on the same circumstances recommended for that parameter.