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' ;

--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+--------

-------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+-

-----------+-------------+----------------+----------------+--------------+--------+------------

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' ;

--------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+--------

-------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+-

-----------+-------------+----------------+----------------+--------------+--------+------------

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 --

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;

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/，如需轉載，請註明出處，否則將追究法律責任。

PG 資料庫的表的不完全恢復的簡易方法

相關文章

PG 資料庫的 表的不完全恢復的簡易方法

相關文章

PG 資料庫的表的不完全恢復的簡易方法