Recover Deleted Records

This guide covers recovering accidentally deleted records by parsing WAL archive files. PDU extracts the deleted data from WAL logs and restores it to CSV files for reimport.

Critical Prerequisites

WAL archiving must be enabled
The principle of recovering deleted data involves extracting and integrating data from WAL files.
Ensure WAL files are archived after deletion
Run these commands after deletion to force archival:SELECT pg_switch_wal(); CHECKPOINT;

I. Initialization

1.1 Configure pdu.ini

For deleted records recovery, you need to configure both PGDATA and ARCHIVE_DEST:

[root@node1 pdu]# cat 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=
#Number of data blocks to skip during dropScan
BLOCK_INTERVAL=20
#PGDATA to be excluded during dropscan
PGDATA_EXCLUDE=

1.2 Start PDU and Initialize

[root@node1 pdu]# ./pdu
 
╔══════════════════════════════════════════════════════╗
║  PDU: PostgreSQL Data Unloader                       ║
║  Version 3.0.25.10  (2025-10-13)                     ║
╚══════════════════════════════════════════════════════╝
 
PDU.public=# b;
 
Starting initialization...
 -pg_database:</home/pg/data/global/1262>
 
Database:postgres
      -pg_schema:</home/pg/data/base/13023/2615>
      -pg_class:</home/pg/data/base/13023/1259> Total 80 rows
      Schemas:
        ▌ public 0 tables
 
Database:mydb
      -pg_schema:</home/pg/data/base/24576/2615>
      -pg_class:</home/pg/data/base/24576/1259> Total 11213 rows
      Schemas:
        ▌ public 1071 tables
        ▌ app 193 tables
        ▌ hr 221 tables

II. Scan for Deleted Records

2.1 Navigate to the Target Table

Switch to the database and schema containing the table you want to recover:

PDU.public=# use mydb;
┌────────────────────────────────────────┐
│          Schema           │  Tab Num   │
├────────────────────────────────────────┤
│    public                 │  1071      │
│    app                    │  193       │
│    hr                     │  221       │
└────────────────────────────────────────┘
 
mydb.public=# set public;

2.2 Execute the Scan Command

Use the scan command to scan WAL files for deleted records of a specific table:

mydb.public=# scan t_payment_records;
 
Scanning deleted Records for table<t_payment_records>...
 
 
▌ Scanning Archived Wal Directory
   StartWal: 000000030000052500000050
   EndWal: 0000000300000525000000EF
 
▌ Time Range Restore Mode [Displayed all within Time Range]
────────────────────────────────────────
▌ End of Scanning, current time range:
  Start: 2025-03-05 05:05:38.006348 EST
  End: 2025-03-05 06:00:19.515825 EST
 
▌ Time Range Details
┌─────────────────────────────────────────────────────────┐
 Start Time: 2025-03-05 05:05:38.006348 EST
 End Time: 2025-03-05 06:00:19.515825 EST
 LSN: 525/532B3990 - 525/DCAF8FD0
 Recommended startwal: 000000030000052500000053
 Recommended endwal: 0000000300000525000000DC
       -------------------.--------------------
 ● Datafile OID: 144835    ● Toastfile OID: 64598
 ● Records deleted in the Time Range: 9648
└─────────────────────────────────────────────────────────┘
 
  [!] Note: The 'Recommended startwal' indicates the suggested value to set for startwal during transaction recovery
            The 'Recommended endwal' mandates that startwal must be set to this value, otherwise recovery may fail
 
Execution Time 38.30 seconds

2.3 Understanding the Scan Output

Field	Description
StartWal / EndWal	The range of WAL files scanned in the archive directory
Start Time / End Time	The time range of transaction commits found in the scanned WAL files
LSN	Log Sequence Number range, identifies the exact WAL position
Recommended startwal/endwal	Suggested WAL file boundaries containing the deletion records
Datafile OID	The main table file OID
Toastfile OID	The TOAST table file OID (if applicable)
Records deleted	Number of deleted records found within the time range

III. Restore Deleted Data

Use the restore del all command to recover all deleted records found in the scan:

mydb.public=# restore del all;
 
▌ Scanning Archived Wal Directory
   StartWal: 000000030000052500000050
   EndWal: 0000000300000525000000EF
 
▌ Time Range Restore Mode [Displayed all within Time Range]
────────────────────────────────────────
|-Records Decoded: 2
Missing FPI blk 272 found during restoreDEL, single record parsing failed
 
Missing FPI blk 253 found during restoreDEL, single record parsing failed
|-Records Decoded: 9646
▌ Restore Complete
┌───────────────────────────────────────────────────────────────┐
 Table <t_payment_records>
 ● Records Restored: 9648
 ● Success: 9646       ● Failure: 2
 ● File Path: restore/public/t_payment_records_del_2025-03-05 05:05:38.006348 EST_2025-03-05 06:00:19.515825 EST.csv
└───────────────────────────────────────────────────────────────┘
Execution Time 18.49 seconds

Handling Missing FPI (Full Page Image)

