amcheck “table-matches-index” enhancement now available, detects "freeze-the-dead" corruption

I’m pleased to announce that v1.2 of amcheck, a tool for detecting that PostgreSQL relations are logically consistent (that they do not appear to be corrupt) is now generally available. This version adds a big enhancement - the optional ability to check if every tuple that should have an entry in the index does in fact have such an entry. Specifically, we check for a table entry with matching data, as well as a matching heap TID. This happens at the end of the existing tests, as an optional extra step.

This enhancement is significant because it seems much more likely to catch corruption in the wild. In general, inconsistencies between a table and its indexes are more likely to occur than inconsistencies between blocks within an index for many reasons. There is simply a much larger window for an inconsistency to arise when something is amiss with database storage that breaks the assumptions PostgreSQL makes during crash recovery, for example.

The enhancement is also significant because it played a role in identifying a PostgreSQL data corruption bug that will be fixed in the next point release, scheduled for November 9th, 2017. This bug affects all supported PostgreSQL versions. It was informally dubbed the “freeze-the-dead” bug.

November 6 2017 update: The fix was reverted due to additional concerns that came to light. The community is working on a new, more comprehensive fix for the next point release.

December 15 2017 update: A new fix has been committed, and will appear in 9.3.21, 9.4.16, 9.5.11, 9.6.7, and 10.2 point releases, scheduled for February 8th, 2018.

Packages for v1.2 are available from the community Debian/Ubuntu apt repository, as well as packages from the community Redhat/CentOS/SLES yum repository. Full details on installing these packages are available from the README:

https://github.com/petergeoghegan/amcheck/

amcheck for Postgres 9.4+ now available from PGDG apt and yum repositories

amcheck, a tool for index corruption detection, now has packages available from the community Debian/Ubuntu apt repository, as well as packages from the community Redhat/CentOS/SLES yum repository.

This means that installations built on those community resources can easily install amcheck, even on PostgreSQL versions before PostgreSQL 10, the release that contrib/amcheck actually first appears in.

Full details on installing these packages are available from the README: https://github.com/petergeoghegan/amcheck/

It's also possible to install the packages on PostgreSQL 10, because the extension these packages install is actually named "amcheck_next" (not "amcheck"). Currently, it isn't really useful to install "amcheck_next" on PostgreSQL 10, because its functionality is identical to contrib/amcheck. That's expected to change soon, though. I will add a new enhancement to amcheck_next in the coming weeks, allowing verification functions to perform "heap matches index" verification on top of what is already possible.

Many thanks to Christoph Berg and Devrim Gündüz for their help with the packaging.

amcheck: Verify the logical consistency of PostgreSQL B-Tree indexes

I've created a project page on Github for amcheck, a tool for verifying the logical consistency of PostgreSQL B-Tree indexes:

https://github.com/petergeoghegan/amcheck

The tool is primarily useful for detecting index corruption in production database systems. It can do this with low overhead; most verification requires only a non-disruptive lock on the index as it is verified. The strength of the lock taken on an index as it is verified matches that of simple SELECT statements (unless the highest level of verification is requested). The locking involved will generally not block concurrent reads or writes, and will not prevent VACUUM from running concurrently.