Was a Table Dropped or Truncated?
Recover Deleted Tables Without Backups
PDU disk-image scanning: a professional offline DROP TABLE / TRUNCATE workflow when no usable backup exists
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 do not store the table's complete old contents in WAL as row-by-row payloads, so targeted WAL decoding alone usually cannot rebuild every row. A valid base backup plus continuous WAL can still PITR the whole cluster to before the command. Without that chain, preserve the source disk and evaluate pages that have not been overwritten.
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 requires a valid base backup and continuous WAL, and restores the whole cluster
- - Professional recovery is expensive
- - Success rate not guaranteed
PDU Solution
DROP TABLE may unlink relation files, while TRUNCATE may replace or shorten them. Underlying blocks may remain temporarily, be reused, or be discarded by SSD TRIM. PDU indexes a complete disk image preserved after writes stop, searches for candidate PostgreSQL pages, and decodes and validates them against matching DDL or surviving metadata.
Recovery Principle
PDU Core Capabilities
- Fragment scanning finds table data that still exists on disk after DROP/TRUNCATE
- Uses matching DDL or surviving catalog metadata to interpret candidate pages
- Provides a documented end-to-end workflow for no-backup DROP TABLE recovery
- Reports retained, overwritten, ambiguous, and failed page evidence separately
- Evaluates matching TOAST pages separately for large values
- Supports saving disk page images for later recovery (isomode)
Critical Factors for Success
Time is Critical
Recoverable scope depends on whether the relevant disk pages remain readable and have not been reused or overwritten. Earlier write stoppage and imaging usually preserve more evidence, but do not guarantee success.
Stop Database Writes
If possible, stop database writes immediately. Minimize the chance of data page overwrite by reducing database activity.
Reliable Schema Evidence Matters
Trustworthy typed reconstruction and safe import require matching DDL or verifiable catalog/equivalent schema evidence. Without reliable schema evidence, PDU may still identify candidate pages and raw fields, but ambiguity is higher and the result cannot be claimed as a complete table recovery.
Required Step: ISO Mode
Image saving is enabled by default. Before ds idx, configure PGDATA_EXCLUDE with every PostgreSQL data directory on DISK_PATH, then run ds idx directly. This reduces unnecessary image and scan work and generates restore/.dsiso/idx and restore/.dsiso/pgiso for ds iso recovery.
Prerequisites
- DISK_PATH configuration required
Use df -h command to find the disk path where PGDATA resides.
- Complete PGDATA_EXCLUDE configuration required
List every PostgreSQL data directory on the DISK_PATH disk. Separate absolute paths with commas and no spaces to reduce unnecessary image and scan work.
- 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.