Skip to content

Recovery Guide for Dropped&Truncated Tables Without Backup

Jay edited this page Dec 8, 2025 · 2 revisions

This article provides a detailed explanation of how to use PDU for disk fragment scanning and data recovery in scenarios where a table has been dropped or truncated in PostgreSQL without a backup.

I. Obtaining the Table Structure of the Deleted Table (Optional Step)

1. Principle Introduction

  • The principle behind PDU's drop data scanning and recovery is to scan and analyze physical blocks on the disk based on the table structure of the table to be recovered, ultimately integrating them into a data file for restoration.
  • Therefore, providing an accurate table structure is crucial. Since DROP TABLE removes the data files and cleans up information in the data dictionary, the table structure no longer exists in the database. Thus, the primary method is to obtain the table structure from the application side.
  • PDU offers an option: if the user has archiving enabled and all archive files are retained, we can directly retrieve the table structure from PDU.

2. Operation Demonstration

2.1 Fill in PGDATA and ARCHIVE_DEST

vim pdu.ini
#Postgresql Data Directory
PGDATA=/home/18pg/data/
#Postgresql Archive Directory
ARCHIVE_DEST=/home/18pg/wal_arch
#the Disk that dropScan need to scan
DISK_PATH=/dev/mapper/xman-data
#The number of data blocks skipped during dropScan. The smaller the value, the more comprehensive the disk coverage, and the slower the speed.
BLOCK_INTERVAL=20
#PGDATA to be excluded during dropscan.
PGDATA_EXCLUDE=

2.2 Initialization

restore.public=# b;

Initializing...
 -pg_database:</home/18pg/data/global/1262>

Database:postgres
      -pg_schema:</home/18pg/data/base/5/2615>
      -pg_class:</home/18pg/data/base/5/1259> 83 Records
      -pg_attribute:</home/18pg/data/base/5/1249> 3161 Records
      Schema:
        ▌ public 0 tables

Database:alldb
      -pg_schema:</home/18pg/data/base/16384/2615>
      -pg_class:</home/18pg/data/base/16384/1259> 11214 Records
      -pg_attribute:</home/18pg/data/base/16384/1249> 189673 Records
      Schema:
        ▌ public 1070 tables
        ▌ bbp 193 tables
        ▌ clothes_space 66 tables
        ▌ demo 10 tables
        ▌ ding 10 tables
        ▌ ecm 83 tables
        ▌ emr 221 tables
        ▌ group_access_control 0 tables
        ▌ hihis 1709 tables
        ▌ keycloak 90 tables
        ▌ msax 24 tables
        ▌ msax_ac 25 tables
        ▌ msax_app 6 tables
        ▌ msax_bi 16 tables
        ▌ msax_codegen 3 tables
        ▌ msax_config 12 tables
        ▌ msax_demo 1 tables
        ▌ msax_job 21 tables
        ▌ msax_mp 6 tables
        ▌ msax_pay 6 tables
        ▌ msax_report 17 tables
        ▌ nsdzda 211 tables
        ▌ srm 1865 tables
        ▌ textile_page 14 tables

2.2 Execute the scan drop command

  • Note: The scan drop command must be executed in the corresponding database. For example, if a table was dropped in the testdb database of a PostgreSQL instance, you must switch to testdb in PDU and then execute scan drop.
PDU.public=# use alldb;
┌────────────────────────────────────────┐
│          Schema           │  Tab Num   │
├────────────────────────────────────────┤
│    public                 │  1070      │
│    bbp                    │  193       │
│    clothes_space          │  66        │
│    demo                   │  10        │
│    ding                   │  10        │
│    ecm                    │  83        │
│    emr                    │  221       │
│    group_access_control   │  0         │
│    hihis                  │  1709      │
│    keycloak               │  90        │
│    msax                   │  24        │
│    msax_ac                │  25        │
│    msax_app               │  6         │
│    msax_bi                │  16        │
│    msax_codegen           │  3         │
│    msax_config            │  12        │
│    msax_demo              │  1         │
│    msax_job               │  21        │
│    msax_mp                │  6         │
│    msax_pay               │  6         │
│    msax_report            │  17        │
│    nsdzda                 │  211       │
│    srm                    │  1865      │
│    textile_page           │  14        │
└────────────────────────────────────────┘
alldb.public=# scan drop;

