Recover Dropped Tables Without Backup

This is PDU's most powerful feature. When tables are dropped or truncated, PostgreSQL marks the space as free but doesn't immediately overwrite the data. PDU can find and recover this data through disk fragment scanning.

The only publicly documented practical method to restore DROP TABLE or TRUNCATE without backups.

I. Preserve the Disk Image Immediately

After a DROP/TRUNCATE incident, the first PDU recovery action must be to preserve the disk image. Before running b;, use, scan drop, meta tab, editing tab.config, or any scan and recovery command, stop writes whenever possible and complete only the required disk configuration below.

1.1 Stop Writes and Configure pdu.ini

The following command identifies the filesystem device that contains the PostgreSQL data directory. Replace /home/pg/data with the actual PGDATA path. df -h displays the filesystem for that directory, while awk 'NR>1 {print $1}' skips the header and prints the first column. The resulting device path, such as /dev/mapper/data-lv, is the value to use for DISK_PATH.

# Replace /home/pg/data with the actual PGDATA path.
# Print the filesystem device containing PGDATA and use it as DISK_PATH.
[root@node1 ~]# df -h /home/pg/data | awk 'NR>1 {print $1}'
/dev/mapper/data-lv
 
# Update pdu.ini
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/data-lv
#Number of data blocks to skip during dropScan
BLOCK_INTERVAL=20
#All PostgreSQL data directories on DISK_PATH, comma-separated
PGDATA_EXCLUDE=/home/pg/data,/home/pg12/data,/home/pg14/data

1.2 Run isomode and ds idx First

PDU.public=# p isomode on;
PDU.public=# ds idx;
 
▌ Starting index retrieval
● Existing data pages will be excluded during index retrieval
Excluding file data pages under directory </home/pg/data/>
Excluding file data pages under directory </home/pg/data/pg_wal>
Excluding file data pages under directory </home/pg/data/base>
Excluding file data pages under directory </home/pg/data/base/16384>
Excluding file data pages under directory </home/pg/data/global>
... (more directories) ...
 
=== Disk Scan Process Monitor ===
Thread 0: 98.26% (Offset: 34288435200/34894512128) Pages: 30879
Thread 1: 100.00% (Offset: 69789024256/69789024256) Pages: 0
 
Total: 99.13% Pages: 30879 Time Elapsed: 111.2s
Estimating Time Left: 1.0 Seconds
 
# Generated files:
restore/.dsiso/idx
restore/.dsiso/pgiso

II. Obtain Table Structure After Imaging (Optional)

Only after the disk index and ISO image have been verified may you initialize metadata and retrieve the table structure. If WAL archiving is enabled, PDU can automatically retrieve it from archived WAL files using the scan drop command.

2.1 Initialize and Switch to Database

PDU.public=# b;
 
Initializing...
 -pg_database:</home/pg/data/global/1262>
 
Database:mydb
      -pg_schema:</home/pg/data/base/16384/2615>
      -pg_class:</home/pg/data/base/16384/1259> 11214 Records
      Schemas:
        ▌ public 1070 tables
        ▌ app 193 tables
 
PDU.public=# use mydb;

2.2 Execute scan drop Command

The scan drop command must be executed in the database where the table was dropped:

mydb.public=# scan drop;
 
Scanning DROP/TRUNCATE Records ...
 
▌ Scanning Archived Wal Directory
   StartWal: 0000000100000002000000A0
   EndWal: 0000000100000002000000A1
 
▌ End of Scanning, current time range:
  Start: 2025-12-07 22:26:52.290369 EST
  End: 2025-12-07 22:26:52.290369 EST
 
▌ Tx Details
────────────────────────────────────────
 Timestamp: 2025-12-07 22:26:52.290369 EST
       -------------------.--------------------
 ● Table: gp_business_event
 ● Tx Number: 15642     ● datafile: 32654
────────────────────────────────────────
Execution Time 0.04 seconds

2.3 Add Table Structure to Configuration

The scanned tables are saved in the restore database. Switch to it and use meta tab to export the table structure:

mydb.public=# use restore;
────────────────────────────────────────
│          Schema           │  Tab Num   │
────────────────────────────────────────
│    public                 │  1         │
────────────────────────────────────────
 
