Back to Scenarios

Was a Table Dropped or Truncated?

Recover Deleted Tables Without Backups

PDU's most powerful capability: the only publicly documented practical method to restore DROP TABLE or TRUNCATE without backups

Scenario Description

Someone executed DROP TABLE or TRUNCATE on a critical table in production. Unlike DELETE, these operations don't log individual row removals to WAL in a recoverable way. The table structure and all its data appear to be completely gone.

Typical scenarios include:

  • Accidental DROP TABLE deleted an important business table
  • TRUNCATE TABLE emptied a table containing critical data
  • Script errors caused table deletion
  • ORM framework automatically executed DROP TABLE
  • Database cleanup scripts accidentally removed production data

Challenges

DROP TABLE and TRUNCATE are among the most devastating operations because they don't leave recoverable traces in WAL like DELETE does. Traditional WAL-based recovery cannot help here. This is considered one of the hardest recovery scenarios in PostgreSQL.

Why So Difficult

  • - DROP/TRUNCATE does not log row data
  • - WAL recovery cannot retrieve data
  • - Table metadata completely lost
  • - Disk space may be overwritten

Traditional Limitations

  • - Recovery normally requires a full backup
  • - PITR cannot recover dropped tables
  • - Professional recovery is expensive
  • - Success rate not guaranteed

PDU Solution

PDU provides a unique disk fragment scanning capability that searches the raw data files for remnants of dropped tables. When PostgreSQL drops a table, it marks the space as free but doesn't immediately overwrite the data. PDU can find and recover these fragments.

Recovery Principle

Scan raw disk
Identify data page fragments
Reconstruct data

PDU Core Capabilities

  • Fragment scanning finds table data that still exists on disk after DROP/TRUNCATE
  • Works without any table metadata - reconstructs structure from data patterns
  • The currently known practical method to recover DROP TABLE without backups
  • Can recover partial data even if some pages have been overwritten
  • Identifies and recovers data from TOAST tables for large values
  • Supports saving disk page images for later recovery (isomode)
Quick Command Example
# Step 1: Configure pdu.ini (set DISK_PATH)
# pdu.ini
PGDATA=/home/pg/data/
DISK_PATH=/dev/mapper/data-lv
# Step 2: (Optional) Scan WAL for table structure
PDU.public=# b;
PDU.public=# use mydb;
mydb.public=# scan drop;
# Step 3: Get disk index (exclude existing data)
PDU.public=# ds idx;
# Step 4: Execute disk fragment scan
PDU.public=# ds;
# Step 5: Repair TOAST data and generate COPY statements
restore.public=# ds repair;
restore.public=# ds copy;

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, stop database writes immediately. Minimize the chance of data page overwrite by reducing database activity.

Table Structure Helps

Table structure helps but is not required. PDU can work even without original table schema information.

Best Practice: ISO Mode

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.

# Enable ISO mode to save page images
PDU.public=# p isomode on;
PDU.public=# ds idx; # Saves page images
# Later, use ISO mode for scanning
PDU.public=# ds iso;

Prerequisites

  • DISK_PATH configuration required

    Use df -h command to find the disk path where PGDATA resides.

  • Table structure configuration

    Can be auto-retrieved via scan drop, or manually configured in restore/tab.config.

  • Disk access permission

    Requires root access or read permission to raw disk device.

View detailed steps for this recovery method