Scanning DROP/TRUNCATE Records ...

▌ Scanning Archived Wal Directory
   StartWal: 000000010000000000000001
   EndWal: 0000000100000002000000A1

WAL file number in current file range is 671, which is over 250, parameter <startwal> and <endwal> are recommended ,quit executing this time
alldb.public=# p startwal 0000000100000002000000A0;
┌─────────────────────────────────────────────────────────────────┐
│            parameter             │             value            │
├─────────────────────────────────────────────────────────────────┤
│    startwal                      │   0000000100000002000000A0   │
│    endwal                        │                              │
│    startlsnt                     │                              │
│    endlsnt                       │                              │
│    starttime                     │                              │
│    endtime                       │                              │
│    resmode(Data Restore Mode)    │              TIME            │
│    exmode(Data Export Mode)      │              CSV             │
│    encoding                      │              UTF8            │
│    restype(Data Restore Type)    │              DELETE          │
          ----------------------DropScan----------------------
│    dsoff(DropScan startOffset)   │              0               │
│    blkiter(Block Intervals)      │              20              │
│    itmpcsv(Items Per Csv)        │              100             │
│    isomode                       │              off             │
└─────────────────────────────────────────────────────────────────┘
alldb.public=# scan drop;

Scanning DROP/TRUNCATE Records ...

▌ Scanning Archived Wal Directory
   StartWal: 0000000100000002000000A0
   EndWal: 0000000100000002000000A1

▌ End of Scanning, current time range:
  Start: 2025-12-07 22:26:52.290369 EST
  End: 2025-12-07 22:26:52.290369 EST

▌ Tx Details
┌─────────────────────────────────────────────────────────┐
 Timestamp: 2025-12-07 22:26:52.290369 EST
       -------------------.--------------------
 ● Table: gp_business_event
 ● Tx Number: 15642     ● datafile: 32654
└─────────────────────────────────────────────────────────┘
Execution Time 0.04 seconds

2.3 Use PDU to directly add the obtained table structure to tab.config

  • Note: The tables obtained by the scan drop command are saved in the restore database. Therefore, you must switch to the restore database to perform operations: use restore.
  • scan drop may scan multiple dropped tables. Use the meta tab table_name command to add the specified table to the tab.config file.
alldb.public=# use restore;
┌────────────────────────────────────────┐
│          Schema           │  Tab Num   │
├────────────────────────────────────────┤
│    public                 │  1         │
└────────────────────────────────────────┘
restore.public=# meta tab gp_business_event;
gp_business_event varchar,bpchar,varchar,varchar,varchar,varchar,varchar,date,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,timestamptz,bpchar,varchar
Completed, imported 1 dropscan-capable table objects in total
  • View the current restore/tab.config:
[root@node1 xman]# cat restore/tab.config
gp_business_event varchar,bpchar,varchar,varchar,varchar,varchar,varchar,date,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,timestamptz,bpchar,varchar

II. Fill in the restore/tab.config configuration file and the DISK_PATH in the pdu.ini file

If the scan drop step was not performed to automatically retrieve the structure of the dropped tables, we need to manually fill in the table structure in the restore/tab.config file.

  • The format is table_name column_types
  • Column types are separated by commas, for example: gp_business_event varchar,bpchar,varchar,varchar,varchar,varchar,varchar,date,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,timestamptz,bpchar,varchar
  • If a custom table name and column types are not filled in tab.config, an error will be reported during scanning.
PDU.public=# ds;

 ▌Index Scan Recovery Mode 
Tables in restore/tab.config is default template

