PostgreSQL Data Corrupted (SQLSTATE XX001)

When PostgreSQL detects that its own stored data is internally inconsistent or structurally invalid, it raises ERROR: could not read block N in file "base/...": read only 0 of 8192 bytes or similar messages accompanied by SQLSTATE XX001 (data_corrupted). This is an internal server error class — the error originates inside PostgreSQL itself, not from a query logic problem.

What This Error Means

SQLSTATE class XX covers internal errors — conditions that should never occur under normal PostgreSQL operation. XX001 specifically signals that PostgreSQL has found corruption in a data page: the on-disk representation of a heap (table) page, index page, visibility map, or free space map does not match what PostgreSQL expects. Unlike constraint violations or type errors, this error does not indicate a user mistake; it indicates a problem with the data PostgreSQL has written to or is reading from storage.

PostgreSQL stores data in 8 kB pages. Each page carries a checksum (when data_checksums is enabled) and internal structural metadata including page headers, item pointers, and line pointer arrays. When PostgreSQL reads a page and finds the header magic number invalid, the checksum wrong, tuple offsets that point outside the page, or other structural violations, it raises XX001 rather than silently returning bad data.

The transaction that encountered the corruption is immediately aborted. Depending on which relation is corrupt and how it is accessed, the error may surface only for specific queries or may block autovacuum and background workers as well. Importantly, the corruption is in the data files on disk — reconnecting or retrying the query will reproduce the same error.

Common Causes

1. Storage hardware failure or firmware bug. Disk write errors, bad sectors, failing SSDs, or faulty RAID controllers can cause partial or silent writes. Data written by PostgreSQL is stored incorrectly on disk, and corruption is detected on subsequent reads.

2. File system or OS-level bugs. Bugs in file system drivers, incorrect use of O_DIRECT, or a file system that does not guarantee write ordering can cause pages to be written out of order or incompletely during a crash, especially when fsync has been disabled.

3. Memory corruption (RAM errors). Faulty RAM or a DIMM with uncorrectable ECC errors can corrupt data in the PostgreSQL shared buffer cache before it is written to disk. The corrupted data is then persisted.

4. Improper PostgreSQL shutdown or crash without WAL replay. Copying data files while PostgreSQL is running, restoring a backup to a wrong point-in-time, or manually moving or truncating files in the data directory can leave data files in an inconsistent state that WAL replay cannot reconcile.

5. Checksum mismatch after enabling data_checksums on an existing cluster. If pg_checksums was run while the cluster was online, or if pages were modified between the offline checksum pass and startup, stale checksums can trigger false XX001 errors.

6. Bugs in PostgreSQL extensions or storage engines that write directly to relation files using low-level APIs can occasionally produce malformed pages that PostgreSQL later rejects.

How to Fix data_corrupted

1. Identify the corrupted object. The error message almost always includes the file path and block number. Map the file back to a relation using:

   SELECT relname, relkind
   FROM pg_class
   WHERE relfilenode = <filenode_number>;
   

   For example, if the error references base/16384/12345, the filenode is 12345. You can also query pg_relation_filepath('mytable') to confirm.

2. Check PostgreSQL logs for the full error chain. Look at postgresql.log for DETAIL and CONTEXT lines — they often include the block number, offset, and the type of page that is corrupt (heap, index, toast, etc.).

3. Run pg_dump to assess scope. Try dumping the affected table:

   pg_dump -t schema.tablename -Fc mydb > /tmp/tablename_dump.dump
   

   If pg_dump also errors on XX001, the corruption is confirmed on that table. If it succeeds, the corruption may be limited to a non-critical secondary structure (e.g., an index).

4. For corrupt indexes — reindex. If the affected file is an index, simply drop and rebuild it:

   REINDEX TABLE CONCURRENTLY myschema.mytable;
   -- or for a specific index:
   REINDEX INDEX CONCURRENTLY myschema.myindex;
   

   Index corruption does not imply heap corruption. Rebuilding the index from the heap data is safe if the heap itself is clean.

5. For corrupt heap pages — use zero_damaged_pages as a last resort. If you have no usable backup and can tolerate data loss for the affected pages:

   -- As a superuser, in a session where you accept data loss:
   SET zero_damaged_pages = on;
   SELECT * FROM mytable;  -- forces PostgreSQL to zero out corrupt pages instead of erroring
   RESET zero_damaged_pages;
   VACUUM FULL mytable;
   

   This will permanently destroy data on the corrupt pages. Use only when data loss is acceptable and you have confirmed no backup is available.

6. Restore from backup. For heap corruption where data integrity matters, a restore from a verified backup (logical dump, pg_basebackup, or a PITR snapshot) is the correct solution. Test the backup before assuming it is clean.

7. Investigate the underlying hardware. After addressing the immediate data issue, check storage health using smartctl, kernel logs (dmesg | grep -i error), and file system integrity tools. A recurring XX001 without an apparent cause strongly suggests hardware problems.

Additional Information

• SQLSTATE class XX (Internal Error) contains three codes: XX000 (internal_error), XX001 (data_corrupted), and XX002 (index_corrupted). If the error explicitly names an index, XX002 may be raised instead of XX001.
• data_checksums must be enabled at initdb time (or retrofitted with pg_checksums in PostgreSQL 12+). Without checksums, many forms of silent corruption go undetected until PostgreSQL encounters a structurally invalid page — at which point damage may already be widespread.
• PostgreSQL 14+ includes pg_amcheck, a dedicated corruption-checking utility that can scan heap and index pages for structural problems without requiring an active error to occur: pg_amcheck -d mydb --all.
• Drivers and ORMs that wrap all server errors into a generic "database error" may obscure XX001. In Java (JDBC), the SQLState field will be "XX001"; in psycopg2 (Python), check e.pgcode.
• Autovacuum and background workers that encounter XX001 will log the error and back off, but they will not crash postmaster. The affected table will accumulate dead tuples if autovacuum cannot process it.

Frequently Asked Questions

Can I recover data from a corrupt page without a backup?

Sometimes. If the corruption is limited to a few pages, zero_damaged_pages = on lets PostgreSQL skip the bad pages and read the rest of the table. You will lose the rows stored on the corrupt pages, but the remaining data is preserved. For a full-table corruption, a backup or replica is the only reliable path to recovery.

Does XX001 mean my entire database is corrupt?

Not necessarily. PostgreSQL raises XX001 on the specific page it cannot read. The corruption is often limited to a single table or index. The first step is to identify exactly which file and block are affected, then assess whether the problem is isolated or widespread.

How do I prevent data corruption in PostgreSQL?

Enable data_checksums at cluster creation time — this allows PostgreSQL to detect silent storage corruption early. Use reliable hardware with ECC RAM, avoid disabling fsync, ensure proper shutdown procedures, and take regular backups tested with pg_restore or pg_dump round-trips. Use pg_amcheck periodically on production clusters.

Why did this error appear after I migrated to a new server?

A common cause is copying data files while PostgreSQL was running, or failing to perform a clean shutdown before copying. Another cause is restoring a pg_basebackup snapshot without applying all required WAL. Both leave the data directory in a state that PostgreSQL flags as corrupted on startup or first access.