Recover Single Database Directory

Use this guide when you still have a single database directory (for example base/15647) or when the PostgreSQL instance cannot start. PDU can read that directory directly to export your data without launching PostgreSQL.

When to Use This Method

Only a single database directory remains (for example base/15647) and you need to export its data
The PostgreSQL instance cannot start, and you need to unload data from the affected database
The database directory files are still accessible on disk

I. Initialize the Database Directory

Use the restore db command to initialize a specific database directory for recovery:

Command Syntax:

restore db <database_name> <path>;

Example:

PDU.public=# restore db mydb /home/pg/data/base/24576;
[Initializing] Restoring the table structure for database 'mydb'...
      -pg_schema:</home/pg/data/base/24576/2615>
      -pg_class:</home/pg/data/base/24576/1259> Total 11213 rows
      -pg_attribute:</home/pg/data/base/24576/1249> Total 189519 rows
      Schemas:
        ▌ public 1071 tables
Table bak_sys_config is missing column information
        ▌ app 193 tables
        ▌ sales 66 tables
        ▌ demo 10 tables
        ▌ hr 221 tables
        ▌ finance 90 tables

Directory Conflict

If a directory with the same database name already exists under PDU, you'll see this error:

PDU.public=# restore db mydb /home/pg/data/base/24576;
Path <mydb> is not empty. Please confirm the input format is `restore db database_name path_name`,
or delete the folder <mydb> under PDU.

Solution: Manually delete or rename the existing directory before proceeding.

II. Access the Database

2.1 View Available Databases

Use the \l; command to view all available databases:

PDU.public=# \l;
 
┌───────────────────┐
│   Database Name   │
├───────────────────┤
│    mydb           │
│    restore(PDU)   │
└───────────────────┘

In the result above, mydb is the database we just initialized, and restore(PDU) is PDU's built-in database for special recovery scenarios.

2.2 Enter the Database and View Schemas

Use use database_name; to enter the database:

PDU.public=# use mydb;
 
┌────────────────────────────────────────┐
│         Schema         │  Table Count  │
├────────────────────────────────────────┤
│    public              │  1071         │
│    app                 │  193          │
│    sales               │  66           │
│    demo                │  10           │
│    hr                  │  221          │
│    finance             │  90           │
│    reports             │  24           │
│    config              │  12           │
└────────────────────────────────────────┘
mydb.public=#

After entering the database, all schemas and their table counts are automatically displayed. You can also use \dn; to view schemas at any time:

mydb.public=# \dn;
 
┌────────────────────────────────────────┐
│         Schema         │  Table Count  │
├────────────────────────────────────────┤
│    public              │  1071         │
│    app                 │  193          │
│    ...                 │  ...          │
└────────────────────────────────────────┘
 
      8 rows selected

III. Export Schema Data

Use the u sch command to export all table data from a schema:

Command Syntax:

unload sch <schema_name>;
# or shorthand:
u sch <schema_name>;

Example:

mydb.public=# u sch public;
 
#1 Table<act_ge_bytearray> Current data page: 72  Parsed data: 1674  Status: Complete
 
▌ Parsing Complete
┌─────────────────────────────────────────────────────────┐
 Table act_ge_bytearray(/home/pg/data/base/24576/38218)
 ● Data pages: 72          ● Total rows: 1674
 ● Success: 1674      ● Failed: 0
 ● File path: mydb/public/act_ge_bytearray.csv0
└─────────────────────────────────────────────────────────┘
 
#1 Table<act_ge_property> Current data page: 1  Parsed data: 6  Status: Complete
 
▌ Parsing Complete
┌─────────────────────────────────────────────────────────┐
 Table act_ge_property(/home/pg/data/base/24576/38223)
 ● Data pages: 1           ● Total rows: 6
 ● Success: 6         ● Failed: 0
 ● File path: mydb/public/act_ge_property.csv0
└─────────────────────────────────────────────────────────┘
 
... (more tables) ...
 
Time taken: 37.80 seconds
 
 
┌───────────────────────────────────────────────────────────────┐
                Schema<public> Total 1071 tables
              ● Success: 693  ● No data: 378  ● Failed: 0
  ● Success log: log/mydb_unload_schema_public_succ.txt
  ● Failure log: log/mydb_unload_schema_public_err.txt
└───────────────────────────────────────────────────────────────┘
┌───────────────────────────────────────────────────────────────┐
  COPY Command Export Complete
 ● File path: mydb/COPY/public_copy.sql  ● CSV file count: 1071
└───────────────────────────────────────────────────────────────┘
┌───────────────────────────────────────────────────────────────┐
  DDL Export Complete
 ● File path: mydb/DDL/public_ddl.sql  ● Total tables: 1071
└───────────────────────────────────────────────────────────────┘
mydb.public=#

IV. Understanding the Exported Data

4.1 Export Components

When PDU exports a schema, it generates three types of files:

CSV Data Files

Contains the actual table data

mydb/public/*.csv0

COPY Statements

SQL commands to import CSV data

mydb/COPY/public_copy.sql

DDL Statements

CREATE TABLE statements

mydb/DDL/public_ddl.sql

DDL Limitations

The DDL statements generated by PDU only include CREATE TABLE statements. Other objects such as indexes, views, sequences, functions, and triggers are NOT exported.

4.2 File Locations

File Type	Path
CSV data files	`<database_name>/<schema_name>/*.csv0`
COPY statements	`<database_name>/COPY/<schema_name>_copy.sql`
DDL statements	`<database_name>/DDL/<schema_name>_ddl.sql`

V. Using the Exported Data

Production Environment Recovery

For production environments, it's recommended to:

Create a new empty database with the same structure as the source
Pre-create all table objects using your existing DDL scripts or schema backup
Use the COPY statements generated by PDU to import the CSV data

# On the target PostgreSQL server:
psql -d target_db -f mydb/COPY/public_copy.sql

Test Environment Recovery

For test environments, you can use the DDL statements to quickly create tables:

# Create tables
psql -d target_db -f mydb/DDL/public_ddl.sql
 
# Import data
psql -d target_db -f mydb/COPY/public_copy.sql

Exporting Multiple Schemas

To export all schemas in a database, repeat the u sch command for each schema:

mydb.public=# u sch public;
... (export completes) ...
 
mydb.public=# set app;
mydb.app=# u sch app;
... (export completes) ...
 
mydb.app=# set sales;
mydb.sales=# u sch sales;
... (export completes) ...

Tip

Use the 'set schema_name;' command to switch between schemas before running the export command.