amcheck/amcheck_next: functions for verifying PostgreSQL relation integrity

Current version: 1.5 (amcheck_next extension/SQL version: 2)

Author: Peter Geoghegan <pg@bowt.ie>

Supported versions: PostgreSQL 9.4 - PostgreSQL 10

Note that Microsoft Windows is supported, but only on point releases that have the necessary workaround for various restrictions on dynamic linking that only exist on that platform. The minimum supported point releases are 9.4.16, 9.5.11, 9.6.7, and 10.2.

Overview

The amcheck module provides functions that allow you to verify the logical consistency of the structure of PostgreSQL indexes. If the structure appears to be valid, no error is raised. Currently, only B-Tree indexes are supported, although since in practice the majority of PostgreSQL indexes are B-Tree indexes, amcheck is likely to be effective as a general corruption smoke-test in production PostgreSQL installations.

See Using amcheck effectively for information about the kinds of real-world problems amcheck is intended to detect.

Project background

amcheck is a contrib extension module that originally appeared in PostgreSQL 10. This externally maintained version of the extension, which is formally named amcheck_next to avoid conflicts with contrib/amcheck, provides the same functionality as PostgreSQL 11's contrib/amcheck to earlier versions of PostgreSQL.

It is safe (though generally not useful) to install amcheck_next alongside contrib/amcheck.

Invariants

amcheck provides functions that specifically verify various invariants in the structure of the representation of particular indexes. The correctness of the access method functions behind index scans and other important operations relies on these invariants always holding. For example, certain functions verify, among other things, that all B-Tree pages have items in "logical", sorted order (e.g., for B-Tree indexes on text, index tuples should be in collated lexical order). If that particular invariant somehow fails to hold, we can expect binary searches on the affected page to incorrectly guide index scans, resulting in wrong answers to SQL queries.

Verification is performed using the same procedures as those used by index scans themselves, which may be user-defined operator class code. For example, B-Tree index verification relies on comparisons made with one or more B-Tree support function 1 routines, much like B-Tree index scans rely on the routines to guide the scan to a point in the underlying table; see the PostgreSQL documentation on Index Access Methods and Operator Classes for details of operator class support functions.

Installation

Prebuilt packages

It is recommended that amcheck be installed using prebuilt packages where available.

Debian/Ubuntu

The most recent amcheck release is available from the PostgreSQL Community APT repository. Setup instructions can be found in the APT section of the PostgreSQL Wiki.

Once the Community APT repository is set up, and PostgreSQL has itself been installed from a community package, installation of amcheck is generally a simple matter of installing the package that matches your PostgreSQL version:

sudo aptitude install postgresql-10-amcheck

Redhat/CentOS/SLES

The most recent amcheck release is available from the PostgreSQL Community yum repository. Setup can be performed by following the PostgreSQL yum Howto.

Once the Community yum repository is set up, and PostgreSQL has itself been installed from a community package, installation of amcheck is generally a simple matter of installing the package that matches your PostgreSQL version:

sudo yum install amcheck_next10

Building from source

Building using PGXS (generic)

The module can be built using the standard PGXS infrastructure. For this to work, you will need to have the pg_config program available in your $PATH.

