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 記錄被抽取出來。
這個文章的思想就是，我們根據數據在數據庫里的存儲形式，來實現了部分數據的不完全恢復。
當然了，這個只是可以在極端情況下的一個補充。
