Back to Scenarios

Are Data Files Damaged?

Data File Damage Recovery

Scenario Description

A hardware issue, storage system failure, or unexpected system crash has corrupted one or more PostgreSQL data files. The database may fail to start, or specific tables return errors when queried. Since the database cannot be accessed normally, pg_dump cannot extract the data either.

Typical causes of corruption:

  • Hard disk failure or bad sectors
  • RAID array degradation or failure
  • Storage system firmware issues
  • Unexpected server power loss
  • VM storage snapshot anomalies
  • File system corruption

Challenges

When data files are corrupted, PostgreSQL may refuse to start or crash when accessing damaged pages. Standard tools like pg_dump require a running database. Low-level tools like pg_filedump exist but require deep PostgreSQL internals knowledge.

Common Error Messages

  • - invalid page in block
  • - could not read block
  • - page verification failed
  • - checksum verification failed

Traditional Method Limitations

  • - pg_dump needs database running
  • - pg_filedump is complex to use
  • - Backup recovery takes long time
  • - May lose some data

PDU Solution

PDU can directly read and parse PostgreSQL data files without requiring a running database. It understands the page format and tuple structure, allowing it to extract readable data even from partially corrupted files, skipping damaged sections.

Recovery Principle

Locate data files
Parse page structure
Extract valid data

PDU Core Capabilities

  • Reads data files directly without needing PostgreSQL to be running
  • Tolerates partial corruption by skipping damaged pages
  • Extracts data from individual table files (e.g., base/16384/16385)
  • Supports all PostgreSQL page formats from version 10 to 18
  • Outputs clean CSV data that can be imported into a new database
  • Handles TOAST tables for large field values automatically
Quick Command Example
# Step 1: Copy corrupted data file to PDU directory
$ cp /home/pg/data/base/16384/26213 restore/datafile/
# Step 2: Start PDU and switch to restore database
PDU.public=# use restore;
# Step 3: Register table file (specify filenode, table name, column types)
restore.public=# add 26213 t_orders varchar,int4,numeric,timestamptz;
# Step 4: Parse and extract data
restore.public=# u tab t_orders;

Recovery Process Details

1

Copy Data File

Copy the data file that needs recovery to PDU's restore/datafile directory. This protects the original file from modification.

2

Register Table Structure

Use the add command to register the table's filenode, table name, and column types. PDU needs the table structure to parse data correctly.

3

Parse and Extract

PDU scans the data file page by page, automatically skipping corrupted pages and extracting all readable data from valid pages.

4

Import to New Database

The recovered CSV data can be imported into a new PostgreSQL database using COPY command or other tools.

Output Example

#1 Tab<t_payment_detail> Current Page: 88342  Records decoded: 3778871  State: Running

        |-Block 88360 Empty Page Or Page Corrupted, Skipped
        |-Block 88363 Empty Page Or Page Corrupted, Skipped
        |-Block 88366 Empty Page Or Page Corrupted, Skipped

#1 Tab<t_payment_detail> Current Page: 88386  Records decoded: 3779610  State: Complete

▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
 Table t_payment_detail(restore/datafile/26213)
 ● Pages: 88386          ● 3779610 Records in total
 ● Success: 3779610      ● Failure: 0
 ● File Path: restore/public/t_payment_detail.csv0
└─────────────────────────────────────────────────────────┘

PDU automatically detects and skips corrupted pages (like Block 88360, 88363, 88366 above) while extracting data from all valid pages.

Prerequisites

  • Only PGDATA configuration required

    This recovery scenario doesn't require WAL archiving, only access to data files.

  • Need to know table structure

    Column type information can be obtained from application code, documentation, or other databases.

  • Data files are accessible

    Even if PostgreSQL cannot read them, as long as the file system can access the data files.

View detailed steps for this recovery method