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

千家信息网最后更新 2025年02月05日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 记录被抽取出来。 这个文章的思想就是，我们根据数据在数据库里的存储形式，来实现了部分数据的不完全恢复。 当然了，这个只是可以在极端情况下的一个补充。