pg_amcheck runs verification in one command

Verification used to be a loop somebody had to write

Corruption checking arrived in Postgres as a pair of functions, and functions take one argument. Asking whether a database was intact therefore meant writing a query over the system catalogue to list the indexes worth checking, feeding that list into something that would call the function once per index, and deciding what to do when the twelfth call raised an error and stopped the run. Every shop that did this seriously had its own version of that loop, in shell or in Python or as a stored procedure, and every version made slightly different choices about what to include and what to do about failures.

PostgreSQL 14 shipped the loop. It is a client program that connects, expands whatever you asked for into a list of relations, runs the checks over them with as many connections as you allow, and prints what it found. The release notes record it in the Server Applications section of the PostgreSQL 14 release notes in a single line, next to a change to the options of another utility. The same release also taught the underlying extension to examine heap pages rather than only index pages, which is what makes the command worth having: before it, checking a table’s data itself was not something the extension could do at all.

Nothing about this breaks anything. It is a new program and two new capabilities, and a cluster upgraded to 14 behaves exactly as it did the day before. The reason it earns a page is not the upgrade risk. It is that the command is easy to run, easy to misread, and silently narrower than most people assume.

What one relation at a time looked like

A small table, one index on it, and the two calls that were the only way in.

CREATE EXTENSION amcheck;
CREATE TABLE depot_stop (stop_id bigserial PRIMARY KEY, stop_code text, dwell_seconds integer);
INSERT INTO depot_stop (stop_code, dwell_seconds)
  SELECT 'STOP-' || to_char(g, 'FM000000'), g % 91 FROM generate_series(1, 20000) AS g;
CREATE INDEX depot_stop_code_idx ON depot_stop (stop_code);
CREATE TABLE amcheck_report (line text);

CREATE EXTENSION
CREATE TABLE
INSERT 0 20000
CREATE INDEX
CREATE TABLE

SELECT bt_index_check('depot_stop_code_idx') AS index_result;
SELECT count(*) AS structural_problems FROM verify_heapam('depot_stop');
SELECT count(*) AS btree_indexes_a_loop_would_have_to_visit
FROM pg_class c JOIN pg_am a ON a.oid = c.relam
WHERE c.relkind = 'i' AND a.amname = 'btree';

 index_result 
--------------
 
(1 row)

 structural_problems 
---------------------
                   0
(1 row)

 btree_indexes_a_loop_would_have_to_visit 
------------------------------------------
                                      159
(1 row)

Silence is the success condition for the index check: it raises an error when it finds a problem and returns nothing when it does not. The heap check is the other shape, returning one row per problem, so an empty result is the good news there. The last number is the reason the command exists. That is a database with two user tables in it, and a loop would already have to visit more than a hundred and fifty b-tree indexes, almost all of them catalogue indexes that nobody thinks about until one of them is the broken one.

Both calls take locks, and the choice between them is an operational choice rather than a thoroughness one. The amcheck documentation is explicit: the plain index check takes the same lock a SELECT does, while the parent check takes a lock that blocks inserts, updates, deletes and vacuum for as long as it runs. That is the sentence to read before scheduling anything against a primary at a time of day when people are working.

The same question asked of a whole database

The command needs somewhere of its own to look at, so the fixture builds a second database with two tables, a couple of indexes and one index built on a function whose result will shortly stop matching what is stored.

CREATE EXTENSION dblink;
CREATE DATABASE route_planner;

CREATE EXTENSION
CREATE DATABASE

