Avoiding PostgreSQL database corruption

TL;DR: Don't ever set fsync=off, don't kill -9 the postmaster then delete postmaster.pid, don't run PostgreSQL on network file systems.

Reports of database corruption on the PostgreSQL mailing list are uncommon, but hardly rare. While a few data corruption issues have been found in PostgreSQL over the years, the vast majority of issues are caused by:

• Administrator action;
• Misconfiguration; or
• Bad hardware

A recent mailing list post asked what can be done to reduce the chance of corruption and keep data safe.

---

If you think you have a corrupt PostgreSQL database, stop the database server and take a complete copy of the data directory now. See the Corruption page on the wiki. Then ask for help on the pgsql-general mailing list, or for critical/urgent issues contact a profesional support provider.

Do not attempt to fix the problem before taking a complete copy of the entire data directory. You might make the problem much worse, turning a recoverable problem into unrecoverable data loss.

---

Here's my advice for avoiding DB corruption issues, with some general PostgreSQL administration advice thrown in for good measure:

DIY data recovery

While perusing ZDNet Australia I encountered this article about data recovery, which appears to be a thinly veiled piece of advertorial about a data recover firm.

The article pissed me off. It doesn't mention the importance of preventative action like good, well-tested backups. It certainly doesn't bother considering the possibility that you can recover from common cases of data loss yourself or with help from a techie friend, avoiding paying huge sums to the DR firm.

Here are a few tips for recovering lost pictures, documents, etc from a hard drive that's in reasonable physical condition but isn't readable from the computer. The same techniques apply to flash media like Compact Flash, MMC, SD Card, USB memory keys, etc, many of which have the unreliable FAT32 file system on them by default and are very prone to being rendered unreadable by minor file system corruption.

You should not attempt these tips unless you can accept the small risk that you might actually make the problem worse. Most importantly, do not attempt any of these steps if you suspect your hard drive has a serious mechanical fault - say it stopped working after being dropped and now makes sqeaky scratchy noises, it was immersed, it was burned, etc. Attempting to power on a hard drive that's damaged like that will make later recovery harder, so you should take drives with serious physical damage straight to DR pros.

For the other 99% of cases, read on.