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

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

pg_upgrade 명령을 사용할 때 기억해야 할 것들

http://postgresql.kr/blog/pg_upgrade.html
PostgreSQL 메이져 버전 업그레이드를 해야 할 경우 그 작업 방법은 현재로써는 두 가지 방법 뿐입니다.

하나는 pg_dumpall 명령과 pg_dump 명령을 이용해서 기존 데이터베이스 내용을 모두 SQL 구문으로 덤프 받아서 새 데이터베이스에서 그 덤프한 SQL 구문을 다시 실행하는 방법과,

또 다른 하나는 pg_upgrade 명령을 이용하는 것입니다.
pg_upgrade --help 명령을 이용하면 사용법이 나옵니다.
비교적 간단합니다. 또한 -k 옵션을 사용하면 덤프 & 복원 작업보다는 디스크 공간을 적게 사용합니다. 또한 엄청 빠릅니다. 그래서, pg_upgrade 명령을 사용해서, 메이져 버전 업그레이드 작업을 하는 것이 추세입니다.

하지만, 숨은 문제들이 제법있습니다.
pg_upgrade -c 옵션을 이용해서 업그레이드에 필요한 모든 사항을 확인 했다 하더라도, pg_upgrade 작업이 정상적으로 완료되지 않을 수 있습니다.
특히나 -k 옵션을 사용해서 하드링크 방식으로 업그레이드를 하는 경우라면, 중간에 작업을 실패하는 경우 난감합니다.

먼저 -c 옵션으로 미리 작업 타당성을 확인하는 작업에 가장 흔하게 나타나는 오류는 사용자 정의 확장 모듈입니다. pgxn 사이트나 github 같은 곳에서 소스를 직접 받아 설치한 확장 모듈은 새 버전에 당연히 그 확장모듈과 관련된 파일이 없습니다. 즉, 그 모듈을 일일히 확인해서 새 버전용으로 다시 만들고 설치해 주어야 합니다.

다음 9.6 업그레이드에서는 textsearch 부가 파일들 검사를 -c 옵션으로 미리 할 수 없습니다. 즉, -c 옵션으로 검사해서 업그레이드 할 준비가 다 되었다고 해서 업그레이드 작업을 진행했는데, 진행 도중 오류를 냅니다. share/tsearch_data 디렉터리 안에 필요한 파일이 없다는 오류입니다.

이 경우는 기존 데이터베이스 엔진 디렉터리에서 복사해와야 합니다.

그리고 다시 업그레이드 작업을 진행하면, initdb 작업을 한 새 데이터베이스 클러스트가 비어있지 않다면서 오류를 냅니다.
즉, 실재 업그레이드 작업이 진행되다가 실패한 경우라면, initdb 작업부터 다시 진행해야 합니다.

여기서 하나 기억해야 할 사실은 만일 새 데이터베이스 클러스트 디렉터리 밖에 있는 사용자 정의 테이블스페이스가 있다면, 여기서 문제가 생길 수 있습니다. 새로 initdb 작업을 한다고 해서, 사용자 정의 테이블스페이스까지 초기화 하지는 않기 때문입니다.

그래서, pg_upgrade 재 실행 전에 사용자 정의 테이블스페이스 디렉터러 안에 있는 이전 pg_upgrade 작업으로 생긴 파일들을 지워주어야 합니다.

이 사이트용 디비 서버의 pg_upgrade 작업은 이정도 시행착오로 잘 업그레이드 되었습니다.

하지만 여기서 끝이 아닙니다.

기존 postgresql.conf , pg_hba.conf , ssl 설정을 위해 사용되었던 서버 인증서, 서버 개인 키 등 이 정보들의 파일 복사, 파일 내용 병합 작업은 pg_upgrade -c 작업으로 확인할 수 없습니다. 이런 자질구레한 작업들을 작업전에 미리 계획에 넣어 두지 않으면, 그 만큼 업그레이드 작업 시간이 길어지게 됩니다.

이렇게 여러 작업 끝에 새 버전으로 잘 실행되었다 하더라도 아직 작업이 더 남아 있습니다.

하나는 기존 옛 버전에서 사용했던 데이터클러스터와 테이블스페이스 정리 작업을 해야 하고,
다른 하나는 새 데이터베이스가 처음 실행 되었기 때문에, 해당 데이터베이스의 빈공간  정리 작업과 자료 통계 정보 수집 작업이 필요합니다.

해당 데이터베이스에서 DML 작업이 아주 빈번하게 일어난다면, 바로 응용 프로그램 사용을 허용하게 되면, 빈번한 DML 작업이 일어날 것이고, 얼마 가지 않아, 트랜잭션 ID 겹침 방지를 위한 데이터베이스 보호 모드로 전환될 가능성이 큽니다.