restore.public=# meta tab gp_business_event;
gp_business_event varchar,bpchar,varchar,varchar,varchar,varchar,varchar,date,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,timestamptz,bpchar,varchar
Completed, imported 1 dropscan-capable table objects in total

Check the generated configuration file:

[root@node1 pdu]# cat restore/tab.config
gp_business_event varchar,bpchar,varchar,varchar,varchar,varchar,varchar,date,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,timestamptz,bpchar,varchar

III. Configure tab.config Manually After Imaging (If Needed)

If the scan drop step was not performed, you need to manually fill in the table structure in restore/tab.config:

Format:

table_name column_type1,column_type2,column_type3,...

Example:

vim restore/tab.config
 
# Add table structure (column types separated by commas)
gp_business_event varchar,bpchar,varchar,varchar,varchar,varchar,varchar,date,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,timestamptz,bpchar,varchar

IV. Scan the Saved ISO Image

After the disk index and ISO image have been generated, execute ds iso to scan the saved page image:

PDU.public=# ds iso;
 
 ▌Image Scan Recovery Mode
 
 ▌Datafile Scan
────────────────────────────────────────
Table: gp_business_event
Progress: 100.000% (21421539328)
Pages: 1089 | Succeeded: 17006
Suspected gibberish: 8 | Failed: 4986
Execution Time: 1.60 seconds
Scan completed, file directory as follows:
        restore/dropscan/gp_business_event
 
 ▌Image Scan Recovery Mode
 
 ▌TOAST Scan
────────────────────────────────────────
Table: gp_business_event
Progress: 100.000% (21421694976)
Pages: 29790 | Succeeded: 120918
Suspected gibberish: 0 | Failed: 0
Execution Time: 6.26 seconds
Scan completed, file directory as follows:
        restore/dropscan/gp_business_event

Important: If the Datafile Scan shows "Failed" records and a TOAST Scan is executed, proceed to the next step to recover TOAST data.

V. Restore TOAST Data

If the Datafile Scan had failed records and a TOAST Scan was performed, use the ds repair command to restore TOAST data:

restore.public=# ds repair;
Table <gp_business_event>. Pages Parsed: 1076, Records Parsed: 4986
▌ Parse Complete
────────────────────────────────────────
 Table gp_business_event(gp_business_event)
 ● Pages: 1076           ● 4986 Records in total
 ● Success: 4986         ● Failure: 0
 ● File Path: restore/dropscan/gp_business_event/TOAST_140737488344272_1076blks_4986records.csv
────────────────────────────────────────
 
Execution Time 3.28 seconds

VI. Generate COPY Statements

Use the ds copy command to generate COPY statements for importing the recovered data:

restore.public=# ds copy;
Unloaded:
/path/to/pdu/restore/dropscan/gp_business_event/COPY.sql

Output Files

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

restore/dropscan/<table_name>/
├── *.csv                    # Recovered data files
├── TOAST_*.csv              # TOAST data (if applicable)
└── COPY.sql                 # COPY statements for import

Q&A: Common Scenarios

Q: How do I recover multiple tables?
Add multiple table definitions to tab.config, one table per line:
table1 varchar,int4,timestamp
table2 varchar,bpchar,date,numeric
Or use scan drop to batch generate, then use meta sch schema_name to add all tables in a schema:
restore.public=# meta sch public;
Completed, imported 5 dropscan-capable table objects in total
Q: What if disk fragments have been overwritten?
After DISK_PATH and the complete PGDATA_EXCLUDE list are configured, the standard workflow saves candidate pages during the index phase. Always enable isomode before running ds idx:
PDU.public=# p isomode on;
PDU.public=# ds idx;  -- This saves page images

-- Later, use iso mode for scanning:
PDU.public=# ds iso;
This allows recovery even if original disk fragments are later overwritten.
Q: What does 'Suspected gibberish' mean in the output?
This indicates records that PDU found but suspects may be corrupted or contain invalid data. These records are still included in the output but should be reviewed carefully.
Q: How long does the scanning process take?
Scanning time depends on disk size and the BLOCK_INTERVAL setting. A smaller BLOCK_INTERVAL provides more comprehensive disk coverage but is slower. For faster scanning on large disks, increase the BLOCK_INTERVAL value.