wublabdubdub/PDU-PostgreSQLDataUnloader: Open-source PostgreSQL data recovery tool for corrupted or offline PGDATA, WAL-based row recovery, and dropped-table recovery. · GitHub

PDU (PostgreSQL Data Unloader) is a professional-grade, open-source disaster recovery and data extraction tool for PostgreSQL databases (versions 14-18). It reads PostgreSQL data files directly without requiring a running database instance—making it the ultimate solution for disaster recovery, forensic analysis, and emergency data extraction.

PostgreSQL DBAs face critical scenarios like these:

▸ Database completely corrupted - won't start, can't recover ▸ Accidental DELETE/UPDATE - critical data gone in seconds ▸ Data files deleted - filesystem corruption or operator error

Traditional tools like pg_filedump, pg_dirtyread, and pg_waldump each solve one problem with different learning curves. PDU unifies all these scenarios into a single, intuitive tool.

💬 In Oracle ecosystems, professionals use ODU/DUL for direct data mining. PostgreSQL deserved the same level of capability—that's why PDU exists.

PDU handles three critical disaster scenarios with two recovery methods:

1. Direct Data File Extraction

• Parse PostgreSQL heap/TOAST files directly
• Bypass database engine entirely
• fast parsing of TOAST data

2. WAL Archive Scanning

• Analyze Write-Ahead Logs (WAL) for transaction history
• Extract deleted rows or pre-update values
• Time-based and transaction-based filtering

# Set PostgreSQL version (14-18)
sed -i 's/#define PG_VERSION_NUM [0-9]\+/#define PG_VERSION_NUM <Version>/g' basic.h

# Compile
make

# Result: ./pdu executable

PGDATA=/var/lib/postgresql/15/main
ARCHIVE_DEST=/var/lib/postgresql/wal_archive

./pdu

PDU> b;                    -- Bootstrap metadata from PGDATA
PDU> \l;                   -- List databases
PDU> use production_db;    -- Switch database
PDU> \dn;                  -- List schemas
PDU> set public;           -- Switch schema
PDU> \dt;                  -- List tables
PDU> \d+ customers;        -- Describe table structure
PDU> unload tab customers; -- Export one table to CSV

📖 Full Guide: pduzc.com/quickstart

Problem: PostgreSQL won't start due to corrupted system catalog.

Solution:

PDU> b;                          -- Initialize metadata
PDU> use mydb;
PDU> set public;
PDU> unload tab customers;       -- Export one table
PDU> unload sch public;          -- Export all tables in schema
PDU> unload ddl;                 -- Generate DDL for current schema
PDU> unload copy;                -- Generate COPY script for exported CSVs

Result: CSV files exported to current directory, even if PostgreSQL won't start.

Problem: A critical DELETE statement was executed, and you need the deleted rows back.

Solution:

PDU> use production;
PDU> set public;
PDU> param restype delete;       -- Scan DELETE records
PDU> param resmode tx;           -- Restore by transaction ID
PDU> scan orders;                -- Scan WAL for deleted rows
PDU> restore del <TxID>;         -- Restore one transaction from scan results

Result: Deleted rows recovered from WAL archives and exported to CSV.

For time-range recovery, use param resmode time;, set param starttime <YYYY-MM-DD HH:MM:SS>; and param endtime <YYYY-MM-DD HH:MM:SS>;, then run scan orders; and restore del all;.

Problem: An UPDATE statement modified hundreds of rows with wrong values.

Solution:

PDU> use production;
PDU> set public;
PDU> param restype update;       -- Scan UPDATE records
PDU> param resmode tx;           -- Restore by transaction ID
PDU> scan users;
PDU> restore upd <TxID>;         -- Restore pre-UPDATE values

Result: Original values before UPDATE statement recovered from WAL.

📖 More Scenarios: pduzc.com/docs/instant-recovery

All interactive commands must end with ;.

📖 Full Reference: pduzc.com/docs

