Data Deleted by Mistake?

Accidental Data Deletion Recovery

Scenario Description

A developer or DBA accidentally executes a DELETE statement without a proper WHERE clause in the production environment, or runs a script that removes critical business data. To make matters worse, the transaction has been committed, and the most recent backup doesn't contain the deleted data.

Typical scenarios include:

Executing DELETE FROM table_name; forgetting to add WHERE clause
Test scripts accidentally executed in production
Scheduled tasks misconfigured causing data cleanup
Application bugs causing batch deletions

Challenges

After DELETE commits, ordinary SQL queries can no longer read the deleted records. Recovery requires pages or WAL that still contain the old values, rather than querying the current table.

Limitations of Traditional Methods

- Requires complete physical backup
- Requires proper PITR setup
- Recovery time can be lengthy
- May result in data loss

Common Misconceptions

- "Data cannot be recovered after commit"
- "Only backup can recover data"
- "Need to restore entire database with downtime"

PDU Solution

PDU scans a retained WAL archive or pg_wal copy to locate DELETE operations. When the required page images and log records are complete, it can reconstruct the deleted records and export them for later import.

Recovery Principle

DELETE executed

WAL records changes

PDU extracts data

PDU Core Capabilities

Scans a preserved WAL archive or pg_wal copy to find DELETE operations and extract deleted rows
Supports time range filtering to narrow down the recovery window
Transaction ID filtering for precise recovery of specific operations
Exports recovered data in CSV format for easy reimport
Non-destructive: original WAL files remain untouched during scanning
Supports PostgreSQL 10–18; the available data types depend on the edition

Quick Command Example

# Without archiving: first back up the table, TOAST, and PGDATA/pg_wal

SHOW data_directory;

# ARCHIVE_DEST=/path/to/copied/pg_wal

# Step 1: Initialize; run ckwal first for a copied pg_wal directory

PDU.public=# b;

PDU.public=# ckwal;

PDU.public=# use mydb;

# Step 2: Scan for deleted records in target table

mydb.public=# scan t_orders;

# Step 3: Restore all deleted data

mydb.public=# restore del all;

# Or restore by specific transaction ID

mydb.public=# restore del 1360536375;

Two Recovery Modes

Time Range Mode

Recover all deleted records within a specified time range. Suitable for batch recovery scenarios.

p resmode time; restore del all;

Transaction Mode

Precisely recover specific operations by transaction ID. Suitable for targeted recovery.

p resmode tx; restore del <tx_id>;

Prerequisites

The WAL containing the DELETE must be preserved
Use the existing archive directory, or immediately back up the target table, TOAST files, and PGDATA/pg_wal when archiving was not enabled.
Check a copied WAL directory with ckwal first
When ARCHIVE_DEST points to a copied pg_wal directory, run ckwal after b and before scan/restore.

View detailed steps for this recovery method