If you see "Missing FPI" errors, it means some WAL records couldn't be fully parsed because the Full Page Image is not present. To resolve this:

Solution

Include more WAL files in the scan range. Set the startwal parameter to an earlier file to provide more context for page reconstruction.

IV. Transaction Recovery Mode

The default recovery mode is Time Range Mode, which recovers all deletions within a time range. PDU also supports Transaction Mode for precise recovery by specific transaction IDs.

4.1 Switch to Transaction Mode

mydb.public=# p resmode tx;
┌─────────────────────────────────────────────────────────────────┐
│            parameter             │             value            │
├─────────────────────────────────────────────────────────────────┤
│    startwal                      │                              │
│    endwal                        │                              │
│    starttime                     │                              │
│    endtime                       │                              │
│    resmode(Data Restore Mode)    │              TX              │
│    restype(Data Restore Type)    │              DELETE          │
└─────────────────────────────────────────────────────────────────┘

4.2 Scan Again to See Transaction Details

mydb.public=# scan t_payment_records;
 
Scanning deleted Records for table<t_payment_records>...
 
▌ Scanning Archived Wal Directory
   StartWal: 000000030000052500000050
   EndWal: 0000000300000525000000EF
 
▌ Tx Restore Mode [Displayed by Tx groups]
────────────────────────────────────────
▌ End of Scanning, current time range:
  Start: 2025-03-05 05:05:38.006348 EST
  End: 2025-03-05 06:00:19.515825 EST
 
▌ Tx Details
┌─────────────────────────────────────────────────────────┐
 Timestamp: 2025-03-05 05:06:20.420863 EST
 LSN: 525/532B3990 - 525/532B4470
 Recommended startwal: 000000030000052500000053
 Recommended endwal: 000000030000052500000053
       -------------------.--------------------
 ● Tx Number: 1360118630   ● Records deleted by the TX: 1
 ● Datafile OID: 144835    ● Toastfile OID: 64598
└─────────────────────────────────────────────────────────┘
 
▌ Tx Details
┌─────────────────────────────────────────────────────────┐
 Timestamp: 2025-03-05 05:48:56.389241 EST
 LSN: 525/CAA750D0 - 525/CAABCEC0
 Recommended startwal: 0000000300000525000000CA
 Recommended endwal: 0000000300000525000000CA
       -------------------.--------------------
 ● Tx Number: 1360536375   ● Records deleted by the TX: 50
 ● Datafile OID: 144835    ● Toastfile OID: 64598
└─────────────────────────────────────────────────────────┘
 
... (more transactions) ...
 
Execution Time 29.48 seconds

4.3 Restore Specific Transaction

Use the restore del <tx_id> command to recover data from a specific transaction:

mydb.public=# restore del 1360536375;
 
▌ Scanning Archived Wal Directory
   StartWal: 000000030000052500000050
   EndWal: 0000000300000525000000EF
 
Current startwal setting has significant deviation from recommended value
causing degraded recovery efficiency. Confirm execution? (y/n)  y
▌ Tx Restore Mode [Displayed by Tx groups]
────────────────────────────────────────
|-Records Decoded: 50
▌ Restore Complete
┌───────────────────────────────────────────────────────────────┐
 Table <t_payment_records>
 ● Records Restored: 50
 ● Success: 50         ● Failure: 0
 ● File Path: restore/public/t_payment_records_1360536375.csv
└───────────────────────────────────────────────────────────────┘
Execution Time 17.93 seconds

Scan Phase Notes

WAL File Count Limit

To ensure scanning efficiency, PDU will report an error if there are more than 250 WAL files in the scan range:

mydb.public=# scan t_payment_records;
 
Scanning deletion records for table <t_payment_records>...
 
▌ Scanning Archive Directory
   Start File: 000000010000000000000001
   End File: 000000010000000100000059
 
▌ Time Range Recovery Mode [Display all within the time range]
────────────────────────────────────────
The current WAL file range contains 345 files, exceeding the limit of 250.
It is recommended to set the parameters startwal and endwal. This execution will be aborted.

Solution: Set startwal Parameter

Check the timestamps of WAL files in the archive directory and set startwal to a file from shortly before the deletion occurred:

# Check WAL file timestamps
ls -la /home/pg/wal_arch/
-rw-------. 1 pg pg 16777216 Nov 15 22:30 000000010000000100000042
-rw-------. 1 pg pg 16777216 Nov 15 22:31 000000010000000100000047
-rw-------. 1 pg pg 16777216 Nov 16 21:36 000000010000000100000048
 
# If deletion occurred around 21:30 on Nov 16, set startwal to 000000010000000100000047
mydb.public=# p startwal 000000010000000100000047;

Output Files

Recovered data is saved as CSV files in the restore/ directory:

Recovery Mode	File Path
Time Range Mode	`restore/<schema>/<table>_del_<start_time>_<end_time>.csv`
Transaction Mode	`restore/<schema>/<table>_<tx_id>.csv`

You can import these CSV files back into PostgreSQL using standard COPY commands or any data import tool.