PostgreSQL XX002 오류 원인과 해결 방법 완벽 가이드

XX002
2026년 10월 03일 | DBMS Error 가이드

이 글에서 다루는 내용

XX002 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

XX002 index corrupted 는?

PostgreSQL 에러 코드 XX002 (index corrupted)는 데이터베이스 인덱스의 내부 구조가 손상되어 더 이상 정상적으로 읽거나 탐색할 수 없는 상태를 의미합니다. 이 에러는 주로 하드웨어 장애, 비정상적인 서버 종료, 파일 시스템 오류 등 외부적인 원인에 의해 인덱스 페이지가 물리적으로 손상될 때 발생합니다. 인덱스 손상은 SELECT, UPDATE, DELETE 등 인덱스를 사용하는 모든 쿼리에 영향을 줄 수 있으며, 방치할 경우 데이터 무결성에 심각한 위협이 될 수 있습니다.

주요 발생 원인

1. 하드웨어 장애 및 디스크 I/O 오류

가장 빈번한 원인으로, 디스크 배드 섹터(bad sector), SSD 컨트롤러 오류, RAID 컨트롤러 결함 등이 인덱스 파일 페이지를 손상시킵니다. 데이터베이스가 인덱스 블록을 디스크에 쓰는 도중 I/O 오류가 발생하면 해당 페이지는 절반만 기록된 상태(partial write)로 남아 손상이 생깁니다. 이 경우 PostgreSQL의 체크섬(checksum) 기능이 활성화되어 있다면 조기에 감지할 수 있지만, 활성화되지 않은 환경에서는 손상이 한참 후에 발견되기도 합니다.

2. 비정상적인 서버 종료 (Unclean Shutdown)

전원 차단, OOM Killer에 의한 PostgreSQL 프로세스 강제 종료, kill -9 명령 등으로 서버가 비정상 종료될 경우 WAL(Write-Ahead Logging)에 기록되지 않은 인덱스 변경사항이 유실되면서 인덱스가 불일치 상태에 빠질 수 있습니다. PostgreSQL은 재시작 시 WAL 복구를 통해 대부분의 상황을 복원하지만, 파일 시스템이 fsync를 제대로 보장하지 않는 환경(일부 NFS, 일부 가상화 스토리지)에서는 복구 후에도 인덱스 손상이 남을 수 있습니다. 특히 fsync=off 설정으로 운영되던 서버가 비정상 종료되면 이 위험이 매우 높아집니다.

3. PostgreSQL 버그 또는 버전 불일치

드물지만 특정 PostgreSQL 버전의 인덱스 관련 버그(특히 GiST, GIN, BRIN 등 복합 인덱스 타입)가 손상을 유발하는 경우가 있습니다. 또한 pg_upgrade를 통한 메이저 버전 업그레이드 후 인덱스가 새 버전의 포맷과 맞지 않아 손상된 것처럼 동작하는 경우도 보고되어 있습니다. 이런 경우에는 REINDEX를 통해 인덱스를 재생성하면 해결되는 경우가 대부분입니다.

해결 방법

1단계: 손상된 인덱스 식별

먼저 어떤 인덱스가 손상되었는지 확인합니다.

-- pg_catalog를 통해 전체 인덱스 목록 조회
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

-- amcheck 확장을 이용한 인덱스 무결성 검사 (PostgreSQL 10+)
-- 먼저 확장 설치 필요
CREATE EXTENSION IF NOT EXISTS amcheck;

-- B-tree 인덱스 검사 (구조 오류만 확인)
SELECT bt_index_check('your_index_name'::regclass);

-- B-tree 인덱스 검사 (힙 데이터와 교차 검증, 더 철저함)
SELECT bt_index_parent_check('your_index_name'::regclass, true);

2단계: 특정 테이블의 모든 인덱스 검사

-- 특정 테이블의 모든 인덱스를 amcheck로 검사
DO $$
DECLARE
    idx RECORD;
