Was Data Updated by Mistake?

Accidental Data Update Recovery

Scenario Description

An UPDATE statement with incorrect WHERE conditions changes thousands of important rows, 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

In PostgreSQL, UPDATE normally creates a new tuple version or HOT-chain member; after commit, ordinary SQL sees only the currently visible version. Reconstructing the old version depends on page cleanup or reuse, relevant WAL/FPW coverage, and the available DDL, version, and TOAST evidence.

Why UPDATE is Hard to Recover

- Ordinary SQL returns only the current visible version
- Old tuple space may be cleaned, reused, or overwritten
- Ordinary UPDATE WAL does not guarantee a complete old row
- Types, version, and TOAST evidence affect decoding

Difference from DELETE

- DELETE and UPDATE both use MVCC; neither means immediate physical erasure
- Their usable page, WAL, and old-row evidence differs
- Use a valid backup or PITR chain first when available
- Without that baseline, PDU correlates offline evidence and reports acceptance limits

PDU Solution

PDU correlates preserved relation files, UPDATE-related WAL records, and available full-page images to evaluate pre-update row states. A normal UPDATE WAL record does not guarantee a complete old row, so results depend on record type, full_page_writes, page images, version, and TOAST data, and must be validated before import.

Recovery Principle

UPDATE executed

Inspect WAL and page images

PDU evaluates candidate old values

PDU Core Capabilities

Evaluates pre-update row states from retained WAL and full-page images
Filters candidate records by available time and transaction evidence
Allows selective recovery of specific columns or rows
Generates SQL statements to restore data to its previous state
Reports recoverable, ambiguous, and failed items separately
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

Aspect	Deleted Records	Updated Records
Recovery Type Setting	`p restype delete;`	`p restype update;`
Restore Command	`restore del all;`	`restore upd all;`
What is Recovered	Complete deleted rows	Original 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