PostgreSQL恢复利器PDU(PostgreSQL Data Unloader)
前言
现实中总会有很多的意外，数据被意外删除、硬件问题、软件 BUG 等原因导致PostgreSQL数据库损坏， 在没有备份的情况下，如何恢复出所有的数据，以避免所有数据丢失造成的损失？今天就给大家介绍一款国人精心打造PostgreSQL恢复利器PDU以及实战。
什么是 PDU
PDU 全称为 PostgreSQL DataUnloader，是用于 PostgreSQL 数据库紧急恢复的软件，在各种原因造成的数据库不能打开或数据删除后没有备份时，使用 PDU 抢救数据，最大限度地减少数据丢失。
PDU特点
恢复速度快
PDU是由C语言所写，恢复速度快，内存占用少。
使用简单
PDU 恢复数据非常简单，只需要简单的配置加上两三条命令即可恢复数据。
使用 PDU 软件
下载 PDU 软件
选择PDU2.0_for_Postgresql10-17社区版_20250521_x86.zip社区版下载，社区版可以直接使用，但是每个表最多恢复记录数不超过100000条，更新恢复和删除恢复的数量最多不超过100000 条，用于验证软件恢复功能。
专业版需要将如下的机器码发邮件或者其他联系方式给PDU作者获取license.pdu授权进行恢复。
[postgres@pg16 ~]$ ./pdu16 在PDU软件同级目录下没有发现license.pdu授权文件 请将下列字符提供给PDU开发者，用于生成license文件： n9VFo6eKmgqV3Rup9IaSVp2Z License校验失败 [postgres@pg16 ~]$
由于测试我们这里只需要下载社区版
开启归档
首先数据库要开启归档模式
[postgres@pg16 ~]$ cd /opt/pgdata/
[postgres@pg16 pgdata]$ vi postgresql.conf
#添加如下
archive_mode = on
archive_command = 'test ! -f /opt/pgarchive/%f && cp %p /opt/pgarchive/%f'
[postgres@pg16 pgdata]$ mkdir -p /opt/pgarchive
[postgres@pg16 pgdata]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2025-05-23 08:45:17.653 CST [4922] LOG: redirecting log output to logging collector process
2025-05-23 08:45:17.653 CST [4922] HINT: Future log output will appear in directory "log".
done
server started
[postgres@pg16 pgdata]$
创建软件目录
将PDU_2.0_for_Postgresql10-17_Free.zip解压后把对应postgresql版本的pud17软件上传空间较大的目录，我们这里就直接到/home/postgres目录下在创建pdu软件目录，同时创建pdu.ini配置文件，将PGDATA和ARCHIVE_DEST这两个参数写入
[postgres@pg16 pgdata]$ cd
[postgres@pg16 ~]$ mkdir pdu
[postgres@pg16 ~]$ cd pdu/
[postgres@pg16 pdu]$ ls -l
total 1888
-rwxr-xr-x 1 postgres postgres 1949416 May 22 23:12 pdu16
[postgres@pg16 pdu]$ vi pdu.ini
#添加如下
PGDATA=/opt/pgdata
ARCHIVE_DEST=/opt/pgarchive
[postgres@pg16 pdu]$
启动PDU
[postgres@pg16 pdu]$ ./pdu16
╔══════════════════════════════════════════════════════╗
║ Copyright 2024-2025 ZhangChen. All rights reserved ║
║ PDU: PostgreSQL Data Unloader ║
║ Version 2.5.0 (2025-05-23) ║
╚══════════════════════════════════════════════════════╝
Current DB Supported Version:
──────────────────────────
• PostgreSQL 16
╔═══════════════════════════════════════════╗
║ COMMUNITY VERSION ║
╠═══════════════════════════════════════════╣
║ • Max 100000 records per table (unload) ║
║ • Max 100000 records per table (restore) ║
║ • Speed limitation ║
╚═══════════════════════════════════════════╝
Contact Me:
───────────────────
• WeChat: x1987LJ2020929
• Email: 1109315180@qq.com
• Tel: 15251853831
PDU.public=#
初始化字典
输入b;进行初始化
PDU.public=# b;
Initializing...
-pg_database:</opt/pgdata/global/1262>
Database:postgres
-pg_schema:</opt/pgdata/base/5/2615>
-pg_class:</opt/pgdata/base/5/1259> 85 Records
-pg_attribute:</opt/pgdata/base/5/1249> 3156 Records
Schema:
▌ public 2 tables
Database:demo
-pg_schema:</opt/pgdata/base/16391/2615>
-pg_class:</opt/pgdata/base/16391/1259> 87 Records
-pg_attribute:</opt/pgdata/base/16391/1249> 3170 Records
Schema:
▌ public 4 tables
Database:mytestdb
-pg_schema:</opt/pgdata/base/16414/2615>
-pg_class:</opt/pgdata/base/16414/1259> 86 Records
-pg_attribute:</opt/pgdata/base/16414/1249> 3172 Records
Schema:
▌ public 3 tables
Database:hr
-pg_schema:</opt/pgdata/base/16538/2615>
-pg_class:</opt/pgdata/base/16538/1259> 89 Records
-pg_attribute:</opt/pgdata/base/16538/1249> 3242 Records
Schema:
▌ public 7 tables
PDU.public=#
显示数据库
输入\l;
PDU.public=# \l;
┌───────────────────┐
│ Database │
├───────────────────┤
│ postgres │
│ demo │
│ mytestdb │
│ hr │
└───────────────────┘
PDU.public=#
进入数据库
比如进入hr数据库use hr;
这里我们看到所有模式下的表数量
PDU.public=# use hr;
┌────────────────────────────────────────┐
│ Schema │ Tab Num │
├────────────────────────────────────────┤
│ public │ 7 │
└────────────────────────────────────────┘
hr.public=#
进入模式
比如进入到public模式set public;
这里我们看到public模式下的7个表
hr.public=# set public;
┌──────────────────────────────────────────────────┐
| Tablename | Size |
├──────────────────────────────────────────────────┤
│ employees │ 16.00 KB │
│ regions │ 8.00 KB │
│ countries │ 8.00 KB │
│ locations │ 8.00 KB │
│ departments │ 8.00 KB │
│ jobs │ 8.00 KB │
│ job_history │ 8.00 KB │
└──────────────────────────────────────────────────┘
Only display the first 7th tables in order of tableSize
hr.public=#
显示创建表的语句
对employees这个表查看创建表的SQL语句
\d+ employees;
hr.public=# \d+ employees;
----------------------------------------------------------------
| 建表语句 |
----------------------------------------------------------------
CREATE TABLE employees (
employee_id int4,
first_name varchar(20),
last_name varchar(25),
email varchar(25),
phone_number varchar(20),
hire_date timestamp,
job_id varchar(10),
salary numeric(8,2),
commission_pct numeric(2,2),
manager_id int4,
department_id int4
);
----------------------------------------------------------------
| |
----------------------------------------------------------------
hr.public=#
恢复单个表的数据
把employees这个表恢复成csv格式的数据文件
unload tab employees;
也可以设置成SQL方式
p exmode sql;
恢复成insert into的SQL方式数据文件
hr.public=# unload tab employees;
Table <employees>. Pages decoded: 2, Records decoded: 107
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname employees(/opt/pgdata/base/16538/16586)
● Pages: 2 ● 107 Records in total
● Success: 107 ● Faliure: 0
● File Path: hr/public/employees.csv
└─────────────────────────────────────────────────────────┘
hr.public=# p exmode sql;
┌─────────────────────────────────────────────────────────────────┐
│ parameter │ value │
├─────────────────────────────────────────────────────────────────┤
│ startwal │ │
│ endwal │ │
│ starttime │ │
│ endtime │ │
│ resmode(Data Restore Mode) │ TX │
│ exmode(Data Export Mode) │ SQL │
│ encoding │ UTF8 │
│ restype(Data Restore Type) │ DELETE │
└─────────────────────────────────────────────────────────────────┘
hr.public=# unload tab employees;
Table <employees>. Pages decoded: 2, Records decoded: 107
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname employees(/opt/pgdata/base/16538/16586)
● Pages: 2 ● 107 Records in total
● Success: 107 ● Faliure: 0
● File Path: hr/public/employees.sql
└─────────────────────────────────────────────────────────┘
hr.public=#
开启psql把employees表恢复demo数据库中
[postgres@pg16 pdu]$ psql
psql (16.3)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
demo | sunying | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
hr | hr | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
mytestdb | u1 | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(6 rows)
postgres=# \c demo
You are now connected to database "demo" as user "postgres".
demo=# CREATE TABLE employees (
employee_id int4,
first_name varchar(20),
last_name varchar(25),
email varchar(25),
phone_number varchar(20),
hire_date timestamp,
job_id varchar(10),
salary numeric(8,2),
commission_pct numeric(2,2),
manager_id int4,
department_id int4
);
CREATE TABLE
demo=# \i /home/postgres/pdu/hr/public/employees.sql
INSERT 0 1
INSERT 0 1
省略。。
INSERT 0 1
INSERT 0 1
demo=# select count(1) from employees;
count
-------
107
(1 row)
demo=#
恢复误删和误更新数据
模拟删除数据，把demo数据库中刚恢复的employees表删除部分记录，同时更新employees表中部分记录
demo=# delete from employees where salary=2600;
DELETE 4
demo=# update employees set job_id='UPDATE' where salary=10000;
UPDATE 4
demo=# select * from employees where salary=10000;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+---------+--------------------+---------------------+--------+----------+----------------+------------+---------------
150 | Peter | Tucker | PTUCKER | 011.44.1344.129268 | 1997-01-30 00:00:00 | UPDATE | 10000.00 | 0.30 | 145 | 80
156 | Janette | King | JKING | 011.44.1345.429268 | 1996-01-30 00:00:00 | UPDATE | 10000.00 | 0.35 | 146 | 80
169 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | 1998-03-23 00:00:00 | UPDATE | 10000.00 | 0.20 | 148 | 80
204 | Hermann | Baer | HBAER | 515.123.8888 | 1994-06-07 00:00:00 | UPDATE | 10000.00 | | 101 | 70
(4 rows)
demo=# select pg_switch_wal();
pg_switch_wal
---------------
0/8802A218
(1 row)
demo=# select pg_switch_wal();
pg_switch_wal
---------------
0/8C000128
(1 row)
demo=# checkpoint;
CHECKPOINT
demo=#
扫描表，如果数据字典有变化在进入PDU后重新初始化b;，进入use demo;数据库，使用scan <表名>;进行扫描
[postgres@pg16 pdu]$ ./pdu16
╔══════════════════════════════════════════════════════╗
║ Copyright 2024-2025 ZhangChen. All rights reserved ║
║ PDU: PostgreSQL Data Unloader ║
║ Version 2.5.0 (2025-05-23) ║
╚══════════════════════════════════════════════════════╝
Current DB Supported Version:
──────────────────────────
• PostgreSQL 16
╔═══════════════════════════════════════════╗
║ COMMUNITY VERSION ║
╠═══════════════════════════════════════════╣
║ • Max 100000 records per table (unload) ║
║ • Max 100000 records per table (restore) ║
║ • Speed limitation ║
╚═══════════════════════════════════════════╝
Contact Me:
───────────────────
• WeChat: x1987LJ2020929
• Email: 1109315180@qq.com
• Tel: 15251853831
PDU.public=# b;
Initializing...
-pg_database:</opt/pgdata/global/1262>
Database:postgres
-pg_schema:</opt/pgdata/base/5/2615>
-pg_class:</opt/pgdata/base/5/1259> 85 Records
-pg_attribute:</opt/pgdata/base/5/1249> 3156 Records
Schema:
▌ public 2 tables
Database:demo
-pg_schema:</opt/pgdata/base/16391/2615>
-pg_class:</opt/pgdata/base/16391/1259> 88 Records
-pg_attribute:</opt/pgdata/base/16391/1249> 3187 Records
Schema:
▌ public 5 tables
Database:mytestdb
-pg_schema:</opt/pgdata/base/16414/2615>
-pg_class:</opt/pgdata/base/16414/1259> 86 Records
-pg_attribute:</opt/pgdata/base/16414/1249> 3172 Records
Schema:
▌ public 3 tables
Database:hr
-pg_schema:</opt/pgdata/base/16538/2615>
-pg_class:</opt/pgdata/base/16538/1259> 89 Records
-pg_attribute:</opt/pgdata/base/16538/1249> 3242 Records
Schema:
▌ public 7 tables
PDU.public=# use demo;
┌────────────────────────────────────────┐
│ Schema │ Tab Num │
├────────────────────────────────────────┤
│ public │ 5 │
└────────────────────────────────────────┘
demo.public=# scan employees;
Scanning deleted Records for table<employees>...
▌ Scanning Archived Wal Directory
StartWal: 000000010000000000000001
EndWal: 000000010000000000000023
▌ Tx Restore Mode [Displayed by Tx groups]
────────────────────────────────────────
▌ End of Scanning, current time range:
Start: 2023-12-25 19:20:43.225414 CST
End: 2025-05-23 08:59:36.859793 CST
▌ Tx Details
┌─────────────────────────────────────────────────────────┐
Timestamp: 2025-05-23 08:57:38.013987 CST
LSN: 0/88026EB0 - 0/88029E98
Recommanded startwal: 000000010000000000000022
Recommanded endwal: 000000010000000000000022
-------------------.--------------------
● Tx Number: 10551186 ● Records deleted by the TX: 4
● Datafiel OID: 16652 ● Toastfile OID: 0
└─────────────────────────────────────────────────────────┘
[!] Note: The 'Recommended startwal' indicates the suggested value to set for startwal during transaction recovery
The 'Recommended endwal' mandates that startwal must be set to this value, otherwise recovery may fail
demo.public=#
恢复被删数据
通过事务号恢复被误删的数据，注意这里的
restore del
通过时间段恢复被误删的数据
restore del all;
demo.public=# p exmode sql;
┌─────────────────────────────────────────────────────────────────┐
│ parameter │ value │
├─────────────────────────────────────────────────────────────────┤
│ startwal │ │
│ endwal │ │
│ starttime │ │
│ endtime │ │
│ resmode(Data Restore Mode) │ TX │
│ exmode(Data Export Mode) │ SQL │
│ encoding │ UTF8 │
│ restype(Data Restore Type) │ DELETE │
└─────────────────────────────────────────────────────────────────┘
demo.public=# restore del 10551186;
▌ Scanning Archived Wal Directory
StartWal: 000000010000000000000001
EndWal: 000000010000000000000023
Current startwal setting has significant deviation from recommended value
causing degraded recovery efficiency. Confirm execution? (y/n)
y
▌ Time Range Restore Mode [Displayed all within Time Range]
────────────────────────────────────────
|-Records Decoded: 4
▌ Restore Complete
┌───────────────────────────────────────────────────────────────┐
Table <employees>
● Records Restored: 4
● Success: 4 ● Failure: 0
● File Path: restore/public/employees_10551186.sql
└───────────────────────────────────────────────────────────────┘
demo.public=#
恢复被更新数据，基本步骤也同删除恢复，主要不同的是需要恢复模式p restype update;
[postgres@pg16 pdu]$ ./pdu16
╔══════════════════════════════════════════════════════╗
║ Copyright 2024-2025 ZhangChen. All rights reserved ║
║ PDU: PostgreSQL Data Unloader ║
║ Version 2.5.0 (2025-05-23) ║
╚══════════════════════════════════════════════════════╝
Current DB Supported Version:
──────────────────────────
• PostgreSQL 16
╔═══════════════════════════════════════════╗
║ COMMUNITY VERSION ║
╠═══════════════════════════════════════════╣
║ • Max 100000 records per table (unload) ║
║ • Max 100000 records per table (restore) ║
║ • Speed limitation ║
╚═══════════════════════════════════════════╝
Contact Me:
───────────────────
• WeChat: x1987LJ2020929
• Email: 1109315180@qq.com
• Tel: 15251853831
PDU.public=# use demo;
┌────────────────────────────────────────┐
│ Schema │ Tab Num │
├────────────────────────────────────────┤
│ public │ 5 │
└────────────────────────────────────────┘
demo.public=# p exmode sql;
┌─────────────────────────────────────────────────────────────────┐
│ parameter │ value │
├─────────────────────────────────────────────────────────────────┤
│ startwal │ │
│ endwal │ │
│ starttime │ │
│ endtime │ │
│ resmode(Data Restore Mode) │ TX │
│ exmode(Data Export Mode) │ SQL │
│ encoding │ UTF8 │
│ restype(Data Restore Type) │ DELETE │
└─────────────────────────────────────────────────────────────────┘
demo.public=# p resmode time;
┌─────────────────────────────────────────────────────────────────┐
│ parameter │ value │
├─────────────────────────────────────────────────────────────────┤
│ startwal │ │
│ endwal │ │
│ starttime │ │
│ endtime │ │
│ resmode(Data Restore Mode) │ TIME │
│ exmode(Data Export Mode) │ SQL │
│ encoding │ UTF8 │
│ restype(Data Restore Type) │ DELETE │
└─────────────────────────────────────────────────────────────────┘
demo.public=# p restype update;
┌─────────────────────────────────────────────────────────────────┐
│ parameter │ value │
├─────────────────────────────────────────────────────────────────┤
│ startwal │ │
│ endwal │ │
│ starttime │ │
│ endtime │ │
│ resmode(Data Restore Mode) │ TIME │
│ exmode(Data Export Mode) │ SQL │
│ encoding │ UTF8 │
│ restype(Data Restore Type) │ UPDATE │
└─────────────────────────────────────────────────────────────────┘
demo.public=# scan employees;
Scanning updated Records for table<employees>...
▌ Scanning Archived Wal Directory
StartWal: 000000010000000000000001
EndWal: 000000010000000000000023
▌ Time Range Restore Mode [Displayed all within Time Range]
────────────────────────────────────────
▌ End of Scanning, current time range:
Start: 2023-12-25 19:20:43.225414 CST
End: 2025-05-23 08:59:36.859793 CST
▌ Time Range Details
┌─────────────────────────────────────────────────────────┐
Start Time: 2023-12-25 19:20:43.225414 CST
End Time: 2025-05-23 08:59:36.859793 CST
LSN: 0/88029F38 - 0/8802A120
Recommanded startwal: 000000010000000000000022
Recommanded endwal: 000000010000000000000022
-------------------.--------------------
● Datafile OID: 16652 ● Toasfile OID: 0
● Records updated in the Time Range: 4
└─────────────────────────────────────────────────────────┘
[!] Note: The 'Recommended startwal' indicates the suggested value to set for startwal during transaction recovery
The 'Recommended endwal' mandates that startwal must be set to this value, otherwise recovery may fail
demo.public=# p startwal 000000010000000000000022;
┌─────────────────────────────────────────────────────────────────┐
│ parameter │ value │
├─────────────────────────────────────────────────────────────────┤
│ startwal │000000010000000000000022 │
│ endwal │ │
│ starttime │2023-12-25 19:20:43.225414 CST│
│ endtime │2025-05-23 08:59:36.859793 CST│
│ resmode(Data Restore Mode) │ TIME │
│ exmode(Data Export Mode) │ SQL │
│ encoding │ UTF8 │
│ restype(Data Restore Type) │ UPDATE │
└─────────────────────────────────────────────────────────────────┘
demo.public=# p endwal 000000010000000000000022;
┌─────────────────────────────────────────────────────────────────┐
│ parameter │ value │
├─────────────────────────────────────────────────────────────────┤
│ startwal │000000010000000000000022 │
│ endwal │000000010000000000000022 │
│ starttime │2023-12-25 19:20:43.225414 CST│
│ endtime │2025-05-23 08:59:36.859793 CST│
│ resmode(Data Restore Mode) │ TIME │
│ exmode(Data Export Mode) │ SQL │
│ encoding │ UTF8 │
│ restype(Data Restore Type) │ UPDATE │
└─────────────────────────────────────────────────────────────────┘
demo.public=# restore upd all;
▌ Scanning Archived Wal Directory
StartWal: 000000010000000000000022
EndWal: 000000010000000000000022
▌ Tx Restore Mode [Displayed by Tx groups]
────────────────────────────────────────
|-Records Decoded: 4
▌ Restore Complete
┌───────────────────────────────────────────────────────────────┐
Table <employees>
● Records Restored: 4
● Success: 4 ● Same Values for Updates: 0 ● Failure: 0
● File Path: restore/public/employees_2023-12-25 19:20:43.225414 CST_2025-05-23 08:59:36.859793 CST.sql
└───────────────────────────────────────────────────────────────┘
demo.public=#
在数据库里操作被删除和被更新的记录恢复。并验证。
[postgres@pg16 pdu]$ psql
psql (16.3)
Type "help" for help.
postgres=# \c demo
You are now connected to database "demo" as user "postgres".
demo=# select count(1) from employees;
count
-------
103
(1 row)
demo=# select * from employees where salary=2600;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+-------+--------------+-----------+--------+--------+----------------+------------+---------------
(0 rows)
demo=# select job_id from employees where salary=10000;
job_id
--------
UPDATE
UPDATE
UPDATE
UPDATE
(4 rows)
demo=# \i /home/postgres/pdu/restore/public/employees_10551186.sql
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
demo=# \i /home/postgres/pdu/restore/public/'employees_2023-12-25 19:20:43.225414 CST_2025-05-23 08:59:36.859793 CST.sql'
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
demo=# select count(1) from employees;
count
-------
107
(1 row)
demo=# select * from employees where salary=2600;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------+---------------------+----------+---------+----------------+------------+---------------
118 | Guy | Himuro | GHIMURO | 515.127.4565 | 1998-11-15 00:00:00 | PU_CLERK | 2600.00 | | 114 | 30
143 | Randall | Matos | RMATOS | 650.121.2874 | 1998-03-15 00:00:00 | ST_CLERK | 2600.00 | | 124 | 50
198 | Donald | OConnell | DOCONNEL | 650.507.9833 | 1999-06-21 00:00:00 | SH_CLERK | 2600.00 | | 124 | 50
199 | Douglas | Grant | DGRANT | 650.507.9844 | 2000-01-13 00:00:00 | SH_CLERK | 2600.00 | | 124 | 50
(4 rows)
demo=# select job_id from employees where salary=10000;
job_id
--------
SA_REP
SA_REP
SA_REP
PR_REP
(4 rows)
demo=#
恢复整个模式数据
比如对hr数据库整个模式public恢复
use hr;
p exmode sql;
unload sch public;
[postgres@pg16 pdu]$ ./pdu16
╔══════════════════════════════════════════════════════╗
║ Copyright 2024-2025 ZhangChen. All rights reserved ║
║ PDU: PostgreSQL Data Unloader ║
║ Version 2.5.0 (2025-05-23) ║
╚══════════════════════════════════════════════════════╝
Current DB Supported Version:
──────────────────────────
• PostgreSQL 16
╔═══════════════════════════════════════════╗
║ COMMUNITY VERSION ║
╠═══════════════════════════════════════════╣
║ • Max 100000 records per table (unload) ║
║ • Max 100000 records per table (restore) ║
║ • Speed limitation ║
╚═══════════════════════════════════════════╝
Contact Me:
───────────────────
• WeChat: x1987LJ2020929
• Email: 1109315180@qq.com
• Tel: 15251853831
PDU.public=# \l;
┌───────────────────┐
│ Database │
├───────────────────┤
│ postgres │
│ demo │
│ mytestdb │
│ hr │
└───────────────────┘
PDU.public=# use hr;
┌────────────────────────────────────────┐
│ Schema │ Tab Num │
├────────────────────────────────────────┤
│ public │ 7 │
└────────────────────────────────────────┘
hr.public=# \dt;
┌──────────────────────────────────────────────────┐
| Tablename | Size |
├──────────────────────────────────────────────────┤
│ employees │ 16.00 KB │
│ regions │ 8.00 KB │
│ countries │ 8.00 KB │
│ locations │ 8.00 KB │
│ departments │ 8.00 KB │
│ jobs │ 8.00 KB │
│ job_history │ 8.00 KB │
└──────────────────────────────────────────────────┘
7 tables in total
hr.public=# unload sch public;
Table <regions>. Pages decoded: 1, Records decoded: 4
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname regions(/opt/pgdata/base/16538/16540)
● Pages: 1 ● 4 Records in total
● Success: 4 ● Faliure: 0
● File Path: hr/public/regions.csv
└─────────────────────────────────────────────────────────┘
Table <countries>. Pages decoded: 1, Records decoded: 25
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname countries(/opt/pgdata/base/16538/16546)
● Pages: 1 ● 25 Records in total
● Success: 25 ● Faliure: 0
● File Path: hr/public/countries.csv
└─────────────────────────────────────────────────────────┘
Table <locations>. Pages decoded: 1, Records decoded: 23
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname locations(/opt/pgdata/base/16538/16557)
● Pages: 1 ● 23 Records in total
● Success: 23 ● Faliure: 0
● File Path: hr/public/locations.csv
└─────────────────────────────────────────────────────────┘
Table <departments>. Pages decoded: 1, Records decoded: 27
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname departments(/opt/pgdata/base/16538/16569)
● Pages: 1 ● 27 Records in total
● Success: 27 ● Faliure: 0
● File Path: hr/public/departments.csv
└─────────────────────────────────────────────────────────┘
Table <jobs>. Pages decoded: 1, Records decoded: 19
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname jobs(/opt/pgdata/base/16538/16580)
● Pages: 1 ● 19 Records in total
● Success: 19 ● Faliure: 0
● File Path: hr/public/jobs.csv
└─────────────────────────────────────────────────────────┘
Table <employees>. Pages decoded: 2, Records decoded: 107
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname employees(/opt/pgdata/base/16538/16586)
● Pages: 2 ● 107 Records in total
● Success: 107 ● Faliure: 0
● File Path: hr/public/employees.csv
└─────────────────────────────────────────────────────────┘
Table <job_history>. Pages decoded: 1, Records decoded: 10
▌ Decode Complete
┌─────────────────────────────────────────────────────────┐
Tabname job_history(/opt/pgdata/base/16538/16615)
● Pages: 1 ● 10 Records in total
● Success: 10 ● Faliure: 0
● File Path: hr/public/job_history.csv
└─────────────────────────────────────────────────────────┘
Schema <public> 7 tables in total。Success: 7, Empty table: 0, Failure: 0
Log Path
|-Succ Log：log/hr_unload_schema_public_err.txt
|-Fail Log：log/hr_unload_schema_public_succ.txt
COPY EXPORTED COMPLETED
|-FILE PATH:hr/COPY/public_copy.sql, 7 csv files found.
DDL EXPORTED COMPLETED
|-PATH:hr/DDL/public_ddl.sql, 7 TABLES IN TOTAL
hr.public=#
psql创建huifu数据库把整个模式public进行恢复
[postgres@pg16 pdu]$ psql
psql (16.3)
Type "help" for help.
postgres=# create database huifu;
CREATE DATABASE
postgres=# \c huifu
You are now connected to database "huifu" as user "postgres".
huifu=# \i /home/postgres/pdu/hr/DDL/public_ddl.sql
psql:/home/postgres/pdu/hr/DDL/public_ddl.sql:1: ERROR: schema "public" already exists
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
huifu=# \i /home/postgres/pdu/hr/COPY/public_copy.sql
SET
COPY 4
COPY 25
COPY 23
COPY 27
COPY 19
COPY 107
COPY 10
huifu=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
public | countries | table | postgres
public | departments | table | postgres
public | employees | table | postgres
public | job_history | table | postgres
public | jobs | table | postgres
public | locations | table | postgres
public | regions | table | postgres
(7 rows)
huifu=# select * from jobs;
job_id | job_title | min_salary | max_salary
------------+---------------------------------+------------+------------
AD_PRES | President | 20000 | 40000
AD_VP | Administration Vice President | 15000 | 30000
AD_ASST | Administration Assistant | 3000 | 6000
FI_MGR | Finance Manager | 8200 | 16000
FI_ACCOUNT | Accountant | 4200 | 9000
AC_MGR | Accounting Manager | 8200 | 16000
AC_ACCOUNT | Public Accountant | 4200 | 9000
SA_MAN | Sales Manager | 10000 | 20000
SA_REP | Sales Representative | 6000 | 12000
PU_MAN | Purchasing Manager | 8000 | 15000
PU_CLERK | Purchasing Clerk | 2500 | 5500
ST_MAN | Stock Manager | 5500 | 8500
ST_CLERK | Stock Clerk | 2000 | 5000
SH_CLERK | Shipping Clerk | 2500 | 5500
IT_PROG | Programmer | 4000 | 10000
MK_MAN | Marketing Manager | 9000 | 15000
MK_REP | Marketing Representative | 4000 | 9000
HR_REP | Human Resources Representative | 4000 | 9000
PR_REP | Public Relations Representative | 4500 | 10500
(19 rows)
huifu=#
写在最后
更多详情请关注作者公众号ZhangChen-PDU。和官网https://www.pgdul.com/
最后修改时间：2025-05-26 10:03:33
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
