Troubleshooting PostgreSQL Page Corruption: A Technical Guide

PostgreSQL page corruption is a critical database issue that can lead to data loss and application downtime. This comprehensive guide covers detection, diagnosis, and recovery strategies for database administrators.

Understanding PostgreSQL Page Corruption

Page corruption occurs when data blocks become unreadable or contain invalid data structures. Common causes include:

• Hardware failures (disk, memory, storage controllers)
• File system corruption
• Power outages during write operations
• Software bugs in PostgreSQL or underlying systems
• Network issues in distributed storage environments

Detection Methods

1. Automated Detection with pg_checksums

-- Enable checksums on new clusters (PostgreSQL 12+)
initdb --data-checksums

-- Check existing cluster checksum status
SHOW data_checksums;

-- Verify checksums manually
pg_checksums --check /path/to/data/directory

2. Query-Based Detection

-- Monitor for corruption-related errors
SELECT * FROM pg_stat_database_conflicts 
WHERE confl_bufferpin > 0 OR confl_deadlock > 0;

-- Check for invalid pages during sequential scans
SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del 
FROM pg_stat_user_tables 
WHERE seq_scan > 0 AND seq_tup_read = 0;

3. Log Analysis

Monitor PostgreSQL logs for corruption indicators:

# Search for corruption-related errors
grep -i "corrupt\|invalid\|checksum\|bad block" /var/log/postgresql/postgresql.log

# Common error patterns
grep "invalid page header\|page verification failed\|could not read block" /var/log/postgresql/postgresql.log

Diagnostic Procedures

Page-Level Analysis

-- Install pageinspect extension
CREATE EXTENSION IF NOT EXISTS pageinspect;

-- Examine page header information
SELECT * FROM page_header(get_raw_page('table_name', 0));

-- Check page checksums
SELECT page_checksum(get_raw_page('table_name', 0), 0);

-- Analyze heap page structure
SELECT * FROM heap_page_items(get_raw_page('table_name', 0));

Table-Level Verification

-- Comprehensive table check
DO $$
DECLARE
    rec RECORD;
    error_count INTEGER := 0;
BEGIN
    FOR rec IN 
        SELECT schemaname, tablename 
        FROM pg_tables 
        WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
    LOOP
        BEGIN
            EXECUTE format('SELECT COUNT(*) FROM %I.%I', rec.schemaname, rec.tablename);
        EXCEPTION 
            WHEN OTHERS THEN
                error_count := error_count + 1;
                RAISE NOTICE 'Corruption detected in table: %.%', rec.schemaname, rec.tablename;
        END;
    END LOOP;

    RAISE NOTICE 'Total corrupted tables: %', error_count;
END $$;

Recovery Strategies

1. Point-in-Time Recovery (PITR)

# Stop PostgreSQL service
systemctl stop postgresql

# Restore from base backup
pg_basebackup -h backup_server -D /var/lib/postgresql/data_new -U postgres -W

# Configure recovery
cat > /var/lib/postgresql/data_new/recovery.conf << EOF
restore_command = 'cp /path/to/wal_archive/%f %p'
recovery_target_time = '2025-06-11 10:00:00'
EOF

# Start recovery process
systemctl start postgresql

2. Selective Data Recovery

-- Create new table structure
CREATE TABLE table_name_recovered (LIKE table_name_corrupted);

-- Copy recoverable data
INSERT INTO table_name_recovered 
SELECT * FROM table_name_corrupted 
WHERE ctid NOT IN (
    SELECT ctid FROM table_name_corrupted 
    WHERE -- corruption detection logic
);

-- Verify data integrity
SELECT COUNT(*) FROM table_name_recovered;

3. Zero-Damage Recovery

-- Set zero_damaged_pages (use with extreme caution)
SET zero_damaged_pages = on;

-- Attempt to read corrupted table
SELECT COUNT(*) FROM corrupted_table;

-- Reset parameter
SET zero_damaged_pages = off;

Prevention Strategies

Hardware-Level Protection

# Enable filesystem checksums (ZFS example)
zpool create -o feature@sha512=enabled pgdata /dev/sdb
zfs set checksum=sha512 pgdata

# Configure RAID with proper write barriers
echo 'barrier=1' >> /etc/fstab

PostgreSQL Configuration

# postgresql.conf optimizations
wal_sync_method = fdatasync
fsync = on
synchronous_commit = on
full_page_writes = on
wal_log_hints = on

# Enable checksums
data_checksums = on

# Monitoring settings
log_checkpoints = on
log_min_duration_statement = 1000

Monitoring Implementation

-- Create monitoring function
CREATE OR REPLACE FUNCTION check_database_health()
RETURNS TABLE(
    check_type TEXT,
    status TEXT,
    details TEXT
) AS $$
BEGIN
    -- Check for checksum failures
    RETURN QUERY
    SELECT 'checksum_failures'::TEXT, 
           CASE WHEN checksum_failures > 0 THEN 'CRITICAL' ELSE 'OK' END,
           'Failures: ' || checksum_failures::TEXT
    FROM pg_stat_database 
    WHERE datname = current_database();

    -- Check for conflicts
    RETURN QUERY
    SELECT 'conflicts'::TEXT,
           CASE WHEN confl_bufferpin > 0 THEN 'WARNING' ELSE 'OK' END,
           'Buffer conflicts: ' || confl_bufferpin::TEXT
    FROM pg_stat_database_conflicts
    WHERE datname = current_database();
END $$ LANGUAGE plpgsql;

-- Schedule regular health checks
SELECT cron.schedule('database-health-check', '*/15 * * * *', 'SELECT * FROM check_database_health();');

Emergency Response Procedures

Immediate Actions

1. Isolate the affected system – Prevent further corruption
2. Document the error – Capture logs and error messages
3. Assess impact – Determine affected tables and data volume
4. Implement temporary workarounds – Route traffic to replicas if available

Recovery Workflow

#!/bin/bash
# Emergency corruption response script

# 1. Create incident timestamp
INCIDENT_TIME=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/var/backups/postgres/incident_${INCIDENT_TIME}"

# 2. Capture current state
mkdir -p "${BACKUP_DIR}"
pg_dumpall --globals-only > "${BACKUP_DIR}/globals.sql"
cp -r /var/lib/postgresql/data/pg_log "${BACKUP_DIR}/"

# 3. Attempt selective backup of uncorrupted data
pg_dump --exclude-table=corrupted_table_name database_name > "${BACKUP_DIR}/partial_backup.sql"

# 4. Document corruption extent
psql -c "SELECT * FROM check_database_health();" > "${BACKUP_DIR}/health_report.txt"

# 5. Initiate recovery procedure
echo "Incident ${INCIDENT_TIME} documented. Proceeding with recovery..."

Performance Impact Mitigation

Checksum Overhead Management

-- Monitor checksum performance impact
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;

-- Optimize frequently scanned tables
CREATE INDEX CONCURRENTLY idx_optimized ON large_table (frequently_queried_column);

Resource Allocation

# Adjust shared buffers for corruption checking
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

# WAL configuration for corruption prevention
wal_buffers = 16MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9

PostgreSQL page corruption requires immediate attention and systematic approach to resolution. Regular monitoring, proper hardware configuration, and tested recovery procedures are essential for maintaining database integrity. Implement these strategies proactively to minimize corruption risks and ensure rapid recovery when issues occur.

Comprehensive Guide to Troubleshooting Unexpected NULL Values in MariaDB

How to avoid “OR” in MariaDB for better performance?

PostgreSQL Health Check