Change in behavior with fsync()

Starting with PostgreSQL 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21 the fsync() behavior has changed. Here is what the PostgreSQL group had to say:

When available in an operating system and enabled in the configuration file (which it is by default), PostgreSQL uses the kernel function fsync() to help ensure that data is written to a disk. In some operating systems that provide fsync(), when the kernel is unable to write out the data, it returns a failure and flushes the data that was supposed to be written from its data buffers.

This flushing operation has an unfortunate side-effect for PostgreSQL: if PostgreSQL tries again to write the data to disk by again calling fsync(), fsync() will report back that it succeeded, but the data that PostgreSQL believed to be saved to the disk would not actually be written. This presents a possible data corruption scenario.

This update modifies how PostgreSQL handles a fsync() failure: PostgreSQL will no longer retry calling fsync() but instead will panic. In this case, PostgreSQL can then replay the data from the write-ahead log (WAL) to help ensure the data is written. While this may appear to be a suboptimal solution, there are presently few alternatives and, based on reports, the problem case occurs extremely rarely.

A new server parameter data_sync_retry has been added to manage this behavior. If you are certain that your kernel does not discard dirty data buffers in such scenarios, you can set data_sync_retry to on to restore the old behavior.

Matt McGuire

Proven technology leader with 25+ years of experience in the installation, administration, development, design, and maintenance of various enterprise data related systems.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.