BEGIN
    FOR idx IN
        SELECT indexrelid::regclass AS idx_name
        FROM pg_index
        WHERE indrelid = 'public.your_table'::regclass
    LOOP
        BEGIN
            PERFORM bt_index_check(idx.idx_name);
            RAISE NOTICE 'Index % is OK', idx.idx_name;
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING 'Index % has issues: %', idx.idx_name, SQLERRM;
        END;
    END LOOP;
END $$;

3단계: 손상된 인덱스 재생성

-- 특정 인덱스만 재생성 (테이블 잠금 발생, 서비스 중단 필요)
REINDEX INDEX your_index_name;

-- 특정 테이블의 모든 인덱스 재생성
REINDEX TABLE public.your_table;

-- 데이터베이스 전체 인덱스 재생성 (대규모 DB에서 시간이 매우 오래 걸림)
REINDEX DATABASE your_database_name;

-- PostgreSQL 12+ : CONCURRENTLY 옵션으로 서비스 중단 없이 재생성
REINDEX INDEX CONCURRENTLY your_index_name;

-- PostgreSQL 12+ : 테이블 전체 인덱스를 무중단으로 재생성
REINDEX TABLE CONCURRENTLY public.your_table;

4단계: 시스템 카탈로그 인덱스 손상 시

시스템 카탈로그 인덱스가 손상된 경우에는 싱글유저 모드에서 작업해야 합니다.

-- 싱글유저 모드에서 실행 (PostgreSQL 서비스 중지 후)
-- $ postgres --single -D /var/lib/postgresql/data your_database

-- 싱글유저 모드 내에서
REINDEX SYSTEM your_database_name;

5단계: 인덱스 재생성 전후 검증

-- 재생성 전 쿼리 플랜 확인 (인덱스 사용 여부)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM public.your_table WHERE your_column = 'value';

-- 재생성 후 동일 쿼리로 비교
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM public.your_table WHERE your_column = 'value';

-- 인덱스 통계 재수집
ANALYZE public.your_table;

예방 방법

1. 데이터 체크섬 활성화 및 정기적인 인덱스 검사 자동화

PostgreSQL 클러스터 초기화 시 반드시 체크섬을 활성화하세요. 체크섬이 활성화되면 디스크에서 블록을 읽을 때마다 무결성을 검증하므로 조기에 손상을 감지할 수 있습니다. 기존 클러스터에 체크섬을 활성화하려면 PostgreSQL 12 이상에서 pg_checksums 도구를 사용할 수 있습니다.

-- 현재 체크섬 활성화 여부 확인
SHOW data_checksums;

-- 또는
SELECT pg_catalog.pg_control_checkpoint();

-- amcheck를 이용한 정기 검사 (pg_cron 또는 외부 스케줄러로 실행)
-- 예: 매주 일요일 새벽 2시에 전체 B-tree 인덱스 검사
SELECT
    n.nspname AS schema,
    c.relname AS table,
    i.relname AS index,
    bt_index_check(i.oid) AS check_result
FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_am a ON a.oid = i.relam
WHERE a.amname = 'btree'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

2. WAL 및 fsync 설정 점검, 그리고 정기 백업 전략 수립

fsync=off는 절대 프로덕션 환경에서 사용해서는 안 됩니다. 성능 향상을 위해 fsync를 끄는 경우가 있지만, 이는 비정상 종료 시 데이터 손상을 피할 수 없게 만듭니다. pg_basebackup을 이용한 정기적인 물리 백업과 WAL 아카이빙을 병행하여, 손상 발생 시 최신 백업으로 복원할 수 있는 체계를 반드시 갖추어야 합니다.

-- 현재 fsync 설정 확인
SHOW fsync;

-- WAL 아카이빙 상태 확인
SHOW archive_mode;
SHOW archive_command;

-- 마지막 백업 정보 확인
SELECT * FROM pg_stat_archiver;