1

I'm using the Django ORM to access a PostgreSQL database, and on rare occassions Django will throw a DatabaseError like django.db.utils.DatabaseError: invalid page header in block 299560 of relation base/83966/84778.

I've researched this, and it seems due to the database getting corrupted somehow. This is immensely frustrating, because I've always shutdown the database cleanly when rebooting, and every check I can run on my disk drive says there's nothing wrong with the disk itself. Therefore, I can only conclude that PostgreSQL is not actually ACID compliant and is corrupting my data in rare instances.

The only fix I've been able to find, is to drop and recreate my database. Obviously, this isn't really a fix, since I'm losing all my data. Is there any other way to resolve this, or should I switch to a more reliable database like MySQL?

I'm running Postgresql-8.4.8 on Ubuntu 10.04.

Cerin
  • 60,957
  • 96
  • 316
  • 522
  • btw,ever been to a .org or .info site? Guess which db served your request for dns resolution? PostgreSQL is a rock solid 24/7 db, but it can't run reliably on unreliable hardware. How much burn in testing did this machine have? – Scott Marlowe May 06 '11 at 17:14

1 Answers1

1

Most of the time you see this you either have bad memory or a bad drive. The difference between PostgreSQL and MySQL is that PostgreSQL sees it and complains as it should, while MySQL often just keeps on going with no stopping. I think the db that stops when the machine corrupts the data store is the more reliable db, because it lets you know right up front there are issues with your system.

BTW, PostgreSQL can survive an emergency shutdown (pull the plug out the back of the machine) just fine as long as the hard drives aren't lying about fsync.

Try memtest86 to see if your memory's ok, and do something like

sudo dd if=/dev/sdc1 of=/dev/null

to see if you get any errors. Anything in your dmesg or message logs about drive read write errors?

Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21
  • 1
    Also, do you get the exact same error each time or does it move around? I've had a single bad block once that I was able to get rid of by rebuilding an index. If that's the case it would be helpful to know if the file(s) that are having problems are indexes or tables. – Scott Marlowe May 07 '11 at 00:47
  • is there any similar command to check, if my machine is Windows? – Hoàng Long Jan 09 '12 at 03:44
  • memtest86+ can be used from a live ubuntu CD, so you don't have to install linux or anything. it's just one of the boot options from the CD, I think it says something like "Check Memory" – Scott Marlowe Jan 09 '12 at 17:30
  • Although this doesn't technically answer my question, you make fair points. I've since determined the cause is likely a buggy disk driver, since this occurred again when I ran out of disk space, and PG or the driver didn't properly handle the transaction rollback. I'm still searching for a way to recover the part of the database that wasn't corrupted. – Cerin Jan 24 '12 at 19:50