Postgres数据库truncate表无有效备份恢复---惜分飞

创建一个Postgres表,并插入数据

postgres=# CREATE TABLE "PeisInterfaceLog"( postgres(#         "PeisInterfaceLogId" text, postgres(#         "PeisDepartmentId" text, postgres(#         "PeisDepartmentName" text, postgres(#         "PeisInterfaceSubjectType" text, postgres(#         "PeisInterfaceSubjectId" text, postgres(#         "PeisInterfaceNo" text, postgres(#         "PeisInterfaceName" text, postgres(#         "PeisInterfaceDirection" text, postgres(#         "PeisInterfaceCallAddress" text, postgres(#         "PeisInterfaceLogStartTime" timestamp, postgres(#         "PeisInterfaceInputContent" json, postgres(#         "PeisInterfaceInputTranscodeContent" json, postgres(#         "PeisInterfaceOutContent" json, postgres(#         "PeisInterfaceOutTranscodeContent" json, postgres(#         "PeisInterfaceSuccessSign" int4, postgres(#         "PeisInterfaceLogStatusCode" text, postgres(#         "PeisInterfaceLogNote" text, postgres(#         "PeisInterfaceLogTimestamp" timestamp, postgres(#         "PeisInterfaceLogInfo" text, postgres(#         "PeisPatientRegisterId" text postgres(# ); CREATE TABLE postgres=# \i /postgres/COPY/public_copy.sql SET COPY 722957 postgres=# select count(1) from "PeisInterfaceLog";  count  --------  722957 (1 row)

验证truncate操作,引起Postgres中oid的变化

postgres=# checkpoint; CHECKPOINT postgres=#  show data_directory;  data_directory ----------------  /pgdata (1 row) postgres=# select oid, datname from pg_database ;   oid  |  datname  -------+-----------  13676 | postgres      1 | template1  13675 | template0 (3 rows) postgres=# select relname, relowner, relfilenode from pg_class where relowner = 10 and relname like '%PeisInterfaceLog%';      relname      | relowner | relfilenode ------------------+----------+-------------  PeisInterfaceLog |       10 |       16384 (1 row) postgres=# truncate table "PeisInterfaceLog"; TRUNCATE TABLE postgres=# select count(1) from  "PeisInterfaceLog";  count -------      0 (1 row) postgres=# select relname, relowner, relfilenode from pg_class where relowner = 10 and relname like '%PeisInterfaceLog%';      relname      | relowner | relfilenode ------------------+----------+-------------  PeisInterfaceLog |       10 |       16394 (1 row)

使用工具进行初始化字典信息

PDU.public=# b; 开始初始化...  -pg_database:</pgdata/global/1262> 数据库:postgres       -pg_schema:</pgdata/base/13676/2615>       -pg_class:</pgdata/base/13676/1259> 共88行       -pg_attribute:</pgdata/base/13676/1249> 共2950行       模式:         ▌ public 1张表 PDU.public=# use postgres; ┌────────────────────────────────────────┐ │          模式             │  表数量    │ ├────────────────────────────────────────┤ │    public                 │  1         │ └────────────────────────────────────────┘ postgres.public=# set public; ┌──────────────────────────────────────────────────┐ │               表名                  │  表大小    │ ├──────────────────────────────────────────────────┤ │    PeisInterfaceLog                 │  0         │ └──────────────────────────────────────────────────┘         仅显示表大小排名前 1 的表名 postgres.public=# \d PeisInterfaceLog; ┌──────────────────────────────────────────────────────────────┐ │                            列类型                            │ └──────────────────────────────────────────────────────────────┘ text,text,text,text,text,text,text,text,text,timestamp,json,json,json,json,int4,text,text,timestamp,text,text

配置软件磁盘扫描操作（pdu.ini中配置）

#dropScan需要扫描的磁盘 DISK_PATH=/data/test.dd #dropScan时跳跃的数据块数量，数值越小覆盖磁盘越全面，速度越慢 BLOCK_INTERVAL=5

启用磁盘扫描操作

PDU.public=# p idxmode off; ┌─────────────────────────────────────────────────────────────────┐ │              参数                │             当前值           │ ├─────────────────────────────────────────────────────────────────┤ │    startwal                      │                              │ │    endwal                        │                              │ │    startlsnt                     │                              │ │    endlsnt                       │                              │ │    starttime                     │                              │ │    endtime                       │                              │ │    resmode(Data Restore Mode)    │              TIME            │ │    exmode(Data Export Mode)      │              CSV             │ │    encoding                      │              UTF8            │ │    restype(Data Restore Type)    │              DELETE          │           ----------------------DropScan---------------------- │    dsoff(DropScan startOffset)   │              0               │ │    blkiter(Block Intervals)      │              5               │ │    itmpcsv(Items Per Csv)        │              100             │ │    idxmode                       │              off             │ └─────────────────────────────────────────────────────────────────┘ PDU.public=# ds;  ▌全量扫描恢复模式  ▌数据文件扫描 ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐           表名           │                                   结果                                    ├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤  PeisInterfaceLog           99.976 %(21469593600)   数据页: 71158      成功: 722947    (疑似乱码: 2809      ) 失败: 0 耗时 15.28 秒 └────────────────────────────────────────────────────────────────────────────────────────────────────┘ 扫描完毕,文件目录如下:         restore/dropscan/PeisInterfaceLog PDU.public=# ds copy; 已导出: /restore/dropscan/PeisInterfaceLog/COPY.sql PDU.public=# [root@xifenfei PeisInterfaceLog]# more COPY.sql [root@xifenfei PeisInterfaceLog]# cat /restore/dropscan/PeisInterfaceLog/COPY.sql COPY PeisInterfaceLog FROM '/restore/dropscan/PeisInterfaceLog/09-28-21:29:55_226738176_32760blks_336787items.csv'; COPY PeisInterfaceLog FROM '/restore/dropscan/PeisInterfaceLog/09-28-21:30:01_595968000_32767blks_330416items.csv'; COPY PeisInterfaceLog FROM '/restore/dropscan/PeisInterfaceLog/09-28-21:30:04_1116061696_5631blks_55744items.csv';

把恢复数据导入到Postgres数据中

[root@xifenfei ~]# su - postgres [postgres@xifenfei ~]$ psql psql (12.8) Type "help" for help. postgres=# \i /restore/dropscan/PeisInterfaceLog/COPY.sql COPY 336787 COPY 330416 COPY 55744 postgres=# select count(1) from "PeisInterfaceLog";  count  --------  722947 (1 row)

经过上述扫描测试证明该工具实现了在Postgres中truncate数据的绝大部分数据恢复(这里的乱码不是由于没有扫描到数据,主要是由于个别字符串由于类型判断关系识别不对导致乱码抛弃).
如果你遇到Postgres 数据库由于drop/truncate等误操作，而且无有效备份进行恢复，面临数据丢失风险,请第一时间保护现场(数据文件所在分区尽可能不要有写入操作)，contact我们提供support