Recover from Corrupted Instance

When a PostgreSQL instance cannot open but the PGDATA is still available, PDU can directly read those files to export data without starting the database service.

iWhen to Use This Method

PostgreSQL instance cannot start or connect due to corruption or misconfiguration
You need to export data from all databases in the instance by reading files directly
The database files are still accessible on disk

This article addresses the scenario where a single PostgreSQL database instance cannot be opened, providing detailed instructions on how to use PDU for data recovery.

I. Initialization

Fill in `PGDATA` in `pdu.ini`, i.e., the database directory.

[root@node1 xman]# cat pdu.ini
#PostgreSQL data directory
PGDATA=/home/14pg/data
#PostgreSQL archive directory
ARCHIVE_DEST=
#Disk for dropScan to scan
DISK_PATH=
#Number of data blocks to skip during dropScan. A smaller value provides more comprehensive disk coverage but is slower.
BLOCK_INTERVAL=20
#PGDATA to exclude during dropscan
PGDATA_EXCLUDE=

Enter the pdu directory and execute `b;` to complete initialization.

[root@node1 xman]# ./pdu

╔══════════════════════════════════════════════════════╗
║  Copyright 2024-2025 ZhangChen. All rights reserved  ║
║  PDU: PostgreSQL Data Unloader                       ║
║  Version 3.0.25.10  (2025-10-13)                     ║
║  Valid Until 2026-01-09 10:00:00                     ║
╚══════════════════════════════════════════════════════╝

  Current DB Supported Version:
  ──────────────────────────
  • PostgreSQL 14


PDU.public=# b;

Starting initialization...
 -pg_database:</home/14pg/data/global/1262>

Database:postgres
      -pg_schema:</home/14pg/data/base/13023/2615>
      -pg_class:</home/14pg/data/base/13023/1259> Total 80 rows
      -pg_attribute:</home/14pg/data/base/13023/1249> Total 2971 rows
      Schemas:
        ▌ public 0 tables

Database:xmandb
      -pg_schema:</home/14pg/data/base/24576/2615>
      -pg_class:</home/14pg/data/base/24576/1259> Total 11213 rows
      -pg_attribute:</home/14pg/data/base/24576/1249> Total 189519 rows
      Schemas:
        ▌ public 1071 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
        ▌ srm 1865 tables
        ▌ textile_page 14 tables

II. Enter the Specified Database and Export Schemas

2.1 View Current Databases

Use `\l;` to view selectable databases. In the result below, testdb is a directory we manually initialized, representing the business database. restore(PDU) is a database built into PDU used for data recovery in special scenarios.

PDU.public=# \l;

┌───────────────────┐
│   Database Name   │
├───────────────────┤
│    testdb         │
│    restore(PDU)   │
└───────────────────┘

2.2 Enter Database and Export Schemas

Use `use database_name;` to select and enter the corresponding database.

PDU.public=# use testdb;

┌────────────────────────────────────────┐
│          Schema          │  Table Count │
├────────────────────────────────────────┤
│    public                 │  1071      │
│    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        │
└────────────────────────────────────────┘
testdb.public=#

After entering the database, all schemas within the current database and their table counts are automatically displayed. You can also use \dn to view the schemas under the current database.

testdb.public=# \dn;

┌────────────────────────────────────────┐
│          Schema          │  Table Count │
├────────────────────────────────────────┤
│    public                 │  1071      │
│    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        │
└────────────────────────────────────────┘

      24 rows selected
testdb.public=#

III. Export Data Under a Specified Schema

Use `unload/u sch schema_name;` to export all table data under the corresponding schema.

testdb.public=# u sch public;

#1 Table<act_ge_bytearray> Current data page: 72  Data parsed: 1674  Status: Complete


▌ Parsing Complete
┌─────────────────────────────────────────────────────────┐
 Table act_ge_bytearray(/home/14pg/data/base/24576//38218)
 ● Data Pages: 72             ● Total Rows: 1674
 ● Success: 1674         ● Failed: 0
 ● File Path: testdb/public/act_ge_bytearray.csv0
└─────────────────────────────────────────────────────────┘

#1 Table<act_ge_property> Current data page: 1  Data parsed: 6  Status: Complete


▌ Parsing Complete
┌─────────────────────────────────────────────────────────┐
 Table act_ge_property(/home/14pg/data/base/24576//38223)
 ● Data Pages: 1              ● Total Rows: 6
 ● Success: 6            ● Failed: 0
 ● File Path: testdb/public/act_ge_property.csv0
└─────────────────────────────────────────────────────────┘

#1 Table<act_hi_actinst> Current data page: 853  Data parsed: 25444  Status: Complete


▌ Parsing Complete
┌─────────────────────────────────────────────────────────┐
 Table act_hi_actinst(/home/14pg/data/base/24576//38228)
 ● Data Pages: 853            ● Total Rows: 25444
 ● Success: 25444        ● Failed: 0
 ● File Path: testdb/public/act_hi_actinst.csv0
└─────────────────────────────────────────────────────────┘

................Omitted.................


▌ Parsing Complete
┌─────────────────────────────────────────────────────────┐
 Table zzgzichan(/home/14pg/data/base/24576//43564)
 ● Data Pages: 0              ● Total Rows: 0
 ● Success: 0            ● Failed: 0
 ● File Path: testdb/public/zzgzichan.csv0
└─────────────────────────────────────────────────────────┘
Time taken: 37.80 seconds


┌───────────────────────────────────────────────────────────────┐
                     Schema<public> Total 1071 Tables
                  ● Success: 693  ● No Data: 378  ● Failed: 0
  ● Success Log: log/testdb_unload_schema_public_err.txt
  ● Failure Log: log/testdb_unload_schema_public_succ.txt
└───────────────────────────────────────────────────────────────┘
┌───────────────────────────────────────────────────────────────┐
  COPY Command Export Complete
 ● File Path: testdb/COPY/public_copy.sql  ● Number of CSV Files: 1071
└───────────────────────────────────────────────────────────────┘
┌───────────────────────────────────────────────────────────────┐
  DDL Export Complete
 ● File Path: testdb/DDL/public_ddl.sql  ● Total 1071 Tables
└───────────────────────────────────────────────────────────────┘
testdb.public=#

3.1 How to Use Exported Data

3.1.1 Components of Exported Data

The data exported using the `unload/u sch schema_name;` command consists of three parts:
1. table data (CSV files)
2. COPY statements (SQL file)
3. and DDL statements (SQL file).

3.1.2 Paths of Exported Data

The CSV table data files are located under the path `database_name/schema_name/` in the same directory as the PDU software.
The COPY statement SQL file is located under the path `database_name/COPY/` in the same directory as the PDU software.
The DDL statement SQL file is located under the path `database_name/DDL/` in the same directory as the PDU software.

3.1.3 Usage of Exported Data

For production environment recovery, create a new empty database with the same structure as the source database and pre-create the table objects. Use the COPY statements generated by PDU to import the exported CSV files into the new database to achieve data recovery.
For test environment recovery, you can use the DDL statements generated by PDU to create a new target database. Note: the DDL generated by PDU only includes CREATE TABLE; other objects like indexes or views are not included. Then use the COPY statements generated by PDU to import the exported CSV files into the new database to achieve data recovery.