PostgreSQL ACE & PostgreSQL Recovery Specialist
Developer of PDU (Prowess Data Unloader) • Writes about database file parsing and recovery
Database parsing and recovery notes
After becoming a PostgreSQL ACE, Zhang Chen explains why he uses the title “PostgreSQL Database Recovery Expert” and how PDU evolved from offline extraction and WAL mining to fragment scanning and recovery without a data dictionary.
Starting from a ransomware recovery case, this article explains how PostgreSQL single-file-per-relation storage can make catalog recovery especially difficult, and compares that exposure with MySQL and Oracle.
In PostgreSQL, a DELETE operation does not immediately erase data from disk. The MVCC mechanism retains deleted rows as dead tuples, and reading these dead tuples is one viable approach to data recovery. However, this approach has a clear time limitation: once autovacuum completes its cleanup, the dead tuples are physically removed, and recovery methods based on data files become ineffective. At this point, the WAL (Write-Ahead Log) offers an alternative recovery path. Specifically, the FPW (Full Page Write) mechanism within WAL is the foundation of this approach. PostgreSQL DELETE recovery tools — including PDU (PostgreSQL Data Unloader) — rely on this technique. Its key property: recovery requires a usable full-page image and the necessary WAL records from that image to the deletion. Retaining only the WAL files from the deletion period does not guarantee complete recovery. This article dissects that recovery path function by function, based on PostgreSQL 18 source code.
It is 3 AM, and an accidental DELETE has removed 500,000 customer records. Where should recovery start? This five-step guide covers preserving source files and WAL, locating the operation with PDU, and exporting the required records. Recovery depends on retaining the necessary WAL and data pages.
When the database cannot start and no backup is available, many teams assume recovery is over. In many cases the data is still on disk, but the data dictionary is no longer usable. This article examines core challenges in unconventional recovery: rebuilding table structures from damaged dictionaries, handling dropped columns and TOAST tables, continuing under imperfect conditions, and why recovery speed is itself part of the capability.
Instant recovery can read retained files when PostgreSQL cannot start. Backup recovery reconstructs database state from an existing backup and logs. This article explains the storage and parsing rules behind both methods, their requirements and limits, and how to choose a method during an incident.
A PostgreSQL instance that cannot start may still have readable data on disk. This article explains how retained pages and logs affect recovery, where instant recovery applies, and how to use the methods provided by PDU.
Disk bad blocks damaged the data dictionary, leaving a 1.5TB PostgreSQL database unable to start and backups unusable. This article records how PDU completed the emergency recovery within 48 hours.
How can table structures be reconstructed when pg_type and pg_attribute are partly damaged? This case records the dictionary repair and data export process, which recovered 46% of the data.
A client accidentally ran rm -rf /*, deleting the operating system and database files. After disk recovery specialists retrieved the files, we used PDU to complete adaptation, data export and import for the domestic PostgreSQL database in 4 hours.
Steps for debugging PostgreSQL source code with VSCode and a Linux virtual machine, including environment preparation, Remote-SSH connections, GDB and tools such as pg_waldump.