A blog which tries to demystify computer security, point out the half-truths and misinformation which floats around about this subject and hopefully reduce the hype created by semi-informed people. It also has some useful tips from time to time.

First time here? I hope that you find something interesting and useful. Check out the most popular pages or the categories I most frequently post in:

Friday, February 27, 2009

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.

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.