Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm currently working with an embedded system that uses Postgresql for data storage. We currently have a problem where the boxes will sometimes get rebooted with no warning, and no proper shutdown. This obviously leaves us with database problems in some cases (invalid page header in certain high-traffic tables is the most common symptom).

What I want to know is, what's the easiest way to clear the errors? I'm obviously going to lose data, but since the errors usually occur in tables that have ephemeral data in them, I don't really care, I just want to get the system back into operation.

Right now our procedure is to drop and re-create any afflicted tables. Is there anything else that we could do that would be faster? As I said, I'm OK with losing any data on the affected page, I just want the thing up and running.

Platform is Ubuntu 7.04, Postgresql 8.2 (We can't force an upgrade onto the customer right now). Filesystem is ext3, on a 2 gig CF card.

Obviously, fixing the unexpected reboots is my top priority, but progress on that is slow (it's hard to reproduce in the lab). In the meantime, I'm hoping for a simpler solution that will let our field people deal more quickly with the issues that do arise.

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

Have you tried setting WAL sync method to fsync_writethrough?

share|improve this answer
    
One more for the ever-expanding list of 'things I didn't know' - we are running with fsync turned off altogether, along with full_page_writes turned off. I should have realized that no one bothered to look at the DB options after our last OS & db version transition. Thank you so much. –  Michael Kohne May 13 '09 at 16:20
    
Additional note: I performed some experiments involving lots of db writing and random removals of the power cord. With fsync turned on, wal_sync_method set and full_page_writes turned on, we don't trash the db on powerfail. –  Michael Kohne May 14 '09 at 18:40
    
Last note - I found out that my co-workers did in fact know about these options. They deliberately turned them off because they thought they had a speed problem. After we force a release out the door, I'm going to demand some SQA time and prove that theory wrong so I can turn them on in the field. –  Michael Kohne Jul 22 '09 at 2:33
add comment

Just want to follow up on bandaiding, in case somebody needs it:

You can enable zero_damaged_pages and run a VACCUUM on the affected table, this should clear out any pages which contain known defect data. This won't protect you from silent corruption of column values though, since PostgreSQL does no block/page level checksumming of it's data as of now.

So this is a last resort option, fixing the source of the problems is always preferable ;).

share|improve this answer
    
Thanks. I did find that option, but I decided that for our purposes, we really only have two repair options: remove and re-instate the affected tables (the table most likely affected has transient data and can be safely dropped and re-instated) or just wipe the whole mess and start over. Since it's an embedded system, we don't have lots of data on any one box, and it's mostly a question of how fast can we get it back in operation. Thanks! –  Michael Kohne Jul 22 '09 at 2:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.