Database in Distress: Testing and Repairing Different Types of Database Corruption
Friday, October 24 at 14:55–15:45
In recent years, I had to repair several corrupted PostgreSQL databases and have seen many different problems. In some cases corrupted data can even crash the session reading it. To better understand these issues and test different strategies for repairs, I created a Python application that surgically creates various types of damages. This talk demonstrates, through practical examples and outputs from the pageinspect extension, different types of data corruption. Why we see specific errors and how to diagnose these issues. PostgreSQL 18 sets checksums ON by default in initdb, therefore talk also shows how checksums can help to detect damaged pages and "zero them out". It also proposes some improvements that could help to handle corrupted data more effectively on already existing databases which do not use checksums.
Takeaways: - PostgreSQL databases can suffer from various types of corruption - Understanding heap table structure is essential for analyzing different types of errors - Data page checksums are the best tool for skipping damaged pages - Without checksums only pages with clearly corrupted header can be removed using the zero_damaged_pages = on setting - Other types of corruption might require time-consuming salvaging operations - Improvements in PostgreSQL code and/or new settings could make some repairs less painful on databases without checksums
