설치·접속: PostgreSQL 설치와 접속
부제: 인덱스가 논리적으로 깨진 것 같을 때, REINDEX로 무작정 재구축하기 전에 손상을 확진하는 절차
데이터 체크섬은 물리적 비트 손상만 잡는다. 인덱스-힙 불일치, 누락 다운링크, VM 비트 어긋남 같은 "논리적" 손상은 체크섬을 통과해도 잘못된 SELECT 결과를 낼 수 있다. amcheck로 손상 여부를 판정하고, pg_visibility로 visibility map 일치를 확인하고, pageinspect로 구조를 눈으로 확인하는 확진 절차를 만든다.
문제 상황
인덱스가 있는 테이블. index-only scan 결과가 이상하거나 특정 행이 인덱스에서 안 걸리는 것 같아 손상을 의심하는 상황이다.
CREATE TABLE chk (id int primary key, email text);
INSERT INTO chk SELECT g, 'user'||g||'@ex.com' FROM generate_series(1, 20000) g;
CREATE INDEX chk_email_idx ON chk (email);
VACUUM (ANALYZE) chk;1단계 — amcheck로 인덱스 논리 검증 (운영 중 실행 가능)
CREATE EXTENSION IF NOT EXISTS amcheck;
-- heapallindexed=true: 힙의 모든 튜플이 인덱스에도 있는지까지 교차 검증
SELECT bt_index_check(index => 'chk_pkey'::regclass, heapallindexed => true) AS pkey_check;
SELECT bt_index_check(index => 'chk_email_idx'::regclass, heapallindexed => true) AS email_idx_check; pkey_check
------------
(1 row)
email_idx_check
-----------------
(1 row)bt_index_check는 정상이면 아무것도(NULL) 반환하고 조용히 끝난다. 오류를 던지면 그것은 100% 진짜 손상이다(false positive 없음). heapallindexed => true를 주면 B-tree 내부 정합성뿐 아니라 "힙에 있는 모든 튜플이 인덱스에 존재하는지"까지 대조한다 — 인덱스에서 행이 누락되는 유형의 손상을 여기서 잡는다. 중요한 건 이 함수가 AccessShareLock(SELECT와 같은 수준)만 잡는다는 점이다. 쓰기를 막지 않으므로 운영 트래픽 중에도 돌릴 수 있다.
2단계 — verify_heapam으로 힙까지 검사
-- 손상 행이 있으면 (blkno, offnum, attnum, msg)로 한 줄씩 반환. 0행 = 정상.
SELECT count(*) AS corruption_rows FROM verify_heapam('chk'); corruption_rows
-----------------
0인덱스가 깨끗해도 힙 자체가 손상됐을 수 있다. verify_heapam은 힙 페이지를 스캔해 튜플 헤더의 xmin/xmax 정합성, TOAST 포인터 유효성, 라인 포인터 범위 등을 검사한다. 0행이면 정상이다. 손상이 있으면 아래 5단계에서 보듯 위치와 이유가 한 줄씩 나온다.
3단계 — pg_visibility로 visibility map 일치 확인
CREATE EXTENSION IF NOT EXISTS pg_visibility;
SELECT * FROM pg_visibility_map_summary('chk'); all_visible | all_frozen
-------------+------------
128 | 0-- all-visible이라 표시됐는데 실제로는 안 보이는 튜플의 TID를 반환. 0행 = 일치.
SELECT count(*) AS vm_mismatch FROM pg_check_visible('chk');
SELECT count(*) AS frozen_mismatch FROM pg_check_frozen('chk'); vm_mismatch
-------------
0
frozen_mismatch
-----------------
0VM이 깨지면 index-only scan이 힙을 확인하지 않고 잘못된 결과를 줄 수 있고, VACUUM이 페이지를 잘못 건너뛴다. pg_visibility_map_summary는 VM에서 all-visible로 표시된 페이지가 128개임을 보여준다(VACUUM 후 전 페이지가 all-visible). pg_check_visible는 "VM은 all-visible이라는데 실제로는 모든 튜플이 보이지 않는" 불일치 페이지의 TID를 콕 집어준다. 0행이면 VM과 힙 상태가 일치한다.
4단계 — pageinspect로 구조 확인
CREATE EXTENSION IF NOT EXISTS pageinspect;
-- B-tree 메타페이지: magic/version과 루트 위치·트리 높이
SELECT magic, version, root, level, fastroot, fastlevel
FROM bt_metap('chk_pkey'); magic | version | root | level | fastroot | fastlevel
--------+---------+------+-------+----------+-----------
340322 | 4 | 3 | 1 | 3 | 1-- 힙 페이지 0: 살아있는 튜플은 t_xmax=0, xmin은 commit됨
SELECT lp, t_xmin, t_xmax, t_ctid,
(t_infomask & 256) <> 0 AS xmin_committed
FROM heap_page_items(get_raw_page('chk', 0))
ORDER BY lp LIMIT 5; lp | t_xmin | t_xmax | t_ctid | xmin_committed
----+--------+--------+--------+----------------
1 | 1243 | 0 | (0,1) | t
2 | 1243 | 0 | (0,2) | t
3 | 1243 | 0 | (0,3) | t
4 | 1243 | 0 | (0,4) | t
5 | 1243 | 0 | (0,5) | tbt_metap의 magic=340322는 유효한 B-tree 메타페이지 매직 넘버다(값이 다르면 메타페이지 자체가 깨진 것). version 4, level 1은 정상적인 2레벨 트리 구조를 뜻한다. 힙 페이지 0의 튜플들은 모두 t_xmax=0(삭제 안 됨), t_ctid가 자기 자신을 가리키고((0,1)→lp 1), xmin_committed=t(infomask의 HEAP_XMIN_COMMITTED 비트) — 전부 정상적으로 커밋된 살아있는 튜플이다.
5단계 — 손상이면 무엇이 어떻게 뜨나
위 검사는 전부 "정상"으로 나왔다. 만약 손상이 있었다면 verify_heapam은 아래 컬럼 구조로 손상을 하나씩 지목한다.
SELECT blkno, offnum, attnum, msg FROM verify_heapam('chk') LIMIT 3; blkno | offnum | attnum | msg
-------+--------+--------+-----
(0 rows)정상이라 0행이지만, 손상 시엔 blkno(손상 블록), offnum(라인 포인터), attnum(문제 컬럼), msg(예: xmin 12345 precedes relfrozenxid 20000, data begins at offset ... beyond block size 같은 진단 메시지)가 채워진다. bt_index_check는 반환값 대신 ERROR: heap tuple (X,Y) from table ... lacks matching index tuple within index ... 형태로 예외를 던진다. pg_check_visible은 불일치 튜플의 TID를 행으로 돌려준다. 이 위치 정보를 heap_page_items(get_raw_page('chk', <blkno>))에 넣어 해당 페이지 튜플 헤더를 부검하면 xmin/xmax가 왜 어긋났는지 원인을 좁힌다.
결론 — 언제 이 조합을 쓰나
"REINDEX 한 번 돌리면 되겠지"로 넘어가기 전에, 진짜 손상인지·어디가 손상인지를 확진하는 절차다. amcheck(bt_index_check + verify_heapam)로 손상을 발견하고(SELECT 수준 락이라 운영 중 가능), pg_visibility로 VM 일관성을 확인하고, pageinspect(bt_metap, heap_page_items)로 메타페이지·튜플 헤더를 눈으로 검증한다. amcheck가 오류를 내면 그건 100% 진짜 손상이니, 그때 비로소 REINDEX(인덱스 손상)나 백업 복구·pg_dump 후 재적재(힙 손상)를 결정한다. 검사 없이 무작정 REINDEX하면 원인을 못 없앤 채 증상만 덮을 수 있다.
이렇게도 쓴다
부모 레벨 다운링크까지 더 엄격하게 검사한다. 대신 ShareLock이라 쓰기를 막는다. (조합: bt_index_parent_check)
-- ShareLock: 운영 쓰기 차단 + 스탠바이 불가. 유지보수 창에서.
SELECT bt_index_parent_check(index => 'chk_pkey'::regclass, heapallindexed => true);
DB 전체 인덱스를 한 번에 훑어 손상을 찾는다. (조합: 인덱스 순회 크론)
SELECT c.relname, bt_index_check(c.oid, true)
FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid
WHERE c.relkind = 'i' AND i.indisready AND c.relam = 403; -- btree
VM이 실제로 깨졌을 때 재구축한다. (조합: pg_truncate_visibility_map → VACUUM)
SELECT pg_truncate_visibility_map('chk'); -- superuser 전용
VACUUM chk; -- VM 재생성
B-tree 특정 페이지의 라인 아이템을 열어 트리 구조를 직접 본다. (조합: bt_page_items)
SELECT itemoffset, ctid, itemlen, dead
FROM bt_page_items('chk_pkey', 1) LIMIT 5;
verify_heapam으로 힙과 함께 TOAST까지 검사한다. (조합: check_toast)
SELECT count(*) FROM verify_heapam('chk',
check_toast => true, skip => 'none');
손상 발견 후 해당 블록의 헤더 상태를 부검한다. (조합: page_header)
SELECT lsn, checksum, flags, lower, upper
FROM page_header(get_raw_page('chk', 0));