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

After UPDATE commits, ordinary SQL queries return the new values. Recovering the old values requires retained pages or WAL containing earlier versions and a confirmed incident time or transaction.

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 parses a preserved WAL archive or pg_wal copy to locate UPDATE operations and their old values. When the required images and logs are complete, select records by time or transaction and generate recovery SQL. The SQL is not executed automatically on the target database.

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
Locates the operation's transaction and available timestamp information
Selects affected transactions or time ranges for recovery
Generates SQL statements to restore data to its previous state
Can process large UPDATE operations when the required WAL is retained
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