PostgreSQL数据delete删除恢复
第一部分 文档描述
本文档适用数据表数据被delete类型的删除语句情况下恢复，需要满足数据库或数据表未被vacuum或者vacuum full
第二部分 操作步骤
2.1 创建测试表
创建测试表novels，并插入测试数据
dbtest=# create table novels (name varchar(200), id int);
CREATE TABLE
dbtest=# insert into novels values('三国演义',1);
INSERT 0 1
dbtest=# insert into novels values('水浒传',2);
INSERT 0 1
dbtest=# insert into novels values('西游记',3);
INSERT 0 1
dbtest=# insert into novels values('红楼梦',4);
2.2 查询表文件位置
查询测试表的文件位置
dbtest=# select oid from pg_database where datname='dbtest';
oid
-------
16571
(1 row)
dbtest=# select oid,relfilenode from pg_class where relname='novels';
oid | relfilenode
-------+-------------
24783 | 24783
(1 row)
也可以通过函数获取表文件位置
dbtest=# select pg_relation_filepath(' novels ');
pg_relation_filepath
----------------------
base/16571/24783
(1 row)
2.3 模拟删除表数据
拟delete删除测试表的数据
dbtest=# delete from novels;
DELETE 4
dbtest=# select * from novels;
name | id
------+----
(0 rows)
2.4 安装pg_filedump
本操作文档通过pg_filedump插件解析表文件来获取数据，我们通过git下载并安装
[root@VM-4-13-centos ~]# yum install git -y
[postgres@VM-4-13-centos ~]$ cd pg_filedump/
[postgres@VM-4-13-centos pg_filedump]$ make
[postgres@VM-4-13-centos pg_filedump]$ make install
/usr/bin/mkdir -p '/software/pgsql13/bin'
/usr/bin/install -c pg_filedump '/software/pgsql13/bin'
2.5 解析删除的数据
解析被删除的数据之前首先查询表在删除时间点之后是否被vacuum（包括手动vacuum和autovacuum）
dbtest=# \x
Expanded display is on.
dbtest=# select * from pg_stat_all_tables where relname = 'novels';
-[ RECORD 1 ]-------+------------------------------
relid | 24783
schemaname | public
relname | novels
seq_scan | 13
seq_tup_read | 44
idx_scan |
idx_tup_fetch |
n_tup_ins | 28
n_tup_upd | 0
n_tup_del | 22
n_tup_hot_upd | 0
n_live_tup | 6
n_dead_tup | 18
n_mod_since_analyze | 50
n_ins_since_vacuum | 24
last_vacuum | 2022-02-09 11:27:30.501748+08
last_autovacuum |
last_analyze |
last_autoanalyze |
vacuum_count | 1
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 0
如果没有被vacuum，则关闭表级别的autovacuum并开始解析步骤
dbtest=# alter table novels set (autovacuum_enabled = off);
通过上述步骤查询到的表数据文件来解析被删除的数据
[postgres@VM-4-13-centos data]$ pg_filedump -D charn,int base/16571/24783
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: base/16571/24783
* Options used: -D charn,int
*******************************************************************
Block 0 ********************************************************
<Header> -----
Block Offset: 0x00000000 Offsets: Lower 40 (0x0028)
Block: Size 8192 Version 4 Upper 8024 (0x1f58)
LSN: logid 0 recoff 0x070351c0 Special 8192 (0x2000)
Items: 4 Free Space: 7984
Checksum: 0x0000 Prune XID: 0x00000242 Flags: 0x0000 ()
Length (including item array): 40
<Data> -----
Item 1 -- Length: 44 Offset: 8144 (0x1fd0) Flags: NORMAL
COPY: 三国演义 1
Item 2 -- Length: 40 Offset: 8104 (0x1fa8) Flags: NORMAL
COPY: 水浒传 2
Item 3 -- Length: 40 Offset: 8064 (0x1f80) Flags: NORMAL
COPY: 西游记 3
Item 4 -- Length: 40 Offset: 8024 (0x1f58) Flags: NORMAL
COPY: 红楼梦 4
可以使用以下命令来过滤需要的数据
[postgres@VM-4-13-centos data]$ pg_filedump -D charn,int base/16571/24783|grep COPY
COPY: 三国演义 1
COPY: 水浒传 2
COPY: 西游记 3
COPY: 红楼梦 4
我们将COPY字段过滤并写入需要的数据到csv文件中
[postgres@VM-4-13-centos data]$ pg_filedump -D varchar,int base/16571/24783|grep COPY|sed 's/\COPY: //g' > import.csv
[postgres@VM-4-13-centos data]$ more import.csv
三国演义 1
水浒传 2
西游记 3
红楼梦 4
2.6 恢复数据到原表中
使用copy命令导入csv文件中的数据，并验证数据是否恢复
dbtest=# copy novels from '/software/pgsql13/data/import.csv';
dbtest=# select * from novels ;
name | id
----------+----
三国演义 | 1
水浒传 | 2
西游记 | 3
红楼梦 | 4
(4 rows)
原文地址：https://blog.csdn.net/weixin_42329915/article/details/144286274
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！