Protect the Recovery Scene

Scenario 1: The Database Cannot Start or Data Files Are Damaged

Scenario 2: Accidental DELETE or UPDATE

WAL Archiving Is Enabled

Preserve the existing archive directory and all archived WAL files that cover the incident period. Do not clean, overwrite, or move them back to the original database disk. If the database is still available, confirm that the WAL containing the accidental operation has reached the archive directory.

WAL Archiving Is Not Enabled

Query the Target Table and TOAST File Paths Directly

Connect to the database containing the target table and run the SQL below. Replace public and t_payment_records with the actual schema and table name. The result returns PGDATA, the main data file path, the TOAST OID, and the TOAST file path directly on screen.

SELECT
    current_setting('data_directory') AS pgdata,
    n.nspname AS schema_name,
    c.relname AS table_name,
    c.oid AS table_oid,
    c.relfilenode AS data_filenode,
    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_payment_records';

Scenario 3: One or More Tables Were Dropped or Truncated

1. Identify PGDATA and Its Disk Device

Assume the PostgreSQL data directory is on /dev/sda. If you are unsure, first run SHOW data_directory; to obtain PGDATA, then use df -h <PGDATA> to identify the device containing that directory.

SHOW data_directory;

df -h /actual/path/to/PGDATA

2. Upload and Extract PDU on Another Disk

Upload the installer to a path that is not on /dev/sda, and extract it into a dedicated directory. Never place it on the PostgreSQL data disk, so the affected device receives no active writes. The extracted files are shown below.

3. Configure pdu.ini

Set PGDATA to the PostgreSQL data directory, DISK_PATH to the disk device to scan (for example, /dev/sda), and PGDATA_EXCLUDE to the PostgreSQL data directory.

PGDATA=/actual/path/to/PGDATA
DISK_PATH=/dev/sda
PGDATA_EXCLUDE=/actual/path/to/PGDATA

4. Preserve the Disk Image

Enter PDU and run p isomode on;, followed immediately by ds idx;, to start scanning the disk and preserving the page image.

PDU.public=# p isomode on;
PDU.public=# ds idx;

iostat -x 1

If the final %util column for the scanned device remains at 100% or above 90%, the disk has reached its I/O limit. When production applications are still using that disk, stop the PDU scan: press Ctrl+C first and confirm that %util drops. If the process remains, locate it with ps -ef | grep '[p]du' and terminate the correct PDU process.

ps -ef | grep '[p]du'
kill <PID>

After the scene has been safely preserved, continue with the DROP / TRUNCATE table recovery guide.