Vault 자습서

http://postgresql.kr/blog/vault_tutorial.html

Vault 자습서

이 글은 https://www.vaultproject.io/intro/getting-started/install.html 페이지를 보며 쭉 따라한 흔적입니다.

우리말로 볼트라고 읽습니다.

1. Vault 소개

보안 쪽에서 늘 하는 이야기 가운데 빠지지 않는 항목은
  • 암호화 복호화 하는 열쇠(키)는 반드시 외부에 있어야 한다.
    (이 글에서는 비대칭 공개키/개인키 부분은 생략합니다)
  • 사용자가 입력한 암호를 뭉겔(digest) 때 사용하는 소금(salt)도 외부에 있어야 한다.
입니다.

이 때, 그 '외부'의 의미로 사용할 수 있는 꽤 괜찮은 서버가 https://www.vaultproject.io 홈페이지에서 제공하는 Vault 입니다.  이 소프트웨어의 사용권은 MPL입니다. 소스를 수정하지 않는다면, 편하게 쓸 수 있습니다. 

2. 서버 실행

2.1. Consul

Vault 서버에서 다루는 자료는 특별하게 지정하지 않는다면, 메모리 안에서만 처리됩니다. 즉 서버를 다시 시작하면, 기존 작업했던 모든 자료는 초기화됩니다.
그래서, 실행 중일때, 사용한 자료를 영구 보관하기 위해서 Vault에서는 또 다른 서버인 Consul 서버 쪽으로 보냅니다.

Consul 서버 이야기도 꽤 복잡한데, 이 글 범위에서 벗어나기에, 생략합니다.

