PostgreSQL data corruption: confirm it with checksums and amcheck, then restore or salvage

#The symptom

Queries against one table or index start failing with errors like these:

ERROR:  invalid page in block 48213 of relation base/16384/24601
WARNING:  page verification failed, calculated checksum 21763 but expected 5113
ERROR:  could not read block 913 in file "base/16384/24601.2": read only 0 of 8192 bytes
ERROR:  missing chunk number 0 for toast value 885412 in pg_toast_24601
ERROR:  index "orders_pkey" contains unexpected zero page at block 7741

Or nothing fails yet, but pg_dump, a logical replication sync or a VACUUM stops on the same block every time. A query that returns different row counts through an index scan and a sequential scan is also a sign of corruption, even with no error.

Caution

Do not run VACUUM FULL, pg_resetwal, zero_damaged_pages = on or delete the damaged file as a first move. Each one can destroy rows you could otherwise have recovered. Copy first, diagnose second, repair last.

#1. Protect the evidence

Stop writes to the damaged object if you can. Then take a file-level copy of the whole data directory, or at least the affected relation files, before you change anything.

# Snapshot the volume if your platform supports it, otherwise copy the files
sudo -u postgres psql -Atc "SELECT pg_relation_filepath('public.orders');"
# base/16384/24601
sudo cp -a /var/lib/postgresql/16/main/base/16384/24601* /backup/corruption-$(date +%F)/

Check that your latest base backup and WAL archive are complete. With pgBackRest:

pgbackrest --stanza=main info
pgbackrest --stanza=main check

Then check the kernel and storage logs. Corruption in PostgreSQL is almost always caused by the layers below it: failing disks, a controller with a lying write cache, fsync disabled, a bad snapshot restore, or memory errors.

journalctl -k --since "-2 days" | grep -Ei "i/o error|blk_update_request|ext4|xfs|nvme|mce"

Warning

If the kernel log shows I/O errors, fail over to a replica or move the data directory to healthy storage before you run any scans. Full-table checks on a dying disk can finish it off.

#2. Find out how much is damaged

#Checksum failures

If data checksums are on, PostgreSQL counts failures per database (PostgreSQL 12 and later):

SHOW data_checksums;

SELECT datname, checksum_failures, checksum_last_failure
FROM pg_stat_database
WHERE checksum_failures > 0;

Checksums are on by default for new clusters from PostgreSQL 18. Clusters created on earlier versions only have them if initdb --data-checksums was used or pg_checksums --enable was run later.

#Map the file to a relation

The error gives you a file node, not a table name. Convert it:

-- 0 means the database's default tablespace
SELECT pg_filenode_relation(0, 24601)::regclass;

Segment files (24601.2) belong to the same relation; each segment is 1 GB by default, so block 913 of segment .2 is a block in the third gigabyte of the table.

#amcheck on indexes

amcheck checks B-tree structure and, with heapallindexed, whether every heap tuple has a matching index entry.

CREATE EXTENSION IF NOT EXISTS amcheck;

-- AccessShareLock only: safe on a busy primary, but it does read the whole index
SELECT bt_index_check(index => 'orders_pkey'::regclass, heapallindexed => true);

-- Stricter, but takes a ShareLock that blocks writes on the table
SELECT bt_index_parent_check(index => 'orders_pkey'::regclass, heapallindexed => true);

#amcheck on heap (PostgreSQL 14+)

SELECT blkno, offnum, attnum, msg
FROM verify_heapam('public.orders', check_toast => true)
LIMIT 50;

Every row returned is a problem tuple. Save the output to a table, because you will need the block and offset numbers later.

#The whole database at once

pg_amcheck runs the same checks across a database in parallel:

pg_amcheck -d appdb --jobs=4 --heapallindexed --install-missing --progress \
  > /tmp/amcheck-appdb.txt 2>&1
echo "exit code: $?"

Run it on a physical replica or a restored copy if you can. A replica built from WAL often has the same logical corruption as the primary but different physical damage, which tells you whether the cause was storage on one host or something that was replicated.

#3. Choose the repair

#Index corruption

REINDEX INDEX CONCURRENTLY public.orders_pkey;
-- then re-run the check
SELECT bt_index_check('public.orders_pkey'::regclass, true);

If many text indexes failed at the same time after an OS upgrade, suspect a glibc collation change rather than storage. Rebuild every index on collatable columns, then run ALTER DATABASE appdb REFRESH COLLATION VERSION; (PostgreSQL 15+).

#Heap corruption: get the good rows out first

Copy everything readable into a new table, skipping the damaged blocks by TID range. The block numbers come from verify_heapam.

CREATE TABLE public.orders_salvage (LIKE public.orders INCLUDING ALL);

-- Rows before and after damaged block 48213
INSERT INTO public.orders_salvage
SELECT * FROM public.orders WHERE ctid < '(48213,0)'::tid;

INSERT INTO public.orders_salvage
SELECT * FROM public.orders WHERE ctid >= '(48214,0)'::tid;

TID range scans make these queries efficient from PostgreSQL 14. Fill in what was lost from the latest backup restored to a separate instance, or from source systems.

#Removing individual bad tuples with pg_surgery

When a few tuples make the table unreadable (for example, VACUUM errors on them every run), pg_surgery can mark them dead:

CREATE EXTENSION IF NOT EXISTS pg_surgery;
SELECT heap_force_kill('public.orders'::regclass, ARRAY['(48213,4)']::tid[]);

Caution

heap_force_kill and heap_force_freeze skip every safety check. They permanently remove or alter tuples and can leave indexes pointing at nothing, so rebuild every index on the table afterwards. Use them only on TIDs that verify_heapam reported, and only after the file copy from step 1.

#Last resort: zero_damaged_pages

zero_damaged_pages = on (superuser, session level) makes PostgreSQL treat a page with a bad header as empty in memory, so a scan can continue. If the page is then written back, its rows are gone for good. Only use it in a session on a copy of the data to pull out whatever else is readable.

#4. Prove it is fixed

pg_amcheck -d appdb --jobs=4 --heapallindexed

SELECT datname, checksum_failures FROM pg_stat_database WHERE datname = 'appdb';

Then take a fresh full backup. Your old backups may contain the same damaged pages, so mark the date the corruption was first seen in your backup catalogue.

#Prevent it next time

• Enable checksums on every cluster. For older clusters, run pg_checksums --enable with the cluster stopped during a maintenance window (it rewrites every block), or turn them on when you rebuild a replica and fail over to it. Checksum settings must match for pg_upgrade.
• Never run with fsync = off or full_page_writes = off on data you care about.
• Run pg_amcheck --heapallindexed weekly on a replica or a restored backup, and alert on a non-zero exit code.
• Alert when pg_stat_database.checksum_failures increases.
• Restore-test backups with a full pg_amcheck pass, not just "the server started".
• After an OS upgrade that changes glibc or ICU, check collation versions before you put traffic back.