-- 創(chuàng)建擴(kuò)展
CREATE EXTENSION pageinspect;

-- 驗(yàn)證是否安裝成功
SELECT * FROM pg_available_extensions WHERE name = 'pageinspect';

-- 獲取表'test'的第0塊數(shù)據(jù)
SELECT * FROM get_raw_page('test', 'main', 0);

-- 檢查表'test'的第0塊內(nèi)容
SELECT * FROM heap_page_items(get_raw_page('test', 0));

-- 查看頁(yè)面頭部信息
SELECT * FROM page_header(get_raw_page('test', 0));

-- 查看索引'test_pkey'的元信息
SELECT * FROM bt_metap('test_pkey');

-- 查看索引'test_pkey'的第1頁(yè)統(tǒng)計(jì)信息
SELECT * FROM bt_page_stats('test_pkey', 1);

-- 查看索引'test_pkey'的第1頁(yè)內(nèi)容
SELECT * FROM bt_page_items('test_pkey', 1);

-- 查看表的FSM頁(yè)面
SELECT * FROM fsm_page_contents(get_raw_page('test', 'fsm', 0));

-- 查看BRIN索引頁(yè)面
SELECT * FROM brin_page_items(get_raw_page('brin_index', 0), 'brin_index'::regclass);

-- 創(chuàng)建測(cè)試表
CREATE TABLE test_fillrate (id serial, data text);
INSERT INTO test_fillrate (data) 
SELECT md5(random()::text) FROM generate_series(1, 1000);

-- 分析頁(yè)面填充情況
SELECT 
    blkno,
    COUNT(*) AS tuples,
    AVG(length(t_data::text)) AS avg_tuple_size,
    COUNT(*) * 100.0 / (
        SELECT setting::float 
        FROM pg_settings 
        WHERE name = 'block_size'
    ) AS fill_percentage
FROM 
    heap_page_items(get_raw_page('test_fillrate', 0))
GROUP BY 
    blkno;

-- 檢查T(mén)OAST表頁(yè)面
SELECT * FROM heap_page_items(
    get_raw_page(
        (SELECT reltoastrelid FROM pg_class WHERE relname = 'large_table'), 
        0
    )
);

-- 檢查B-tree索引的完整性
SELECT 
    level, 
    count(*) as pages, 
    avg(bt_page_stats.blksize) as avg_page_size
FROM 
    generate_series(0, 
        (SELECT level FROM bt_metap('test_pkey'))
    as level,
    lateral (
        SELECT * 
        FROM bt_page_stats('test_pkey', blkno) 
        WHERE btpo_level = level
    ) as bt_page_stats
GROUP BY 
    level
ORDER BY 
    level;

-- 1. 識(shí)別損壞頁(yè)面
SELECT corrupt_page 
FROM verify_heapam('table_name');

-- 2. 檢查損壞頁(yè)面內(nèi)容
SELECT * FROM heap_page_items(get_raw_page('table_name', corrupt_page));

-- 3. 嘗試從其他副本恢復(fù)或使用pg_resetwal

-- 分析索引頁(yè)面填充率
SELECT 
    blkno, 
    live_items, 
    dead_items,
    free_size,
    (page_size - free_size) * 100.0 / page_size AS fill_percentage
FROM 
    bt_page_stats('index_name', blkno) 
ORDER BY 
    blkno;

-- 跟蹤元組在不同事務(wù)中的變化
BEGIN;
INSERT INTO test VALUES (1, 'first');
SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('test', 0));

-- 在另一個(gè)會(huì)話中...
UPDATE test SET data = 'updated' WHERE id = 1;

-- 回到第一個(gè)會(huì)話
SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('test', 0));
COMMIT;

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('table_name');

-- 需要單獨(dú)安裝
pg_repack -d dbname -t table_name