pageinspect Forensics: Reading Heap and Index Pages When PostgreSQL Reports Corruption

The alert text is the kind that wakes you fully before you finish reading it: ERROR: invalid page header in block 488291 of relation base/16402/17542, repeated by three different backends within a minute. It was 03:12 on a Saturday, the relation turned out to be orders_pkey — the primary key index of the orders table — and the two questions that mattered were whether the heap underneath was also damaged and how far back the damage went, because those two answers decide whether you rebuild an index over lunch or restore a cluster over a weekend. The tool that answered them, without taking anything offline, was pageinspect: the contrib extension that lets you read raw relation pages from SQL. Combined with amcheck, it turned what felt like a restore scenario into a REINDEX CONCURRENTLY and a storage ticket, and this is the field procedure we now keep in the runbook.

What do you do in the first ten minutes?

Nothing destructive, and that sentence exists because the temptation is real: do not delete anything, do not restart anything, do not reach for zero_damaged_pages, which converts errors into silently skipped data and turns a forensics problem into a data-loss problem with better uptime. Map the relation from the error’s file numbers, scope the blast radius, and capture evidence. The mapping query identifies what you are actually dealing with, and the scoping query tells you whether one page is bad or a thousand:

-- Which relation is base/16402/17542?
SELECT oid::regclass AS relation, relkind, relnamespace::regnamespace
FROM pg_class
WHERE relfilenode = 17542
  AND reltablespace = 0;

-- Is it index or heap, and how big?
SELECT relname, relkind, relpages,
       pg_size_pretty(pg_relation_size(oid))
FROM pg_class
WHERE oid = 'orders_pkey'::regclass;

Our answers: an index, not the heap; one block reported by every backend that touched it; and data checksums enabled on the cluster, which meant the checksum verification had already told PostgreSQL the page’s bytes were not what was written — the detection half of this story is covered in the data checksums notes, and this incident is where that earlier work paid for itself. An invalid page on an index is the best version of a bad morning, because an index is a derived structure: if the heap is provably clean, the index is disposable.

What does pageinspect actually let you see?

The page itself, parsed. CREATE EXTENSION pageinspect gives you functions that read a block by number and return its header fields and item pointers: page_header for the LSN, checksum, and flags; heap_page_items for tuple-level detail on heap pages; bt_metap and bt_page_items for B-tree structure. One caution we absorbed from the documentation and now repeat in every incident channel: get_raw_page reads the live buffer, which means it can block briefly on a contended page and it requires superuser or the pg_read_server_files-adjacent privileges depending on version, so run the forensics from a dedicated admin role and prefer doing the heavy reading on a replica when one is current — the corruption we cared about was on disk, identical on both nodes, and the replica gave us a place to poke at it without adding load to a primary that was having a bad night. Reading the broken page confirmed the error from the inside — the header was garbage, not a valid-but-old page — and reading its neighbors showed pages that were structurally sound, with a clean LSN sequence around the damaged block:

CREATE EXTENSION IF NOT EXISTS pageinspect;

-- The damaged block: this errored, which is itself information
SELECT * FROM page_header(get_raw_page('orders_pkey', 488291));

-- The neighbors: sane LSNs and flags around the hole
SELECT blkno, lsn, checksum, flags
FROM generate_series(488288, 488294) AS blkno,
LATERAL page_header(get_raw_page('orders_pkey', blkno))
WHERE blkno != 488291;

-- The B-tree's own metadata page, for orientation
SELECT * FROM bt_metap('orders_pkey');

That pattern — one corrupt page, healthy neighbors, an intact metapage — is the signature of a localized write failure rather than systemic corruption, and it pointed the investigation at the storage layer rather than at PostgreSQL. The cloud provider’s event log, checked the next business day, showed a brief storage-backend incident in the same minute. pageinspect did not just guide the repair; it produced the evidence for the root-cause conversation, which matters when the alternative root cause on the table is "PostgreSQL ate the page," a conclusion the MVCC internals notes should make anyone slow to reach for.

How do you prove the heap is clean before trusting a rebuild?

With amcheck, not with optimism. REINDEX fixes the index by scanning the heap, which means a rebuild over a silently corrupt heap produces a beautifully consistent index of damaged data. amcheck’s bt_index_check with heapallindexed verifies structural consistency between index and heap, and for the heap itself there is no in-core full verifier — so our proof combined three passes: amcheck on every other index of the table, a sequential scan of the full table through a replica with checksums doing their job per page, and a pg_dump of the table to /dev/null as a read-every-tuple smoke test. All three passed, which converted the repair decision from a judgment call into a formality: REINDEX INDEX CONCURRENTLY orders_pkey, forty minutes, zero downtime, with the build-failure discipline from the reindex notes applied to make sure the rebuild itself left no debris.

CREATE EXTENSION IF NOT EXISTS amcheck;

-- Verify every index on the table against the heap
SELECT bt_index_check(indexrelid, heapallindexed := true)
FROM pg_index
WHERE indrelid = 'orders'::regclass;

-- Then, and only then, rebuild the damaged index
REINDEX INDEX CONCURRENTLY orders_pkey;

What changed in the runbook afterward?

Three additions, all cheap. The incident header now requires capturing pageinspect output and pg_controldata before any repair action, because the evidence that identifies root cause is also the evidence a repair destroys. The decision tree is explicit: index page corrupt plus heap proven clean means concurrent rebuild; heap page corrupt means stop and plan a restore of the affected data from the backup layer described in the backup strategies guide, because PostgreSQL has no in-core heap repair and pretending otherwise is how silent data loss starts. And every new cluster gets data checksums at initdb time, non-negotiably — the only reason Saturday’s error existed as an error, rather than as quietly wrong query results, is that a checksum compared bytes to expectations and refused to proceed. Corruption without checksums is not absence of corruption; it is absence of detection.

Watching for corruption signals with MonPG

The operational gap this exposed was not detection of the error — the logs were loud — but context: which relation, how big, what reads it, what the storage layer was doing at that minute. MonPG monitors PostgreSQL in production today, and its PostgreSQL monitoring keeps per-relation size and access trends alongside disk and WAL timelines, so the first hour of a corruption incident starts from a dashboard that already knows the blast radius instead of from a cold login to a frightened database. Forensics go faster when the scene was already being observed.

Related documentation

• PostgreSQL: pageinspect