Recover Updated Records

This guide covers recovering the original data before accidental UPDATE operations from a WAL archive or a preserved copy of PGDATA/pg_wal. PDU extracts the pre-update values so incorrectly modified data can be restored.

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 Preserve the Data Files and WAL Source

First query PostgreSQL for the actual data directory and the complete paths of the target table and its TOAST relation. The result can be used directly for file copying:

SHOW data_directory;
 
SELECT
    n.nspname,
    c.relname,
    c.oid,
    c.relfilenode,
    current_setting('data_directory') || '/' ||
        pg_relation_filepath(c.oid) AS data_file_path,
    c.reltoastrelid AS toast_oid,
    CASE
        WHEN c.reltoastrelid = 0 THEN NULL
        ELSE current_setting('data_directory') || '/' ||
             pg_relation_filepath(c.reltoastrelid)
    END AS toast_file_path
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname = 't_user_profiles';

If WAL archiving is enabled, force the WAL containing the incident into the configured archive directory:

SELECT pg_switch_wal();
CHECKPOINT;

If WAL archiving was not enabled, use the directory returned by SHOW data_directory and immediately copy all files under $PGDATA/pg_wal to a separate directory. If pg_wal is a symbolic link, copy files from its real target:

mkdir -p /data/pdu-backup/incident-20260803/pg_wal
cp -a /home/pg/data/pg_wal/. /data/pdu-backup/incident-20260803/pg_wal/

1.2 Configure pdu.ini

Set PGDATA to the value returned by SHOW data_directory. Set ARCHIVE_DEST to the existing archive directory, or to the copied pg_wal directory when archiving was not enabled:

[root@node1 pdu]# cat pdu.ini
#PostgreSQL Data Directory
PGDATA=/home/pg/data/
#WAL source directory: archive directory or copied pg_wal directory
ARCHIVE_DEST=/data/pdu-backup/incident-20260803/pg_wal
#Disk for dropScan to scan
DISK_PATH=
#Number of data blocks to skip during dropScan
BLOCK_INTERVAL=20

1.3 Start PDU, Initialize, and Check WAL

[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

When ARCHIVE_DEST points to a copied pg_wal directory, run ckwal immediately after initialization and before any scan or restore command:

PDU.public=# ckwal;

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              │
────────────────────────────────────────

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.