Debugging Data Corruption in PostgreSQL
summary
A practical framework walks from extracting clues in PostgreSQL logs to verifying files with ls, stat and dd, using pageinspect and GDB to inspect pages and trace mdread, and relying on pg_basebackup and smartctl for recovery and disk health. Common errors covered include Could not read block, Cannot freeze committed xmax, and Could not locate valid checkpoint; corrupted indexes are often fixed by reindex, missing heap blocks require a backup copy, and desperate recovery uses pg_resetwal.
AI-written summary · about
chapters (11)
- 0:00Introduction
- 1:17What is data corruption
- 2:21Causes of corruption
- 3:49Database layout basics
- 7:05Analyzing corruption cases
- 12:07Best practices for detection
- 14:36Management practices
- 15:39Backup and restore
- 16:44Detecting corruption
- 17:47Recovery techniques
- 19:12Q&A
related talks
32mDebugging Data Corruption in PostgreSQL: A Systematic Approach
51mDatabase in Distress: Testing and Repairing Different Types of Database Corruption
37mDatabase in Distress: Testing and Repairing Different Types of Database Corruption
52mData Corruption Bugs: Diagnosis and Lessons
45mDebugging Databases for Developers
33mFinding and fixing a data-corruption bug...
47mMy Journey in PostgreSQL bug fixing
42mHow to corrupt your database (and how to deal with data corruption)