Recover from Corrupted Data File

This guide covers recovering data from a single corrupted PostgreSQL data file. When PostgreSQL reports file corruption errors and a table becomes inaccessible, PDU can extract readable data directly from the damaged file.

Prerequisites

Only PGDATA configuration is required for this recovery scenario
You need to know the table structure (column types) of the corrupted table
The corrupted data file should be accessible (even if PostgreSQL cannot read it)

I. Initialization

1.1 Configure pdu.ini

Edit the pdu.ini configuration file. For data file recovery, only the PGDATA directory needs to be specified.

vim pdu.ini
#PostgreSQL data directory
PGDATA=/home/pg/data
#PostgreSQL archive directory
ARCHIVE_DEST=/home/pg/wal_arch
#Disk for dropScan to scan
DISK_PATH=/dev/mapper/centos-root
#Number of data blocks to skip during dropScan
BLOCK_INTERVAL=5

1.2 Start PDU and Execute Initialization

Enter the PDU directory and execute the b; command to complete initialization:

[root@node1 pdu]# ./pdu
 
╔══════════════════════════════════════════════════════╗
║  Copyright 2024-2025 ZhangChen. All rights reserved  ║
║  PDU: PostgreSQL Data Unloader                       ║
║  Version 3.0.25.9   (2025-09-03)                     ║
╚══════════════════════════════════════════════════════╝
 
PDU.public=# b;
 
Initializing...
 -pg_database:</home/pg/data/global/1262>
 
Database:postgres
      -pg_schema:</home/pg/data/base/5/2615>
      -pg_class:</home/pg/data/base/5/1259> Total 82 rows
      -pg_attribute:</home/pg/data/base/5/1249> Total 3126 rows
      Schemas:
        ▌ public 0 tables
 
Database:mydb
      -pg_schema:</home/pg/data/base/16384/2615>
      -pg_class:</home/pg/data/base/16384/1259> Total 506 rows
      -pg_attribute:</home/pg/data/base/16384/1249> Total 9232 rows
      Schemas:
        ▌ public 1 tables
        ▌ app 211 tables
PDU.public=#

II. Add Data File to PDU

2.1 Copy the Corrupted Data File

Copy the data file that needs data extraction to the restore/datafile directory under the PDU installation path:

[root@node1 pdu]# cp /home/pg/data/base/16384/26213 restore/datafile/

2.2 Register the Table File

Switch to the restore database and use the add command to register the copied table file:

Command Syntax:

add <filenode> <table_name> <column_type_list>;

Example:

PDU.public=# use restore;
 
┌────────────────────────────────────────┐
│          Schema           │  Tab Num   │
├────────────────────────────────────────┤
│    public                 │  0         │
└────────────────────────────────────────┘
 
restore.public=# add 26213 t_payment_detail varchar,bpchar,varchar,date,varchar,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,timestamptz,bpchar,varchar;
Addition complete. Please use \dt; to view tables available for unload.
 
restore.public=# \dt;
 
┌──────────────────────────────────────────────────┐
│               TableName             │  Tab Size  │
├──────────────────────────────────────────────────┤
│    t_payment_detail                 │  690.30 MB │
└──────────────────────────────────────────────────┘
 
        Total 1 table

Important: Filenode Must Match

The filenode parameter must exactly match the actual data file name. If they don't match, you'll see an error like this:

restore.public=# add 26212 t_payment_detail varchar,bpchar,...;
There is not datafile <26212> in path [restore/datafile], adding datafile Failed

Solution: Change the filenode to match the actual data file name in the datafile directory.

III. Parse the Data File

Use the u tab command to parse and extract data from the registered table file:

restore.public=# u tab t_payment_detail;
 
#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
        |-Block 88369 Empty Page Or Page Corrupted, Skipped
        |-Block 88372 Empty Page Or Page Corrupted, Skipped
        |-Block 88375 Empty Page Or Page Corrupted, Skipped
        |-Block 88378 Empty Page Or Page Corrupted, Skipped
        |-Block 88381 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
└─────────────────────────────────────────────────────────┘
Execution Time 44.41 seconds

PDU automatically detects and skips corrupted blocks while extracting all valid records. The recovered data is saved as a CSV file in the restore/public/ directory.

IV. Handling TOAST Files

If your table contains large data fields (text, bytea, etc.), it may have an associated TOAST file. PDU automatically detects TOAST dependencies.

4.1 When TOAST File is Missing

If a required TOAST file is not present, PDU will report the missing filenode and skip related records:

restore.public=# u tab gp_public_invest;
 
#1 Tab<gp_public_invest> Current Page: 1908  Records parsed: 11842  State: Complete
 
▌ Parse Complete
┌─────────────────────────────────────────────────────────┐
 Table gp_public_invest(restore/datafile/177866)
 ● Pages: 1908           ● 18450 Records in total
 ● Success: 11842        ● Failure: 6608
 ● File Path: restore/public/gp_public_invest.csv0
└─────────────────────────────────────────────────────────┘
toast file 177869 does not exist, all related records will not be parsed
FAIL PARSING TABLE<gp_public_invest>,please check log log/restore_public_unload_gp_public_invest_err.txt
Execution Time 0.21 seconds

4.2 Adding the TOAST File

To recover all records including those with TOAST data, follow these steps:

Step 1: Copy the TOAST file

[root@node1 pdu]# cp /home/pg/data/base/16384/177869 restore/datafile/

Step 2: Edit the metadata file

[root@node1 pdu]# vim restore/meta/public_tables.txt

Find the row for your table and replace TOASTOID and TOASTNODE with the actual values:

# Before:
177866  177866  TOASTOID  TOASTNODE  2200  gp_public_invest  ATTR  varchar,bpchar,...

# After:
177866  177866  177869    177869     2200  gp_public_invest  ATTR  varchar,bpchar,...

Step 3: Unload again

restore.public=# u tab gp_public_invest;
 
#1 Tab<gp_public_invest> Current Page: 1908  Records parsed: 18450  State: Complete
 
▌ Parse Complete
┌─────────────────────────────────────────────────────────┐
 Table gp_public_invest(restore/datafile/177866)
 ● Pages: 1908           ● 18450 Records in total
 ● Success: 18450        ● Failure: 0
 ● File Path: restore/public/gp_public_invest.csv0
└─────────────────────────────────────────────────────────┘
Execution Time 5.94 seconds

Output Files

After successful recovery, the extracted data is saved in CSV format:

Location:restore/public/<table_name>.csv0

You can import this CSV file back into PostgreSQL using the COPY command or any data import tool.

Common Issues

Q: How do I find the filenode of my table?

If PostgreSQL is still running, you can query the pg_class system table:

SELECT relfilenode FROM pg_class WHERE relname = 'your_table_name';

Q: How do I know the column types of my table?

You can get column types from your application code, database documentation, or if PostgreSQL is accessible, use:

\d+ your_table_name

Q: What if I see 'Empty Page Or Page Corrupted, Skipped' messages?

This is normal behavior. PDU automatically detects and skips corrupted or empty data blocks while extracting data from valid blocks. The final success count shows how many records were successfully recovered.