-
Notifications
You must be signed in to change notification settings - Fork 21
Recovery Guide for Dropped&Truncated Tables Without Backup
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.
- 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 TABLEremoves 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.
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=
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
- Note: The
scan dropcommand must be executed in the corresponding database. For example, if a table was dropped in thetestdbdatabase of a PostgreSQL instance, you must switch totestdbin PDU and then executescan 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
- Note: The tables obtained by the
scan dropcommand are saved in therestoredatabase. Therefore, you must switch to therestoredatabase to perform operations:use restore. -
scan dropmay scan multiple dropped tables. Use themeta tab table_namecommand to add the specified table to thetab.configfile.
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
If the
scan dropstep 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=
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.configfile, you can proceed to obtain the disk index.
- Executing the
dropscan/ds idxcommand 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_EXCLUDEparameter, 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
- After the disk index is obtained and the
restore/tab.configconfiguration is complete, you can executeds/dropscanfor 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: 4986appears. 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.
- Assuming the Datafile Scan phase has
Faileddata and the TOAST Scan phase was executed, we need to run theds/dropscan repaircommand 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
- The recovered data will be placed in the
restore/dropscan/table_name/path as CSV files. Users can generate the corresponding COPY statements using thedropscan/ds copycommand.
restore.public=# ds copy;
Unloaded:
/isoTest/xman/restore/dropscan/gp_business_event/COPY.sql
/isoTest/xman/restore/dropscan/job_status_trace_log/COPY.sql
- Configure multiple lines of table information in the
tab.configfile, with each table occupying one line. - Alternatively, use the
scan dropcommand 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_nameto add a single table totab.config. - Use
meta sch schema_nameto add all tables under a schema totab.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
Faileddata 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 repaircommand to recover tables containing TOAST fragments.
- 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:
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
- To recover using image files, append
isoto the originaldropscan/dscommand. 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
- Still use
ds repairto recover TOAST fragment data, which is no different from before.