Shulou(Shulou.com)06/01 Report--

If an object or table in the pg database is damaged, you can only perform a full library recovery, then export the table, and then pour it in. During this period, the table cannot be accessed.

Today, let's simulate a simple recovery scheme in this case (incomplete recovery, some of the data in bad data blocks will be lost).

[code]

Building tables: inserting data

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

Then look at the information of the table:

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

[/ code]

Total rows of the table: 154112 filenode: 5961160 now let's physically destroy the data file for this table. Vi the table in the table's data directory, and modify some of its contents to make the table physically damaged. [code] restart the database and empty the database memory because it is a new table, so the data has cache in memory. At this time, even if it is physically damaged, some data is found. 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] re-query the data table to see what happens: [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] Table data can no longer be found. Look at the data information of the table:

[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 | p | r | 27 | 0 | f | f | f | 11764150 | | [/ code] at this time We can query part of the data of the table according to the relpages and reltuple of the table.

Popular science knowledge:

The identification of row records in the PG database looks up the corresponding row records through the block + row index within the block.

We know the total number of blocks in this table, so we should be able to read some data from the index records that traverse all the blocks:

Write a function as follows:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.