How to Turn PostgreSQL Unconventional Recovery into an Engineering Practice

When the database won't start, and there's no backup available either. In most teams, this means the incident report is already being written.

It's not that the data has definitely "disappeared," but because the database operating logic we're familiar with has failed. SQL is no longer usable, system catalogs cannot be queried, and the overall framework the database once provided has been pulled away, leaving only a set of files on disk that we need to understand all over again.

In PostgreSQL, this situation is especially clear. As long as there's no large-scale disk damage, its data files remain open and transparent to us. But once the database cannot start, transparency becomes a burden.

And this is where unconventional recovery truly begins.

The Data Is Still There, But It No Longer "Speaks"

Many people's first reaction is to read the data files. This is a natural response, but we quickly hit the first bottleneck.

Pages can be scanned, tuples can be parsed, and bytes can even be fully extracted. The problem lies at another layer: this content can no longer be easily interpreted. How many columns does a record have? Which are fixed-length, which are variable-length, has the order changed—without this information, the data loses its actual meaning.

Once structure disappears, the data itself is no longer complete.

And structure is not backed up in each table's data file.

Table Structure Determines Whether You Can Still "Understand" the Data

In PostgreSQL, table structure completely depends on the data dictionary. This is the first reality we must face in unconventional recovery.

Since the database cannot start, it means the data dictionary also cannot be accessed normally. pg_class, pg_attribute, pg_type—these system tables that are rarely noticed in normal times are now no different from regular business tables. They can also be corrupted, they can also have missing pages.

Most importantly, they also need to be parsed.

The question is not "can we read the system tables," but rather: can the content we read still form a reliable table structure?

The Data Dictionary Itself Is Not Complete Either

Even if system tables are successfully parsed, you cannot assume the entire database's table structures have been fully recovered.

A pitfall that's easy to step into comes from dropped columns. When PostgreSQL deletes a column, it handles it at the logical layer, not the physical layer. Deleted columns are marked, but they still occupy their original physical positions in the data area. If you ignore these marks, the parsing process will definitely drift off course without you noticing, and you'll only discover the result is unusable after the export is complete.

This kind of error is not obvious, but the impact is huge.

More complex situations appear with TOAST tables. Large fields are not stored in the main table, but scattered across TOAST tables. The mapping between main table records and TOAST data heavily depends on the system catalog. Once the database cannot start, this relationship needs to be clarified again, or large fields can only exist as fragments.

There are also more hidden problems. For example, system hidden columns. xmin, xmax, ctid exist at the physical layer, but they do not belong to user data. If you introduce these fields indiscriminately when rebuilding structure, the parsing process can easily be affected, and the final result will become unstable.

At this stage, the focus of recovery work has already shifted. It's no longer "how much can we read," but "which results can we trust?"

After Structure Is Clear, Engineering Problems Really Start to Show

Once table structure is correctly restored, table data regains the prerequisite for being processed. But this does not mean things start to become easy.

Real incident scenes are never clean and tidy.

Bad pages, incomplete tuples, unparsable data blocks will very likely appear. If parsing stops directly when encountering exceptions, then the sustainability of the entire recovery process cannot be guaranteed.

A mature recovery solution must be able to keep moving forward under imperfect conditions. Which data was successfully recovered, which data had to be skipped, can it be processed again later, what was the reason. This information needs to be clear and verifiable later.

At the same time, the project focus has officially shifted from "can it be done" to "how long will it take to finish." Unconventional recovery often happens when the business is already under high stress. Data volumes are easily hundreds of GBs, or even reach TBs. Once efficiency cannot be guaranteed, technical problems easily get amplified into business risks.

In this situation, recovery speed itself is part of the capability.

Looking Back at These Problems, They Are Not Actually Scattered

If we look at all the problems mentioned above together, we'll find they don't appear independently.

• Table structure is hard to determine, often because the data dictionary information itself is hard to extract.
• The data dictionary is hard to use directly, because it also has a learning and operational barrier.
• Dropped columns, TOAST, system hidden columns...

These seemingly different problems are all essentially related to unclear structural relationships.

And if engineers only handle a single point individually, it's easy to lose sight of the whole picture.

• Manually building structure only covers the current scenario; with a different batch of data, you have to start over.
• Temporary scripts solve the immediate problem, but it's hard to guarantee they can be reused next time.
• Overpursuing "complete recovery" actually makes the result lose credibility.

A more realistic approach is to accept the imperfection of existing conditions from the start. Under this premise, use the same set of methods to handle structure, data, and exceptional cases. Which information to keep, which to skip, all based on clear criteria, using the same processing logic, rather than making temporary decisions.

When these things are handled on the same line, unconventional recovery is no longer a series of passive responses, but a process that can be executed stably.

And precisely because of this, when this approach truly lands, the entire unconventional recovery process will become very easy and elegant. It doesn't rely on luck, nor does it require improvisation. The process is clear, conclusions can be reviewed, and even if the result is not 100% complete, you know where the problem is.

So what if I tell you there's already a tool that has fully implemented all the capabilities I mentioned above?