If you are using a packaged PostgreSQL build and have pg_config available (and in your OS user's $PATH), the procedure is as follows:

tar xvzf amcheck-1.5.tar.gz
cd amcheck-1.5
make
make install

Note that just because pg_config is located in one user's $PATH does not necessarily make it so for the root user.

Building Debian/Ubuntu packages from source

The Makefile also provides a target for building Debian packages. The target has a dependency on debhelper, devscripts, postgresql-server-dev-all, and the PostgreSQL source package itself (e.g. postgresql-server-dev-9.4).

The packages can be created and installed from the amcheck directory as follows:

sudo aptitude install debhelper devscripts postgresql-server-dev-all
make deb
sudo dpkg -i ./build/postgresql-9.4-amcheck_*.deb

Setting up PostgreSQL

Once the module is built and/or installed, it may be created as a PostgreSQL extension:

mydb=# CREATE EXTENSION amcheck_next;

amcheck functions may be used only by superusers.

Interface

The amcheck_next extension has a simple interface. amcheck_next consists of just a few functions that can be used for verification of a named B-Tree index. Note that currently, no function inspects the structure of the underlying heap representation (table).

regclass function arguments are used by amcheck to identify particular index relations. This allows amcheck to accept arguments using various SQL calling conventions:

  -- Use string literal regclass input:
  SELECT bt_index_check('pg_database_oid_index', true);
  -- Use oid regclass input (both perform equivalent verification):
  SELECT bt_index_check(2672, false);
  SELECT bt_index_check(oid, false) FROM pg_class
  WHERE relname = 'pg_database_oid_index';

See the PostgreSQL documentation on Object identifier types for more information.

`bt_index_check`

bt_index_check(index regclass, heapallindexed boolean DEFAULT false)
returns void

bt_index_check tests that its target, a B-Tree index, respects a variety of invariants. Example usage:

  SELECT bt_index_check(index => c.oid, heapallindexed => i.indisunique),
          c.relname,
          c.relpages
  FROM pg_index i
  JOIN pg_opclass op ON i.indclass[0] = op.oid
  JOIN pg_am am ON op.opcmethod = am.oid
  JOIN pg_class c ON i.indexrelid = c.oid
  JOIN pg_namespace n ON c.relnamespace = n.oid
  WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'
  -- Don't check temp tables, which may be from another session:
  AND c.relpersistence != 't'
  -- Function may throw an error when this is omitted:
  AND c.relkind = 'i' AND i.indisready AND i.indisvalid
  ORDER BY c.relpages DESC LIMIT 10;

   bt_index_check |             relname             | relpages
  ----------------+---------------------------------+----------
                  | pg_depend_reference_index       |       43
                  | pg_depend_depender_index        |       40
                  | pg_proc_proname_args_nsp_index  |       31
                  | pg_description_o_c_o_index      |       21
                  | pg_attribute_relid_attnam_index |       14
                  | pg_proc_oid_index               |       10
                  | pg_attribute_relid_attnum_index |        9
                  | pg_amproc_fam_proc_index        |        5
                  | pg_amop_opr_fam_index           |        5
                  | pg_amop_fam_strat_index         |        5

This example shows a session that performs verification of catalog indexes. Verification of the presence of heap tuples as index tuples is requested for unique indexes only. Since no error is raised, all indexes tested appear to be logically consistent. Naturally, this query could easily be changed to call bt_index_check for every index in the database where verification is supported. An AccessShareLock is acquired on the target index and heap relation by bt_index_check. This lock mode is the same lock mode acquired on relations by simple SELECT statements.

bt_index_check does not verify invariants that span child/parent relationships, but will verify the presence of all heap tuples as index tuples within the index when heapallindexed is true. When a routine, lightweight test for corruption is required in a live production environment, using bt_index_check often provides the best trade-off between thoroughness of verification and limiting the impact on application performance and availability.

`bt_index_parent_check`

bt_index_parent_check(index regclass, heapallindexed boolean DEFAULT false)
returns void

bt_index_parent_check tests that its target, a B-Tree index, respects a variety of invariants. Optionally, when the heapallindexed argument is true, the function verifies the presence of all heap tuples that should be found within the index, and that there are no missing downlinks in the index structure. The checks performed by bt_index_parent_check are a superset of the checks performed by bt_index_check when called with the same options. bt_index_parent_check can be thought of as a more thorough variant of bt_index_check: unlike bt_index_check, bt_index_parent_check also checks invariants that span parent/child relationships. bt_index_parent_check follows the general convention of raising an error if it finds a logical

Amcheck

Install / Use

README