• 반드시 Super User 
• 해당 Table 에는 Primary Key OR NOT NULL 컬럼에 Unique Index 존재 
• PostgreSQL 개발 패키지 설치 (PostgreSQL- Devel)

다운로드

https://pgxn.org/dist/pg_repack/ 에서 다운로드 가능합니다. 

 

설치

$ cd pg_repack
$ make
$ make install
$ psql -c "create extension pg_repack" -d [Database_name]

 

사용법

pg_repack [옵션] ... [DB_Name]

 

옵션

옵션에는 다음 옵션을 지정할 수 있습니다.

 

공통 옵션

옵션명	설명
-a, --all	모든 데이터베이스를 실행
-t, --table=TABLE	특정 테이블만 실행
-I, --parent-table=TABLE	특정 상위 테이블만 실행
-c, --schema=SCHEMA	특정 스키마의 테이블만 실행
-s, --tablespace=TBLSPC	실행된 테이블을 새로운 테이블스페이스로 이동
-S, --moveidx	실행된 인덱스를 새로운 테이블스페이스로 이동
-o, --order-by=COLUMNS	클러스터 키 대신 열별로 정렬
-n, --no-order	클러스터 대신 vacuum 전체 실행
-N, --dry-run	다시 실행된 내용을 프린트 후 종료
-j, --jobs=NUM	각 테이블에 대해 이만큼의 병렬 작업을 실행
-i, --index=INDEX	지정된 인덱스만 실행
-x , - -only-indexes	지정된 테이블의 인덱스만 실행
-T, --wait-timeout=SECS	충돌 시 다른 백엔드 종료
-D, --no-kill-backend	시간 초과 시 다른 백엔드를 종료
-Z, --no-analyze	analyze 하지 않음
-k, --no-superuser-check	슈퍼유저 확인 건너뜀
-C, --exclude-extension	특정 확장자에 속하는 테이블을 다시 압축하지 않음

 

Connection 옵션

옵션명	설명
-d, --dbname=DBNAME	연결할 데이터베이스 이름
-h, --host=HOSTNAME	데이터베이스 서버 호스트 또는 소켓 디렉토리
-p, --port=PORT	데이터베이스 서버 포트
-U, --username=USERNAME	연결할 사용자 이름
-w, --no-password	비밀번호를 묻지 마세요
-W, --password	비밀번호를 물어보세요

 

Generic 옵션

옵션명	설명
-e, --echo	에코 쿼리
-E, --elevel=LEVEL	출력 메세지 레벨 설정
--help	도움말 확인
--version	버전 정보 출력

 

Examples

데이터베이스에서 모든 클러스터링된 테이블에 대해 온라인 CLUSTER를 수행하고, 모든 비 클러스터링된 테이블에 대해 온라인 VACUUM FULL을 수행합니다. 

$ pg_repack test

 

데이터베이스에서 foo 및 bar 테이블에 대해 온라인 VACUUM FULL을 수행합니다.

$ pg_repack --no-order --table foo --table bar test

 

테이블 foo 의 모든 인덱스를 테이블스페이스 tbs로 이동합니다.

$ pg_repack -d test --table foo --only-indexes --tablespace tbs

 

지정된 인덱스를 테이블스페이스 tbs 로 이동합니다.

$ pg_repack -d test --index idx --tablespace tbs

 

제약사항

• temp table 들은 pg_repack 동작을 할 수 없습니다.
• GiST indexes 를 사용하는 테이블은 pg_repack 동작을 할 수 없습니다.
• pg_repack이 작업 중인 동안에는 대상 테이블의 DDL 명령을 VACUUM이나 ANALYZE를 제외하고 수행할 수 없습니다. 전체 테이블 재패킹 중에는 ACCESS SHARE 락이 대상 테이블에 걸려 이 제한을 강제하며 버전 1.1.8 이전을 사용하는 경우 pg_repack이 실행 중인 동안 대상 테이블에 대한 어떤 DDL 명령도 시도해서는 안 됩니다. 대부분의 경우 pg_repack은 올바르게 실패하고 롤백할 것이지만, 이전 버전에서는 몇 가지 경우에 데이터 손상이 발생할 수 있는 경우가 있습니다. 

 

동작 방식

code level (1170 lines):

https://github.com/reorg/pg_repack/blob/ebc8a425fcabc06d62a1c9b51fbc3a5f460b2baa/bin/pg_repack.c#L1170C2-L1170C2

 

전체 테이블 재패킹을 수행하려면, pg_repack은 다음과 같은 단계를 따릅니다.

