PG 數(shù)據(jù)庫的 表的不完全恢復(fù)的簡易方法
pg 數(shù)據(jù)庫中如果某個(gè)對(duì)象或者表損壞，只能執(zhí)行全庫恢復(fù)，然后導(dǎo)出表，再倒入，這期間這個(gè)表都不能訪問了
今天我們就來模擬一下這種情況下個(gè)一個(gè)簡易的恢復(fù)方案（不完全恢復(fù)，部分順壞的數(shù)據(jù)塊的的數(shù)據(jù)將丟失）
[code]
建表 : 插入數(shù)據(jù)
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]
表的總行數(shù)：
154112
filenode ：
5961160
現(xiàn)在我們來物理上破壞這個(gè)表的數(shù)據(jù)文件。
到表的數(shù)據(jù)目錄里 vi 這個(gè)表，修改里面一部分內(nèi)容讓表物理損毀。
[code]
重啟數(shù)據(jù)庫，清空數(shù)據(jù)庫內(nèi)存
因?yàn)槭俏覀冃陆ǖ谋恚詳?shù)據(jù)在內(nèi)存里是有cache 的，這個(gè)時(shí)候即便是物理損毀了，還是查到一些數(shù)據(jù)出來的。
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]
重新查詢數(shù)據(jù)表看看情況：
[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]
表數(shù)據(jù)已經(jīng)無法查出來了。
看看表的數(shù)據(jù)信息：
[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]
這個(gè)時(shí)候，我們是可以根據(jù)表的relpages 跟 reltuple 來查詢出表的一部分?jǐn)?shù)據(jù)來的。
科普知識(shí)：
PG 數(shù)據(jù)庫的對(duì)行記錄的標(biāo)識(shí)是通過塊+行在塊內(nèi)的索引來查找的對(duì)應(yīng)的一行記錄的。
我們知道了這個(gè)表的總的塊數(shù)，那我們遍歷所有的數(shù)據(jù)塊的里索引記錄應(yīng)該是可以讀出一些數(shù)據(jù)的：
寫個(gè)函數(shù)如下：
[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]
這個(gè)代碼有個(gè)地方是可以優(yōu)化的，每個(gè)行在數(shù)據(jù)庫里的suoyin 是一個(gè)int 類型，這個(gè)數(shù)字是最大值是65535 所以我們?cè)诖a了在塊內(nèi)的循環(huán)我們?cè)O(shè)置了這個(gè)數(shù)字，而實(shí)際上，每數(shù)據(jù)塊里存放多少數(shù)據(jù)是可以通過 sum(rows)/sum(pages) 計(jì)算出一個(gè)平均值的，考慮到不是所有的行的長度都是一樣的，所以我們應(yīng)該取個(gè)比平均值大一些的數(shù)字，盡可能的減少數(shù)據(jù)的丟失
本例中[postgres@test-11-16 ~]$ echo 154112/3649 |bc
42
一個(gè)塊的平均有42行，我們給大一些，給100行 或者60,70行就可以了。
我們就用修改后的代碼來跑一下這個(gè)函數(shù)，看看能提取出多少數(shù)據(jù)。
[code]
函數(shù)已經(jīng)建好：
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)
有些數(shù)據(jù)塊已經(jīng)損壞了，所以告警出來，這些塊里的數(shù)據(jù)是沒有辦法提取出來的了。
我們看看提取出了多少數(shù)據(jù)：
postgres=# select count(*) from salvaged ;
count
-------
(1 row)
[/code]
總共有
77068 記錄被抽取出來。
這個(gè)文章的思想就是，我們根據(jù)數(shù)據(jù)在數(shù)據(jù)庫里的存儲(chǔ)形式，來實(shí)現(xiàn)了部分?jǐn)?shù)據(jù)的不完全恢復(fù)。
當(dāng)然了，這個(gè)只是可以在極端情況下的一個(gè)補(bǔ)充。