Correctly fill in the DISK_PATH in the pdu.ini file

  • The dropscan function essentially scans data page fragments on the disk, so we must correctly fill in the disk path.
  • The most convenient method is to execute df -h <PGDATA>|awk 'NR>1 {print $1}'
[root@node1 ~]# df -h /home/18pg/data|awk 'NR>1 {print $1}'
/dev/mapper/xman-data
  • Fill the result into DISK_PATH
cat pdu.ini
#Postgresql Data Directory
PGDATA=/home/18pg/data
#Postgresql Archive Directory
ARCHIVE_DEST=/home/18pg/wal_arch
#the Disk that dropScan need to scan
DISK_PATH=/dev/mapper/xman-data
#The number of data blocks skipped during dropScan. The smaller the value, the more comprehensive the disk coverage, and the slower the speed.
BLOCK_INTERVAL=20
#PGDATA to be excluded during dropscan.
PGDATA_EXCLUDE=

III. Obtaining the Disk Index

Since the scale of most production disks is very large, we need to accurately locate PostgreSQL data pages to minimize recovery time. The method adopted here is to accurately locate by identifying the PostgreSQL data page index on the disk. After completing the tab.config file, you can proceed to obtain the disk index.

  • Executing the dropscan/ds idx command will start obtaining the index.
  • First, it scans all data files under the PGDATA path, excluding files of tables that have not been dropped from the index. This significantly reduces the time required for location.
  • If there is more than one PostgreSQL instance on the server, list the PGDATA paths of other instances in the PGDATA_EXCLUDE parameter, separated by commas, e.g.:
#PGDATA to be excluded during dropscan.
PGDATA_EXCLUDE=/home/16pg/data,/home/17pg/data
  • The output of index retrieval is as follows:
PDU.public=# ds idx;

▌ Starting index retrieval
┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
 ● Existing data pages will be excluded during index retrieval
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘
Excluding file data pages under directory </home/18pg/data/>
Excluding file data pages under directory </home/18pg/data//pg_serial>
Excluding file data pages under directory </home/18pg/data//pg_multixact>
Excluding file data pages under directory </home/18pg/data//pg_multixact/members>
Excluding file data pages under directory </home/18pg/data//pg_multixact/offsets>
Excluding file data pages under directory </home/18pg/data//pg_stat_tmp>
Excluding file data pages under directory </home/18pg/data//pg_wal>
Excluding file data pages under directory </home/18pg/data//pg_wal/summaries>
Excluding file data pages under directory </home/18pg/data//pg_wal/archive_status>
Excluding file data pages under directory </home/18pg/data//pg_commit_ts>
Excluding file data pages under directory </home/18pg/data//pg_snapshots>
Excluding file data pages under directory </home/18pg/data//pg_replslot>
Excluding file data pages under directory </home/18pg/data//pg_xact>
Excluding file data pages under directory </home/18pg/data//global>
Excluding file data pages under directory </home/18pg/data//pg_dynshmem>
Excluding file data pages under directory </home/18pg/data//pg_subtrans>
Excluding file data pages under directory </home/18pg/data//pg_tblspc>
Excluding file data pages under directory </home/18pg/data//pg_logical>
Excluding file data pages under directory </home/18pg/data//pg_logical/mappings>
Excluding file data pages under directory </home/18pg/data//pg_logical/snapshots>
Excluding file data pages under directory </home/18pg/data//pg_notify>
Excluding file data pages under directory </home/18pg/data//pg_twophase>
Excluding file data pages under directory </home/18pg/data//base>
Excluding file data pages under directory </home/18pg/data//base/1>
Excluding file data pages under directory </home/18pg/data//base/4>
Excluding file data pages under directory </home/18pg/data//base/5>
Excluding file data pages under directory </home/18pg/data//base/16384>
Excluding file data pages under directory </home/18pg/data//pg_stat>
Excluding file data pages under directory </home/18pg/data//pg_log>
=== Disk Scan Process Monitor ===
Thread 0: 98.26% (Offset: 34288435200/34894512128) Pages: 30879
Thread 1: 100.00% (Offset: 69789024256/69789024256) Pages: 0