그냥, 실행 파일 내려 받고(https://www.consul.io/downloads.html), 압축 풀고,

./consul agent -dev

현재 디렉터리에 있는 consul 프로그램은 에이전트 모드로 실행하는데, 그 환경은 개발환경으로 한다.

2.2. Vault 내려 받고, 압축 풀기

https://www.vaultproject.io/downloads.html

2.3. Vault 서버 환경 설정

환경 설정 파일인 config.hcl 파일을 편집합니다.

vi config.hcl

내용은
storage "consul" {
  address = "127.0.0.1:8500"
  path    = "vault/"
}

listener "tcp" {
 address     = "127.0.0.1:8200"
 tls_disable = 1
}

2.4. 서버 실행

./vault server -config=vault_config.hcl

제 작업 환경에서는 메모리 할당 문제가 있어, 반드시 root 권한으로 실행해야 하더군요.

2.5. 서버 초기화

vault 명령어는 서버로도 쓸 수 있고, 클라이언트로도 쓸 수 있습니다.
서버 초기화 작업은 클라이언트 작업으로
클라이언트 작업을 할 때는 그 서버가 어딘지를 환경 변수로 지정해야 합니다.

export VAULT_ADDR='http://127.0.0.1:8200'

위 서버 실행 방법으로 처음 실행하게 되면, 이 서버는 누구도 사용하지 못하도록 봉인(sealed) 되어있습니다.

이 봉인을 푸는 작업이 바로 서버 초기화입니다.

vault operator init

이렇게 명령을 실행하면,

Unseal Key 1: ljP5XPLv4j6A48SEwyf+xrg5m3F29AXpyXiK8YfYals7
Unseal Key 2: 3mI1uQbkWZ2KbSaTZNKjO6KasATqNG4jnLqCNDN73Led
Unseal Key 3: 3TQlxhVB2erY3EIrHYlq0IwBZazSii1i+oXwfb+nEFe1
Unseal Key 4: vuxxQiW5M0aeB2qgurp1vBScxdewlKjcyyxiFgoiNauv
Unseal Key 5: /Cp1Utrfs/iduukW8YlNj6wASrB8F/9CA7HJaPvWW/uM

Initial Root Token: 7b668cd1-600e-3440-b648-440d95a146d4
.....

이렇게 결과가 나옵니다.

봉인을 풀려면, 여기서 보여준 봉인을 푸는 열쇠 세 개(기본값)로 각각 작업을 해야 합니다. 다음 명령입니다.

vault operator unseal

이 명령이 실행되면, 봉인을 풀 열쇠를 입력하라고 나옵니다.  그럼 위에 있는 것 가운데 하나를 입력합니다.

그럼, 봉인 푸는 과정이 1/3, 2/3 이런식으로 보입니다.

세 번 작업을 반복하면, 드디어 봉인이 풀려 서버 역할을 합니다.

아주 재미난 발상이지요? (마치 옛날 putty 개인키 만들 때, 마우스를 마구 움직였던 것 처럼)

2.5. 서버 로그인

이제 이 서버를 사용하기 위해서는 로그인을 제일 먼저 해야 합니다.
로그인 작업 없이, 그냥 사용하면, '접근 권한 없음' 오류 메시지를 보입니다.
로그인 하는 방법은

vault login 7b668cd1-600e-3440-b648-440d95a146d4

login 뒤에 있는 값은 초기화 할때 보여준 root 토큰입니다.

2.6. 사용하기

아주 많은 이야기가 있는데, 여기서는 간단하게,
암/복호화에서 사용할 키를 구하는 것만 하겠습니다.

vault 서버에 어떤 키를 등록하는 명령은

vault kv put secret/hello foo=world

그 키값을 구하는 명령은

vault kv get -format=json secret/hello

이러면, foo=world 라는 자료가 보일 것입니다.

3. 마무리

아주 간략하게 정리했습니다.
위에서 소개한 문서를 차근하게 읽어가면서 개념과 사용법을 익히면 잘 쓸 수 있겠죠.

각 프로그래밍 언어별 Vault API들은 대부분 있습니다.

아직까지 Vault 서버를 이용해서 PostgreSQL 서버 측에서 Vault와 통신하는 확장 모듈은 없더군요.

이것을 만들면, 열쇠 관리(key managing)를 외부에서 하고, 그 열쇠를 PostgreSQL 서버 내부에서 사용할 수 있을 것 같습니다.

PostgreSQL 파티션 테이블 1부

http://postgresql.kr/blog/postgresql_partition_table.html

PostgreSQL 파티션 테이블

파티션 테이블 만들기

PostgreSQL 10.x 버전에서 파티션 테이블 구문을 지원하면서 좀 더 쉽게 파티션 테이블을 사용할 수 있지만, 아직까지는 이 버전보다 하위 버전을 사용하는 경우가 많아서 정리합니다. 

PostgreSQL 파티션 테이블을 이해하기 위해서는 먼저 PostgreSQL 특유 기능인 테이블 상속에 대해서 어느 정도 알고 있어야 합니다. 

테이블의 상속 개념은 객체 지향 프로그래밍 언어에서 소개하고 있는 그것과 많이 비슷합니다. 한 테이블을 만들 때, 이미 있는 어떤 테이블의 칼럼 정보를 상속 받아서 만들 수 있습니다. 

상위 테이블을 만듭니다. 
CREATE TABLE wwwlog (
        seq serial not null, 
        ctime timestamp not null default current_timestamp, 
        node bigint not null, 
        data jsonb not null);
일단 not null과 default를 제외한 어떤 제약 조건도 추가하지 않았습니다. 다음 실재 자료가 담길 테이블은 ctime 칼럼 기준 to_char(ctime, 'YYYYMMDD') 이름의  테이블을 만듭니다.  이 때, 각각의 하위 테이블에는 ctime 칼럼에 저장될 값 기준으로 해당 날의 자료만 입력 받도록 check 제약 조건을 지정합니다.  물론 wwwlog 테이블에서 상속 받아서 만들기 때문에, 칼럼 정의는 하지 않습니다.  또한 기본키 정의도 함께 합니다.
CREATE TABLE wwwlog_20180111 (                                             
  PRIMARY KEY (seq),                                                       
  CHECK (ctime >= '2018-01-11 00:00:00' AND ctime < '2018-01-12 00:00:00') 
) INHERITS (wwwlog);
CREATE TABLE wwwlog_20180110 (                                             
  PRIMARY KEY (seq),                                                       
  CHECK (ctime >= '2018-01-10 00:00:00' AND ctime < '2018-01-11 00:00:00') 
) INHERITS (wwwlog);
CREATE TABLE wwwlog_20180109 (                                             
  PRIMARY KEY (seq),                                                       
  CHECK (ctime >= '2018-01-09 00:00:00' AND ctime < '2018-01-10 00:00:00') 
) INHERITS (wwwlog);
CREATE TABLE wwwlog_20180108 (                                             
  PRIMARY KEY (seq),                                                       
  CHECK (ctime >= '2018-01-08 00:00:00' AND ctime < '2018-01-09 00:00:00') 
) INHERITS (wwwlog);
CREATE TABLE wwwlog_20180107 (                                             
  PRIMARY KEY (seq),                                                       
  CHECK (ctime >= '2018-01-07 00:00:00' AND ctime < '2018-01-08 00:00:00') 
) INHERITS (wwwlog);
CREATE TABLE wwwlog_20180106 (                                             
  PRIMARY KEY (seq),                                                       
  CHECK (ctime >= '2018-01-06 00:00:00' AND ctime < '2018-01-07 00:00:00') 
) INHERITS (wwwlog);
CREATE TABLE wwwlog_20180105 (                                             
  PRIMARY KEY (seq),                                                       
  CHECK (ctime >= '2018-01-05 00:00:00' AND ctime < '2018-01-06 00:00:00') 
) INHERITS (wwwlog);
CREATE TABLE wwwlog_20180104 (                                             
  PRIMARY KEY (seq),                                                       
  CHECK (ctime >= '2018-01-04 00:00:00' AND ctime < '2018-01-05 00:00:00') 
) INHERITS (wwwlog);
이렇게 만들어진 테이블들을 psql에서 보면 다음과 같습니다.
mydb=> \d+ wwwlog
                                "ioseph.wwwlog" 테이블           
 필드명 |            종류             | NULL허용 |               초기값                
--------+-----------------------------+----------+-------------------------------------
 seq    | integer                     | not null | nextval('wwwlog_seq_seq'::regclass) 
 ctime  | timestamp without time zone | not null | CURRENT_TIMESTAMP                   
 node   | bigint                      | not null |                                     
 data   | jsonb                       | not null |
하위 테이블: wwwlog_20180104,
             wwwlog_20180105,
             wwwlog_20180106,
             wwwlog_20180107,
             wwwlog_20180108,
             wwwlog_20180109,
             wwwlog_20180110,
             wwwlog_20180111

mydb=> \d+ wwwlog_20180104
                            "ioseph.wwwlog_20180104" 테이블           
 필드명 |            종류             | NULL허용 |               초기값                
--------+-----------------------------+----------+-------------------------------------
 seq    | integer                     | not null | nextval('wwwlog_seq_seq'::regclass) 
 ctime  | timestamp without time zone | not null | CURRENT_TIMESTAMP                   
 node   | bigint                      | not null |                                     
 data   | jsonb                       | not null |인덱스들:
    "wwwlog_20180104_pkey" PRIMARY KEY, btree (seq)
체크 제약 조건:
    "wwwlog_20180104_ctime_check" CHECK (ctime >= '2018-01-04 00:00:00'::timestamp without time zone 
         AND ctime < '2018-01-05 00:00:00'::timestamp without time zone)
상속: wwwlog

wwwlog 테이블에서는 하위 테이블이 있다는 내용이 있고, 각 하위 테이블에는 각각 기본키와 체크 제약 조건과 wwwlog 에서 상속 받은 테이블임을 보여주고 있습니다.

하위 테이블이 많은 경우 하위 테이블을 만드는 SQL 명령어 자체가 많아집니다. 
윗 경우는 generate_series() 함수를 다음과 같이 CREATE TABLE 구문 자체를 만드는 쿼리로 만들었습니다. 
SELECT 'CREATE TABLE wwwlog_' ||
 to_char(current_date - generate_series, 'YYYYMMDD') || ' (' || E'\n' || 
'  PRIMARY KEY (seq),' || E'\n' || 
'  CHECK (ctime >= ''' || (current_date - generate_series)::timestamp || 
''' AND ctime < ''' || (current_date - (generate_series - 1))::timestamp || ''')' || E'\n' || 
') INHERITS (wwwlog);' FROM generate_series (-1, 6);
 
이 SQL 명령어를 실행하고 그 결과를 다시 실행해서 테이블을 만들었습니다.
이렇게 만들어진 테이블들은 타 RDBMS와 달리 자료가 입력될 수 있는 조건만 맞다면 어느 테이블에나 다 입력됩니다.
mydb=> INSERT INTO wwwlog (node, data) 
VALUES (1, '{"code": 200, "url": "/index.html"}');
INSERT 0 1
mydb=> INSERT INTO wwwlog_20180110 (node, data)
VALUES (1, '{"code": 200, "url": "/index.html"}');
INSERT 0 1
mydb=> INSERT INTO wwwlog_20180111 (node, data)
VALUES (1, '{"code": 200, "url": "/index.html"}');
오류:  새 자료가 "wwwlog_20180111" 릴레이션의 "wwwlog_20180111_ctime_check" 체크 제약 조건을
위반했습니다
상세정보:  실패한 자료: (3, 2018-01-10 13:03:13.699829, 1, {"url": "/index.html", "code": 200})
mydb=> INSERT INTO wwwlog_20180111 (node, data, ctime)
VALUES (1, '{"code": 200, "url": "/index.html"}', current_timestamp + interval '1 day');
INSERT 0 1
위와 같이 wwwlog 테이블에도, wwwlog_20180110 (오늘이 2018-01-10) 테이블에도 자료가 입력되지만, 체크 제약 조건 때문에 오늘 자료를 wwwlog_20180111 테이블에는 입력될 수 없습니다. 그래서, 목요일 자료는 ctime 값까지 지정해서 임의로 넣었습니다.

이렇게 입력된 자료를 살펴보면, 
mydb=> SELECT tableoid::regclass,seq,ctime FROM wwwlog;
     tableoid    | seq |           ctime            
-----------------+-----+----------------------------
 wwwlog          |   1 | 2018-01-10 13:02:46.429527
 wwwlog_20180110 |   2 | 2018-01-10 13:03:01.773082
 wwwlog_20180111 |   4 | 2018-01-11 13:04:36.741096
(3개 행)

mydb=> SELECT tableoid::regclass,seq,ctime FROM ONLY wwwlog;
 tableoid | seq |           ctime            
----------+-----+----------------------------
 wwwlog   |   1 | 2018-01-10 13:02:46.429527
(1개 행)
여기서 tableoid 라는 숨은 칼럼값도 함께 살펴보면, 그 자료가 입력된 실재 테이블이 무엇인지 알 수 있습니다. 또한 상위 테이블만 조회한다면, ONLY 예약어를 사용할 수 있습니다.

LIKE INCLUDING ALL

일반적인 상속 구문에는 다음과 같은 제약들이 있습니다. 
  • 칼럼 속성(자료형, not null, default)들을 제외한 모든 정보가 상속되지 않는다.
    - 즉 모델링을 하면서 이 테이블을 사용하는 쿼리들을 고려해서 상위 테이블에 인덱스를 만들었다고 해도 그 실 자료가  하위 테이블에 있다면, 그 하위 테이블에 인덱스가 있어야하며, 그 인덱스는 CREATE TABLE ... inherits () 명령이 실행 될 때 자동으로 만들지 않습니다. 하위 테이블 각각 인덱스를 만들어 주어야 합니다. 이렇게 각 하위 테이블 별로 각각 작업해야할 것들이, 인덱스 뿐만 아니라, 기본키, 참조키, 트리거 등입니다.  
  • serial 자료형은 그 사용하는 시퀀스 정보를 그대로 상속된다.
    - 하위 테이블별 채번 작업이 분리되어야 할 경우라면, 하위 테이블을 만들 때, 해당 칼럼의 default 정의를 재정의해야 합니다.
  • 상위 테이블 기준 전역 유니크 제약조건(기본키)을 사용할 수 없다. 
이런 특성 때문에, 상속용 테이블을 만들 때, 각 하위 테이블에서 사용할 인덱스들이 미리 정의되어 있다면, 그 인덱스들을 상위 테이블에 만들고, LIKE INCLUDING 옵션을 이용해서 만드는 방법도 있습니다.

윗 예제라면, wwwlog.ctime 칼럼에 대해서는 brin 인덱스를, wwwlog.data 칼럼에 대해서는 gin 인덱스를 만듭니다.
CREATE INDEX wwwlog_ctime_i ON wwwlog USING brin (ctime);
CREATE INDEX wwwlog_data_i ON wwwlog USING gin (data);
다음 테스트로 wwwlog_20180111 테이블을 지우고, 다시 만들어 보았습니다.
CREATE TABLE wwwlog_20180111 (
    LIKE wwwlog INCLUDING ALL, 
    PRIMARY KEY (seq), 
    CHECK (ctime >= '2018-01-11 00:00:00'
         AND ctime < '2018-01-12 00:00:00')
) INHERITS (wwwlog);

mydb=> \d wwwlog_20180111
                       "ioseph.wwwlog_20180111" 테이블
 필드명 |            종류             | NULL허용 |               초기값                            
--------+-----------------------------+----------+-------------------------------------
 seq    | integer                     | not null | nextval('wwwlog_seq_seq'::regclass)
 ctime  | timestamp without time zone | not null | CURRENT_TIMESTAMP
 node   | bigint                      | not null |  
 data   | jsonb                       | not null |  
인덱스들:
    "wwwlog_20180111_pkey" PRIMARY KEY, btree (seq)
    "wwwlog_20180111_ctime_idx" brin (ctime)
    "wwwlog_20180111_data_idx" gin (data)
체크 제약 조건:
    "wwwlog_20180111_ctime_check" CHECK (ctime >= '2018-01-11 00:00:00'::timestamp without time zone 
         AND ctime < '2018-01-12 00:00:00'::timestamp without time zone)
상속: wwwlog

INSERT 트리거

아직 파티션 테이블 만들기가 끝나지 않았습니다. 
자료 입력 처리 시간을 최적화 해야하는 상황이라면,  각 하위 테이블에 자료를 각각 넣는 것이 제일 좋습니다. 이렇게 하려면, 응용 프로그램 차원에서 어느 테이블에 자료를 넣어야 할지를 결정해야한다. 
이런 요구 조건이 아니라, 조금은 늦어도 괜찮으니, 기존 응용 프로그램에서 입력 SQL 수정 없이 일반 테이블이 파티션 테이블로 바뀌는 경우는 앞에서 설명한대로 INSERT 작업에 대한 입력 분기 처리를 하지 않으면, wwwlog 테이블에만 자료가 쌓이게 됩니다. 

wwwlog 테이블에 자료를 입력하려고 한다면, 트리거가 그 자료의 실재로 저장되어야 할 하위 테이블을 결정해서 그 하위 테이블에 자료를 입력하고, wwwlog 테이블에는 자료를 저장하지 않는 트리거가 필요합니다. 

PostgreSQL에서 트리거를 사용하려면, 트리거 함수부터 만들어야 합니다. 
CREATE OR REPLACE FUNCTION wwwlog_insert()
  RETURNS trigger
  LANGUAGE plpgsql
AS $body$
BEGIN
  if ((new.ctime >= '2018-01-11 00:00:00'::timestamp without time zone) 
    AND (new.ctime < '2018-01-12 00:00:00'::timestamp without time zone))
  then insert into wwwlog_20180111 values (new.*);
  elsif ((new.ctime >= '2018-01-10 00:00:00'::timestamp without time zone) 
    AND (new.ctime < '2018-01-11 00:00:00'::timestamp without time zone))
  then insert into wwwlog_20180110 values (new.*);
  elsif ((new.ctime >= '2018-01-09 00:00:00'::timestamp without time zone) 
    AND (new.ctime < '2018-01-10 00:00:00'::timestamp without time zone))
  then insert into wwwlog_20180109 values (new.*);
  elsif ((new.ctime >= '2018-01-08 00:00:00'::timestamp without time zone) 
    AND (new.ctime < '2018-01-09 00:00:00'::timestamp without time zone))
  then insert into wwwlog_20180108 values (new.*);
  elsif ((new.ctime >= '2018-01-07 00:00:00'::timestamp without time zone) 
    AND (new.ctime < '2018-01-08 00:00:00'::timestamp without time zone))
  then insert into wwwlog_20180107 values (new.*);
  elsif ((new.ctime >= '2018-01-06 00:00:00'::timestamp without time zone) 
    AND (new.ctime < '2018-01-07 00:00:00'::timestamp without time zone))
  then insert into wwwlog_20180106 values (new.*);
  elsif ((new.ctime >= '2018-01-05 00:00:00'::timestamp without time zone) 
    AND (new.ctime < '2018-01-06 00:00:00'::timestamp without time zone))
  then insert into wwwlog_20180105 values (new.*);
  elsif ((new.ctime >= '2018-01-04 00:00:00'::timestamp without time zone) 
    AND (new.ctime < '2018-01-05 00:00:00'::timestamp without time zone))
  then insert into wwwlog_20180104 values (new.*);
  end if;

  RETURN null;
END;
$body$;

CREATE TRIGGER tr_wwwlog_insert
  BEFORE INSERT ON wwwlog 
  FOR EACH ROW EXECUTE PROCEDURE wwwlog_insert();
여기서는 wwwlog_insert()라는 INSERT 이벤트에 대한 트리거 함수를 만들고, wwwlog 테이블(상위 테이블)에 그 트리거 함수를 이용하는 트리거를 만듭니다.
중요한 사항은 그 함수의 반환값은 null 이어야 wwwlog 테이블에 자료가 입력되지 않습니다. 또한 이 트리거는 자료 입력 작업 전에 실행되어야 하기 때문에, BEFORE 트리거여야 합니다. 

여기까지 진행되면 파티션 테이블 만들기는 마무리 됩니다.
이 작업에는 다음의 문제점들이 있습니다. 
  • 매일 내일 자료가 담길 하위 테이블을 만들어 주어야 하고, 
  • 그 하위 테이블에 정말 자료가 담길 수 있도록 wwwlog_insert() 함수를 매일 수정 해야 하며, 
  • 더 이상 보관이 필요없는 하위 테이블에 대해서는 wwwlog 테이블의 종속 관계를 끊고, (ALTER TABLE 오래된하위테이블이름 NO INHERIT wwwlog) 그 테이블을 따로 보관하던가 삭제 해야함
결국 이런 작업은 매일 오류 없이 진행되어야 하며, 따로 관리 되어야합니다. 아울러 더 이상 자료 수정이 없는 하위 테이블들에 대해서는 autovacuum 프로세스에서 처리하는 트랜잭션 ID 겹침 방지 작업 (vacuum freeze 작업) 시 몰리는 디스크 I/O를 줄이기 위해 미리 해당 테이블에 대한 freeze 작업을 해 두는 것도 좋습니다. 

문제는 wwwlog_insert() 함수를 매번 수정해 주어야 한다는 것은 꽤 성가신 일입니다. 위 예제와 같이 매일 테이블들이 만들어지고, 매일 삭제 되는 경우라면, 이 insert 트리거 함수는 동적 쿼리를 사용하는 것이 오히려 낫습니다. 

아주 간단합니다. 
CREATE OR REPLACE FUNCTION wwwlog_insert_dynamic()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  execute format('insert into wwwlog_%s select $1.*', 
    to_char(new.ctime, 'YYYYMMDD')) using new;
  RETURN null;
END;
$function$

이 함수를 쓴다면, 트리거 정의도 재정의 해야겠지요.

테이블 관리 예약 작업

이제 남은 것은 앞에서 언급한 매일 작업해야 하는 예약 작업을 등록하는 일입니다. 
일단 하위 테이블을 만들고 오래된 하위 테이블을 wwwlog 테이블과의 상속 관계를 끊는 함수 하나를 만듭니다. 
CREATE OR REPLACE FUNCTION wwwlog_child_manager()
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
  nextday date;
  nextday2 date;
  purgeday date;
BEGIN
  -- 내일 테이블을 미리 만듦
  nextday := current_date + 1;
  nextday2 := current_date + 2;
  -- 7일 이상 오래된 테이블에 정리용 날짜
  purgeday := current_date - 7;

  execute format('CREATE TABLE IF NOT EXISTS wwwlog_%s' ||
    '( LIKE wwwlog INCLUDING ALL, PRIMARY KEY (seq), ' ||
    'CHECK (ctime >= ''%s'' and ctime < ''%s'')) INHERITS (wwwlog)',
    to_char(nextday,'YYYYMMDD'), nextday::timestamp, nextday2::timestamp);

  execute format('ALTER TABLE wwwlog_%s NO INHERIT wwwlog',
    to_char(purgeday, 'YYYYMMDD'));

  return 'wwwlog_' || to_char(purgeday, 'YYYYMMDD');

EXCEPTION
  WHEN undefined_table THEN
    raise notice '%, %', SQLSTATE, SQLERRM;
    return '';
END;
$function$;
이 함수를 pgagent 나, crontab 에 등록해서 사용하면 되겠죠. 일반적으로 이 작업은 쉡 스크립트에서 psql 명령어로 호출하고, 그것과 함께 vacuum 작업들도 함께 합니다.  그렇게 작성된 쉘 스크립트를 crontab 에 등록합니다. 왜냐하면, vacuum 작업이 plpgsql 함수 안에서는 실행될 수 없기 때문입니다.  
윗 관리 함수는 반환값이 있으면 그 테이블에 대해서 후속 작업을 하는 형태의 쉘 스크립트를 만들것을 염두해 두고 만들었습니다.
쉘 스크팁트 내용은 다음과 같습니다. 
$ cat wwwlog_manager.sh 
#!/bin/sh

vacuum_table=`psql mydb -t -c "select wwwlog_child_manager()"`
if [ $vacuum_table != "" ]; then
	vacuumdb -t $vacuum_table -F -z mydb
fi
이 예약 작업은 서버 상황에 맞게 적당한 시간에 실행 되도록 하면 될 것입니다. 물론 서버의 용량 관리 차원에서 상속 관계를 끊은 하위 테이블에 대한 삭제 작업이나, 다른 호스트로의 백업 관련 작업은 이 글 범위를 벗어나 생략합니다. 

드디어 다 만들었습니다!

파티션 테이블 부분 선택 조회

파티션 테이블을 사용하는 이유는
  • 자료 조회할 때 검색 범위를 하위 테이블로 한정할 수 있다. - 디스크 읽기 비용을 줄인다.
  • 자료량 관리 시 필요 없는 자료를 한꺼번에 지울 수 있다.
입니다. 즉 자료량이 많은데 대부분의 쿼리가 하위 테이블로 한정 지어 쿼리 실행 비용을 최적화 하는 것이 그 첫번째 목적입니다. 이것을 영어 표현으로 파티션 프루닝 partition pruning이라고 합니다. 

윗 예제의 경우라면, 어제 저장된 로그 가운데, code 값이 404인 로그만 찾고 싶다면, 일반적으로 그 쿼리는 
SELECT * FROM wwwlog
WHERE ctime >= (CURRENT_DATE - 1) 
  AND ctime < CURRENT_DATE 
  AND data @> '{"code": 404}';
형태가 될 것입니다. @> 연산자는 GIN 인덱스를 사용하기 위한 jsonb 자료형에 대한 검색 연산자입니다. 자세한 이야기는 지난 jsonb 관련 글을 참조하시고.

문제는 ctime에 대한 조건절입니다. 
이 쿼리의 실행 계획을 보면, (쿼리 앞에 explain 넣어서 실행하면 실행 계획이 보여요)
mydb=> explain 
select * from wwwlog 
where ctime >= (current_date - 1) 
  and ctime < current_date 
  and data @> '{"code": 404}';
                                     QUERY PLAN                                                       
-----------------------------------------------------------------------------------------
 Append  (cost=0.00..1130.20 rows=9 width=67)
   ->  Seq Scan on wwwlog  (cost=0.00..1.02 rows=1 width=69)
         Filter: ((data @> '{"code": 404}'::jsonb) 
              AND (ctime < CURRENT_DATE) AND (ctime >= (CURRENT_DATE - 1)))
   ->  Bitmap Heap Scan on wwwlog_20180111  (cost=104.94..138.78 rows=1 width=69)
         Recheck Cond: ((ctime >= (CURRENT_DATE - 1)) 
                    AND (ctime < CURRENT_DATE) AND (data @> '{"code": 404}'::jsonb))
         ->  BitmapAnd  (cost=104.94..104.94 rows=9 width=0)
               ->  Bitmap Index Scan on wwwlog_20180111_ctime_i  
                   (cost=0.00..12.04 rows=8719 width=0)
                     Index Cond: ((ctime >= (CURRENT_DATE - 1)) 
                              AND (ctime < CURRENT_DATE))
               ->  Bitmap Index Scan on wwwlog_20180111_data_i  
                   (cost=0.00..92.65 rows=87 width=0)
                     Index Cond: (data @> '{"code": 404}'::jsonb)
   ->  Bitmap Heap Scan on wwwlog_20180110  (cost=124.94..162.39 rows=1 width=69)
         Recheck Cond: ((ctime >= (CURRENT_DATE - 1)) 
                    AND (ctime < CURRENT_DATE) AND (data @> '{"code": 404}'::jsonb))
         ->  BitmapAnd  (cost=124.94..124.94 rows=10 width=0)
               ->  Bitmap Index Scan on wwwlog_20180110_ctime_i  
                   (cost=0.00..12.04 rows=9600 width=0)
                     Index Cond: ((ctime >= (CURRENT_DATE - 1)) 
                              AND (ctime < CURRENT_DATE))
               ->  Bitmap Index Scan on wwwlog_20180110_data_i  
                   (cost=0.00..112.65 rows=86 width=0)
                     Index Cond: (data @> '{"code": 404}'::jsonb)
   ...... (20180109부터 쭉 반복)
   ->  Bitmap Heap Scan on wwwlog_20180104  (cost=124.94..162.39 rows=1 width=69)
         Recheck Cond: ((ctime >= (CURRENT_DATE - 1)) 
                    AND (ctime < CURRENT_DATE) AND (data @> '{"code": 404}'::jsonb))
         ->  BitmapAnd  (cost=124.94..124.94 rows=10 width=0)
               ->  Bitmap Index Scan on wwwlog_20180104_ctime_i  
                   (cost=0.00..12.04 rows=9600 width=0)
                     Index Cond: ((ctime >= (CURRENT_DATE - 1)) 
                              AND (ctime < CURRENT_DATE))
               ->  Bitmap Index Scan on wwwlog_20180104_data_i  
                   (cost=0.00..112.65 rows=86 width=0)
                     Index Cond: (data @> '{"code": 404}'::jsonb)
이렇게 조회할 테이블은 어제(2018-01-09)만 대상으로 했기 때문에, wwwlog_20180109 테이블만 조회하면 되는데, 실행 계획에서는 전체 하위 테이블을 다 조회하겠다고 합니다. 이런 상황을 DBA 용어로 파티션 테이블 프루닝 오동작이라고 합니다.

PostgreSQL에서는 파티션 테이블 프루닝 조건은 상수값으로 와야합니다. 이 프루닝 계획이 쿼리 실행 단계가 아닌 계획 단계에서 결정하는데, 그 결정 당시 CURRENT_DATE 같은 예약어로는 판단할 수 없기 때문입니다.  윗 쿼리를 조금 수정하면 파티션 테이블 프루닝이 잘 될 것 입니다. 실행 계획은 직접 확인하세요.  
SELECT * FROM wwwlog
WHERE ctime >= '2018-01-10'::date - 1 
  AND ctime < '2018-01-10'::date 
  AND data @> '{"code": 404}';
즉, '2018-01-10' 이라는 문자열은 쿼리를 실행하기 전에 미리 구해 놓아야하는 한계가 있습니다. 
이 부분이 응용 프로그램을 개발하면서 가장 흔히 범하는 실수입니다.  꼭 기억해 두어야 할 부분입니다.

상위 테이블의 전역 인덱스 이용 못하는 문제

파티션 테이블을 이용하면서 조회성 쿼리에서 종종 문제가 생기는 것이 다른 RDBMS와 달리 전역 인덱스를 사용할 수 없어 하위 테이블의 개별 인덱스를 모두 조사해야 하는 문제가 발생합니다. 윗 쿼리에서 보듯이 ctime 조건을 빼고, data 칼럼에 대한 전역 인덱스를 사용할 수 있다면, 해당 부분만 가져오면 되는데 PostgreSQL에서는 파티션 프루닝 작업을 하지 않는다고 판단하면, 무조건 상속된 모든 하위 테이블의 인덱스를 모두 조사합니다. 즉, seq = 1 과 같은 기본키를 조회해도 마찬가지입니다. 이 문제는 앞에서 설명한 상위 테이블 기준으로 유니크한 자료를 데이터베이스 차원에서 관리할 수 없다는 것을 의미합니다. 즉 얼마든지 의도하지 않게 같은 값의 기본키 자료가 여러 하위 테이블에 저장 될 수 있음을 의미합니다. 이 예외처리는 INSERT 트리거에서 조금은 예방할 수는 있겠지만, 근본 방지 대책이 되지 못할 뿐만 아니라, INSERT 작업 속도를 저하시키는 원인이 되기 때문에 권장하지 않습니다.  이 문제는 윗 예제처럼 채번을 상위 테이블에서 하고, 그것 기준으로 기본키를 사용하는 방식 선에서 타협점을 찾아야합니다. 

파티션 테이블 자료 변경

파티션 테이블을 사용하면서 또 하나 신경 써야 할 부분은 UPDATE/DELETE 구문에 대한 처리 정책입니다. 

UPDATE와 DELETE 작업도 앞에서 설명한 SELECT 작업의 테이블 프루닝 정책을 그대로 따르기 때문에, 파티션 키(윗 예제라면 ctime 칼럼값)를 선택할 수 있는 WHERE 절의 검색 조건이 없다면, 전체 하위 테이블을 대상으로 쿼리가 실행됩니다.  하위 테이블이 많고, 자료량이 방대하다면, 기본키에 대한 아주 단순한 UPDATE 작업일지라도 그 비용은 단일 테이블일 때와는 확연히 차이가 납니다. 

가장 좋은 방법은 그 해당 자료를 업데이트 하기 전에 그 자료의 파티션 키 값을 미리 알고 있고, 그 값을 UPDATE나 DELETE의 WHERE 절 검색 조건으로 포함하는 방법입니다.  당연히 이때도 그 파티션 키 값은 상수로 지정되어야 정확히 프루닝 할 것입니다. 

파티션 키 값 자체를 바꾸는 경우

윗 예제 기준으로 ctime 값 자체가 바뀌어야 하는 상황은 꽤 어려운 문제입니다. 왜냐하면 물리적으로 A테이블에서 B테이블로 자료를 옮겨야하는 부분인데, 이 부분은 응용프로그램의 해당 자료 보관 정책과 관계되기 때문에, 옮겨갈 테이블이 없을 수도 있겠죠.

그래서, 이 문제는 일반적으로 내버려둡니다. ctime이 체크 제약 조건 위반이 되어 쿼리 실패나도록 합니다. 그리고, 이런 업무가 발생하면 수작업으로 진행합니다. 그렇지 않고, 데이터베이스 관리자가 없이 응용프로그램 개발자만으로 운영한다면, wwwlog 테이블 하위 테이블에 체크 제약 조건 위반을 피해 갈 수 있는 트리거를 각각(!) 만들어야 합니다. 

그 트리거 함수는 다음과 같습니다. 
CREATE OR REPLACE FUNCTION wwwlog_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF NEW.ctime <> OLD.ctime 
    AND (NEW.ctime::date - OLD.ctime::date) <> 0 THEN
    EXECUTE format('DELETE FROM %s WHERE seq = $1.seq', TG_TABLE_NAME) using old;
    INSERT INTO wwwlog values (NEW.*);
  END IF;
  RETURN NULL;
END;
$function$;
체크 제약 조건 검사는 자료가 저장될 실재 테이블에서 발생하기 때문에, 이 트리거를 wwwlog 테이블에 걸어두어도 작동하지 않습니다.  이 트리거 함수를 모든 트리거에 같이 이용해서, 다음과 같이 트리거를 만듭니다. 
CREATE TRIGGER tr_wwwlog_update 
BEFORE UPDATE ON wwwlog_20180111 
FOR EACH ROW EXECUTE PROCEDURE wwwlog_update();
CREATE TRIGGER tr_wwwlog_update 
BEFORE UPDATE ON wwwlog_20180110 
FOR EACH ROW EXECUTE PROCEDURE wwwlog_update();
......
CREATE TRIGGER tr_wwwlog_update 
BEFORE UPDATE ON wwwlog_20180105 
FOR EACH ROW EXECUTE PROCEDURE wwwlog_update();
위의 하위 파티션 테이블을 만드는 함수를 이용한다면, 그 함수에도 트리거를 자동으로 만들어 주는 부분이 추가 되어야겠지요.

한편,
이 업데이트 트리거에서는 앞에서 이야기한 기본키 변경에 따른 자료 중복 문제를 고려조차 하지 않았습니다. 단지 방지책으로 new.seq 값과, old.seq 값이 다르면 무조건 오류 메시지를 보여주는 예외 처리 정도는 할 수 있겠죠.

기존 테이블을 파티션 테이블로 만들기

데이터베이스 서버를 운영하다 보면, 한 테이블에 자료량이 많아지고, 그에 따른 쿼리 실행 시간이 길어지는 문제가 발생해서, 그 테이블을 여러 개의 하위 테이블로 나눠 관리하는 작업을 해야하는 경우가 생깁니다. 

물론 가장 손쉬운 방법은 응용 프로그램 접속을 막고, 각 하위 테이블 자료를 파티션 단위로 뽑아서 (COPY (SELECT...) TO) 각 하위 테이블에 한꺼번에 넣고, (COPY 해당하위테이블 FROM
) 상위 테이블에 트리거를, 하위 테이블에 인덱스를 만들고, 응용 프로그램을 다시 사용하는 방법이 제일 안정적이고, 빠른 방법이겠지만, 실 운영 환경에서 이런 작업을 할 기회가 없다면, 기존 서비스를 그대로 제공하면서 이 작업을 진행해야합니다. 

이 때, 위에서 언급한 것들을 모두 고려해서 파티션 테이블 전환 전략을 짜야합니다. 일반 적인 순서는 다음과 같습니다. 
  1. 파티션 키를 정한다.
  2. 응용 프로그램에서 해당 테이블을 사용하는 모든 쿼리를 검토 해서 파티션 테이블로 바뀌었을 때, 쿼리가 수정되어야 할 부분을 수정한다.  관심 가져야 할 부분은 파티션 프루닝 부분입니다. UPDATE/DELETE 도 함께 고려해야합니다.
  3. 하위 테이블을 만들고, 그것에 속한 각종 인덱스, 트리거, 제약 조건을 모두 준비해서, 그 테이블들을 ALTER TABLE ... INHERIT 상위테이블 명령어로 상속 관계를 추가한다.
  4. INSERT 트리거 작업을 한다.
  5. 기존 부모 테이블에 있는 자료를 각 하위 테이블로 옮긴다.
문제는 마지막 작업입니다. 이 작업은 실 운영 환경 내에서 대량 배치작업으로 진행하게 되면 대량의 DML 작업으로 서비스 품질을 많이 떨어뜨립니다. 
최대한 영향을 적게 주는 방법은 상위테이블의 자료 하나만 DELETE 하고 그 지운 자료를 하위 테이블로 옮기는 방식을 택합니다. 
다행스럽게도 이 작업은 WITH 구문과 DELETE ... RETURNING 구문으로 하나의 쿼리로 하나의 트랜잭션으로 처리할 수 있습니다. 

윗 예제를 예로 한다면, 
WITH t AS (
  DELETE FROM ONLY wwwlog 
  WHERE ctime >= '2018-01-10'::date 
    AND ctime < '2018-01-11'::date 
    AND seq = 1 RETURNING *
) 
INSERT INTO wwwlog_20180110 
SELECT * FROM t;
이 쿼리에서 기본키(seq) 값 기준으로 DELETE & INSERT 하는 쿼리를 SELECT 쿼리로 만들고, 그 만든 결과를 서버 부하를 최소화하는 방식으로 실행합니다. 주의할 사항은 만일을 대비해서 파티션 키는 꼭 사용하도록 쿼리를 만든다는 것입니다.

물론 대량의 DML 작업을 온라인 상에서 진행하는 작업임으로 약간의 부분 작업을 먼저 진행해 보고, 작업 예상 시간과 작업 중 서버 영향도를 꼭 파악하고 전체 작업을 진행하는 것이 바람직 할 것입니다. 

오프라인 방식과 온라인 방식을 섞어 지금 사용하고 있는 하위 테이블만 온라인 방식으로 자료를 옮기고 사용 빈도가 낮은 옛날 자료는 COPY 구문으로 대량 이동을 하는 방식도 고려해볼만 합니다. 

이 자료 이전 전략에는 정답은 없습니다. 업무 환경에 맞춰 가장 효율적인 방식을 직접 구현해야 할 것입니다. 

마치며

이상으로 PostgreSQL 9.6.x 이하 버전에서의 파티션 테이블에 대해서 살펴보았습니다.이제껏 경험 했던 파티션 테이블에 대한 것들을 최대한 모두 담아내려고 했는데, 얼마나 내용이 빠졌는지는 모르겠네요. 
이 글에서 언급하지 않는 또 다른 많은 이야기 거리가 있을 수도 있습니다. 그 부분은 각자 알아서 풀어가시고, 기회 닿으면 공유도 해 주시면 보다 많은 이들에게 도움이 될 것으로 기대합니다. 
2부는 PostgreSQL 10.x 이상 버전에서의 파티션 테이블 이야기로 꾸밀 예정입니다. 그 글은  꽤 짧을 것 같네요.

Docker와 PostgreSQL

http://postgresql.kr/blog/docker_postgresql.html

Docker와 PostgreSQL

이 글은 CentOS 7.x 배포판에서의 Docker를 이용한 PostgreSQL 사용법에 대한 소개입니다.

docker 설치 및 확인

설치

yum install docker-engine

확인

ps ax | grep dockerd

PostgreSQL 이미지 받기

이 글에서는 도커 공식 저장소에 있는 PostgreSQL 이미지를 사용한다. 설명서 링크

docker pull postgres:9.6.6

뒤 버전을 빼면 마지막 배포판 선택, 버전을 지정하면, 해당 버전

컨테이너 만들기

로컬 호스트의 디스크를 컨테이너 안에서 사용하기 위해서, 미리 준비하고, (아래 예제에서는 /home/postgres/pgdata 이다) 그것을 데이터 디렉터리로 사용한다. 또한 한국어 처리를 위해서, 초기 환경 설정을 위해 run 명령어가 조금 복잡하다.

하나 기억해 두어야 할 부분은 아래와 같이 포트 포워딩 설정을 한다면 이 run 작업 내용안에 방화벽 설정이 포함된다.  그렇기 때문에, firewalld 서비스가 활성화 되어 있어야 한다.

docker run \
-e PGDATA=/data/pgdata \
--volume /home/postgres/pgdata:/data \
-e POSTGRES_INITDB_ARGS="--data-checksums -E utf8 --no-locale" \
--name postgres96 \
--publish 5432:5432 \
postgres:9.6.6

컨테이너를 만들 때 데몬 모드로 만들지 않았다. 진행 과정을 보고 어디에 문제가 있는지 살펴보고 적당히 대응해야한다. start / stop 명령으로 서버를 언제든지 실행, 중지 할 수 있음으로 run 작업을 때는 데몬 모드를 사용하지 않는 것이 나은 것 같다.

PostgreSQL 초기 설정

문제는 docker 쓰는 것과 관계 없이 DB 서버를 운영 환경에서 쓰기 위해서 해야하는 작업들은 똑 같다는 것이다. 오히려 OS 의존성을 끊어버리는 바람에 postgresql.conf 쪽에 손을 봐야할 부분이 오히려 더 많다.

손 봐야 postgresql.conf 파일의 부분은 다음과 같다.

  • shared_buffers
  • wal_level
  • archive_mode
  • archive_command
  • effective_cache_size
  • min|max_wal_size
  • logging_collector
  • log_line_prefix
  • autovacuum_max_workers
  • timezone
  • log_timezone
  • 그리고, pg_hba.conf 파일 (복제 환경도 함께 고려해야한다)

서버 실행 & 중지

docker stop postgres96
docker start postgres96

영구보관 디스크와 도커 철학

문제는 위와 같은 로컬 호스트 디스크 영역을 도커 내에서 쓰는 경우 도커 도입의 장점들이 많이 희석된다.

데이터베이스의 자료가 저장되는 위치는 결국 로컬 호스트와도 분리되는 것이 고가용성 측면에서 운영 비용을 최소화할 것이다.

그렇다면, fail over 상황에서의 대기 서버를 운영 서버로 전환하는 작업은 온프레미스 환경이거나, 가상 호스트 환경 보다 더 복잡해 진다.

생각보다 복.잡.하.다.

LISTEN & NOTIFY 명령으로 구현하는 비동기식 작업

http://postgresql.kr/blog/pg_listen_notify.html

PostgreSQL LISTEN & NOTIFY SQL 명령어

데이터베이스의 비동기식 처리

이 글은 지극히 응용 프로그램 개발에 관계된 이야기입니다.

PostgreSQL을 이용한 이벤트 기반 프로그램을 어떻게 할 것인가에 대한 힌트입니다.

데이터베이스에 새 자료가 등록된 것을 확인하고 싶다면, 전통적으로 주기적으로 해당 테이블의 마지막 자료를 확인하고, 지금까지의 마지막 자료와 방금 조회한 마지막 자료가 다르다면, 새 자료가 등록되었다고 판단합니다.

또한 이 보다 좀 더 세련된 방법으로 해당 테이블에 트리거를 등록해서 새 자료가 입력되면, 알림 테이블에 그 내용을 기록하고, 그 알림 테이블을 주기적으로 살펴 보아 윗 방식과 같이 처리합니다.

하지만 이런 방법은 데이터베이스 자원을 필요 이상으로 사용하는 것이기도 하고, 실 자료 테이블에 대한 과도한 접근까지 일어나기에 해당 테이블에 대한 특정 이벤트를 검사하는 주기를 짧게 잡기 힘듭니다. 그 만큼 실시간성이 떨어지겠죠.

그래서, 이런 작업의 처리는 응용 프로그램의 도움을 받습니다. 물론 서버가 이런 비동기식 작업들에 대한 다양한 기능을 제공한다면, 보다 쉽게 구현할 수 있습니다. 그 가운데 대표적인 SQL 명령어가 listen과 notify 입니다.


PostgreSQL의 비동기 작업

PostgreSQL에서는 비동기 작업을 위해 다양한 API를 제공하고 있습니다. 대부분 libpq라는 C로 만들어진 데이터베이스 조작용 기본 클라이언트 라이브러리에서 제공합니다. libpq 라이브러리를 기반으로 하는 다른 언어의 PostgreSQL 조작 모듈들도 제 각각의 비동기식 작업에 대한 처리 방법을 제공합니다. 예: PostgreSQL 고가용성 글에서 소개한 Python 코드


LISTEN & NOTIFY SQL 명령어

이 명령어는 한 쪽에서는 어떤 채널에 어떤 내용을 공지하고(NOTIFY), 다른 여러 쪽에서는 그 채널이라는 것을 듣고(LISTEN) 있다가 필요한 작업을 할 수 있습니다.

사용법은 아주 간단합니다.

http://postgresql.kr/docs/current/sql-listen.html

http://postgresql.kr/docs/current/sql-notify.html

페이지가 SQL 설명서입니다.

일반적으로 이 명령어는 클라이언트-서버 환경에서 어떤 사용자가 어떤 자료 변경 작업 시 다른 사용자가 그 자료 변경 자료에 대한 동시성 제어를 위해서 설계되었지만, 요즘처럼 웹 기반 폴링처리 비용을 줄이기 위해서 많이(?) 사용합니다.


구현 예제

  • 모니터링 프로그램에서 자료 수집 처리기에서 해당 자료를 데이터베이스에 입력할 때 NOTIFY - 대쉬보드에서 그 입력된 자료만 데이터베이스에서 가져와서 처리 할 때LISTEN
  • 게시판에서 사용자가 새 글을 등록할 때 NOTIFY - SNS 발행 대행 데몬이 그것을 LISTEN하고 있다가 필요한 발행 처리
  • 작업량이 많은 배치성 통계 작업 시작-종료 시 NOTIFY - 통계 작업 이후 타 시스템으로 작업 된 내용만 전달하는 데몬이 LISTEN 하고 있다가 필요한 처리
  • 자신의 질문에 댓글이 달리면 자신이 사용하고 있는 메신져로 알려주는 기능

이 글에서는 맨 마지막 예제를 구현하는 방법을 설명하겠습니다.


NOTIFY

응용 프로그램에서 NOTIFY는 LISTEN 보다는 구현하는 부분에서는 간단합니다. 그냥

NOTIFY 채널이름, '구체적인 내용'

이런 형태의 쿼리만 서버로 보내면 됩니다.

문제는 채널 이름의 이름 규칙과, '구체적인 내용'의 내용 규칙 설계를 LISTEN을 사용하는 응용 프로그램과 잘 맞춰야 하는 것이 어렵습니다.

여기서는 단순하게,

NOTIFY newanswer, '해당 게시물 URL'

형태로 합니다. 댓글이 달리면, 댓글 처리 하는 끝 부분에, 위의 쿼리를 추가로 실행합니다.

NOTIFY 작업은 여기까지입니다. 


LISTEN

LISTEN 작업은 데이터베이스 쪽으로는 그냥,

LISTEN 채널이름

형태의 쿼리를 서버로 보냅니다. 물론 여러 개의 채널이 있다면 데이터베이스 접속 시 각 LISTEN 명령을 모두 실행 하는 것이 일반적입니다.  물론 특정 작업으로 진행할 때 - 예를 들어 공연장 좌석 예약 시작 같은 것) LISTEN 작업을 하고 있다가 예약 도중 다른 사람이 먼저 해당 좌석 예약을 완료 하면 사용자에게 해당 좌석은 먼저 예약 되어버렸다고 알려주는 식의 기능을 구현하고자 할 때는 작업 중간에 LISTEN 작업을 실행하기도 합니다. 

LISTEN newanswer

윗 NOTIFY라면, 이런 식으로 실행합니다.

다음 이 알림을 받기 위한 감시 작업 (polling 작업)을 응용프로그램에서 해야합니다.  대부분의 PostgreSQL 클라이언트 라이브러리들은 이 기능을 위한 api를 제공하고 있습니다.

이 글에서는 python psycopg 모듈을 예로 설명하겠습니다.  사용설명서는

http://initd.org/psycopg/docs/advanced.html#async-notify

핵심 코드는

conn.poll()
while conn.notifies:
    notify = conn.notifies.pop(0)
    print "Got NOTIFY:", notify.pid, notify.channel, notify.payload

부분입니다. notify.payload 에는 앞 NOTIFY 명령에서 지정한 '해당 게시물 URL'이 담겨져 있습니다.  윗 예제라면, 이 값은 단순한 url 문자열입니다. 이 값을 다시 url query string 분석작업을 하고, 댓글의 상위 글을 찾고, 그 상위 글의 글쓴이에게 알림 메시지를 보내는 것은 전적으로 응용프로그램 몫입니다.  (python 경우라면, 이 palyload 값을 json.dumps() 함수의 반환값인 문자열로 지정하고, json.loads() 로 가져와서 python 내 딕셔너리 자료형으로 처리합니다.)

이 작업은 connection 기준으로 notify가 더 이상 없으면 해당 순환문을 벗어납니다. 즉, 계속 알림이 있는지 확인하려면, 윗 코드가 작업 스케줄러를 이용하든, 상위 while 문을 이용하든 어떻게든 계속 반복 되어야합니다.

이 부분은 이벤트 기반 프로그래밍에서 사용하는 방식 대로 한 클래스의 사용자 정의 핸들러 함수로 등록하는 것이 일반적입니다.


사용할 때 기억해야 할 것들

  • LISTEN & NOTIFY 작업은 connection.poll() 같은 각 언어 별 클라이언트 라이브러리에서 제공하는 API를 사용하는 것이 작업 비용을 효율화 할 수 있습니다.
    단순하게, 'select 1' 쿼리를 계속 서버로 보내면서, 서버 notice 메시지를 확인하는 수준 낮은 코딩은 하지 마세요.
  • LISTEN 등록된 세션이 하나도 없는 경우의 NOTIFY는 버려집니다.
    일반 메시지큐처럼 다른 세션이 가져가지 않은 오래된 NOTIFY까지 모두 보관하고 있지 않습니다.
  • LISTEN 작업은 세션 단위로 이루워집니다.
    다른 세션이 해당 알림을 큐에서 꺼냈다 하더라도  자기 세션에는 그대로 남아 있습니다. (정확하게 말하면 세션 프로세스 ID 단위입니다. 그래서 새 새션을 맺었다면, LISTEN 명령으로 채널을 지정하지 않으면 알림을 받을 수 없습니다.)
  • NOTIFY는 해당 채널을 LISTEN 하는 모든 세션을 대상으로 진행됩니다.
    즉, 특정 세션만을 대상으로 NOTIFY를 할 수 없습니다. (꼼수가 있다면 채널 이름 자체를 세션 단위로 하면 되기도 하겠죠.)


사용자 정의 집계 함수 만들기

http://postgresql.kr/blog/pg_aggregate.html

사용자 정의 집계 함수 만들기

1. 왜 집계 함수까지 만들지?

sum(), avg() 처럼 여러 자료를 집계 해서 하나의 값으로 표현하는 것을 집계 함수라고 합니다. 요즘 같이 데이터 사이언스 이야기가 자주 등장하는 때에 집계 함수는 아주 중요한 키워드 가운데 하나입니다. 대부분 통계 함수들이죠. 평균, 분산, 표준편차, 왜도, 첨도, ... 이런 여러 통계학에서 사용되는 계산들이 대부분 RDBMS에서는 집계 함수라고 합니다.

PostgreSQL은 거의 대부분의 집계 함수를 이미 내장하고 있습니다. 공식 설명서 9.20에서 이들을 자세히 설명하고 있습니다.

일반적으로 사용자 정의 집계 함수는 그 집계 대상 자료가 사용자 정의 자료형인 경우에 그 자료를 집계 해야 할 때 그 집계 함수 또한 사용자가 만들어서 씁니다.

가장 대표적인 것이, postgis 확장 모듈에 있는 각종 집계 함수들이겠지요. postgis 모듈에는 지리 정보라는 자료가 있고, 그 지리 정보에 대한 집계가 필요합니다. 예를 들어 전국 고속도로를 기준으로 하는 국토 총 면적 이런 것들이겠죠. 

이런 작업을 하려고 할 때, 사용자 정의 집계 함수를 만듭니다.

2. 집계 함수를 만들 때 꼭 필요한 것

집계 함수의 기본 작동 원리는 그 함수가 반환되는 자료형과 입력값을 계산하는 함수, 이 두 가지는 꼭 필요합니다.

예를 들어서, sum(int) 함수라면, int형 값들을 더해서 중간 집계하는 함수와, 최종 합계를 반환할 때의 자료형이 필요합니다.

여기서 중간 집계 값을 상태 값이라고 하고, 이 중간 집계 작업을 하는 함수를 전달 함수라고 합니다. 즉, 상태 값 자료형과 전달 함수가 꼭 필요합니다. 이것이 집계 함수를 만들 때 꼭 필요한 것들입니다.

3. 집계 함수 종류

집계 함수의 종류에 대한 자세한 설명은 공식 설명서 36.10에서 자세히 소개하고 있습니다. 여기서는 간단하게 요약만 하겠습니다.

  • 그냥 쭉 계산하는 일반 집계 함수: sum
  • 이동 집계 모드를 이용하는 창(window) 함수: rank
  • 입력 자료형에 구애받지 않는 범용 집계 함수: min, max
  • 집계 함수의 입력 인자가 자료의 값이 아니라, 상수인 정렬된 집합 집계 함수: percentile_disc
  • 병렬 쿼리 지원 집계 함수

이렇게 나열하면, 그냥 막연하게 알고 있던 집계 함수의 종류와 달리 꽤 복잡하다는 것을 알 수 있을 것입니다.

위의 각종 집계 함수를 사용자가 직접 구현할 수 있도록 하기 위해서 PostgreSQL의 CREATE AGGREGATE SQL 구문은 생각보다 복잡합니다. 자세한 내용은 링크를 참조하세요.

4. 상권 구하기

자, 이제 구체적인 예제 하나로 사용자 정의 집계 함수를 만들어 봅니다. 물론 이 상권을 구하는 집계 함수는 postgis 모듈에 이미 있습니다. 실무 안에서는 각 상점들의 지리 정보를 그냥 postgis 모듈에서 제공하고 있는 집계 함수를 사용하면 간단하게 구할 수 있습니다. (postgis 모듈 사용자 설명서의 집계 함수 부분 참조)

여기서는 각 상점을 그냥 PostgreSQL 기본 자료형인 점(point)으로 저장하고, 그 점들을 모두 포함 하는 사각형(box)를 상권으로 처리하도록 하겠습니다.

4.1 표본 자료

$ create table store as 
select point(int4(random() * 100 ), int4(random() * 200)), null::text 
from generate_series(1,100);
SELECT 100
$ \d store
   "public.store" 테이블
 필드명 | 종류  | 기타 조건
--------+-------+-----------
 point  | point |
 text   | text  |

$ select * from store limit 5;
  point   | text
----------+------
 (81,186) |
 (1,172)  |
 (25,6)   |
 (90,151) |
 (18,35)  |
(5개 행)

4.2 집계 함수의 입력 자료형과 반환 자료형

위의 store 테이블의 point 칼럼을 대상으로 상권 범위를 사각형으로 나타내는 sum() 집계 함수를 만들려고 합니다. 이 함수의 입력 자료형은 당연히 point 형이고, 반환 자료형은 box형입니다.

즉, 이 집계 함수는 상태 값이 저장될 자료형이 곧 반환 자료형입니다.

4.3 sum() 집계 함수에서 사용할 전달 함수

집계 함수에 필요한 두 가지 가운데, 나머지 하나는 이 중간 상태 값을 계산 하는 전달 함수입니다.

이 전달 함수는 규칙이 있습니다. 이 전달 함수의 반환 자료형은 상태 값으로 사용하는 자료형이고, 입력 인자의 자료형은 두 개(또는 그 이상)인데, 그 입력 인자의 첫번째 인자는 반드시 현 상태값이 입력되어야 합니다.

여기서는 이 전달 함수의 이름을 appendpoint 라고 했습니다.

CREATE OR REPLACE FUNCTION public.appendpoint(p_box box, p_point point)
 RETURNS box
 LANGUAGE plpgsql
AS $function$
declare
ret_box box;
begin
if p_box is null then
  ret_box := box(p_point,p_point);
else
  ret_box := box(
  point(greatest((p_box[0])[0],p_point[0]), greatest((p_box[0])[1],p_point[1])),
  point(least((p_box[1])[0],p_point[0]), least((p_box[1])[1],p_point[1]))
  );
end if;
return ret_box;
end;
$function$;

설명하면, 사각형과 점을 합쳐 그 점을 포함하는 다른 사각형을 만드는 함수입니다.

입력 되는 현재 상태값(함수의 첫번째 입력 인자)이 null 이면, 두번째 입력되는 점값 그 자체를 사각형으로 해서 반환하고, 그렇지 않으면, 점을 포함하는 사각형을 반환합니다.

point 형의 x, y 값은 각각 [0],[1] 형태로 구하고, box 의 우측 상단([0])과 좌측 하단([1])에 대한 x,y값은 p_box([0])[0] 형태로 구합니다. 이것들의 greatest, least 함수 반환값으로 처리합니다.

함수 작동 예제:

$ select appendpoint(null, '(1,1)');
 appendpoint
-------------
 (1,1),(1,1)
(1개 행)

$ select appendpoint('((1,1),(1,1))', '(2,2)');
 appendpoint
-------------
 (2,2),(1,1)
(1개 행)

4.4 sum(point) 집계 함수

이제 집계 함수를 만들어봅니다.

$ create aggregate sum(point) ( sfunc = appendpoint, stype =  box);
CREATE AGGREGATE
$ select sum(point) from store;
      sum
----------------
 (99,197),(0,1)
(1개 행)

거의 전 영역에 골고루 분포하네요. (random() 함수의 품질을 확인하는 기회)

이때 sfunc 이 전달 함수이고, stype 이 중간 집계 용 상태 값 자료형입니다. 이들은 그냥 이름만 지정합니다.

5. 집계 함수의 병렬 처리

PostgreSQL 9.6 버전에서는 쿼리의 병렬 처리가 가능합니다. (물론 아직 많이 부족하지만, 잘 쓰면 기존 성능보다 좋은 성능을 낼 수 있습니다.)

앞에서 만든 sum(point) 집계 함수를 병렬로 처리할 수 있도록 하려면, 윗 집계 함수를 만들 때, combinefunc 옵션을 추가해야 합니다. 이 옵션의 값으로 지정하는 것은 중간 집계 함수 입니다.

sum(point) 집계 함수에서 중간 집계 함수는 중간 집계 값(box)들의 계산으로 또 다른 box 형을 반환하는 함수여야 합니다.

CREATE OR REPLACE FUNCTION public.appendbox(p_box1 box, p_box2 box)
 RETURNS box
 LANGUAGE plpgsql
AS $function$
declare
ret_box box;
begin
if p_box1 is null then
  ret_box := p_box2;
elsif p_box2 is null then
  ret_box := p_box1;
else
  ret_box := box(
   point(greatest((p_box1[0])[0],(p_box2[0])[0]), greatest((p_box1[0])[1],(p_box2[0])[1])),
   point(least((p_box1[1])[0],(p_box2[1])[0]), least((p_box1[1])[1],(p_box2[1])[1]))
  );
end if;
return ret_box;
end;
$function$;

이렇게 중간 집계 함수를 만들었으면, 이제 sum(point) 집계 함수를 다시 만듭니다. 이때 기억해야 할 것은 이 sum(point) 집계 함수는 병렬 처리를 해도 안전하다는 옵션도 함께 지정해야 합니다. (parallel = safe)

$ drop sum(point);
DROP AGGREGATE
$ create aggregate sum(point) ( sfunc = appendpoint, stype =  box, 
  combinefunc = appendbox, parallel = safe);
CREATE AGGREGATE

앞에서 표본 자료는 자료량이 너무 적어서 병렬처리를 하지 않습니다. 병렬 처리를 하는 최소 테이블 크기(기본값은 8MB)보다 작기 때문입니다. 그래서 일단 store 테이블에 자료를 더 추가하고 병렬 처리 테스트를 합니다.

$ insert into store 
select point(int4(random() * 100 ), int4(random() * 200)), null::text 
from generate_series(1,1000000);
INSERT 0 1000000
$ set max_parallel_workers_per_gather = 4; -- 병렬 처리 활성화
SET
$ explain verbose  select sum(point) from store;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=114750.79..114750.80 rows=1 width=32)
   Output: sum(point)
   ->  Gather  (cost=114750.08..114750.29 rows=2 width=32)
         Output: (PARTIAL sum(point))
         Workers Planned: 2
         ->  Partial Aggregate  (cost=113750.08..113750.09 rows=1 width=32)
               Output: PARTIAL sum(point)
               ->  Parallel Seq Scan on public.store  (cost=0.00..9573.08 rows=416708 width=16)
                     Output: point
(9개 행)

이 글의 범위에는 벗어나지만, 이렇게 병렬 처리를 하면, 쿼리 수행 시간이 확실히 줄어듭니다. 그 작업 비용은 직접 확인해 보세요.

6. 마무리

지금까지 소개한 것 외에 사용자 정의 집계 함수를 참 다양하게 만들 수 있습니다.  이 부분은 만드는 이의 몫입니다.

지금까지 소개한 것은 전달 함수, 중간 집계 함수만 소개했는데, 마무리 함수, 이동 집계 전달 함수, .... 이런 저런 꽤 많은 함수들이 집계 함수를 만드는데 사용됩니다.

실무 안에서는 이들 집계 함수를 구성하는 작업 함수들은 대부분 C 언어로 만들어집니다. 그래야 집계 작업이 최적의 성능을 낼테니까요.  이 부분에 대해서는 이 글의 범위에서 벗어나 생략합니다. 또한 집계 함수를 만들 때 기억 해야할 것들도 제법 있는데, 이런 부분은 공식 설명서를 참조하세요.

참신한 집계 함수를 만들어 공유하는 분들이 많아졌으면 좋겠네요.