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
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
Best Answer
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.