PGDay Lowlands2025

Every relation in PostgreSQL can be damaged, and sometimes the errors reported by the database are rather strange. In some cases, a session reading corrupted data can even crash the whole database. In recent years, I had to work on repairs of several corrupted PostgreSQL databases and have seen many different problems. To better understand these issues and test different strategies for repairs, I created a Python application that simulates various types of damage. This talk demonstrates, through practical examples and outputs from the pageinspect extension, different types of data corruption—and proposes some future improvements that would help handle them more effectively.

Takeaways: - PostgreSQL databases can suffer from various types of corruption across different relations - Understanding heap table structure is essential for recognizing different types of errors - Currently, only pages with a crearly corrupted header can be easily removed using the zero_damaged_pages = on setting - Other types of corruption often require time-consuming salvaging operations - Improvements in PostgreSQL code and new settings could make repairs less painful