PostgreSQL XX001 Error: Causes and Solutions Complete Guide

PostgreSQL Error XX001: Data Corrupted — Causes, Fixes, and Prevention

PostgreSQL error XX001 (data corrupted) is one of the most severe errors a DBA can encounter. It occurs when PostgreSQL detects that an internal data structure — such as a heap page, index block, or WAL segment — has become physically inconsistent and can no longer be trusted. Left unaddressed, this error can result in permanent data loss and extended service downtime.

---

Top 3 Causes

1. Hardware Failures (Bad Sectors, Faulty RAM)

Disk bad sectors or RAID controller errors silently corrupt data blocks written to disk. Non-ECC RAM can introduce bit flips that cause incorrect data to be persisted.

-- Check for checksum failures (requires data checksums enabled)
SELECT datname, checksum_failures, checksum_last_failure
FROM pg_stat_database
WHERE checksum_failures > 0;

-- Verify checksum status
SHOW data_checksums;

2. Unclean PostgreSQL Shutdown (SIGKILL, Power Loss)

Forcibly killing the PostgreSQL process with SIGKILL or a sudden power outage can interrupt WAL writes mid-flight, leaving data files and WAL in an inconsistent state. PostgreSQL's crash recovery may fail if the WAL itself is corrupted.

-- Check WAL archiver status after restart
SELECT
    last_archived_wal,
    last_archived_time,
    last_failed_wal,
    failed_count
FROM pg_stat_archiver;

-- Verify database consistency after recovery
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

3. Filesystem or Volume-Level Errors

Using unsafe filesystem mount options (e.g., nobarrier), misconfigured LVM volumes, or NFS mounts can corrupt PostgreSQL data files. Poorly timed cloud volume snapshots (without freezing I/O) are another common culprit in modern infrastructure.

-- Check table-level bloat and anomalies
SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

---

Quick Fix Solutions

Step 1: Force-read past corrupted pages with zero_damaged_pages

-- Enable temporarily to extract data from corrupted tables
-- WARNING: causes data loss for corrupted pages
SET zero_damaged_pages = on;

-- Attempt to read and export the table data
SELECT * FROM corrupted_table;

-- Always turn it off after use
SET zero_damaged_pages = off;

Step 2: Rebuild corrupted indexes

-- Find invalid indexes
SELECT indexrelid::regclass AS index_name,
       indrelid::regclass  AS table_name
FROM pg_index
WHERE indisvalid = false;

-- Rebuild without locking the table (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_your_index;

-- Rebuild all indexes on a table
REINDEX TABLE CONCURRENTLY your_table;

Step 3: Restore from backup using PITR

If data extraction fails, restore from the latest base backup and replay WAL archives.

-- After restore, confirm data integrity
SELECT schemaname, tablename, n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC;

-- Verify row counts on critical tables
SELECT COUNT(*) FROM critical_table;

---

Prevention Tips

Enable data checksums at cluster initialization or use pg_checksums --enable on an offline cluster. This lets PostgreSQL detect corruption at read time before it spreads.

-- Monitor checksum failures proactively
SELECT datname, checksum_failures
FROM pg_stat_database
WHERE checksum_failures > 0;

Set up WAL archiving and practice PITR drills regularly. A backup that has never been tested is not a reliable backup. Combine pg_basebackup with continuous WAL archiving and store archives on separate storage.

-- Confirm archiving is running cleanly
SELECT archived_count, failed_count, last_archived_time
FROM pg_stat_archiver;

---

Related Errors

Error Code	Name	Notes
XX002	index corrupted	Index-specific corruption; often fixable with REINDEX
58P01	undefined_file	Data file missing from disk entirely
57P03	cannot_connect_now	Server in recovery mode due to corruption

Key takeaway: XX001 is always a sign of a deeper infrastructure problem. Fix the root cause — whether hardware, OS, or operational — before restoring data, or you risk corruption recurring.

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.