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 evaluates deleted rows from preserved relation files, relevant WAL records, and available full-page images. A normal DELETE WAL record does not guarantee a complete old row, so recoverability depends on surviving dead tuples, full_page_writes, page images, WAL continuity, version, and TOAST data. PDU exports candidate recovery results for table-, field-, and business-sample validation; it does not directly undo the original transaction.

Recovery Principle

DELETE executed
WAL records changes
PDU evaluates and exports candidates

PDU Core Capabilities

  • Correlates preserved relation files, relevant WAL records, and available full-page images
  • 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
  • Uses version-matched decoding and reports unsupported values separately
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