PostgreSQL Data Checksums

If you use SQL Server, you’re used to the database doing page verification for you as the sensible default. If you want SQL Server to not verify data, you have to do a bit of extra work. Naturally, I would’ve assumed that this was the case with other databases since, after all, having good data on disk is important.

Not quite a check sum, but delicious enough.

Not quite a check sum, but delicious enough.

Turning on PostgreSQL Checksums

Data checksums were added to PostgreSQL 9.3. This is great, but there’s a catch – the data checksum has to be turned on during server set up – specifically when running initdb. Checksums can’t be enabled after a database is created either.

To turn on checksums, during initialization an administrator needs to supply either --data-checksums or the -k flag, e.g. initdb --data-checksums databas.

If you haven’t enabled the checksums, you’ll have to move the data into a new PostgreSQL installation through one of the usual means – some kind of export or logical replication. Have fun!

Automatic Repair

If you’ve turned on checksums, PostgreSQL still won’t fix data problems for you. It will, however, throw an error when bad data is retrieved from disk. This is a start, and your application should be set up to handle this possibility. But what if you’re lazy?

I found out about checksums in PostgreSQL through an announcement about pg_healer. The idea behind pg_healer is that it sits in the background and attempts to correct different data corruption problems as they arise. It’s still early days for pg_healer, but the author admits that they want it to repair data as queries are happening as well as in the background, much like SQL Server’s DBCC CHECKDB command.

It’s still early days for database repair in PostgreSQL, but we should all be setting up our PostgreSQL installations so that we at least know that corruption is happening.


Chex Mix” by Steve Johnson is licensed with CC BY 2.0

2 Comments. Leave new

  • Raju Angani
    2018-06-08 13:33

    Hi Jeremiah,

    This is the exact tool (pg_healer) I was looking for (knowing MSSQL DBCC CHECKDB {with repair options}).
    Is this tool a production quality?

    Regards
    Raju

    Reply
  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Fill out this field
    Fill out this field
    Please enter a valid email address.

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Menu