Contrib Extension에서 Function을 제공하는 Extension을 정리한 1탄입니다.
- amcheck
- intagg
- pg_prewarm
- pg_surgery
amcheck
관계구조의 논리적 일관성을 확인할 수 있는 function을 제공합니다.
제공되는 함수
- bt_index_check(index regclass, heapallindexed boolean) returns void
btree index의 불변성을 조사하는 함수입니다. index이외 relation을 인자로 넣으면 index가 아니라는 에러메시지를 내보내며, heapallindexed 인자를 true로 두면, 모든 인덱스 내 튜플에 대한 check를 진행합니다. 이 함수는 사용시 AccessShareLock을 획득합니다. 인덱스의 정상여부를 확인할 수 있으며, 정상일경우 아무 메시지가 출력되지 않습니다.
- bt_index_parent_check(index regclass, heapallindexed boolean, rootdescend boolean) returns void
rootdescend라는 인자를 통해, leaf 레벨에서부터 각 튜플의 root page를 검사하는 기능이 추가됩니다. 이 함수 사용시 ShareLock을 획득합니다. 이 함수는 hot standby 모드가 활성화되면 사용 불가능합니다.(bt_index_check는 사용가능 함)
- verify_heapam(relation regclass, on_error_stop boolean, check_toast boolean, skip text, startblock bigint, endblock bigint, blkno OUT bigint, offnum OUT integer, attnum OUT integer, msg OUT text) returns setof record
relation page에 잘못된 형식의 데이터가 포함된 구조적 손상, page는 구조적으로 유효하지만 나머지 데이터베이스 클러스터와 일치하지 않는 논리적 손상이 있는지 테이블을 확인합니다. 아래와 같은 인자를 선택적으로 사용합니다.
- 인자 인자 설명 on_error_stop true인 경우 손상이 발견된 첫 번째 블록의 끝에서 손상 검사가 중지됩니다.(default false) check_toasttrue true인 경우 대상 관계의 TOAST 테이블에 대해 토스트된 값을 확인합니다. 이 옵션은 성능에 영향을 미치며, toast 테이블 또는 해당 인덱스가 손상된 경우 toast 값과 비교하여 확인하면 서버가 충돌할 수 있습니다.(default false) skip 유효한 옵션은 all-visible, all-frozen및 none입니다.(default none) startblock 지정된 경우 손상 검사는 지정된 블록에서 시작하여 이전 블록을 모두 건너뜁니다. startblock대상 테이블의 블록 범위를 벗어나 지정하면 오류가 발생합니다.(default는 첫번째 블록) endblock 지정된 경우 손상 검사는 지정된 블록에서 종료되고 나머지 블록은 모두 건너뜁니다. endblock대상 테이블의 블록 범위를 벗어나 지정하면 오류가 발생합니다.(default는 마지막 블록)
- 손상 감지 시 반환 내용 열 이름 내용 blkno 손상된 페이지가 포함된 블록의 번호입니다. offnum 손상된 튜플의 OffsetNumber입니다. attnum 손상이 전체 튜플이 아니라 열에 특정된 경우 튜플에서 손상된 열의 속성 번호입니다. msg 감지된 문제를 설명하는 메시지입니다.
로그레벨을 아래와 같이 수정하면 함수 결과의 세부 내용을 확인 할 수 있습니다(amcheck의 로그레벨은 DEBUG1, 2가 적당하다고 합니다).
set client_min_messages = DEBUG1;
예시
간단한 테이블을 생성해, btree index를 생성하고 각 함수를 테스트 하는 예시입니다.
DEBUG1로 설정하여 결과가 나타납니다.
CREATE TABLE bttest_a(id int8);
CREATE TABLE bttest_b(id int8);ALTER TABLE bttest_a SET (autovacuum_enabled = false);
ALTER TABLE bttest_b SET (autovacuum_enabled = false);INSERT INTO bttest_a SELECT * FROM generate_series(1, 100000);
INSERT INTO bttest_b SELECT * FROM generate_series(100000, 1, -1);CREATE INDEX bttest_a_idx ON bttest_a USING btree (id) WITH (deduplicate_items = ON);
CREATE INDEX bttest_b_idx ON bttest_b USING btree (id);SELECT bt_index_check(‘bttest_a_idx’); — 결과 값 void 확인
SELECT bt_index_parent_check(‘bttest_a_idx’); — 결과 값 void 확인
set client_min_messages = DEBUG1; — 해당 세션에서만 DEBUG level을 1로 설정
SELECT bt_index_check(‘bttest_a_idx’); — 결과 값 나오기 전 debug 내용 확인
postgres=# SELECT bt_index_check(‘bttest_a_idx’);
DEBUG: verifying consistency of tree structure for index “bttest_a_idx”
DEBUG: verifying level 1 (true root level)
DEBUG: verifying level 0 (leaf level)bt_index_check
(1 row)
SELECT bt_index_parent_check(‘bttest_a_idx’); — 결과 값 나오기 전 debug 내용 확인
postgres=# SELECT bt_index_parent_check(‘bttest_a_idx’);
DEBUG: verifying consistency of tree structure for index “bttest_a_idx” with cross-level checks
DEBUG: verifying level 1 (true root level)
DEBUG: verifying level 0 (leaf level)bt_index_parent_check
(1 row)
다음은 간단한 테이블을 만들고, verify_heapam에 인자를 추가해 return 값을 확인하는 예제입니다. 예제처럼 결과값이 0으로 나와야 정상인 테이블입니다.
CREATE TABLE heaptest (a integer, b text);
SELECT * FROM verify_heapam(relation := ‘heaptest’, skip := ‘rope’); — 오타로 인한 오류 반환
ERROR: invalid skip option
HINT: Valid skip options are “all-visible”, “all-frozen”, and “none”.SELECT * FROM verify_heapam(relation := ‘heaptest’, startblock := 0, endblock := 0);
blkno | offnum | attnum | msg
——-+——–+——–+—–
(0 rows)
SELECT * FROM verify_heapam(relation := ‘heaptest’, startblock := 5, endblock := 8);
blkno | offnum | attnum | msg
——-+——–+——–+—–
(0 rows)
SELECT * FROM verify_heapam(relation := ‘heaptest’, skip := ‘none’);
blkno | offnum | attnum | msg
——-+——–+——–+—–
(0 rows)
SELECT * FROM verify_heapam(relation := ‘heaptest’, skip := ‘all-frozen’);
blkno | offnum | attnum | msg
——-+——–+——–+—–
(0 rows)
SELECT * FROM verify_heapam(relation := ‘heaptest’, skip := ‘all-visible’);
blkno | offnum | attnum | msg
——-+——–+——–+—–
(0 rows)
intagg
intagg 모듈은 정수 집계자와 열거자를 제공합니다. 이 함수의 상위 집합을 제공하는 내장 함수가 있기 때문에 지금은 사용되지 않으나, 내장 함수에 대한 호환성 wrapper로 제공됩니다.
집계자는 int_array_aggregate(integer) 함수를 제공하며 정수를 정확히 포함하는 정수 배열을 생성합니다. 모든 배열 유형에 대해 동일한 작업을 수행하는 wrapper 입니다.
열거자는 int_array_enum(integer[]) returns set of integer 형식으로, 집계기의 역 작업입니다. 정수 배열이 주어지면 행 집합으로 확장합니다. 모든 unnest 배열 유형에 대해 동일한 작업을 수행하는 wrapper 입니다.
예시
집계자, 열거자 함수를 사용하는 예시입니다.
–일대 다 테이블의 예
CREATE TABLE left (id INT PRIMARY KEY, …);
CREATE TABLE right (id INT PRIMARY KEY, …);
CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);–left 테이블의 항목에 대해 right 테이블의 모든 항목이 반환. 일반적인 구성
SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
WHERE one_to_many.left = item;–테이블 항목의 수가 많아 번거로워, 집계기 함수를 사용해 예시와 같이 테이블 생성가능
–왼쪽 항목당 하나의 행과 오른쪽 항목 배열이 있는 테이블
CREATE TABLE summary AS
SELECT left, int_array_aggregate(right) AS right
FROM one_to_many
GROUP BY left;–위 테이블을 보충하기 위해 열거자 함수 사용
SELECT left, int_array_enum(right) FROM summary WHERE left = item;–위 함수의 결과는 아래와 같음
SELECT left, right FROM one_to_many WHERE left = item;–종합하여 one_to_many테이블과 조인 대신 다음과 같이 대체 가능
SELECT right, count(right) FROM
( SELECT left, int_array_enum(right) AS right
FROM summary JOIN (SELECT left FROM left_table WHERE left = item) AS lefts
ON (summary.left = lefts.left)
) AS list
GROUP BY right
ORDER BY count DESC;
pg_prewarm
pg_prewarm은 관계 데이터를 운영 체제 버퍼 캐시나 PostgreSQL 버퍼 캐시에 로드하는 편리한 방법을 제공합니다.
함수를 사용하여 수동으로 수행하거나 shared_preload_libraries에 포함하여 자동으로 수행할 수 있습니다. 후자의 경우 시스템은 autoprewarm.blocks라는 파일에 공유 버퍼의 내용을 주기적으로 기록하는 background worker 를 실행하고, 2개의 background worker를 사용하여 다시 시작한 후 동일한 블록을 다시 로드합니다.
제공되는 함수
- pg_prewarm(regclass, mode text default ‘buffer’, fork text default ‘main’, first_block int8 default null, last_block int8 default null) RETURNS int8
첫번째 인수는 예열(prewarm)이 될 relation입니다.
두번째 인수는 사용할 예열 방법입니다.
- 예열 방법 정리 예열 방법 설명 prefetch 운영체제에 비동기 prefetch 요청을 발행, 그렇지 않을 경우 오류 발생 read 요청된 블록 범위를 읽음. 동기식 buffer 요청된 블록 범위를 데이터베이스 버퍼 캐시로 읽음 OS에서 prefetch 또는 read를 사용할 때, postgresql에서 캐시할 수 있는 것보다 더 많은 블록을 buffer에 사전 예열 하려고하면 더 높은 번호의 블록을 읽을 때 더 낮은 번호의 블록이 제거될 수 있습니다. 또한 미리 예열된 데이터는 캐시 제거로부터 특별한 보호를 받지 못하므로 다른 시스템 활동이 읽은 직후 새로 미리 예열된 블록을 제거할 수 있습니다. 반대로 예열은 캐시에서 다른 데이터도 제거할 수 있습니다. 이러한 이유로 예열은 일반적으로 캐시가 거의 비어 있는 시작 시 가장 유용합니다.
세번째 인수는 예열될 realtion 포크로 일반적으로 main입니다.
네번째 인수는 예열할 첫번째 블록 번호입니다(0의 동의어로 null 허용).
다섯번째 인수는 예열할 마지막 블록 번호입니다(NULL은 relation의 마지막 블록을 통해 예열함을 의미함). 반환되는 값은 미리 예열된 블록 수 입니다.
- autoprewarm_start_worker() RETURNS void
기본 autoprewarm worker를 시작합니다. 이는 일반적으로 자동으로 실행되지만, 서버 시작 시 autoprewarm이 구성되지 않았고, 나중에 worker를 시작하려는 경우에 유용합니다.
- autoprewarm_dump_now() RETURNS int8
autoprewarm.blocks 파일을 즉시 업데이트 합니다. 이는 autoprewarm worker가 실행되고 있지 않지만, 다음에 다시 시작한 후에 실행할 것으로 예상되는 경우에 유용할 수 있습니다. 반환 값은 autoprewarm.blocks에 기록된 레코드 수입니다.
제공되는 매개변수(postgresql.conf)
- pg_prewarm.autoprewarm (boolean)
서버가 autoprewarm worker를 실행해야하는지 여부를 제어합니다. default는 true이고 이 매개변수는 서버 시작시에만 설정할 수 있습니다.
- pg_prewarm.autoprewarm_interval (int)
autoprewarm.blocks에 대한 업데이트 간격 입니다. default는 300이고 단위는 second입니다. 0으로 설정하면 파일이 일정한 간격으로 dump 되지 않고, 서버가 종료될 때만 dump 됩니다.
예시
autoprewarm을 위해 postgresql.conf에 세팅하는 값과, 세팅 후 재 시작시 보이는 프로세스에 대한 예시입니다.
postgresql.conf
shared_preload_libraries = ‘pg_prewarm’pg_prewarm.autoprewarm = true
pg_prewarm.autoprewarm_interval = 300s
세팅 전 프로세스
[opensql@localhost:~]$ ps -ef |grep postgres
opensql 58277 1 0 Sep23 ? 00:00:09 /usr/pgsql-14/bin/postgres -D /opensql/pg/14/data
opensql 58278 58277 0 Sep23 ? 00:00:00 postgres: logger
opensql 58280 58277 0 Sep23 ? 00:00:04 postgres: checkpointer
opensql 58281 58277 0 Sep23 ? 00:00:03 postgres: background writer
opensql 58282 58277 0 Sep23 ? 00:00:08 postgres: walwriter
opensql 58283 58277 0 Sep23 ? 00:00:06 postgres: autovacuum launcher
opensql 58284 58277 0 Sep23 ? 00:00:01 postgres: archiver
opensql 58285 58277 0 Sep23 ? 00:00:21 postgres: stats collector
opensql 58286 58277 0 Sep23 ? 00:00:00 postgres: logical replication launcher
세팅 후 프로세스
밑줄 표시된 autoprewarm Leader 추가 확인
[opensql@localhost:~]$ ps -ef |grep postgres
opensql 58277 1 0 Sep23 ? 00:00:09 /usr/pgsql-14/bin/postgres -D /opensql/pg/14/data
opensql 58278 58277 0 Sep23 ? 00:00:00 postgres: logger
opensql 58280 58277 0 Sep23 ? 00:00:04 postgres: checkpointer
opensql 58281 58277 0 Sep23 ? 00:00:03 postgres: background writer
opensql 58282 58277 0 Sep23 ? 00:00:08 postgres: walwriter
opensql 58283 58277 0 Sep23 ? 00:00:06 postgres: autovacuum launcher
opensql 58284 58277 0 Sep23 ? 00:00:01 postgres: archiver
opensql 58285 58277 0 Sep23 ? 00:00:21 postgres: stats collector
opensql 81904 81895 0 01:08 ? 00:00:00 postgres: autoprewarm leader
opensql 58286 58277 0 Sep23 ? 00:00:00 postgres: logical replication launcher
pg_surgery
pg_surgery 모듈은 이름 그대로 손상된 관계에 수술을 수행하는 다양한 function을 제공합니다.
이러한 기능은 설계상으로는 안전하지 않으며, 사용시 데이터베이스가 손상될 수 있습니다(함수 사용하여 테이블을 자체 인덱스와 일치하지 않게하거나, unique 또는 foreign key 제약조건 위반을 일으키거나 하는 등).
따라서 이슈 발생시 여러가지 조치를 취해보고 이 extension은 최후에 고려해보고 사용해야한다고 공식홈페이지에 소개되어있습니다.
제공되는 함수
- heap_force_kill(regclass, tid[]) returns void
튜플을 검사하지 않고 “used” 라인 포인터를 “dead” 로 표시합니다. 이 함수의 의도는 다른 방법으로 액세스 할 수 없는 튜플을 강제로 제거하는 것입니다.
- heap_force_freeze(regclass, tid[]) returns void
튜플을 검사하지 않고 이를 고정된 것으로 표시합니다. 이 함수의 의도는 손상된 가시성 정보로 인해 액세스 할 수 없거나 테이블이 성공적으로 vacuum되는 것을 방지하는 액세스 가능한 튜플을 만드는 것입니다.
예시
실제 사용하는 table이 아닌 temp table을 활용하여 함수를 테스트하는 예시입니다. 테스트 할 경우에도 별도 개발 환경에서 수행하길 권장합니다.
create temp table htab (a int);
insert into htab values (100), (200), (300), (400), (500);— freeze forcibly
select heap_force_freeze(‘htab’::regclass, ARRAY[‘(0, 4)’]::tid[]);heap_force_freeze
(1 row)
— frozen tuple 확인
select ctid, xmax from htab where xmin = 2;
ctid | xmax
——-+——
(0,4) | 0
(1 row)–frozen tuple을 강제로 dead로 표시
select heap_force_kill(‘htab’::regclass, ARRAY[‘(0, 4)’]::tid[]);heap_force_kill
(1 row)
–dead tuple이라 검색이 안됨
select * from htab where ctid = ‘(0, 4)’;a
(0 rows)
지금까지 ‘PostgreSQL의 Contrib Extensions – Function part1’에 관해 알아보았습니다
‘PostgreSQL의 Contrib Extensions – Function part2’를 바로 이어서 확인해보세요!