Total: 99.13% Pages: 30879 Time Elapsed: 111.2秒
Estimating Time Left: 1.0Seconds

IV. Performing Disk Fragment Scanning

  • After the disk index is obtained and the restore/tab.config configuration is complete, you can execute ds/dropscan for automated scanning.
PDU.public=# ds;

 ▌Index Scan Recovery Mode

 ▌Datafile Scan
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
       TableName        │                                   Result
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
 gp_business_event          100.000 %(21421539328)   Pages: 1089        Succeeded: 17006     (Suspected gibberish: 8         )  Failed: 4986

Execution Time 1.60 seconds
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Scan completed, file directory as follows:
        restore/dropscan/gp_business_event

 ▌Index Scan Recovery Mode

 ▌TOAST Scan
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
       TableName        │                                   Result
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
 gp_business_event          100.000 %(21421694976)   Pages: 29790       Succeeded: 120918    (Suspected gibberish: 0         )  Failed: 0

Execution Time 6.26 seconds
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Scan completed, file directory as follows:
        restore/dropscan/gp_business_event
  • In the Datafile Scan phase, Failed: 4986 appears. This is likely because the table contains TOAST data. Therefore, after the Datafile Scan phase ends, PDU automatically starts the TOAST Scan phase to scan for TOAST fragments of that table.

V. Restoring TOAST Data (Non-mandatory)

  • Assuming the Datafile Scan phase has Failed data and the TOAST Scan phase was executed, we need to run the ds/dropscan repair command to restore the TOAST data scanned during the TOAST Scan phase.
restore.public=# ds repair;
Table <gp_business_event>. Pages Parsed: 1076, Records Parsed: 4986
▌ Parse Complete
┌─────────────────────────────────────────────────────────┐
 Table gp_business_event(gp_business_event)
 ● Pages: 1076           ● 4986 Records in total
 ● Success: 4986         ● Faliure: 0
 ● File Path: restore/dropscan/gp_business_event/TOAST_140737488344272_1076blks_4986records.csv
└─────────────────────────────────────────────────────────┘

Execution Time 3.28 seconds

VI. Q&A

1. What is the path for the recovered data?

  • The recovered data will be placed in the restore/dropscan/table_name/ path as CSV files. Users can generate the corresponding COPY statements using the dropscan/ds copy command.
restore.public=# ds copy;
Unloaded: 
/isoTest/xman/restore/dropscan/gp_business_event/COPY.sql
/isoTest/xman/restore/dropscan/job_status_trace_log/COPY.sql

2. What should I do if I need to recover multiple tables?

  • Configure multiple lines of table information in the tab.config file, with each table occupying one line.
  • Alternatively, use the scan drop command to batch generate them, as demonstrated below:
alldb.public=# scan drop;

Scanning DROP/TRUNCATE Records ...

▌ Scanning Archived Wal Directory
   StartWal: 0000000100000002000000A0
   EndWal: 0000000100000002000000A2

▌ End of Scanning, current time range:
  Start: 2025-12-07 22:26:52.290369 EST
  End: 2025-12-08 03:06:11.221758 EST

▌ Tx Details
┌─────────────────────────────────────────────────────────┐
 Timestamp: 2025-12-07 22:26:52.290369 EST
       -------------------.--------------------
 ● Table: gp_business_event     
 ● Tx Number: 15642     ● datafile: 32654
└─────────────────────────────────────────────────────────┘

▌ Tx Details
┌─────────────────────────────────────────────────────────┐
 Timestamp: 2025-12-08 03:06:11.221758 EST
       -------------------.--------------------
 ● Table: job_status_trace_log     
 ● Tx Number: 15643     ● datafile: 17246
