Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When running a PostgreSQL database system how do I know my database as a whole has 100% integrity? Basically how do I know if my data files and pages are all 100% good with no corruption?

In the Microsoft SQL Server world there's a command that you can execute DBCC CHECKDB that will tell you if there're issues. Here's a link if your intrested in learning more on the command. DBCC CHECKDB (Transact-SQL)

I'm a paranoid database integrity minded person (which anyone who works with the database in a DBA type role should be) and this type of stuff makes it hard for me to sleep well at night. A utility like this is a must! Searches on google have found a few attempts at tools like this and in my opinion unless it's an official accepted tool by the PostgreSQL project I won't trust it for something this important.

Here are some links to people asking similar questions with what I consider no real definitive answer. And in my opinion shows that PostgreSQL needs to have some tools in place that Oracle and Microsoft SQL Server seem to have.

The first link is the most interesting I have found on this subject. I think a comment on the article that probably sums it up states: "Postgres is pretty lame when it comes to identifying database corruption and repairing it. The only way to detect it is by dumping the database or select * from every table in the database."

How PostgreSQL protects against partial page writes and data corruption

Checking data and index file corruption - Dev Shed

Help: my table is corrupt!

PostgreSQL: Corrupt primary key, inconsistent table

I believe there is a chance 9.3 might have some corruption checking features. It appears there may be hope to having page files check summed if one chooses. So things are looking bright if you consider using ZFS and/or a future version of Postgres with page check summing. https://commitfest.postgresql.org/action/patch_view?id=759

UPDATE: 14-JAN-2012 - Seems like using a file system based on ZFS can detect corruption by check summing each block of data. I will have to look into this further and see if this is a work around to allow one to sleep well at night knowing their database data is not silently going corrupt.

UPDATE: 17-JAN-2012 - How to find what files are corrupt with ZFS. http://docs.oracle.com/cd/E18752_01/html/819-5461/gbbwl.html#gbcuz

UPDATE:14-APR-2014 9.3 did get data checksums. https://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3

share|improve this question
2  
What exactly do you mean with integrity check? Constraints will make sure your data is correct. –  a_horse_with_no_name Jan 11 '12 at 20:32
2  
By integrity I meant integrity of the page files and such. The type of stuff that you only see normally if you write query that happens to hit a page that has corruption. For example as a disk drive starts to fail some of the data on the platter might start going bad. How would you spot this? –  Bob Jan 11 '12 at 20:35
1  
I think most people use a combination of either database wide vacuums, or doing select * from each table; ie. try to scan / process every row somehow. Yeah, that's not a very comforting answer for most, just one reason why I run a lot of zfs. –  xzilla Jan 28 '12 at 15:43
add comment

1 Answer

up vote 5 down vote accepted

PostgreSQL prior to 9.3 did not have block checksums. The feature was added in 9.3 (long after this question was posted).

To answer your needs, I would probably develop my own checksumming (triggers?) - working on attribute values, not data pages.

share|improve this answer
2  
Might be worth adding that since Postgres 9.3 you can optionally enable checksums –  a_horse_with_no_name Dec 29 '13 at 17:39
    
@ypercube Thanks –  Craig Ringer Dec 30 '13 at 0:01
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.