This guide covers recovering accidentally deleted records from a WAL archive or a preserved copy of PGDATA/pg_wal. PDU extracts the deleted data from WAL and restores it to CSV files for reimport.
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_pathFROM pg_class cJOIN pg_namespace n ON n.oid = c.relnamespaceWHERE n.nspname = 'public' AND c.relname = 't_payment_records';
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_walcp -a /home/pg/data/pg_wal/. /data/pdu-backup/incident-20260803/pg_wal/
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 DirectoryPGDATA=/home/pg/data/#WAL source directory: archive directory or copied pg_wal directoryARCHIVE_DEST=/data/pdu-backup/incident-20260803/pg_wal#Disk for dropScan to scanDISK_PATH=#Number of data blocks to skip during dropScanBLOCK_INTERVAL=20#PGDATA to be excluded during dropscanPGDATA_EXCLUDE=
Use the scan command to scan WAL files for deleted records of a specific table:
mydb.public=# scan t_payment_records;Scanning deleted Records for table<t_payment_records>...▌ Scanning Archived Wal Directory StartWal: 000000030000052500000050 EndWal: 0000000300000525000000EF▌ Time Range Restore Mode [Displayed all within Time Range]────────────────────────────────────────▌ End of Scanning, current time range: Start: 2025-03-05 05:05:38.006348 EST End: 2025-03-05 06:00:19.515825 EST▌ Time Range Details──────────────────────────────────────── Start Time: 2025-03-05 05:05:38.006348 EST End Time: 2025-03-05 06:00:19.515825 EST LSN: 525/532B3990 - 525/DCAF8FD0 Recommended startwal: 000000030000052500000053 Recommended endwal: 0000000300000525000000DC -------------------.-------------------- ● Datafile OID: 144835 ● Toastfile OID: 64598 ● Records deleted in the Time Range: 9648──────────────────────────────────────── [!] Note: The 'Recommended startwal' indicates the suggested value to set for startwal during transaction recovery The 'Recommended endwal' mandates that startwal must be set to this value, otherwise recovery may failExecution Time 38.30 seconds
Use the restore del all command to recover all deleted records found in the scan:
mydb.public=# restore del all;▌ Scanning Archived Wal Directory StartWal: 000000030000052500000050 EndWal: 0000000300000525000000EF▌ Time Range Restore Mode [Displayed all within Time Range]────────────────────────────────────────|-Records Decoded: 2Missing FPI blk 272 found during restoreDEL, single record parsing failedMissing FPI blk 253 found during restoreDEL, single record parsing failed|-Records Decoded: 9646▌ Restore Complete──────────────────────────────────────── Table <t_payment_records> ● Records Restored: 9648 ● Success: 9646 ● Failure: 2 ● File Path: restore/public/t_payment_records_del_2025-03-05 05:05:38.006348 EST_2025-03-05 06:00:19.515825 EST.csv────────────────────────────────────────Execution Time 18.49 seconds
The default recovery mode is Time Range Mode, which recovers all deletions within a time range. PDU also supports Transaction Mode for precise recovery by specific transaction IDs.
To ensure scanning efficiency, PDU will report an error if there are more than 250 WAL files in the scan range:
mydb.public=# scan t_payment_records;Scanning deletion records for table <t_payment_records>...▌ Scanning Archive Directory Start File: 000000010000000000000001 End File: 000000010000000100000059▌ Time Range Recovery Mode [Display all within the time range]────────────────────────────────────────The current WAL file range contains 345 files, exceeding the limit of 250.It is recommended to set the parameters startwal and endwal. This execution will be aborted.
Check the timestamps of WAL files in the archive directory and set startwal to a file from shortly before the deletion occurred:
# Check WAL file timestampsls -la /home/pg/wal_arch/-rw-------. 1 pg pg 16777216 Nov 15 22:30 000000010000000100000042-rw-------. 1 pg pg 16777216 Nov 15 22:31 000000010000000100000047-rw-------. 1 pg pg 16777216 Nov 16 21:36 000000010000000100000048# If deletion occurred around 21:30 on Nov 16, set startwal to 000000010000000100000047mydb.public=# p startwal 000000010000000100000047;