PG 数据库的 表的不完全恢复的简易方法
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 记录被抽取出来。
这个文章的思想就是，我们根据数据在数据库里的存储形式，来实现了部分数据的不完全恢复。
当然了，这个只是可以在极端情况下的一个补充。