ALTER DATABASE route_planner SET demo.sort_generation = 'first';
SELECT dblink_exec('dbname=route_planner', $fixture$
  CREATE TABLE route_leg (leg_id bigserial PRIMARY KEY, depot text, legs integer);
  INSERT INTO route_leg (depot, legs)
    SELECT 'depot-' || g, g % 9 FROM generate_series(1, 5000) AS g;
  CREATE TABLE route_note (note_id bigserial PRIMARY KEY, body text);
  INSERT INTO route_note (body) SELECT repeat('n', 60) FROM generate_series(1, 3000);
  CREATE INDEX route_leg_legs_hash ON route_leg USING hash (legs);
  CREATE FUNCTION sort_key(t text) RETURNS text LANGUAGE sql IMMUTABLE
    AS 'SELECT t || current_setting(''demo.sort_generation'')';
  CREATE INDEX route_leg_sort_idx ON route_leg (sort_key(depot));
$fixture$) AS fixture_built;

ALTER DATABASE
 fixture_built 
---------------
 CREATE INDEX
(1 row)

The command is run from inside the server here, because that is what this page can execute and capture. On your own machines it is a program you run from a shell like any other client. The first thing it does on a database it has not been prepared for is decline to check it.

TRUNCATE amcheck_report;
COPY amcheck_report FROM PROGRAM
  'pg_amcheck -U postgres -d route_planner 2>&1; echo "exit status: $?"';
SELECT line FROM amcheck_report;

TRUNCATE TABLE
COPY 3
                                       line                                       
----------------------------------------------------------------------------------
 pg_amcheck: warning: skipping database "route_planner": amcheck is not installed
 pg_amcheck: error: no relations to check
 exit status: 1
(3 rows)

That is worth dwelling on, because it is the difference between a tool that lies and a tool that does not. The extension carrying the actual checks has to be installed in each database being examined, and a database without it is skipped with a warning rather than reported as healthy. The command then exits non-zero because it was asked to check something and checked nothing. There is an option to install what is missing as it goes, which is the right thing on a database you own and the wrong thing on one you are auditing.

TRUNCATE amcheck_report;
COPY amcheck_report FROM PROGRAM
  'pg_amcheck -U postgres -d route_planner --install-missing 2>&1; echo "exit status: $?"';
SELECT line FROM amcheck_report;

TRUNCATE TABLE
COPY 1
      line      
----------------
 exit status: 0
(1 row)

One line of output and a zero. Everything the command was willing to check was checked, nothing was found, and it says so by saying nothing, which is the convention its exit code exists to make bearable.

A clean run is not the same as a complete run

The quiet output above hides the most useful thing the command can tell you, which is what it decided not to look at. Asking it to describe its work changes that. Dependent toast tables are excluded here only to keep the list readable; by default they are pulled in with their parents, which is a good default and a noisy one.

TRUNCATE amcheck_report;
COPY amcheck_report FROM PROGRAM
  'pg_amcheck -U postgres -d route_planner --schema=public --no-dependent-toast --verbose 2>&1;
    echo "exit status: $?"';
SELECT line FROM amcheck_report;

TRUNCATE TABLE
COPY 8
                                            line                                             
---------------------------------------------------------------------------------------------
 pg_amcheck: including database "route_planner"
 pg_amcheck: in database "route_planner": using amcheck version "1.3" in schema "pg_catalog"
 pg_amcheck: checking heap table "route_planner.public.route_leg"
 pg_amcheck: checking btree index "route_planner.public.route_leg_sort_idx"
 pg_amcheck: checking btree index "route_planner.public.route_leg_pkey"
 pg_amcheck: checking btree index "route_planner.public.route_note_pkey"
 pg_amcheck: checking heap table "route_planner.public.route_note"
 exit status: 0
(8 rows)

Count the indexes in that list and compare them with the ones the fixture created. The hash index is not there. It was not skipped because of an option or a pattern; it is simply not a kind of index the extension knows how to verify, and its absence from a clean run is reported nowhere unless you ask. Ask for it by name and the command is blunt about it.

TRUNCATE amcheck_report;
COPY amcheck_report FROM PROGRAM
  'pg_amcheck -U postgres -d route_planner --index=route_leg_legs_hash --verbose 2>&1;
    echo "exit status: $?"';
