Debugging Data Corruption in PostgreSQL: A Systematic Approach
summary
A systematic debugging framework walks through concrete steps: validate pg_controldata, run pg_waldump on the WAL named by the last checkpoint, use pg_checksums / pg_verify_checksums to detect bad pages (Postgres uses 8 KB pages and 1 GB relation segments), isolate corrupt objects with pg_amcheck, repair indexes with REINDEX, and only as a last resort use pg_resetwal (risking data loss). The talk reproduces corruption with dd, shows "could not read block" and "could not locate valid checkpoint", and cites backports to 15.11, 14.16, 13.19.
AI-written summary · about
chapters (7)
- 0:00Introduction
- 2:23What is Data Corruption?
- 3:16Causes of Corruption
- 10:49Analysis of a Few Corruption Cases
- 21:35Best Practices to Handling Corruption Cases
- 23:08How to detect corruption
- 29:36Corruption Recovery
related talks
20mDebugging Data Corruption in PostgreSQL
52mData Corruption Bugs: Diagnosis and Lessons
37mDatabase in Distress: Testing and Repairing Different Types of Database Corruption
51mDatabase in Distress: Testing and Repairing Different Types of Database Corruption
33mFinding and fixing a data-corruption bug...
45mDebugging Databases for Developers
47mMy Journey in PostgreSQL bug fixing
26mThe Wonderful World of WAL