• Numeric: int2, int4, int8, float4, float8, numeric, decimal
• Temporal: date, time, timestamp, timestamptz, interval
• Text: varchar, text, char, bpchar
• JSON: json
• UUID: uuid
• Network: inet, cidr, macaddr, macaddr8
• TOAST: Automatically handles large objects with LZ4 decompression

• User-defined enum types
• Composite types
• Range types (int4range, tsrange, etc.)
• Full-text search vectors (tsvector, tsquery)

📚 Comprehensive guides available at pduzc.com/docs/instant-recovery

Q: make fails with "lz4.h: No such file or directory"

A: Install LZ4 development library:

# Ubuntu/Debian
sudo apt-get install liblz4-dev zlib1g-dev

# RHEL/CentOS
sudo yum install lz4-devel zlib-devel

Q: How do I change the PostgreSQL version target?

A: Edit basic.h and modify PG_VERSION_NUM:

sed -i 's/#define PG_VERSION_NUM [0-9]\+/#define PG_VERSION_NUM 16/g' basic.h
make clean && make

Q: PDU shows "PGDATA directory not found"

A: Verify the PGDATA path in pdu.ini is correct and accessible:

ls -la /var/lib/postgresql/15/main

Q: WAL scanning (scan <table>) returns no results

A: Ensure:

• ARCHIVE_DEST in pdu.ini points to the correct WAL archive directory
• WAL archiving was enabled when the DELETE/UPDATE occurred
• Make sure WAL files scanned includes the DELETE/UPDATE operations

Q: Exported CSV files contain garbled characters

A: Check database encoding:

PDU> \l;  -- Look at the encoding column

The CSV will use the database's encoding. Convert if needed:

iconv -f UTF8 -t GBK input.csv > output.csv

Q: Unloading large tables takes too long

A: PDU performs full table scans. For better performance:

• Run PDU on a machine with fast disk I/O
• Consider unloading specific columns only (if supported in future versions)
• Use parallel exports for different tables

• OS: Linux (x86_64)
• Compiler: GCC (C99 standard)
• Libraries:
  • liblz4-dev (Debian/Ubuntu) or lz4-devel (RHEL/CentOS)
  • zlib library

sudo apt-get update
sudo apt-get install build-essential liblz4-dev zlib1g-dev

sudo yum install gcc lz4-devel zlib-devel

We welcome contributions from the community! Here's how you can help:

Found a bug or have a feature request? Open an issue

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

• Follow C99 coding standards
• Add tests for new recovery features
• Update documentation for new commands
• Ensure compatibility with PostgreSQL 14-18

Help make PDU accessible to more communities by translating the documentation.

This project is licensed under the Apache License, Version 2.0. See LICENSE for the full license text.

Historical Note: Earlier drafts used the Business Source License 1.1. Starting from this release, PDU is 100% open-source under Apache-2.0 for maximum community compatibility.

This project includes code derived from:

• PostgreSQL (PostgreSQL License) - See LICENSE-PostgreSQL
• NTT pg_rman (PostgreSQL License)

See NOTICE for complete attribution.

ZhangChen (Jay) Copyright © 2024-2025

• 🌐 Official Website: pduzc.com
• 📚 Full Documentation: pduzc.com/docs
• ⚡ Features Overview: pduzc.com/features
• 🚀 Quick Start Guide: pduzc.com/quickstart
• 🧭 No-Backup Recovery Decision Guide: DELETE / DROP TABLE / DROP DATABASE
• 💬 GitHub Issues: github.com/wublabdubdub/PDU-PostgresqlDataUnloader/issues
• 📂 GitHub Repository: github.com/wublabdubdub/PDU-PostgresqlDataUnloader

If PDU helped you recover critical data or saved your day, please consider:

⭐ Starring this repository on GitHub 📢 Sharing PDU with your team 🐛 Reporting issues to help improve the tool 💬 Joining discussions and sharing your use cases

PDU (PostgreSQL Data Unloader) 是一款专业级、开源的 PostgreSQL 数据库灾难恢复和数据提取工具，支持 PostgreSQL 14-18 版本。它可以直接读取 PostgreSQL 数据文件，无需运行中的数据库实例——是灾难恢复、取证分析和紧急数据提取的终极解决方案。