└─────────────────────────────────────────────────────────┘
Execution Time 0.05 seconds
alldb.public=# use restore;
┌────────────────────────────────────────┐
│          Schema           │  Tab Num   │
├────────────────────────────────────────┤
│    public                 │  2         │
└────────────────────────────────────────┘
restore.public=# \dt;
┌──────────────────────────────────────────────────┐
|              Tablename              |    Size    |
├──────────────────────────────────────────────────┤
│    gp_business_event                │  0         │
│    job_status_trace_log             │  0         │
└──────────────────────────────────────────────────┘

        2 tables in total
restore.public=# meta sch public;
Completed, imported 2 dropscan-capable table objects in total
  • Use meta tab table_name to add a single table to tab.config.
  • Use meta sch schema_name to add all tables under a schema to tab.config.
[root@node1 xman]# cat restore/tab.config
gp_business_event varchar,bpchar,varchar,varchar,varchar,varchar,varchar,date,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,timestamptz,bpchar,varchar
job_status_trace_log varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,timestamp

3. During the ds stage, is there any difference between handling multiple tables and a single table?

  • Output example:
restore.public=# ds;

 ▌Index Scan Recovery Mode 

 ▌Datafile Scan 
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
       TableName        │                                   Result                                    
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
 gp_business_event          100.013 %(21421654016)   Pages: 1089        Succeeded: 17006     (Suspected gibberish: 8         )  Failed: 4986      
 job_status_trace_log       100.013 %(21421654016)   Pages: 79840       Succeeded: 2881902   (Suspected gibberish: 0         )  Failed: 0         

Execution Time 77.95 seconds
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Scan completed, file directory as follows: 
        restore/dropscan/gp_business_event
        restore/dropscan/job_status_trace_log

 ▌Index Scan Recovery Mode 

 ▌TOAST Scan 
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
       TableName        │                                   Result                                    
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
 gp_business_event          99.999 %(21421686784)   Pages: 29789       Succeeded: 120916    (Suspected gibberish: 0         )  Failed: 0         

Execution Time 6.91 seconds
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Scan completed, file directory as follows: 
        restore/dropscan/gp_business_event
  • In the Datafile Scan stage, all tables will be scanned.
  • Only tables that have Failed data during the Datafile Scan stage will proceed to the Datafile Scan stage (Note: This appears to be a typo or repetition in the original. Likely meant "TOAST Scan stage").
  • Subsequently, use the ds repair command to recover tables containing TOAST fragments.

4. What if the dropped data page fragments are overwritten?

  • Data page fragments on disk have a high risk of being overwritten. Therefore, PDU incorporates a feature that allows saving data page fragments as image files during the index scan phase. This enables recovery using the image files even if the original disk fragments are overwritten.
  • Operations are as follows:

4.1 Enable the isomode parameter

Execute p isomode on; to enable image saving. This will save data page fragments as image files when executing ds idx.

PDU.public=# p isomode on;
┌─────────────────────────────────────────────────────────────────┐
│            parameter             │             value            │
├─────────────────────────────────────────────────────────────────┤
│    startwal                      │                              │
│    endwal                        │                              │
│    startlsnt                     │                              │
│    endlsnt                       │                              │
│    starttime                     │                              │
│    endtime                       │                              │
│    resmode(Data Restore Mode)    │              TIME            │
│    exmode(Data Export Mode)      │              CSV             │
│    encoding                      │              UTF8            │
│    restype(Data Restore Type)    │              DELETE          │
          ----------------------DropScan----------------------
│    dsoff(DropScan startOffset)   │              0               │
│    blkiter(Block Intervals)      │              20              │
│    itmpcsv(Items Per Csv)        │              100             │
│    isomode                       │              on              │
└─────────────────────────────────────────────────────────────────┘
PDU.public=# ds idx;
Available space on the disk where the current PDU program is located: 29.72 GB.
The size limit for PDU scan saved index files is: 14 GB. Confirm to continue? y/n
y

