-- 安裝擴展
CREATE EXTENSION pg_surgery;

-- 驗證安裝
SELECT * FROM pg_available_extensions WHERE name = 'pg_surgery';

-- 修復(fù)包含損壞元組的表
SELECT heap_force_kill('my_table'::regclass, ARRAY['(0,1)']::tid[]);

-- 處理事務(wù)ID回卷問題
SELECT heap_force_freeze('problem_table'::regclass, ARRAY['(0,1)']::tid[]);

-- 修復(fù)損壞的B-tree索引
SELECT btree_force_options('my_index'::regclass, ARRAY['fastupdate=off']);

-- 將卡住的事務(wù)標(biāo)記為已提交
SELECT txid_force_status(123456, 'committed');

-- 1. 首先識別損壞的元組
SELECT ctid, * FROM my_table WHERE ...; -- 返回錯誤

-- 2. 強制刪除損壞元組
SELECT heap_force_kill('my_table'::regclass, ARRAY['(0,1)']::tid[]);

-- 3. 重建表
VACUUM FULL my_table;

-- 1. 識別需要凍結(jié)的元組
SELECT ctid, xmin, xmax FROM problem_table 
WHERE age(xmin) > 2000000000;

-- 2. 強制凍結(jié)這些元組
SELECT heap_force_freeze(
  'problem_table'::regclass,
  ARRAY(SELECT ctid::text::tid 
        FROM problem_table 
        WHERE age(xmin) > 2000000000)
);

-- 3. 執(zhí)行常規(guī)VACUUM
VACUUM problem_table;

-- 在單用戶模式下使用：
postgres --single -D /path/to/data/directory dbname

-- 修復(fù)系統(tǒng)目錄不一致
SELECT pg_surgery_function(...);

pg_basebackup -D /backup/location -X stream

CREATE TABLE surgery_audit AS
SELECT now(), current_user, * FROM pg_surgery_function(...);

REVOKE ALL ON FUNCTION heap_force_kill FROM PUBLIC;
GRANT EXECUTE ON FUNCTION heap_force_kill TO dbadmin;

-- 檢查表完整性
ANALYZE repaired_table;

-- 驗證索引
SELECT indexrelid::regclass, indisvalid 
FROM pg_index WHERE indrelid = 'repaired_table'::regclass;