왜냐하면, pg_upgrade 후 해당 데이터베이스는 급격하게 늙어버렸고, 그것에 대한 트랜잭션 ID 겹침 방지 작업을 autovacuum 작업으로 감당이 안되는 상황이 발생할 수 있기 때문입니다.
그래서, DML 작업이 빈번한 서버라면, pg_upgrade 작업 뒤 반드시 응용프로그램이 해당 데이터베이스를 사용하기 전에, vacuumdb -F -z -a 같은 명령으로 데이터베이스 클러스터를 정리해 줄 필요가 있습니다.

까먹기 전에 남겨 놓으려고 막 적었습니다. 문서가 많이 지저분하지만 알아서 잘 읽으세요.

추가.
역시 이렇게 정리를 해도 빼먹은 것이 있네요. 확장 모듈 버전이 올라간 경우, 기존 데이터베이스에서 사용했던 그 확장 모듈은 pg_update 작업으로 자동으로 그 확장 모듈의 버전까지 올리는 작업을 하지는 않습니다.  psql 접속 뒤 \dx 명령으로 사용하고 있는 확장 모듈을 찾아서,
alter extension 모듈이름 update;
명령으로 모든 확장 모듈을 업그레이드 해주어야 합니다.
물론 이 작업은 해당 서버에 있는 모든 데이터베이스에 해당됩니다.

PostgreSQL 9.6 ERROR: snapshot too old

http://postgresql.kr/blog/pg_snapshot_too_old.html
이 오류는 보편적인 업무 환경에서 나타날 수 있는 상황을 설명합니다.

세션A에서 cursor를 쓰기 위해서,
begin;
declare cur1 cursor for select * from test_table;
-- 첫 데이터를 가져오고
fetch first from cur1;
-- 그 자료를 업데이트함
update test_table set ..... where pk= 1;

이 상황에서
세션B가 똑 같이
begin;
declare cur1 cursor for select * from test_table;
-- 첫 데이터를 가져오고
fetch first from cur1;

세션B에서 update 하려고 하는데, 세션A에서 commit 일어남
그러면, 일반적인 상황에서는 세션B에서는 해당 pk의 로우가 없어, 어떤 자료도 변경되지 않았다고 나옵니다.

그럼 다시, 세션B에서
fetch first from cur1;
하면, old 자료가 보입니다.

이때, 서버 환경 설정 변수 old_snapshot_threshold 서버 환경 설정 변수값으로 -1 이상으로 지정했다면, (1분부터 60일까지) 그 기간이 지나고 난 뒤, vacuum 작업이 있으면, 세션B에서 가지고 있던 snapshot은 너무 옛날 것으로 판단해서 vacuum 작업에서 그 old version 자료는 더이상 참조 할 수 없게 되어버립니다.

세션B에서
다시
fetch first from cursor1;
명령을 내려면, snapshot too old 오류 메시지를 보게됩니다.

즉, 이 설정과 그 처리 방식은 autovacuum 이 해당 테이블에 대한 vacuum 작업을 얼마나 빈번하게 하는가와, 세션이 얼마나 오랫동안 트랜잭션을 유지하고 있느냐에 따라 실무에서 충분히 나타날 수 있는 오류 메시지입니다.


초간단 postgresql.conf

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

정말 초간단 postgresql.conf 파일 내용입니다. 이 정도면 단독 운영 서버로 써도 웬만한 환경에서 무난하게 작동할겝니다.

listen_addresses = '*'                       # 로컬 호스트 밖에서의 접속 허용
shared_buffers = 3GB                         # 물리 메모리  2/3 ~ 1/4
checkpoint_segments = 128                    # 2GB redo 로그, 9.4 이하에서
max_wal_size = 2GB                           # 2GB redo 로그, 9.5 이상에서
min_wal_size = 2GB                           # 2GB redo 로그, 9.5 이상에서
wal_level = logical                          # 일단 최대 자세하게
archive_mode = on                            # 아카이빙 기능은 켜두고,
archive_command = 'true'                     # 아카이빙을 임시로 사용 안함
log_destination = 'stderr'                   # pg_log 에 로그 남김
logging_collector = on                       # 자체 로그 프로세스 사용
log_line_prefix = '%t %u@%r/%d(%c 또는 %p) ' # 좀 더 자세히
stats_temp_directory = '/run/shm'            # 실시간 통계 정보는 공유 메모리로
effective_cache_size = 4GB                   # 물리 메모리 1/2 , 9.4 이하

복제용 운영 서버와 대기 서버 설정에 대해서는 좀 더 손을 봐야 하는데, 이 부분은 공식 설명서를 참조하세요.