PostgreSQL data corruption issues

Lately I’ve been helping out a friend with PG data corruption issues. Usually PG is pretty good about data consistency, but it too can fail under extreme conditions (multiple power failures, fsync=off in the name of speed, no battery-backed RAID controller). The interesting thing I didn’t realize, is that your transaction log can get corrupted!

Some errors I’ve seen include:

Exception [OperationalError] - [could not access status of transaction 1277830 DETAIL:  Could not open file "pg_clog/0001": No such file or directory. 

PANIC: corrupted item pointer [...]

Some ideas I've found / had:

• Recreate the missing files if=/dev/zero of=0001 bs=1024 count=256– from here
• Use pg_resetxlog (located in /usr/lib/postgresql/8.3/bin/pg_resetxlog under Debian/Ubuntu)
• Dump and reload the data on an other machine. A problem which can appear is that of data which violates constraints (like NOT NULL). One should remove all the constraints and add them back one by one, cleaning out the data which violates it.

This is very much a “work in progress” situation, since I didn’t manage to solve it to my satisfaction, but maybe these pointers will be useful for somebody.

Image taken from nvshn's photostream with permission. Created with corrupt – the data corruption software :-)