在某些情况下,比如我们需要对单个的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 的表名
4.使用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
5.导入数据到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