Recover Updated Records

This guide covers recovering the original data before accidental UPDATE operations. PDU extracts the pre-update values from WAL archive files, allowing you to restore data that was incorrectly modified.

Critical Prerequisites

WAL archiving must be enabled
The principle is the same as deleted records recovery - extracting data from WAL files.
Ensure WAL files are archived after the update
Run these commands after the accidental update:SELECT pg_switch_wal(); CHECKPOINT;

Comparison with Deleted Records Recovery

The workflow for recovering updated records is very similar to recovering deleted records. The main differences are:

Aspect	Deleted Records	Updated Records
Recovery Type Setting	`p restype delete;` (default)	`p restype update;`
Restore Command	`restore del all;`	`restore upd all;`
What is Recovered	The complete deleted rows	The original values before update

I. Initialization

1.1 Configure pdu.ini

Same as deleted records recovery, configure both PGDATA and ARCHIVE_DEST:

[root@node1 pdu]# cat pdu.ini
#PostgreSQL Data Directory
PGDATA=/home/pg/data/
#PostgreSQL Archive Directory
ARCHIVE_DEST=/home/pg/wal_arch
#Disk for dropScan to scan
DISK_PATH=
#Number of data blocks to skip during dropScan
BLOCK_INTERVAL=20

1.2 Start PDU and Initialize

[root@node1 pdu]# ./pdu
 
PDU.public=# b;
 
Starting initialization...
 -pg_database:</home/pg/data/global/1262>
 
Database:mydb
      -pg_schema:</home/pg/data/base/24576/2615>
      -pg_class:</home/pg/data/base/24576/1259> Total 11213 rows
      Schemas:
        ▌ public 1071 tables

II. Switch Recovery Type to Update

Before scanning, you must switch the recovery type from DELETE (default) to UPDATE:

PDU.public=# p restype update;
┌─────────────────────────────────────────────────────────────────┐
│            parameter             │             value            │
├─────────────────────────────────────────────────────────────────┤
│    startwal                      │                              │
│    endwal                        │                              │
│    startlsnt                     │                              │
│    endlsnt                       │                              │
│    starttime                     │                              │
│    endtime                       │                              │
│    resmode(Data Restore Mode)    │              TIME            │
│    exmode(Data Export Mode)      │              CSV             │
│    encoding                      │              UTF8            │
│    restype(Data Restore Type)    │              UPDATE          │
          ----------------------DropScan----------------------
│    dsoff(DropScan startOffset)   │              0               │
│    blkiter(Block Intervals)      │              20              │
└─────────────────────────────────────────────────────────────────┘

Important

The restype setting persists during your PDU session. Remember to change it back to 'delete' if you need to recover deleted records later.

III. Scan for Updated Records

3.1 Navigate to the Target Table

PDU.public=# use mydb;
┌────────────────────────────────────────┐
│          Schema           │  Tab Num   │
├────────────────────────────────────────┤
│    public                 │  1071      │
└────────────────────────────────────────┘
 
mydb.public=#

3.2 Execute the Scan Command

The scan command works the same way, but now scans for UPDATE operations instead of DELETE:

mydb.public=# scan t_user_profiles;
 
Scanning updated Records for table<t_user_profiles>...
 
 
▌ Scanning Archived Wal Directory
   StartWal: 000000010000000500000010
   EndWal: 00000001000000050000002F
 
▌ Time Range Restore Mode [Displayed all within Time Range]
────────────────────────────────────────
▌ End of Scanning, current time range:
  Start: 2025-03-10 14:22:15.123456 EST
  End: 2025-03-10 14:35:42.789012 EST
 
▌ Time Range Details
┌─────────────────────────────────────────────────────────┐
 Start Time: 2025-03-10 14:22:15.123456 EST
 End Time: 2025-03-10 14:35:42.789012 EST
 LSN: 5/10A23450 - 5/2F8B1230
 Recommended startwal: 000000010000000500000010
 Recommended endwal: 00000001000000050000002F
       -------------------.--------------------
 ● Datafile OID: 156789    ● Toastfile OID: 0
 ● Records updated in the Time Range: 2500
└─────────────────────────────────────────────────────────┘
 
Execution Time 15.32 seconds

IV. Restore Updated Data

4.1 Restore All Updated Records

Use the restore upd all command to recover all pre-update values:

mydb.public=# restore upd all;
 
▌ Scanning Archived Wal Directory
   StartWal: 000000010000000500000010
   EndWal: 00000001000000050000002F
 
▌ Time Range Restore Mode [Displayed all within Time Range]
────────────────────────────────────────
|-Records Decoded: 2500
▌ Restore Complete
┌───────────────────────────────────────────────────────────────┐
 Table <t_user_profiles>
 ● Records Restored: 2500
 ● Success: 2500       ● Failure: 0
 ● File Path: restore/public/t_user_profiles_upd_2025-03-10 14:22:15.123456 EST_2025-03-10 14:35:42.789012 EST.csv
└───────────────────────────────────────────────────────────────┘
Execution Time 8.76 seconds

4.2 Transaction Mode for Precise Recovery

Like deleted records, you can use Transaction Mode to recover updates from specific transactions:

# Switch to transaction mode
mydb.public=# p resmode tx;
 
# Scan to see transaction details
mydb.public=# scan t_user_profiles;
 
▌ Tx Restore Mode [Displayed by Tx groups]
────────────────────────────────────────
 
▌ Tx Details
┌─────────────────────────────────────────────────────────┐
 Timestamp: 2025-03-10 14:25:30.456789 EST
 LSN: 5/15C34560 - 5/15C34890
       -------------------.--------------------
 ● Tx Number: 987654321   ● Records updated by the TX: 150
 ● Datafile OID: 156789    ● Toastfile OID: 0
└─────────────────────────────────────────────────────────┘
 
# Restore specific transaction
mydb.public=# restore upd 987654321;
 
▌ Restore Complete
┌───────────────────────────────────────────────────────────────┐
 Table <t_user_profiles>
 ● Records Restored: 150
 ● Success: 150        ● Failure: 0
 ● File Path: restore/public/t_user_profiles_987654321.csv
└───────────────────────────────────────────────────────────────┘

V. Using the Recovered Data

The recovered CSV file contains the original values before the UPDATE. You have several options for using this data:

Option 1: Direct UPDATE Back

If you know exactly which columns were incorrectly updated, you can use the CSV data to construct UPDATE statements to restore original values.

Option 2: Import to Temporary Table

Import the CSV to a temporary table for comparison and selective restoration:

-- Create temporary table with same structure
CREATE TABLE t_user_profiles_restore (LIKE t_user_profiles);
 
-- Import recovered data
COPY t_user_profiles_restore FROM '/path/to/recovered.csv' WITH CSV;
 
-- Compare and update as needed
UPDATE t_user_profiles t
SET column1 = r.column1, column2 = r.column2
FROM t_user_profiles_restore r
WHERE t.id = r.id;

Option 3: Replace Entire Table

For complete restoration, you can truncate and reimport all data.

Output Files

Recovered data is saved as CSV files:

Recovery Mode	File Path
Time Range Mode	`restore/<schema>/<table>_upd_<start_time>_<end_time>.csv`
Transaction Mode	`restore/<schema>/<table>_<tx_id>.csv`

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 or user action. 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.

Tip

PDU supports both Time Range Mode and Transaction Mode for UPDATE recovery. Transaction Mode is particularly useful when you need to recover only specific transactions among many changes.