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

  • 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?
    – Kuberchaun
    Commented Jan 11, 2012 at 20:35

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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