PostgreSQL DBA 经常面临这些关键场景：

▸ 数据库完全损坏 - 无法启动，无法恢复 ▸ 误执行 DELETE/UPDATE - 关键数据瞬间消失 ▸ 数据文件被删除 - 文件系统损坏或操作失误

传统工具如 pg_filedump、pg_dirtyread 和 pg_waldump 各自解决一个问题，且有不同的学习曲线。PDU 将所有这些场景统一到一个直观的工具中。

💬 在 Oracle 生态系统中，专业人士使用 ODU/DUL 进行直接数据挖掘。PostgreSQL 也应该拥有同等级别的能力——这就是 PDU 存在的原因。

PDU 使用两种恢复方法处理三种关键灾难场景：

1. 直接数据文件提取

• 直接解析 PostgreSQL 堆/TOAST 文件
• 完全绕过数据库引擎
• 快速解析 TOAST 数据

2. WAL 归档扫描

• 分析预写日志 (WAL) 的事务历史
• 提取已删除的行或更新前的值
• 基于时间和事务的过滤

# 设置 PostgreSQL 版本（14-18）
sed -i 's/#define PG_VERSION_NUM [0-9]\+/#define PG_VERSION_NUM 大版本号/g' basic.h

# 编译
make

# 结果：./pdu 可执行文件

PGDATA=/var/lib/postgresql/15/main
ARCHIVE_DEST=/var/lib/postgresql/wal_archive

./pdu

PDU> b;                    -- 从 PGDATA 引导元数据
PDU> \l;                   -- 列出数据库
PDU> use production_db;    -- 切换数据库
PDU> \dn;                  -- 列出模式
PDU> set public;           -- 切换模式
PDU> \dt;                  -- 列出表
PDU> \d+ customers;        -- 查看表结构
PDU> unload tab customers; -- 导出单表为 CSV

📖 完整指南: pduzc.com/quickstart

问题：PostgreSQL 因系统目录损坏无法启动。

解决方案：

PDU> b;                          -- 初始化元数据
PDU> use mydb;
PDU> set public;
PDU> unload tab customers;       -- 导出单表
PDU> unload sch public;          -- 导出模式中的所有表
PDU> unload ddl;                 -- 生成当前模式的 DDL
PDU> unload copy;                -- 为已导出的 CSV 生成 COPY 脚本

结果：CSV 文件导出到当前目录，即使 PostgreSQL 无法启动。

问题：执行了关键的 DELETE 语句，需要找回已删除的行。

解决方案：

PDU> use production;
PDU> set public;
PDU> param restype delete;       -- 扫描 DELETE 记录
PDU> param resmode tx;           -- 按事务号恢复
PDU> scan orders;                -- 扫描 WAL 查找已删除的行
PDU> restore del <TxID>;         -- 根据扫描结果恢复指定事务

结果：从 WAL 归档中恢复已删除的行并导出为 CSV。

如需按时间区间恢复，先执行 param resmode time;，再设置 param starttime <YYYY-MM-DD HH:MM:SS>; 和 param endtime <YYYY-MM-DD HH:MM:SS>;，之后执行 scan orders; 与 restore del all;。

问题：一个 UPDATE 语句用错误的值修改了数百行。

解决方案：

PDU> use production;
PDU> set public;
PDU> param restype update;       -- 扫描 UPDATE 记录
PDU> param resmode tx;           -- 按事务号恢复
PDU> scan users;
PDU> restore upd <TxID>;         -- 恢复 UPDATE 前的值

结果：从 WAL 恢复 UPDATE 语句执行前的原始值。

📖 更多场景: pduzc.com/docs/instant-recovery

所有交互式命令都必须以 ; 结尾。

📖 完整参考: pduzc.com/docs

• 数值类型：int2、int4、int8、float4、float8、numeric、decimal
• 时间类型：date、time、timestamp、timestamptz、interval
• 文本：varchar、text、char、bpchar
• JSON：json
• UUID：uuid
• 网络：inet、cidr、macaddr、macaddr8

