How to Recover PostgreSQL When Data Dictionary Gets Corrupted - A Real Case Study

This is a scenario I encountered last weekend. I was busy this week and just had time to organize it. I found it quite interesting, so I'd like to share it with you.

Customer Scenario

A PostgreSQL database with a very small amount of data, version 9.5, had certain disk blocks damaged, causing every file to have a regular 256K sector loss (I'm not an expert in hardware, but that's roughly what happened - the subsequent analysis confirmed this). The database definitely couldn't be opened, and the customer hoped to recover it.

Parsing the Data Dictionary

Since PDU has only been adapted for PostgreSQL 10-17 and openGauss, I had never initialized it on PG 9 before, so I was a bit uncertain. However, I figured it should be the same as PG 10, so I first requested the data dictionary files to try initialization.

Unable to Parse

The result was very unexpected. The initialization crashed after just a few steps. After careful analysis, I found it wasn't due to the database version issue. The root cause was data dictionary corruption. Remember I mentioned that this scenario has:

Every file has a regular 256K sector loss

This includes dictionary tables like pg_class, pg_type, and pg_attribute. I had never imagined what it would mean when these dictionary tables were partially corrupted.

Specific Cause

pg_attribute stores column information for each table, including column name attname, column type id atttypid (which doesn't directly store the column type name, but an id), etc. The atttypid is equivalent to the oid in pg_type, and by comparing these two columns, we can derive the actual type name - the typname in pg_type.

As I mentioned earlier, all dictionary tables have corrupted 256k data (32 PG data pages), and the corrupted region of the pg_type table happened to contain basic types such as:

• 1043 varchar
• 1082 date
• 1083 time
• 1114 timestamp
• ......

This prevented PDU from getting the column types for certain columns: Suppose a column in a table has an atttypid of 1114. Since the data block containing 1114 in the pg_type file is within the lost 256k, PDU didn't parse this information, so it couldn't match the specific type for this column, leading to a series of subsequent failures.

Solution

The cause was both frustrating and amusing. Later I realized that in all versions of PG, the ids of basic types in pg_type seem to be the same, so I copied a PDU-parsed result of pg_type from another version.

I added a breakpoint to the program, and before PDU needed to read pg_type.txt, I manually copied and pasted the missing parts. This ensured that subsequent parsing could proceed.

Missing Many Tables

This database had only one main public schema (default id is 2200). Under this schema, PDU parsed out 122 table objects from pg_class.

Table objects under public schema

However, after matching all data types, only 57 tables were successfully parsed under the public schema.

Parsing result of public schema

This puzzled me greatly:

Where did I filter out the other tables?

After going through the code, I suddenly realized - I had specifically commented: only write tables with column information

Code snippet

So some tables didn't have their corresponding column information matched.

To see which tables were affected, I added a print statement to output the names of tables without column information.

Principle Explanation

Why would some tables have no column information? Suppose a table object has an oid of 274402, then pg_attr.txt would contain the column information corresponding to this oid.

The tables printed as missing column information in the image below, without exception, had their columns completely unsearchable in pg_attr.txt. Obviously, their column information was all within the lost 256k of pg_attribute.

Tables missing column information

Overall Situation

• I originally thought pg_class should have lost quite a bit of data too, but from PDU's output, only pg_attribute and pg_type had empty pages - pg_class and pg_schema were not corrupted. So the 112 tables parsed from pg_class should be close to the actual number.
• PDU parsed 122 tables from pg_class, but only 57 tables had complete parseable table information - that's only 46% of the tables remaining.
• After explaining the situation, the customer indeed had no desire to proceed with recovery. After all, with less than 50% of the tables remaining, it would be better to restore from backup.