Back to Scenarios

Was 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

In PostgreSQL, once a DELETE operation is committed, the data appears to be gone forever through normal database operations. From the application's perspective, the data has permanently disappeared.

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 recovers deleted data by scanning WAL (Write-Ahead Log) archive files. PostgreSQL records all changes in WAL before committing them. PDU leverages this mechanism by parsing WAL records to extract the original deleted data, effectively "undoing" the DELETE operation.

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 with full data type compatibility
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