Back to Scenarios

Was Data Updated by Mistake?

Accidental Data Update Recovery

Scenario Description

An UPDATE statement with incorrect WHERE conditions overwrites thousands of rows of important data, or a batch job writes wrong values to the database. The original values before the update are needed, but no backup exists from before the incident.

Typical scenarios include:

  • UPDATE SET column = value without WHERE clause, affecting entire table
  • Wrong WHERE clause updating incorrect data
  • ETL jobs writing incorrect data to production tables
  • Data migration scripts overwriting correct data
  • Application logic errors causing data overwrites

Challenges

UPDATE operations in PostgreSQL overwrite existing data. Once committed, the previous values are no longer accessible through SQL queries. Finding what the data looked like before the update is extremely difficult.

Why UPDATE is Hard to Recover

  • - Original values directly overwritten
  • - Cannot query old data via SQL
  • - Traditional backup recovery is costly
  • - Scope of impact can be massive

Difference from DELETE

  • - DELETE completely removes rows
  • - UPDATE modifies specific columns
  • - Both require WAL-based recovery
  • - PDU can handle both cleanly

PDU Solution

PDU uses a preserved WAL archive or pg_wal copy to recover pre-update values. It parses the WAL records for the UPDATE and extracts the row state from before the unwanted change.

Recovery Principle

UPDATE executed
WAL records old/new values
PDU extracts old values

PDU Core Capabilities

  • Extracts original row values from before UPDATE operations in WAL
  • Identifies the exact timestamp and transaction of the unwanted changes
  • Allows selective recovery of specific columns or rows
  • Generates SQL statements to restore data to its previous state
  • Works even when the UPDATE affected millions of rows
  • Supports both time range and transaction-based recovery
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: Switch recovery type and scan the target table
mydb.public=# p restype update;
mydb.public=# scan t_user_profiles;
# Step 3: Restore all pre-update data
mydb.public=# restore upd all;
# Or restore by specific transaction ID
mydb.public=# restore upd 987654321;

DELETE vs UPDATE Recovery Comparison

AspectDeleted RecordsUpdated Records
Recovery Type Settingp restype delete;p restype update;
Restore Commandrestore del all;restore upd all;
What is RecoveredComplete deleted rowsOriginal values before update

Common Use Cases

Accidental Mass UPDATE

When a WHERE clause was missing or incorrect, causing unintended rows to be updated. Use Time Range Mode to recover all affected rows.

Specific Transaction Rollback

When you need to undo changes from a specific application operation. Use Transaction Mode to target that exact transaction.

Data Auditing

When you need to see what values existed before certain changes were made, for compliance or debugging purposes.

Prerequisites

  • The WAL containing the UPDATE 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