PostgreSQL中恢复被误删除的行数据

如果自己搞不定可以找诗檀软件专业PostgreSQL数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638   QQ号:47079569    邮箱：[email protected]

在PostgreSQL中如果误删除了行数据 要如何恢复呢？

例如下面的例子:

postgres=# create database test3;
CREATE DATABASE
postgres=# \c test3
您现在已经连接到数据库 "test3",用户 "postgres".

test3=# create table novels(name varchar(100), id int);
CREATE TABLE

test3=# select * from pg_database where datname='test3';
oid | datname | datdba | encoding | datcollate | datctype | datistemplate | 
datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
-------+---------+--------+----------+--------------------------------+--------------------------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+--------
16432 | test3 | 10 | 6 | Chinese (Simplified)_China.936 | 
Chinese (Simplified)_China.936 | f | t | -1 | 13317 | 480 | 1 | 1663 |
(1 行记录)


test3=# select * from pg_class where relname='novels';
oid | relname | relnamespace | reltype | reloftype | relowner | relam | 
relfilenode | reltablespace | relpages | reltuples | relallvisible | 
reltoastrelid | relhasindex | relisshared | relpersistence | relkind | 
relnatts | relchecks | relhasrules | relhastriggers | relhassubclass | relrowsecurity | 
relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | 
relfrozenxid | relminmxid | relacl | reloptions | relpartbound
-------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
16433 | novels | 2200 | 16435 | 0 | 10 | 2 | 16433 | 0 | 0 | 0 | 0 | 0 | f | f | 
p | r | 2 | 0 | f | f | f | f | f | t | d | f | 0 | 612 | 1 | | |
(1 行记录)

test3=# select * from novels;
name | id
----------+----
三国演义 | 1
水浒传 | 2
红楼梦 | 3
西游记 | 4
(4 行记录)

cd $PGDATA


λ ls -l base\16432\16433
-rw-r--r-- 1 st 197121 0 11月 15 14:54 'base\16432\16433'


我们通过cat命令查看改文件 可以看到 其中内容

λ cat base\16432\16433
X$□| , 0 ♦ i ПX □□P □□P X□P 0□P i
♣ □)↑ 西游记 ♦ h i ♣ @♣↑ 西游记 ♥ g ♥ ↑ 红楼梦 ♥ f ↑ 水浒传 e ↑ □□国演义


如果我们尝试删除该表所有行:

test3=# delete from novels;
DELETE 4

再次用 cat 查看文件

λ cat base\16432\16433
X$□| , 0 ♦ i ПX □□P □□P X□P 0□P i
♣ □)↑ 西游记 ♦ h i ♣ @♣↑ 西游记 ♥ g ♥ ↑ 红楼梦 ♥ f ↑ 水浒传 e ↑ □□国演义


仍能看到数据 还存放于文件中 ， 等待60s后

λ sleep 60
λ cat base\16432\16433
H'□| , 0 ♦ i ПX □□P □□P X□P 0□P i
j ♣ □!↑ 西游记 ♦ h i ♣ @♣↑ 西游记 ♥ g j ♥ ↑ 红楼梦 ♥ f j ↑ 水浒传 e j ↑ □□国演义



数据仍在没有被vacuum清除 ，如果我们尝试手动vacuum 该表，先备份

cp base\16432\16433 base\16432\16433.bak

然后 vacuum

test3=# vacuum novels;
VACUUM


cat base\16432\16433

再次验证可以看到 数据已经没了；

所以postgreSQL中对于没有备份的且被delete的行数据而言，是否能恢复 其主要取决于是否被auto vacuum了。

对于大表的大量删除一般会引发这种自动清理，而对于小表而言一般不会。

可以得出如下结论：

1. 对于postgreSQL中的大规模delete操作，因为其将引发vacuum操作，从而导致其物理上不可能恢复
2. 对于少量数据的delete操作，不会引发vacuum，其物理上存在恢复可能

至于 truncate / drop table 在postgreSQL 中是不太可能恢复的，其原因说明