• 用户自定义枚举类型
• 复合类型
• 范围类型（int4range、tsrange 等）
• 全文搜索向量（tsvector、tsquery）

📚 完整指南请访问 pduzc.com/docs/instant-recovery

问：make 失败，提示 "lz4.h: No such file or directory"

答：安装 LZ4 开发库：

# Ubuntu/Debian
sudo apt-get install liblz4-dev zlib1g-dev

# RHEL/CentOS
sudo yum install lz4-devel zlib-devel

问：如何更改 PostgreSQL 版本目标？

答：编辑 basic.h 并修改 PG_VERSION_NUM：

sed -i 's/#define PG_VERSION_NUM [0-9]\+/#define PG_VERSION_NUM 16/g' basic.h
make clean && make

问：PDU 显示 "PGDATA directory not found"

答：验证 pdu.ini 中的 PGDATA 路径是否正确且可访问：

ls -la /var/lib/postgresql/15/main

问：WAL 扫描（scan <表>）返回无结果

答：确保：

• pdu.ini 中的 ARCHIVE_DEST 指向正确的 WAL 归档目录
• DELETE/UPDATE 发生时启用了 WAL 归档
• 扫描的WAL日志包含误操作期间的日志

问：导出的 CSV 文件包含乱码

答：检查数据库编码：

PDU> \l;  -- 查看编码列

CSV 将使用数据库的编码。如需转换：

iconv -f UTF8 -t GBK input.csv > output.csv

问：卸载大表耗时太长

答：PDU 执行全表扫描。为获得更好性能：

• 在具有快速磁盘 I/O 的机器上运行 PDU
• 考虑只卸载特定列（如果未来版本支持）
• 对不同表使用并行导出

• 操作系统：Linux (x86_64)
• 编译器：GCC（C99 标准）
• 库：
  • liblz4-dev（Debian/Ubuntu）或 lz4-devel（RHEL/CentOS）
  • zlib 库

sudo apt-get update
sudo apt-get install build-essential liblz4-dev zlib1g-dev

sudo yum install gcc lz4-devel zlib-devel

欢迎社区贡献！您可以通过以下方式帮助：

发现错误或有功能请求？提交问题

1. Fork 仓库
2. 创建功能分支（git checkout -b feature/amazing-feature）
3. 提交更改（git commit -m 'Add amazing feature'）
4. 推送到分支（git push origin feature/amazing-feature）
5. 打开拉取请求

• 遵循 C99 编码标准
• 为新恢复功能添加测试
• 为新命令更新文档
• 确保与 PostgreSQL 14-18 兼容

帮助让 PDU 惠及更多社区，翻译文档。

本项目采用 Apache License, Version 2.0 许可。完整文本见 LICENSE。

历史说明：早期草案使用 Business Source License 1.1。从本版本开始，PDU 在 Apache-2.0 下 100% 开源，以实现最大的社区兼容性。

本项目包含来自以下项目的派生代码：

• PostgreSQL（PostgreSQL 许可证）- 见 LICENSE-PostgreSQL
• NTT pg_rman（PostgreSQL 许可证）

完整归属见 NOTICE。

张晨 (Jay) 版权所有 © 2024-2025

• 🌐 官方网站：pduzc.com
• 📚 完整文档：pduzc.com/docs
• ⚡ 功能概览：pduzc.com/features
• 🚀 快速开始指南：pduzc.com/quickstart
• 🧭 无备份恢复决策指南：DELETE / DROP TABLE / DROP DATABASE
• 💬 GitHub Issues：github.com/wublabdubdub/PDU-PostgresqlDataUnloader/issues
• 📂 GitHub 仓库：github.com/wublabdubdub/PDU-PostgresqlDataUnloader

如果 PDU 帮助您恢复了关键数据或拯救了您的一天，请考虑：

⭐ 在 GitHub 上给仓库加星 📢 与您的团队分享 PDU 🐛 报告问题以帮助改进工具 💬 加入讨论并分享您的使用案例