운영 중 발생 가능한 🧨Block Corruption 을 대응(해결)하기 위해 테스트 후 분석하는 글입니다.
OS환경 : Rocky 8.10 (64bit)
DB 환경 : PostgreSQL 17.4
테스트 DB: mydb
데이터 디렉토리: /postgres_data/test
로그 디렉토리: /postgres_log/test
🛠️ 초기 환경 셋업: Checksums 기능 활성화
PostgreSQL은 페이지 단위로 블록의 무결성을 검사하기 위해 page-level checksum 기능이 존재하며
활성화 시 블록 손상 시 PostgreSQL이 감지할 수 있습니다.
✅ 기존 데이터 디렉토리에 Checksums 활성화
# checksums 확인
show data_checksums;
data_checksums
----------------
off
(1 row)
# PostgreSQL 인스턴스 중지
pg_ctl stop
# checksums enable
pg_checksums --enable -D /postgres_data/test
# PostgreSQL 재시작
pg_ctl start
# checksums 확인
show data_checksums;
data_checksums
----------------
on
(1 row)
🧪 Block Corruption 테스트 시작
📦 테이블 Block Corruption CASE
📌 테스트 테이블 생성
CREATE DATABASE mydb;
CREATE TABLE corrupted_table (
id SERIAL PRIMARY KEY,
data TEXT
);
INSERT INTO corrupted_table (data)
SELECT 'Row #' || generate_series(1, 15000);
SELECT COUNT(*) FROM corrupted_table;
count
-------
15000
(1 row)
📂 테이블 데이터 파일 경로 찾기
# 데이터베이스 oid 확인
SELECT oid FROM pg_database WHERE datname = 'mydb';
oid
-------
17015
(1 row)
# 테이블 relfilenode 확인
SELECT relfilenode FROM pg_class WHERE relname = 'corrupted_table';
relfilenode
-------------
17027
(1 row)
-- 편하게 relfilenode 조회
SELECT pg_relation_filepath('corrupted_table');
pg_relation_filepath
----------------------
base/17015/17027
(1 row)
-- 손상시킬 영역 확인
-- 각 row의 block 위치 확인(37 Block)
mydb=# SELECT ctid, id FROM corrupted_table WHERE id BETWEEN 7000 AND 7500 LIMIT 100;
ctid | id
----------+------
(37,155) | 7000
(37,156) | 7001
(37,157) | 7002
(37,158) | 7003
(37,159) | 7004
(37,160) | 7005
(37,161) | 7006
(37,162) | 7007
(37,163) | 7008
(37,164) | 7009
(37,165) | 7010
(37,166) | 7011
(37,167) | 7012
(37,168) | 7013
(37,169) | 7014
※ 예시 경로: /postgres_data/test/base/<database_oid>/<relfilenode>
💥 Block Corruption 시뮬레이션
-- DB종료
pg_ctl stop
-- dd로 Block값 zere로 밀어넣기
dd if=/dev/zero of=$PGDATA/base/17015/17027 bs=1 seek=$((37 * 8192 + 24)) count=512 conv=notrunc
-- DB시작
pg_ctl start
| 옵션 |
설명 |
| dd |
데이터를 블록 단위로 복사하는 명령어 |
| if=/dev/urandom |
입력 파일 (input file)로, 난수 데이터를 생성하는 특수 디바이스 |
| of=$PGDATA/base/16949/16951 |
출력 파일 (output file). PostgreSQL 테이블의 실제 데이터 파일일 가능성이 높음 |
| bs=8192 |
블록 크기 (bytes per block). 8KB는 PostgreSQL의 기본 페이지 크기와 동일 |
| seek=50 |
출력 파일에서 50 블록(= 50 * 8192 bytes = 409600 bytes) 만큼 건너뛰고 데이터 쓰기 시작 |
| count=1 |
1 블록(8KB)만 복사 |
| conv=notrunc |
출력 파일을 자르지 않고 유지. 기존 파일 크기를 줄이지 않음 |
🧨 Block Corruption 확인
-- 손상된 영역 접근하기
SELECT * FROM corrupted_table WHERE ctid >= '(37,0)' AND ctid < '(39,0)';
WARNING: page verification failed, calculated checksum 43097 but expected 48841
ERROR: invalid page in block 37 of relation base/17015/17027
-- 손상되지 않는 영역 접근하기
mydb=# SELECT * FROM corrupted_table WHERE ctid >= '(1,0)' AND ctid < '(10,0)';
id | data
------+-----------
186 | Row #186
187 | Row #187
188 | Row #188
189 | Row #189
190 | Row #190
191 | Row #191
192 | Row #192
193 | Row #193
194 | Row #194
195 | Row #195
196 | Row #196
197 | Row #197
198 | Row #198
199 | Row #199
200 | Row #200
201 | Row #201
202 | Row #202
203 | Row #203
204 | Row #204
205 | Row #205
206 | Row #206
207 | Row #207
208 | Row #208
209 | Row #209
210 | Row #210
211 | Row #211
212 | Row #212
213 | Row #213
손상된 영역은 접근 불가, 손상 되지 않은 영역은 조회 가능
🤔 Block Corruption 분석
\c mydb
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT * FROM heap_page_items(get_raw_page('corrupted_table', 37));
WARNING: page verification failed, calculated checksum 43097 but expected 48841
ERROR: invalid page in block 37 of relation base/17015/17027
SELECT * FROM heap_page_items(get_raw_page('corrupted_table', 36));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
-----+--------+----------+--------+--------+--------+----------+----------+-------------+------------+--------+--------+-------+--------------------------------
1 | 8152 | 1 | 38 | 1042 | 0 | 0 | (36,1) | 2 | 2306 | 24 | | | \x051a000015526f77202336363631
2 | 8112 | 1 | 38 | 1042 | 0 | 0 | (36,2) | 2 | 2306 | 24 | | | \x061a000015526f77202336363632
3 | 8072 | 1 | 38 | 1042 | 0 | 0 | (36,3) | 2 | 2306 | 24 | | | \x071a000015526f77202336363633
4 | 8032 | 1 | 38 | 1042 | 0 | 0 | (36,4) | 2 | 2306 | 24 | | | \x081a000015526f77202336363634
5 | 7992 | 1 | 38 | 1042 | 0 | 0 | (36,5) | 2 | 2306 | 24 | | | \x091a000015526f77202336363635
6 | 7952 | 1 | 38 | 1042 | 0 | 0 | (36,6) | 2 | 2306 | 24 | | | \x0a1a000015526f77202336363636
7 | 7912 | 1 | 38 | 1042 | 0 | 0 | (36,7) | 2 | 2306 | 24 | | | \x0b1a000015526f77202336363637
8 | 7872 | 1 | 38 | 1042 | 0 | 0 | (36,8) | 2 | 2306 | 24 | | | \x0c1a000015526f77202336363638
9 | 7832 | 1 | 38 | 1042 | 0 | 0 | (36,9) | 2 | 2306 | 24 | | | \x0d1a000015526f77202336363639
.... 중략
-- pg_amcheck
\c mydb
CREATE EXTENSION amcheck;
$ pg_amcheck -d mydb -t public.corrupted_table --no-strict-names --heapallindexed --verbose
pg_amcheck: including database "mydb"
pg_amcheck: in database "mydb": using amcheck version "1.4" in schema "public"
pg_amcheck: checking heap table "mydb.public.corrupted_table"
WARNING: XX001: page verification failed, calculated checksum 43097 but expected 48841
LOCATION: PageIsVerifiedExtended, bufpage.c:153
heap table "mydb.public.corrupted_table":
ERROR: XX001: invalid page in block 37 of relation base/17015/17027
LOCATION: WaitReadBuffers, bufmgr.c:1542
query was: SELECT v.blkno, v.offnum, v.attnum, v.msg FROM pg_catalog.pg_class c, "public".verify_heapam(
relation := c.oid, on_error_stop := false, check_toast := true, skip := 'none'
) v WHERE c.oid = 17027 AND c.relpersistence != 't'
pg_amcheck: checking btree index "mydb.public.corrupted_table_pkey"
WARNING: XX001: page verification failed, calculated checksum 43097 but expected 48841
LOCATION: PageIsVerifiedExtended, bufpage.c:153
btree index "mydb.public.corrupted_table_pkey":
ERROR: XX001: invalid page in block 37 of relation base/17015/17027
LOCATION: WaitReadBuffers, bufmgr.c:1542
query was: SELECT "public".bt_index_check(index := c.oid, heapallindexed := true )
FROM pg_catalog.pg_class c, pg_catalog.pg_index i WHERE c.oid = 17033 AND c.oid = i.indexrelid AND c.relpersistence != 't' AND i.indisready AND i.indisvalid AND i.indislive
pg_amcheck: checking btree index "mydb.pg_toast.pg_toast_17027_index"
pg_amcheck: checking heap table "mydb.pg_toast.pg_toast_17027"
-- PostgreSQL Server Log
2025-04-22 01:01:18 UTC postgres@[local] /mydb (2025625)WARNING: page verification failed, calculated checksum 43097 but expected 48841
2025-04-22 01:01:18 UTC postgres@[local] /mydb (2025625)ERROR: invalid page in block 37 of relation base/17015/17027
✔️ Checksum mismatch
calculated checksum 43097 but expected 48841
→ 손상된 블록의 헤더는 살아 있지만, 내용이 손상되어 checksum 검증 실패
✔️ Invalid Page
invalid page in block 37 of relation base/17015/17027
→ PostgreSQL이 해당 블록을 읽을 수 없음 또는 구조적으로 무결하지 않음
🚑 테이블 복구 진행
-- 복구 전 최종 확인, 해당 Block은 접근 불가
SELECT ctid, * FROM corrupted_table WHERE ctid >= '(37,0)' AND ctid < '(38,0)';
WARNING: page verification failed, calculated checksum 43097 but expected 48841
ERROR: invalid page in block 37 of relation base/17015/17027
-- 백업 본이 없다는 가정하에 진행
-- 해당 블록은 데이터 유실로 가정하여 복구
CREATE TABLE corrupted_table_recovered AS
SELECT * FROM corrupted_table
WHERE ctid >= '(0,0)' AND ctid < '(37,0)';
INSERT INTO corrupted_table_recovered
SELECT * FROM corrupted_table
WHERE ctid >= '(38,0)';
-- 블록 확인
SELECT pg_relation_size('corrupted_table') / 8192 AS blocks;
blocks
--------
82
(1 row)
SELECT pg_relation_size('corrupted_table_recovered') / 8192 AS blocks;
blocks
--------
81
(1 row)
1개 Block Corrupt로인해 recovery Block 1개 유실
🚑 테이블 복구 진행 2
-- 테이블 조회 시도
SELECT * FROM corrupted_table;
WARNING: page verification failed, calculated checksum 25081 but expected 18907
ERROR: invalid page in block 36 of relation base/17015/17123
SELECT * FROM corrupted_table limit 1;
id | data
----+--------
1 | Row #1
(1 row)
-- 해당 기능 on
show zero_damaged_pages;
zero_damaged_pages
--------------------
off
(1 row)
--Parameter 적용하기
ALTER SYSTEM SET zero_damaged_pages=on;
SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
show zero_damaged_pages;
zero_damaged_pages
--------------------
on
(1 row)
-- Block은 손상 났지만 조회는 가능함
SELECT * FROM corrupted_table;
WARNING: page verification failed, calculated checksum 25081 but expected 18907
WARNING: invalid page in block 36 of relation base/17015/17123; zeroing out page
id | data
-------+------------
1 | Row #1
2 | Row #2
3 | Row #3
4 | Row #4
5 | Row #5
6 | Row #6
7 | Row #7
8 | Row #8
9 | Row #9
10 | Row #10
11 | Row #11
12 | Row #12
13 | Row #13
14 | Row #14
15 | Row #15
-- 복구 진행
CREATE TABLE corrupted_table_recovered AS
SELECT * FROM corrupted_table;
Block Corruption Error 발생 했지만, 해당 테이블 조회는 가능함.
🧪 추가 테스트 VACUUM 진행
-- 테이블 조회
select * from corrupted_table;
WARNING: page verification failed, calculated checksum 30546 but expected 10092
ERROR: invalid page in block 35 of relation base/17015/17123
-- VACUUM 수행, 정상 or 비정상
mydb=# vacuum corrupted_table;
VACUUM
PostgreSQL의 VACUUM은 "page skipping" 이라는 최적화 기법 사용하는데 해당 35번 Block을 건너 뛴 것으로 예상됨
건너 뛰지 않도록 테스트 수행
-- zero_damaged_pages=on
-- VACUUM이 모든 블록을 강제로 방문하게 만들고, 로그로 출력함
VACUUM (DISABLE_PAGE_SKIPPING, VERBOSE) corrupted_table;
INFO: aggressively vacuuming "mydb.public.corrupted_table"
INFO: finished vacuuming "mydb.public.corrupted_table": index scans: 0
pages: 0 removed, 82 remain, 82 scanned (100.00% of total)
tuples: 0 removed, 14630 remain, 0 are dead but not yet removable
removable cutoff: 1073, which was 0 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 176 hits, 0 misses, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
-- zero_damaged_pages=off
VACUUM (DISABLE_PAGE_SKIPPING, VERBOSE) corrupted_table;
INFO: aggressively vacuuming "mydb.public.corrupted_table"
WARNING: page verification failed, calculated checksum 30546 but expected 10092
ERROR: invalid page in block 35 of relation base/17015/17123
CONTEXT: while scanning block 35 of relation "public.corrupted_table"
DISABLE_PAGE_SKIPPING 처리하여 VACUUM이 모든 블록을 강제로 방문하게 만들어서 Error 발생 시킴
결국 Block Corruption 난 TABLE은 VACUUM이 수행이 안될 수도 있다.
🧩 INDEX Block Corruption CASE
🔎 Index 대상 확인
-- 이전 작업 초기화
\c mydb
DROP TABLE IF EXISTSE corrupted_table;
CREATE TABLE corrupted_table (
id SERIAL PRIMARY KEY,
data TEXT
);
INSERT INTO corrupted_table (data)
SELECT 'Row #' || generate_series(1, 15000);
SELECT COUNT(*) FROM corrupted_table;
count
-------
15000
(1 row)
-- 인덱스 relfilenode 확인
SELECT relname, relfilenode FROM pg_class
WHERE relname = 'corrupted_table_pkey';
relname | relfilenode
----------------------+-------------
corrupted_table_pkey | 17119
(1 row)
--블록 갯수 확인
SELECT pg_relation_size('corrupted_table_pkey') / 8192 AS blocks;
blocks
--------
43
(1 row)
💣 인덱스 블록 손상
# DB 중지
pg_ctl -D $PGDATA stop
# 인덱스 파일의 블록 2번 손상 (헤더 포함 전부 덮어쓰기)
dd if=/dev/urandom of=$PGDATA/base/17015/17119 bs=8192 seek=2 count=1 conv=notrunc
# DB시작
pg_ctl -D $PGDATA start
🧨 인덱스 사용 강제 & 에러 유발
\c mydb
-- PRIMARY KEY는 id니까, 이 쿼리에서 인덱스가 쓰일 것
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 1000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.042..0.043 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 1000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.078 ms
Execution Time: 0.057 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 2000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.035..0.036 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 2000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.057 ms
Execution Time: 0.049 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 3000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.068..0.069 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 3000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.056 ms
Execution Time: 0.082 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 4000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.047..0.047 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 4000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.057 ms
Execution Time: 0.060 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 5000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.065..0.068 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 5000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.116 ms
Execution Time: 0.093 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 6000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.044..0.045 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 6000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.055 ms
Execution Time: 0.059 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 7000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.038..0.039 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 7000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.060 ms
Execution Time: 0.053 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 8000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.043..0.044 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 8000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.060 ms
Execution Time: 0.058 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 9000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 9000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.055 ms
Execution Time: 0.051 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 11000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.043..0.044 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 11000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.057 ms
Execution Time: 0.057 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 12000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.038..0.039 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 12000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.067 ms
Execution Time: 0.054 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 13000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.044..0.045 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 13000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.064 ms
Execution Time: 0.058 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 14000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.054..0.055 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 14000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.057 ms
Execution Time: 0.069 ms
(7 rows)
mydb=# EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM corrupted_table WHERE id = 15000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using corrupted_table_pkey on public.corrupted_table (cost=0.29..4.30 rows=1 width=4) (actual time=0.039..0.039 rows=1 loops=1)
Output: id
Index Cond: (corrupted_table.id = 15000)
Heap Fetches: 0
Query Identifier: 3834585159638330129
Planning Time: 0.056 ms
Execution Time: 0.053 ms
(7 rows)
✔ 모든 Index Only Scan을 하였지만, 에러 확인 되지 않음
-- pg_amcheck 조회
$ pg_amcheck -d mydb -t public.corrupted_table --no-strict-names --heapallindexed --verbose
pg_amcheck: including database "mydb"
pg_amcheck: in database "mydb": using amcheck version "1.4" in schema "public"
pg_amcheck: checking heap table "mydb.public.corrupted_table"
pg_amcheck: checking btree index "mydb.public.corrupted_table_pkey"
WARNING: XX001: page verification failed, calculated checksum 13516 but expected 50609
LOCATION: PageIsVerifiedExtended, bufpage.c:153
btree index "mydb.public.corrupted_table_pkey":
ERROR: XX001: invalid page in block 2 of relation base/17015/17119
LOCATION: WaitReadBuffers, bufmgr.c:1542
query was: SELECT "public".bt_index_check(index := c.oid, heapallindexed := true )
FROM pg_catalog.pg_class c, pg_catalog.pg_index i WHERE c.oid = 17119 AND c.oid = i.indexrelid AND c.relpersistence != 't' AND i.indisready AND i.indisvalid AND i.indislive
pg_amcheck: checking btree index "mydb.pg_toast.pg_toast_17113_index"
pg_amcheck: checking heap table "mydb.pg_toast.pg_toast_17113"
-- 확장 설치
CREATE EXTENSION IF NOT EXISTS pageinspect;
-- block 2의 인덱스 내용 분석
SELECT * FROM bt_page_items(get_raw_page('corrupted_table_pkey', 2));
WARNING: page verification failed, calculated checksum 13516 but expected 50609
ERROR: invalid page in block 2 of relation base/17015/17119
Index Only Scan을 하였지만 에러 발생되지 않았고, 해당 블록을 직접 조회 확인하면 에러를 확인 할 수 있다,
아마 Index Only Scan 시에 장애난 블록을 건드리지 못한 모양이다.
🛠️ 인덱스 복구(인덱스 복구전 통계 수집 → 인덱스 복구)
-- 인덱스 통계 확인
mydb=# SELECT *
mydb-# FROM pg_stat_user_indexes
mydb-# WHERE indexrelname = 'corrupted_table_pkey';
relid | indexrelid | schemaname | relname | indexrelname | idx_scan | last_idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+-----------------+----------------------+----------+-------------------------------+--------------+---------------
17113 | 17119 | public | corrupted_table | corrupted_table_pkey | 16 | 2025-04-22 01:32:31.520818+00 | 16 | 0
idx_scan 인덱스가 몇 번 사용됐는지 (스캔 수)
idx_tup_read 인덱스에서 읽힌 튜플 수
idx_tup_fetch 인덱스 통해 heap에서 실제로 읽은 튜플 수
-- 통계 정보 최신화
ANALYZE corrupted_table;
-- block 2의 인덱스 내용 분석
mydb=# SELECT * FROM bt_page_items(get_raw_page('corrupted_table_pkey', 2));
WARNING: page verification failed, calculated checksum 13516 but expected 50609
ERROR: invalid page in block 2 of relation base/17015/17119
-- 인덱스 relfilenode 확인
SELECT relname, relfilenode FROM pg_class
WHERE relname = 'corrupted_table_pkey';
relname | relfilenode
----------------------+-------------
corrupted_table_pkey | 17119
(1 row)
--REINDEX 수행
REINDEX INDEX corrupted_table_pkey;
-- block 2의 인덱스 내용 분석
SELECT * FROM bt_page_items(get_raw_page('corrupted_table_pkey', 2));
itemoffset | ctid | itemlen | nulls | vars | data | dead | htid | tids
------------+---------+---------+-------+------+-------------------------+------+---------+------
1 | (3,1) | 16 | f | f | dd 02 00 00 00 00 00 00 | | |
2 | (1,182) | 16 | f | f | 6f 01 00 00 00 00 00 00 | f | (1,182) |
3 | (1,183) | 16 | f | f | 70 01 00 00 00 00 00 00 | f | (1,183) |
4 | (1,184) | 16 | f | f | 71 01 00 00 00 00 00 00 | f | (1,184) |
5 | (1,185) | 16 | f | f | 72 01 00 00 00 00 00 00 | f | (1,185) |
6 | (2,1) | 16 | f | f | 73 01 00 00 00 00 00 00 | f | (2,1) |
7 | (2,2) | 16 | f | f | 74 01 00 00 00 00 00 00 | f | (2,2) |
8 | (2,3) | 16 | f | f | 75 01 00 00 00 00 00 00 | f | (2,3) |
9 | (2,4) | 16 | f | f | 76 01 00 00 00 00 00 00 | f | (2,4) |
10 | (2,5) | 16 | f | f | 77 01 00 00 00 00 00 00 | f | (2,5) |
-- 인덱스 relfilenode 확인
SELECT relname, relfilenode FROM pg_class
mydb-# WHERE relname = 'corrupted_table_pkey';
relname | relfilenode
----------------------+-------------
corrupted_table_pkey | 17121
(1 row)
통계정보를 재 수집해도 인덱스는 복구되지 않는다, 이미 해당 인덱스파일이 손상된 상태이기에 불가능 하였고
reindex 시에 refilenode가 바뀐 것을 확인 할 수 있다. 즉, 새로운 파일이 만들어 졌음.
🧱 Datafile Header Corruption
PostgreSQL Datafile Header는 데이터 파일내에 블록들을 해석하고 관리하기 위해 필요한 메타정보를 가지고 있습니다. Header 손상 시 PostgreSQL은 파일 자체를 인식하지 못할 수도 있으며 데이터 유실이 발생될 수 있습니다.
📌 Page Header (PageHeaderData)의 구조
| 필드 |
크기 |
설명 |
| pd_lsn |
8 bytes |
WAL 로그 시퀀스 번호 (Last modification LSN) |
| pd_checksum |
2 bytes |
블록의 체크섬 (data_checksum 설정 시 사용) |
| pd_flags |
2 bytes |
플래그 비트 (예: PD_HAS_FREE_LINES 등) |
| pd_lower |
2 bytes |
ItemIdData 영역 끝 위치 |
| pd_upper |
2 bytes |
HeapTupleData 시작 위치 (Free Space 시작점) |
| pd_special |
2 bytes |
Special space 시작 위치 (예: 인덱스에서 사용) |
| pd_pagesize_version |
2 bytes |
페이지 크기 & 포맷 버전 정보 |
| pd_prune_xid |
4 bytes |
VACUUM 시 필요한 XID (가장 오래된 삭제 필요 트랜잭션) |
💥 Header Corruption 시뮬레이션
-- relfilenode 조회
SELECT pg_relation_filepath('corrupted_table');
pg_relation_filepath
----------------------
base/17015/17113
(1 row)
-- db 종료
pg_ctl stop
-- currupt 수행
dd if=/dev/zero of=$PGDATA/base/17015/17113 bs=24 count=1 conv=notrunc
-- db 시작
pg_ctl start
🚨 조회 시 에러
SELECT * FROM corrupted_table LIMIT 1;
ERROR: invalid page in block 0 of relation base/17015/17113
ERROR: invalid page header in block 0 of relation base/17015/17113
⚠️ 복구가 불가능한 상태, 물리/논리 백업으로 만 복구 가능
📌 관리 포인트 정리
- initdb 시 반드시
--data-checksums 옵션을 활성화 하기
pg_checksums 명령어로 정기PM 시에 체크를 수행하기, DB 정상 종료 시 체크 가능- 정기적인
pg_dump & pg_basebackup 백업하기
- Log 모니터링 및 기타 모니터링 솔루션 관제하기
🙌 댓글, 공감, 공유는 큰 힘이 됩니다! 😄
참조 : https://postgresql.kr/docs/13/runtime-config-developer.html