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.

Critical Factors for Success

Time is critical
Recovery success depends on whether disk space has been overwritten. The sooner you attempt recovery, the higher the success rate.
Stop database writes if possible
Minimize the chance of data page overwrite by reducing database activity.
Table structure helps but is not required
PDU can work even without original table schema information.

I. Obtain Table Structure (Optional)

If you have WAL archiving enabled, PDU can automatically retrieve the table structure from archived WAL files using the scan drop command.

1.1 Configure 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
#PGDATA to be excluded during dropscan
PGDATA_EXCLUDE=

1.2 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;

1.3 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

1.4 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

II. Configure tab.config Manually (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

Required Step

If tab.config is not properly configured, you'll see this error:

PDU.public=# ds;

▌Index Scan Recovery Mode
Tables in restore/tab.config is default template

III. Configure DISK_PATH

The dropscan function scans data page fragments on the disk, so you must correctly specify the disk path:

# Find the disk path for PGDATA
[root@node1 ~]# df -h /home/pg/data | awk 'NR>1 {print $1}'
/dev/mapper/data-lv
 
# Update pdu.ini
vim pdu.ini
DISK_PATH=/dev/mapper/data-lv

IV. Obtain Disk Index

Before scanning, PDU builds an index of PostgreSQL data pages on the disk to minimize recovery time. This step excludes existing table data from the scan.

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

Excluding Multiple PostgreSQL Instances

If there are multiple PostgreSQL instances on the server, list their PGDATA paths in the PGDATA_EXCLUDE parameter:

vim pdu.ini
#PGDATA to be excluded during dropscan
PGDATA_EXCLUDE=/home/pg12/data,/home/pg14/data

V. Execute Disk Fragment Scanning

After obtaining the disk index, execute the ds (dropscan) command for automated scanning:

PDU.public=# ds;
 
 ▌Index Scan Recovery Mode
 
 ▌Datafile Scan
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
       TableName        │                                   Result
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
 gp_business_event          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
 
 ▌Index Scan Recovery Mode
 
 ▌TOAST Scan
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
       TableName        │                                   Result
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
 gp_business_event          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.

VI. 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

VII. 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?

PDU supports saving data page fragments as image files during the index scan phase. 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.

Best Practice

For critical data, enable isomode immediately after discovering the drop/truncate to preserve disk page images. This provides a safety net even if the actual disk pages are overwritten later.