▌ Starting index retrieval
┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
 ● Existing data pages will be excluded during index retrieval
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘
Excluding file data pages under directory </dropscan/18data>
Excluding file data pages under directory </dropscan/18data/pg_serial>
Excluding file data pages under directory </dropscan/18data/pg_multixact>
Excluding file data pages under directory </dropscan/18data/pg_multixact/members>
Excluding file data pages under directory </dropscan/18data/pg_multixact/offsets>
Excluding file data pages under directory </dropscan/18data/pg_stat_tmp>
Excluding file data pages under directory </dropscan/18data/pg_wal>
Excluding file data pages under directory </dropscan/18data/pg_wal/summaries>
Excluding file data pages under directory </dropscan/18data/pg_wal/archive_status>
Excluding file data pages under directory </dropscan/18data/pg_commit_ts>
Excluding file data pages under directory </dropscan/18data/pg_snapshots>
Excluding file data pages under directory </dropscan/18data/pg_replslot>
Excluding file data pages under directory </dropscan/18data/pg_xact>
Excluding file data pages under directory </dropscan/18data/global>
Excluding file data pages under directory </dropscan/18data/pg_dynshmem>
Excluding file data pages under directory </dropscan/18data/pg_subtrans>
Excluding file data pages under directory </dropscan/18data/pg_tblspc>
Excluding file data pages under directory </dropscan/18data/pg_logical>
Excluding file data pages under directory </dropscan/18data/pg_logical/mappings>
Excluding file data pages under directory </dropscan/18data/pg_logical/snapshots>
Excluding file data pages under directory </dropscan/18data/pg_notify>
Excluding file data pages under directory </dropscan/18data/pg_twophase>
Excluding file data pages under directory </dropscan/18data/base>
Excluding file data pages under directory </dropscan/18data/base/1>
Excluding file data pages under directory </dropscan/18data/base/4>
Excluding file data pages under directory </dropscan/18data/base/5>
Excluding file data pages under directory </dropscan/18data/base/16384>
Excluding file data pages under directory </dropscan/18data/pg_stat>
Excluding file data pages under directory </dropscan/18data/pg_log>
=== Disk Scan Process Monitor ===
Thread 0: 97.66% (Offset: 34078720000/34894512128) Pages: 110719
Thread 1: 100.00% (Offset: 69789024256/69789024256) Pages: 0

Total: 98.83% Pages: 110719 Time Elapsed: 119.1Seconds
Estimating Time Left: 1.4Seconds

4.2 Use dropscan/ds iso to perform disk scanning

  • To recover using image files, append iso to the original dropscan/ds command. Otherwise, recovery will still proceed from the disk.
PDU.public=# ds iso;

 ▌Image Scan Recovery Mode 

 ▌Datafile Scan 
 gp_business_event          99.999 %(110718)   Pages: 1089        Succeeded: 17006     (Suspected gibberish: 8         )  Failed: 4986      
 job_status_trace_log       99.999 %(110718)   Pages: 79840       Succeeded: 2881902   (Suspected gibberish: 0         )  Failed: 0         
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
Execution Time 104.02 seconds
Scan completed, file directory as follows: 
        restore/dropscan/gp_business_event
        restore/dropscan/job_status_trace_log

 ▌Image Scan Recovery Mode 

 ▌TOAST Scan 
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
 gp_business_event          99.999 %(110718)   Pages: 29789       Succeeded: 120916    (Suspected gibberish: 0         )  Failed: 0         
├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
Execution Time 18.93 seconds
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Scan completed, file directory as follows: 
        restore/dropscan/gp_business_event
PDU.public=# ds repair;
Table <gp_business_event>. Pages Parsed: 1076, Records Parsed: 4986
▌ Parse Complete
┌─────────────────────────────────────────────────────────┐
 Table gp_business_event(gp_business_event)
 ● Pages: 1076           ● 4986 Records in total
 ● Success: 4986         ● Faliure: 0
 ● File Path: restore/dropscan/gp_business_event/TOAST_140737488344272_1076blks_4986records.csv
└─────────────────────────────────────────────────────────┘

Execution Time 3.17 seconds

4.3 Recover TOAST data

  • Still use ds repair to recover TOAST fragment data, which is no different from before.