1. 원본 테이블에 대한 변경 내용을 기록하기 위한 로그 테이블을 생성합니다.
2. 원본 테이블에 트리거를 추가하여 INSERT, UPDATE 및 DELETE 작업을 로그 테이블에 기록합니다.
3. 이전 테이블의 모든 행을 포함하는 새로운 테이블을 생성합니다.
4. 이 새로운 테이블에 인덱스를 구축합니다.
5. 로그 테이블에 발생한 모든 변경 사항을 새 테이블에 적용합니다.
6. 시스템 카탈로그를 사용하여 테이블, 인덱스 및 toast 테이블을 포함하여 테이블을 교체합니다.
7. 원본 테이블을 삭제합니다.

pg_repack은 초기 설정 단계 (위의 단계 1과 2) 및 최종 교체 및 삭제 단계 (위의 단계 6과 7) 중에 ACCESS EXCLUSIVE 락을 짧은 기간 동안만 보유합니다. 나머지 시간 동안 pg_repack은 원본 테이블에 대해 ACCESS SHARE 락만 보유해야 하므로 INSERT, UPDATE 및 DELETE 작업은 계속 진행될 수 있습니다.

 

테스트

Vacuum full

1. Vacuum full
   • 2번 사진에서 vacuum full 수행 후 1번 사진에서 select 문 수행 후 확인 시 select 문이 응답을 기다리고 있습니다. 

2. 확인
   • Lock, dead_tuple

-- Lock 확인 
SELECT
locktype,
virtualtransaction,
transactionid,
nspname,
relname,
mode,
granted,
cast(date_trunc('second',query_start) AS timestamp) AS query_start,
substr(query,1,25) AS query
FROM
pg_locks
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.
relnamespace),
pg_stat_activity
WHERE
NOT pg_locks.pid=pg_backend_pid() AND
pg_locks.pid=pg_stat_activity.pid ;

-- dead_tuple 확인
SELECT
  schemaname || '.' || relname AS table_name,
  n_dead_tup,
  n_live_tup,
  CASE
    WHEN (n_dead_tup + n_live_tup) = 0 THEN -1
    ELSE ROUND((n_dead_tup::decimal / (n_dead_tup + n_live_tup)) * 100, 2)
  END AS dead_tuple_ratio
FROM
  pg_stat_all_tables
WHERE
  schemaname NOT LIKE 'pg_%' AND
  schemaname != 'information_schema'
ORDER BY
  dead_tuple_ratio DESC;

 

pg_repack

1. pg_repack 수행 중 Select 문 수행
   • pg_repack 은 accessExclusive 잡지 않기 때문에 Select 문이 수행됩니다. 
   • pg_repack 수행 후 Select 문 수행

2. pg_repack 수행 중 Update 진행
   • pg_repack 수행이 끝나기전까지 update 문이 끝나야 update문이 수행됩니다. 
     그렇지 않으면 pg에서 cancel 커맨드를 실행합니다. 

 

• pg_repack 수행 후 해당 테이블에 update 문 수행

 

• pg_repack 수행이 끝나기전에 update 문이 끝나지 않는다면 pg에서 해당 update문 트랜잭션을 cancel 하게 됩니다. 

• cancel 수행 후 Analyze를 수행

   

3. pg_repack 수행이 끝나기전에 UPDATE 문이 끝날을 시에는 문제가 없습니다. 

 

Failover

테스트 환경 사전 준비

• pg_repack 구성 완료

 

• autovacuum = off 설정

 

• 데드 튜플 생성

 

1. pg_repack 수행 중 DB 다운

-- Primary (192.168.1.222)
nohup pg_repack -t bmsql_stock -d benchmarksql -U benchmarksql &

• pg_ctl stop 전

• pg_ctl stop 후 Failover 후

• failover 후 Primary (192.168.1.223) 에서 조회 시 통계정보를 갱신이 필요

-- Primary (192.168.1.222)
psql benchmarksql                  #dead_tuple 현황을 보기 위해 analyze 
analyze public.bmsql_stock     ;
analyze public.bmsql_item      ; 
analyze public.bmsql_order_line; 
analyze public.bmsql_new_order ; 
analyze public.bmsql_config    ; 
analyze public.bmsql_warehouse ; 
analyze public.bmsql_history   ; 
analyze public.bmsql_district  ; 
analyze public.bmsql_oorder    ; 
analyze public.bmsql_customer  ;

• 통계 정보 생신 후 tuple 비교

• pg_repack 수행 시 생성됐던 테이블은 그대로 넘어온 걸 확인

• 해당 문제가 발생하고 후 Primary (192.168.1.223) 에서 오류 발생
• pg_repack 도중 failover가 발생하여 pg_repack을 수행하기 위해 생성 되었던 오브젝트들은 그대로 존재하기에 관련된 오브젝트를 다 drop 해줘야합니다.
• 삭제해야하는 목록
  • table
  • trigger
  • type

• drop extension 후 다시 create extension 진행하여 무방합니다. 

• pg_repack 수행할 때 -e 옵션을 주고 수행하게 되면 세부 동작 확인이 가능합니다.