SELECT line FROM amcheck_report;

TRUNCATE TABLE
COPY 4
                                            line                                             
---------------------------------------------------------------------------------------------
 pg_amcheck: including database "route_planner"
 pg_amcheck: in database "route_planner": using amcheck version "1.3" in schema "pg_catalog"
 pg_amcheck: error: no btree indexes to check matching "route_leg_legs_hash"
 exit status: 1
(4 rows)

So the honest reading of a silent run is narrower than it looks. It says the heap pages of your tables are structurally sound and your b-tree indexes are internally consistent. It says nothing at all about the other access methods, and on a database with hash, gin or gist indexes carrying real queries, that is a gap you should know the size of rather than discover during an incident.

Making it find something

A verification tool nobody has seen fail is a tool nobody trusts. One well documented cause of index corruption has nothing to do with a disk fault: an index whose keys were computed under one set of rules and is now being searched under another, which is what happens when the sorting rules underneath a text column change beneath a running system. The fixture reproduces that effect honestly by changing what the indexed function returns after the index was built.

Physical damage would have been the more obvious demonstration and is the harder one to stage, because the extension examines the page as it sits in shared memory when the page is already there, so overwriting bytes in a file does not reliably produce anything to find. Logical corruption needs no such trickery.

ALTER DATABASE route_planner SET demo.sort_generation = 'second';

ALTER DATABASE

TRUNCATE amcheck_report;
COPY amcheck_report FROM PROGRAM
  'pg_amcheck -U postgres -d route_planner --schema=public --heapallindexed 2>&1;
    echo "exit status: $?"';
SELECT line FROM amcheck_report;

TRUNCATE TABLE
COPY 4
                                                       line                                                       
------------------------------------------------------------------------------------------------------------------
 btree index "route_planner.public.route_leg_sort_idx":
     ERROR:  heap tuple (0,1) from table "route_leg" lacks matching index tuple within index "route_leg_sort_idx"
     HINT:  Retrying verification using the function bt_index_parent_check() might provide a more specific error.
 exit status: 2
(4 rows)

Three things in that output earn their place. The object is named in full, so a report covering many databases stays unambiguous. The message is the extension’s own error text rather than a summary, so it can be searched for. And the exit status is two rather than one, which is the distinction the earlier failures were teaching: one means the command could not do its job, two means it did its job and the news is bad. A scheduled run that treats any non-zero status the same way throws that away and will page somebody at three in the morning because a database was renamed.

Note also that the corruption is only visible because of the option comparing every heap row against the index. Without it, the index is internally consistent and passes, because nothing about its own structure is wrong. The documentation warns that verification with it will typically take several times longer, which is a trade to make deliberately rather than by default.

Running it without regretting it

• Schedule it against a restored backup or a standby rather than a busy primary, and if it must run on a primary, use the plain checks rather than the parent checks so it takes no more than a reader’s lock.
• Branch on the exit status: zero is clean, two is corruption, and anything else is the run itself failing and needs a different alert with a different urgency.
• Give it more than one connection on a large cluster, and remember each one is a connection against the same server, so the number belongs under whatever headroom you keep.
• Keep the verbose output of at least one run per cluster. It is the only record of which relations were in scope, and the answer changes as indexes are created.

What it costs

There is no setting to turn on and nothing to restart, which is unusual for something this useful. The cost is entirely in the run: the checks read every page of everything in scope through the shared buffer pool, so a full pass over a large database evicts a great deal of what the working set had cached, and the next few minutes of production traffic pay for it. This is the argument for running it somewhere other than the machine serving your customers, more than any lock is.

The other cost is attention. The command is easy enough to schedule that it can end up running weekly for a year with nobody reading the output, which is worse than not running it, because it converts an unknown into a false assurance. If it is worth running, its exit status belongs in the same place as the rest of your alerts, and its verbose list belongs somewhere a person will eventually compare against the indexes that exist today.