Database Won’t Start?

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 instance cannot start, clients and pg_dump cannot extract data through a database connection. Running pg_resetwal without first preserving the source may change the files. Save the available files, then assess which pages and schema information remain readable.

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 reads the data directory without starting PostgreSQL. Readable catalogs allow it to identify databases and tables and export records. Damaged catalogs require additional schema information from surviving definitions, original DDL or older backups.

Recovery Principle

Scan data directory

Identify DBs/tables

Batch export data

PDU Core Capabilities

Works completely independently of PostgreSQL processes
Reads available catalogs to identify databases and tables
Extracts data even when system catalogs (pg_class, pg_attribute) are damaged
Uses surviving definitions, original DDL or backup schemas when metadata is missing
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

Initialize Database Directory

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

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.

Batch Export Schema

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

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