Customer Recovery Records

Real PostgreSQL Data Recovery Cases

Organized by real recovery incidents across unwanted updates, TRUNCATE, file damage, ransomware, and domestic PG-compatible systems. Each case focuses on environment, challenges, recovery path, and validation.

2.9M Rows Recovered After TRUNCATE
TRUNCATE
1 / 5
1TRUNCATEChina communications industry customerNo backupUser-operatedPostgreSQL 12

2.9M Rows Recovered After TRUNCATE

Recovered a truncated table after early intervention, using an image index and table structure scanning to export about 1GB of CSV data.

Environment
PostgreSQL 12 / 578 tables
Result
2,906,414 rows, 0 failed
Time
Index 6833s / recovery 3850s

Main Timeline

1
Incident
11:07

The user accidentally ran TRUNCATE and the target table file was no longer available through normal paths.

2
Contact
12:34

The site conditions were checked first, with priority on preventing further writes and overwrite expansion.

3
Image index
13:06

isomode was enabled and ds idx was run to preserve the fragment image index.

4
Index done
14:57

The image-index phase took longer because disk IO performance was poor.

5
Recovery start
15:13

ds iso was run with the image index and table definition to export importable CSV files.

6
Export done
16:04

The recovery export completed and was prepared for import-side validation.

7
Confirmed
18:11

The customer confirmed the import was usable, closing the recovery loop.

Situation
  1. 1The target table was truncated and its original data file was no longer available through normal paths.
  2. 2The incident was handled within about three hours, reducing the chance of fragment overwrite.
Constraints
  1. 1The site could not take a snapshot or copy the full disk.
  2. 2The recovery could not rely on inode-level file recovery and had to preserve fragment locations quickly.
Recovery Path
  1. 1Initialized database, schema, table, and column metadata.
  2. 2Enabled isomode and ran ds idx to save the fragment image index.
  3. 3Generated the target table structure with meta tab.
  4. 4Ran ds iso to export importable CSV files from the image index and table definition.
Validation
  1. 1The image scan covered about 767,993 pages and produced about 6GB of index data.
  2. 2The target table scan covered 112,221 pages, recovered 2,906,414 rows, and reported 0 failures.
  3. 3The final output was three CSV files totaling about 1GB and was confirmed importable.
Engineering note: Early response and concentrated fragments made the recovery practical; in TRUNCATE/DROP incidents, freezing scan material quickly directly affects the recovery ceiling.
2FILE DAMAGEChina energy industry customerFile damageCatalog damagePostGIS1.5TB

1.5TB PostGIS Catalog Damage Recovery

Recovered from disk bad blocks that damaged the catalog and made the instance unbootable, then exported data into a new instance after fixing GIS/TOAST parsing issues.

Environment
PostgreSQL 16.7 / PostGIS 3.2.8
Scale
28,891 pg_class objects
Result
Exported and restored into a new instance

Main Timeline

1
Startup failed
Day 14 12:18

The database could not start, later confirmed to be related to catalog damage.

2
Contact
Day 15 11:43

The instance was unbootable and backups were unavailable, so the work moved to file-level recovery.

3
Read catalog
Day 15 13:17

Damaged pages were skipped while intact catalog pages continued to be read.

4
Parse issue
Day 15 18:22

A GIS/TOAST parsing issue was found after a core dump during recovery.

5
Fix done
Day 15 22:08

The GIS/TOAST parsing issue was fixed and export capability was restored.

6
Parallel export
Day 15 22:37

Parallel export started with support for resuming after interruption.

7
Export done
Day 17 03:46

The large database export completed and moved into restoration on a new instance.

8
New instance
Day 17 09:12

The exported data was restored into a new PostgreSQL instance.

9
Confirmed
Day 18 12:58

The customer confirmed the recovery result and business-side usability.

Situation
  1. 1The PostgreSQL 16.7 instance was about 1.5TB and included PostGIS 3.2.8 spatial data.
  2. 2Disk bad blocks damaged the catalog, the instance could not start, and backups were unavailable.
Constraints
  1. 1Damaged system-table pages caused errors during normal initialization.
  2. 2GIS field parsing triggered core dumps and had to be fixed during the recovery.
  3. 3The dataset was large and type-rich, requiring resumable export behavior.
Recovery Path
  1. 1Skipped damaged pages during initialization and kept reading intact catalog data.
  2. 2Temporarily disabled GIS parsing and added resumable unload behavior.
  3. 3Fixed the GIS TOAST parsing issue and continued parallel export.
  4. 4Restored exported data into a new PostgreSQL instance.
Validation
  1. 1The recovered catalog contained 28,891 pg_class objects, 14,653 table objects, and 14,238 TOAST objects.
  2. 2Counts were compared with a month-old restored database to validate catalog parsing.
  3. 3Encoding and escaping issues during import were handled.
Engineering note: When system catalog pages are partially damaged, the recovery path should not depend on server startup; readable pages and object-count validation become key.
Click to view
3UPDATEManufacturing industry customerUnwanted update1GB WALLarge-scale DB

Unwanted Update Recovery on a 1GB-WAL Large Database

A long-running database used 1GB WAL segments and exposed multiple boundary issues. After fixing WAL range calculation and transaction-id overflow, pre-update data was recovered.

Scale
About 9.48 trillion WAL records
Challenge
FPW found about 9GB before the incident
Result
Pre-update records exported

Main Timeline

1
Accidental update
Day 25 16:47

The unwanted update occurred, and the target was a small set of pre-update records.

2
Contact
Day 25 17:23

Initialization and diagnosis started, confirming a long-running database with massive WAL history.

3
WAL issue
Day 25 19:06

A 1GB WAL segment calculation issue was found because legacy logic still assumed 16MB segments.

4
WAL fixed
Day 25 19:24

The WAL segment-count calculation was fixed.

5
XID issue
Day 25 19:27

Transaction IDs beyond int32 caused negative display and parsing issues.

6
XID fixed
Day 25 19:36

Transaction-id handling was adjusted to uint32-based logic.

7
FPW found
Day 25 22:11

The needed FPW was found about 9GB before the incident point.

8
Restore
Day 25 23:08

restore started, and the export completed quickly because the target data set was small.

9
Confirmed
Day 25 23:34

The customer confirmed the pre-update records were usable.

Situation
  1. 1The site used 1GB WAL segments instead of PostgreSQL default 16MB segments.
  2. 2The target was a small set of pre-update rows, but the database had accumulated a very large WAL history.
Constraints
  1. 1One specific database triggered a core dump during initialization.
  2. 2The required FPW was found about 9GB before the incident time.
  3. 3Legacy logic assumed 16MB WAL segments when calculating segment counts.
  4. 4Transaction IDs exceeded int32 and caused negative-number handling problems.
Recovery Path
  1. 1Fixed a null-pointer issue in typmod parsing so initialization could complete.
  2. 2Changed segment-count calculation from fixed 256 to 4GB / wal_segment_size.
  3. 3Adjusted transaction-id handling from int/atoi/%d to uint32/strtoul/%u.
  4. 4Expanded the WAL search window, added the needed FPW, and ran restore.
Validation
  1. 1The current WAL position mapped to the 1,755,925th 1GB segment.
  2. 2Sampling estimated about 9.48 trillion accumulated WAL records.
  3. 3After fixes, pre-update data was exported successfully.
Engineering note: Long-running production-like databases expose assumptions that lab data rarely covers, including fixed 16MB WAL logic, int32 transaction-id overflow, and unusual metadata.

Facing a similar PostgreSQL recovery issue?

Preserve original data files, WAL, and disk state first, avoid further writes, then confirm a recovery path.

Contact recovery support