Back to Scenarios

Is Database Integrity Damaged?

Instance-Level Database Recovery

Scenario Description

The entire PostgreSQL data directory has serious issues - perhaps from a failed version upgrade, incomplete backup restoration, or severe system failure. PostgreSQL won't start at all, showing errors about corrupted control files, invalid WAL, or system catalog corruption.

Possible causes:

  • PostgreSQL major version upgrade failure (e.g., 12 to 15)
  • Incomplete or corrupted pg_basebackup
  • Improper pg_resetwal usage causing corruption
  • Severe server hardware failures (memory, disk controller)
  • VM snapshot restore causing data inconsistency
  • System catalog (pg_class, pg_attribute) corruption

Challenges

When the database instance itself is compromised, no standard PostgreSQL tools work. You can't connect, can't run pg_dump, and pg_resetwal might make things worse. The data exists in files but is completely inaccessible through normal means.

Common Error Messages

  • - database system is not yet initialized
  • - control file contains invalid data
  • - could not locate valid checkpoint record
  • - requested WAL segment has already been removed

Traditional Methods Fail

  • - Cannot start PostgreSQL
  • - pg_dump cannot connect
  • - pg_resetwal may worsen corruption
  • - Need to restore entire instance

PDU Solution

PDU operates at the file system level, completely independent of PostgreSQL's running state. It can scan the entire data directory, identify all databases and tables, and extract data without requiring any PostgreSQL processes or valid system catalogs.

Recovery Principle

Scan data directory
Identify DBs/tables
Batch export data

PDU Core Capabilities

  • Works completely independently of PostgreSQL processes
  • Can scan entire data directories to discover all databases and tables
  • Extracts data even when system catalogs (pg_class, pg_attribute) are damaged
  • Reconstructs table schemas from data file analysis when metadata is lost
  • Supports recovery at database, schema, or individual table granularity
  • Generates DDL and COPY statements for easy data migration
Quick Command Example
# Step 1: Initialize the corrupted database directory
PDU.public=# b;
# Step 2: View available databases and enter target
PDU.public=# \l;
PDU.public=# use mydb;
# Step 3: Export all table data from entire schema
mydb.public=# u sch public;
# Output includes CSV data, COPY statements, and DDL
mydb/public/*.csv0 # CSV data files
mydb/COPY/public_copy.sql # COPY import statements
mydb/DDL/public_ddl.sql # DDL create statements

Export Components

CSV Data Files

Contains actual table data, one CSV file per table.

mydb/public/*.csv0

COPY Statements

SQL commands to import CSV data into PostgreSQL.

mydb/COPY/public_copy.sql

DDL Statements

CREATE TABLE statements (basic table structure only).

mydb/DDL/public_ddl.sql

Note: DDL statements only include CREATE TABLE. Other objects like indexes, views, sequences, functions, and triggers are NOT exported. Use existing DDL scripts in production.

Recovery Workflow

1

Initialize Database Directory

Use b; command to initialize the corrupted database directory. PDU reads pg_class and pg_attribute to identify all tables.

2

View and Select Schema

After entering the database with use command, all schemas and their table counts are displayed. Use set command to switch schemas.

3

Batch Export Schema

u sch command exports all table data from entire schema, automatically generating CSV files, COPY statements, and DDL statements.

4

Import to New Database

Execute the generated DDL and COPY statements on the target PostgreSQL server to complete data migration.

When to Use

  • PostgreSQL instance cannot start due to database corruption
  • Need to recover data from a specific database directory
  • Database files are still accessible on disk
  • Need to batch recover multiple tables or entire schema
View detailed steps for this recovery method