ITPub博客

首页 > 数据库 > PostgreSQL > PG 数据库的 表的不完全恢复的简易方法

PG 数据库的 表的不完全恢复的简易方法

原创 PostgreSQL 作者:babyyellow 时间:2020-04-27 14:51:03 0 删除 编辑

pg 数据库中如果某个对象或者表损坏,只能执行全库恢复,然后导出表,再倒入,这期间这个表都不能访问了



今天我们就来模拟一下这种情况下个一个简易的恢复方案(不完全恢复,部分顺坏的数据块的的数据将丢失) 



[code] 

建表  :  插入数据


postgres=# create table my_bad_table as select  * from pg_class; 
SELECT 301
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 301
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 602
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 1204
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 2408
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 4816
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 9632
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 19264
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 38528
postgres=# insert into  my_bad_table select  * from my_bad_table; 
INSERT 0 77056


然后查看表的信息: 

postgres=# select * from pg_class where relname='my_bad_table' ;            
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallv
isible | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | 
relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions 
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+--------
-------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+-
-----------+-------------+----------------+----------------+--------------+--------+------------
 my_bad_table |         2200 | 5961162 |         0 |       10 |     0 |     5961160 |             0 |     3649 |    154112 |        
     0 |       5961163 |             0 | f           | f           | p              | r       |       27 |         0 | f          | 
f          | f           | f              | f              |     11764150 |        


[/code]


表的总行数:  154112  

filenode :  5961160   


现在我们来物理上破坏这个表的数据文件。

到表的数据目录里 vi 这个表,修改里面一部分内容让表物理损毁。 


[code]
重启数据库,清空数据库内存  
因为是我们新建的表,所以数据在内存里是有cache 的,这个时候即便是物理损毁了,还是查到一些数据出来的。 
postgres=# \q
[postgres@test-11-16 ~]$ pg_ctl restart -m fast 
????·????÷??????±? .... ?ê??
·????÷??????????±?
????????·????÷????
[postgres@test-11-16 ~]$ LOG:  could not create IPv6 socket: Address family not supported by protocol

[postgres@test-11-16 ~]$ psql
psql (9.2.4)
???? "help" ??????°??ú????.

[/code] 



重新查询数据表看看情况: 

[code]
postgres=# select count(*) from my_bad_table ;
ERROR:  invalid page header in block 1 of relation base/12870/5961160
postgres=# select * from my_bad_table ;
ERROR:  invalid page header in block 1 of relation base/12870/5961160
postgres=# 
[/code]

表数据已经无法查出来了。 

看看表的数据信息:

[code]

postgres=# select * from pg_class where relname='my_bad_table' ;
   relname    | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallv
isible | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | 
relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions 
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+--------
-------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+-
-----------+-------------+----------------+----------------+--------------+--------+------------
 my_bad_table |         2200 | 5961162 |         0 |       10 |     0 |     5961160 |             0 |     3649 |    154112 |        
     0 |       5961163 |             0 | f           | f           | p              | r       |       27 |         0 | f          | 
f          | f           | f              | f              |     11764150 |        | 
[/code]

这个时候,我们是可以根据表的relpages  跟 reltuple 来查询出表的一部分数据来的。 



科普知识: 

PG 数据库的对行记录的标识是通过块+行在块内的索引来查找的对应的一行记录的。



我们知道了这个表的总的块数,那我们遍历所有的数据块的里索引记录应该是可以读出一些数据的: 

写个函数如下: 

[code]
CREATE OR REPLACE FUNCTION salvage_damaged_table(bad_table varchar) 
returns void 
language plpgsql 
AS $$ 
DECLARE
bad_table ALIAS FOR $1;
totpages int;
tottuples bigint;
pageno int; 
tupno int; 
pos tid; 
cnt bigint ;

BEGIN 
SELECT relpages, reltuples::bigint INTO totpages, tottuples
FROM pg_class 
WHERE relname = quote_ident(bad_table)
AND relkind = 'r';

RAISE NOTICE 'totpages %, tottuples %', totpages::text, tottuples::text;
for pageno in 0..totpages
loop -- pg_class.relpages for the damaged table 
cnt :=cnt+1
if cnt > 1000
then
RAISE NOTICE ' %  rows getted',cnt::text;
end if ;
for tupno in 1..65535 loop
pos = ('(' || pageno || ',' || tupno || ')')::tid; 
begin 
insert into salvaged 
select * 
from my_bad_table -- <-- Replace with actual table name here.
where ctid = pos; 
exception 
when sqlstate 'XX001' then 
raise warning 'skipping page %', pageno; 
continue pageloop; 
when others then 
raise warning 'skipping row %, SQLSTATE %', pos, SQLSTATE::text; 
end; 
end loop; 
end loop; 

RETURN;
end; 
$$; 

[/code] 


这个代码有个地方是可以优化的,每个行在数据库里的suoyin 是一个int 类型,这个数字是最大值是65535  所以我们在代码了在块内的循环我们设置了这个数字,而实际上,每数据块里存放多少数据是可以通过  sum(rows)/sum(pages) 计算出一个平均值的,考虑到不是所有的行的长度都是一样的,所以我们应该取个比平均值大一些的数字,尽可能的减少数据的丢失

本例中[postgres@test-11-16 ~]$ echo 154112/3649 |bc 
42 

一个块的平均有42行,我们给大一些,给100行 或者60,70行就可以了。 

我们就用修改后的代码来跑一下这个函数,看看能提取出多少数据。 

[code] 

函数已经建好: 


postgres=# \df 
                                    List of functions
 Schema |         Name          | Result data type |     Argument data types     |  Type  
--------+-----------------------+------------------+-----------------------------+--------
 public | salvage_damaged_table | void             | bad_table character varying | normal
(1 row)


postgres=# select  salvage_damaged_table('my_bad_table') ;

WARNING:  skipping page 1809
WARNING:  skipping page 1810
WARNING:  skipping page 1811
WARNING:  skipping page 1812
WARNING:  skipping page 1813
WARNING:  skipping page 1814
WARNING:  skipping page 1815
WARNING:  skipping page 1816
WARNING:  skipping page 1817
WARNING:  skipping page 1818
WARNING:  skipping page 1819
WARNING:  skipping page 1820
WARNING:  skipping page 1821
WARNING:  skipping page 1822
WARNING:  skipping page 1823
 salvage_damaged_table 
-----------------------
 
(1 row)


有些数据块已经损坏了,所以告警出来,这些块里的数据是没有办法提取出来的了。 

我们看看提取出了多少数据: 

postgres=# select count(*) from salvaged ; 
 count 
-------
(1 row)



[/code]

总共有   77068 记录被抽取出来。


这个文章的思想就是,我们根据数据在数据库里的存储形式,来实现了部分数据的不完全恢复。 

当然了,这个只是可以在极端情况下的一个补充。 





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/133735/viewspace-2160655/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
oracle MySQL Postgresql 专职数据库dba。 系统架构师。 mysql 官方认知dba 。 15年专职dba 经验。

注册时间:2010-12-02

  • 博文量
    300
  • 访问量
    1580295