Hardware is not always a problem. It might easily happen that you face something as simple as a power out. In general, PostgreSQL can easily survive power outs. The transaction log takes care of all failure-related issues, and with the help of xlog, it is always possible to bring the database back to a consistent state. It is safely possible to pull the plug and rely on the quality of PostgreSQL.

However, there are some nasty cases that can lead to even nastier troubles. On commit, PostgreSQL relies on the fact that data can be flushed to the disk. A system call, fsync, is used to ensure that everything relevant is forced to the disk. However, what happens if fsync cannot do its job? What if data cannot be (or data is not) flushed to the disk? If your system is up and running, everything is fine. The data will make it to the disk at some point, and life is good! However, what if a crash happens? The situation is grimmer. In short, anything can happen. If PostgreSQL does not know anymore what has been safely written to disk and what is not, any kind of corruption may occur.

Why would fsync fail? Things can always go south if hardware fails. However, there is a more common issue here—virtualization. Not all pieces of virtualization software will flush data to the disk by default. For performance reasons, some systems such as VirtualBox will default to optimizing fsync by just writing in a more lazy way. The results are likely to be disastrous, not only for PostgreSQL but also for the entire virtual machine.

The importance of fsync cannot be stressed enough. If flushing to the disk is somewhat broken, the entire database instance is at the mercy of your electricity company. Surely, not flushing to the disk is a lot faster, but it can also mean that your data will be lost much faster.

In case data is lost in a virtualized setup, always try to figure out whether fsync is really doing what it is supposed to do on the underlying hardware component.