PostgreSQL恢复工具—pdu恢复单个表文件

在某些情况下,比如我们需要对单个的PostgreSQL库的表文件进行恢复(比如文件系统损坏,drop库/表,truncate表等原因,然后找到了部分oid文件),可以使用pdu对其进行完美恢复（相比pg_filedump也方便很多）,具体操作步骤:
1. 由于只有单个表文件,无法获取字典信息,因此需要应用厂商/客户提供具体表创建语句

his5_dms=#    CREATE TABLE t_xff (
his5_dms(#         id                       bigint,
his5_dms(#         hospital_id              bigint,
his5_dms(#         parent_id                bigint,
his5_dms(#         disease_code             varchar(60),
his5_dms(#         disease_name             varchar(60),
his5_dms(#         type                     smallint,
his5_dms(#         py                       varchar(60),
his5_dms(#         wb                       varchar(60),
his5_dms(#         sc                       varchar(20),
his5_dms(#         order_no                 int,
his5_dms(#         state                    smallint,
his5_dms(#         create_datetime          timestamp(6),
his5_dms(#         create_id                bigint,
his5_dms(#         edit_datetime            timestamp(6),
his5_dms(#         edit_id                  bigint,
his5_dms(#         search_path              varchar(300),
his5_dms(#         diagnosis_sort           int,
his5_dms(#         category_name            varchar(40),
his5_dms(#         input_option             varchar(40),
his5_dms(#         category_class           smallint,
his5_dms(#         memo1                    varchar(300),
his5_dms(#         memo2                    varchar(300),
his5_dms(#         other_code               varchar(60),
his5_dms(#         other_name               varchar(60),
his5_dms(#         special_disease_flag     smallint
his5_dms(#    );
CREATE TABLE

2. 把oid文件pdu放到restore库中

[root@xifenfeidg public]# pwd
/tmp/pdu/restore/public
[root@xifenfeidg public]# ls -l
total 7144
-rw-r--r--. 1 root root 7315456 Mar  2 21:04 123456
[root@xifenfeidg public]# 

3. 使用add语句在pdu加载数据类型

restore.public=# add 123456 t_xff bigint,bigint,bigint,varchar,varchar,smallint,varchar,varchar,varchar,
int,smallint,timestamp,bigint,timestamp,bigint,varchar,int,varchar,varchar,
smallint,varchar,varchar,varchar,varchar,smallint;
添加完成，请用\dt;查看可unload的表
restore.public=# \dt;
|--------------------------------------------------|
|               表名                  |  表大小    |
|--------------------------------------------------|
|    t_xff                            |  6.98 MB   |
|--------------------------------------------------|

        仅显示表大小排名前 1 的表名

使用pdu恢复表数据

restore.public=# unload t_xff;
正在解析表 <t_xff>. 已解析数据页: 893, 已解析数据: 46998 条
<t_xff>-<restore/public/123456> 解析完成, 894 个数据页 ,共计 46998 条数据. 成功 46998 条; 失败【0】条 
 COPY文件路径为:<restore/public/t_xff.csv>
restore.public=# unload COPY;

COPY命令导出完成, 文件路径: restore/COPY/public_copy.sql

导入数据到pg库中

his5_dms=# \i restore/COPY/public_copy.sql
SET
COPY 46998
his5_dms=# select count(1) from t_xff;
 count 
-------
 46998
(1 row)
his5_dms=# \x
Expanded display is on.
his5_dms=# select * from t_xff limit 1;
-[ RECORD 1 ]--------+---------------------------
id                   | 323839
hospital_id          | 0
parent_id            | 301
disease_code         | 57.8900x003
disease_name         | 腹腔镜下膀胱颈悬吊术
type                 | 2
py                   | fqjxpgjxds
wb                   | eeqgeeceks
sc                   | 
order_no             | 0
state                | 1
create_datetime      | 2022-09-29 15:22:58.588492
create_id            | 
edit_datetime        | 
edit_id              | 
search_path          | 301,
diagnosis_sort       | 
category_name        | 
input_option         | 
category_class       | 3
memo1                | 
memo2                | 
other_code           | 
other_name           | 
special_disease_flag | 0