확장 기능

카카오클라우드 PostgreSQL에서 제공하는 확장 기능과 주요 사용 방법을 설명합니다.

확장 기능 조회 및 설치

카카오클라우드 PostgreSQL은 주요 확장 기능을 기본 제공합니다. 신규 데이터베이스를 기본 템플릿으로 생성하면, template1에 등록된 확장 기능을 별도 설치 없이 사용할 수 있습니다.

확장 기능 기본 제공 방식

• pg_cron을 제외한 기본 제공 확장 기능은 template1 데이터베이스에 등록되어 있습니다.
• pg_cron은 기본 데이터베이스인 postgres에 등록되어 있습니다. 사용 방법과 제한 사항은 pg_cron을 참고해 주세요.
• template1이 아닌 다른 템플릿으로 데이터베이스를 생성한 경우에는 필요한 확장 기능을 직접 설치해야 합니다.

직접 설치가 필요한 경우, 다음과 같이 설치 가능한 확장 기능을 조회하고 설치할 수 있습니다.

설치 가능한 확장 기능 조회

설치 가능한 확장 기능은 다음과 같이 조회할 수 있습니다.

설치 가능 확장 기능 조회 예시

select * from pg_available_extensions ;

확장 기능 설치

확장 기능을 설치하려면 최초 생성한 사용자 계정에서 create_extension을 사용합니다.
create_extension은 postgres.public에 있으므로 반드시 postgres 데이터베이스에 접속한 후 호출해야 합니다.
postgres 데이터베이스에서 수행하지 않을 경우 타입 오류가 발생할 수 있습니다.

확장 기능 설치, create_extension 예시

# template0 기반의 데이터베이스를 생성하여 orafce 확장 기능을 설치하는 예시입니다.
# 예시에 사용될 사용자 계정은 admin으로 가정합니다.

# 1. template0 기반의 'test' 데이터베이스를 생성합니다.
postgres=> create database test TEMPLATE template0 ;
CREATE DATABASE

# 2. postgres 데이터베이스에서 create_extension 프로시저를 호출합니다. 호출 시 각 필드의 타입을 예시와 같이 지정하는 것을 권장합니다.
postgres=> \c postgres admin
postgres=> CALL create_extension('test'::text, 'orafce'::text);
NOTICE: Extension "orafce" added to database "test"
CALL

# 3. 'test' 데이터베이스에 설치된 확장 기능을 확인합니다.
postgres=> \c test admin
You are now connected to database "test" as user "admin".

test=> \dx
                                                  List of installed extensions
  Name   | Version |   Schema   |                                          Description
---------+---------+------------+-----------------------------------------------------------------------------------------------
 orafce  | 4.15    | public     | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

지원하는 확장 기능

확장 기능에 대한 자세한 설명은 PostgreSQL 공식 추가 확장 문서를 참고하시기 바랍니다.

이름	설명
adminpack	서버 설정, 로그 디렉터리, 파일 상태 등 관리 정보를 조회하는 함수 모음입니다. 데이터베이스 관리와 문제 진단에 활용할 수 있습니다.
amcheck	B-Tree 인덱스 등 데이터베이스 구조의 손상 여부와 일관성을 검사합니다. 데이터 무결성 점검과 장애 원인 분석에 유용합니다.
autoinc	트리거 기반 자동 증가 열을 구현할 때 사용합니다.
bloom	여러 열 조건을 빠르게 필터링하기 위한 블룸 필터 인덱스를 제공합니다. 대용량 테이블의 다중 컬럼 검색에 활용할 수 있습니다.
intarray	정수 배열을 검색하고 조작하는 함수와 연산자를 제공합니다.
btree_gin	일반 B-Tree 비교 연산을 GIN 인덱스에서 사용할 수 있습니다. 배열, JSONB 등 복합 데이터 타입 인덱싱에 활용할 수 있습니다.
btree_gist	일반 B-Tree 비교 연산을 GiST 인덱스와 함께 사용할 수 있게 합니다. 범위 타입이나 복합 조건 인덱싱에 활용합니다.
citext	대소문자를 구분하지 않는 텍스트 타입을 제공합니다. 이메일 주소나 사용자 이름처럼 대소문자 구분이 불필요한 값에 유용합니다.
cube	다차원 점과 큐브 데이터를 저장하고 검색하는 타입과 연산자를 제공합니다. 다차원 좌표나 간단한 공간 계산에 활용할 수 있습니다.
dblink	다른 PostgreSQL 데이터베이스에 연결해 쿼리를 실행합니다. 분산 환경에서 외부 데이터베이스의 데이터를 조회할 때 사용합니다.
dict_xsyn	텍스트 검색에서 동의어 사전을 사용할 수 있게 합니다. 같은 의미의 다른 표현을 동일하게 검색해야 할 때 활용합니다.
dict_int	텍스트 검색에서 정수 토큰을 처리하기 위한 사전을 제공합니다. 숫자 기반 검색어를 정규화할 때 사용합니다.
earthdistance	위도와 경도 등 지리 좌표 간 거리를 계산합니다. 위치 기반 검색이나 거리 계산에 활용할 수 있습니다.
file_fdw	서버의 파일을 외부 테이블로 조회할 수 있게 합니다. CSV 같은 파일 데이터를 테이블처럼 조회할 때 사용합니다.
fuzzystrmatch	유사 문자열 검색과 철자 오류 보정에 사용할 수 있는 문자열 매칭 함수를 제공합니다. 이름, 주소 등 유사 텍스트 비교에 유용합니다.
hstore	키-값 쌍 데이터를 저장하고 검색하기 위한 데이터 타입을 제공합니다. JSONB가 필요하지 않은 단순 속성 저장에 활용할 수 있습니다.
hstore_plperl	Perl 함수에서 hstore 데이터를 처리할 수 있게 합니다.
hstore_plperlu	신뢰할 수 없는 Perl 함수에서 hstore 데이터를 처리할 수 있게 합니다.
hypopg	실제 인덱스를 생성하지 않고 가상 인덱스로 실행 계획을 검토합니다. 인덱스 추가 전 쿼리 성능 영향을 예측할 때 유용합니다.
insert_username	행 삽입 또는 업데이트 시 현재 사용자 이름을 자동으로 기록합니다.
intagg	정수 값을 배열 형태로 집계하는 함수를 제공합니다. 여러 정수 값을 하나의 배열 결과로 반환해야 할 때 사용합니다.
isn	ISBN, ISMN, ISSN 등 국제 표준 번호 타입과 검증 기능을 제공합니다. 표준 번호를 저장하고 형식을 검증할 때 사용합니다.
jsonb_plperl	Perl 함수에서 jsonb 데이터를 처리할 수 있게 합니다.
jsonb_plperlu	신뢰할 수 없는 Perl 함수에서 jsonb 데이터를 처리할 수 있게 합니다.
jsonb_plpython3u	Python 3 함수에서 jsonb 데이터를 처리할 수 있게 합니다.
lo	대용량 객체(Large Object)를 관리하는 기능을 제공합니다. 바이너리 파일처럼 큰 데이터를 데이터베이스에서 다룰 때 사용합니다.
ltree	계층형 라벨 데이터를 저장하고 검색하기 위한 데이터 타입과 연산자를 제공합니다. 트리 구조 데이터 처리에 활용할 수 있습니다.
ltree_plpython3u	Python 3 함수에서 ltree 데이터를 처리할 수 있게 합니다.
moddatetime	행이 변경될 때 수정 시간을 자동으로 기록합니다.
old_snapshot	오래된 스냅샷 사용과 관련된 상태 정보를 제공합니다. 장기 실행 쿼리 관련 문제를 진단할 때 사용할 수 있습니다.
orafce	Oracle 호환 함수와 패키지를 제공하며, Oracle 기반 애플리케이션을 PostgreSQL로 마이그레이션할 때 사용할 수 있습니다.
pageinspect	테이블과 인덱스 페이지의 내부 구조를 검사합니다. 인덱스나 페이지 단위 문제를 상세 분석할 때 사용합니다.
pg_buffercache	공유 버퍼 캐시의 현재 상태를 조회합니다. 데이터베이스 캐시 사용 현황을 분석할 때 사용합니다.
pg_freespacemap	테이블과 인덱스 페이지의 여유 공간 정보를 조회합니다. 공간 사용 효율을 분석할 때 활용합니다.
pgcrypto	암호화, 복호화, 해시 생성 함수를 제공합니다. 데이터 암호화와 무결성 검증에 사용할 수 있습니다.
pgrowlocks	특정 테이블의 행 잠금 정보를 조회합니다. 잠금 경합이나 트랜잭션 문제를 분석할 때 사용합니다.
pgstattuple	테이블과 인덱스의 공간 사용량과 dead tuple 정보를 분석합니다. 테이블 팽창과 정리 필요성을 판단할 때 유용합니다.
pg_stat_kcache	쿼리별 CPU와 커널 레벨 I/O 사용 통계를 수집합니다. 쿼리 성능 병목을 시스템 리소스 관점에서 분석할 때 사용합니다.
pg_visibility	테이블 페이지와 행의 가시성 정보를 조회합니다. VACUUM 상태나 dead tuple 관련 문제를 분석할 때 참고할 수 있습니다.
plperl	Perl로 사용자 정의 함수를 작성할 수 있게 합니다.
plpgsql	PostgreSQL의 기본 절차적 언어로 함수와 트리거를 작성할 때 사용합니다.
plprofiler	PL/pgSQL 함수의 실행 시간과 성능 병목을 분석합니다. 저장 함수나 프로시저 최적화에 활용할 수 있습니다.
plpython3u	Python 3으로 사용자 정의 함수를 작성할 수 있게 합니다.
plpythonu	Python 2 기반 사용자 정의 함수를 지원합니다. Python 2는 지원 종료되었으므로 plpython3u 사용을 권장합니다.
postgis	공간 데이터 타입과 공간 인덱스, 거리 계산 등 GIS 기능을 제공합니다. 위치 기반 서비스나 공간 데이터 분석에 사용합니다.
postgis_topology	PostGIS에서 위상 기반 공간 모델을 사용할 수 있게 합니다. 공간 객체 간 연결, 경계, 인접 관계를 관리할 때 사용합니다.
postgres_fdw	외부 PostgreSQL 데이터베이스의 테이블을 로컬에서 조회합니다. 데이터 통합이나 마이그레이션에 활용할 수 있으며, 사용 방법은 postgres_fdw를 참고해 주세요.
refint	트리거 기반 참조 무결성 검사 기능을 제공합니다.
seg	선분 데이터를 저장하고 비교하는 타입과 연산자를 제공합니다.
sslinfo	현재 SSL 연결과 클라이언트 인증서 정보를 조회합니다.
tablefunc	크로스탭 등 테이블 변환 함수를 제공합니다. 집계 결과를 피벗 형태로 표현할 때 유용합니다.
tcn	테이블 변경 사항을 알림으로 전달하는 트리거 기반 기능을 제공합니다.
tsm_system_rows	지정한 행 수만큼 테이블 샘플링을 수행합니다.
tsm_system_time	지정한 시간 동안 테이블 샘플링을 수행합니다.
unaccent	문자열에서 악센트 기호를 제거하여 검색과 비교에 활용합니다.
uuid-ossp	UUID 생성 함수를 제공합니다.
pgvector	벡터 데이터를 저장하고 유사도 검색을 수행합니다. 검색, 추천, AI 애플리케이션의 임베딩 데이터 처리에 활용합니다.
pg_cron	데이터베이스 작업을 cron 형식으로 예약 실행합니다. 정기적인 VACUUM, 집계, 유지 관리 작업에 활용할 수 있으며, 사용 방법은 pg_cron을 참고해 주세요.
pg_hint_plan	SQL 힌트로 쿼리 실행 계획을 제어할 수 있게 합니다.
pg_partman	시간 또는 순번 기반 파티션 테이블 관리를 지원합니다.
pg_proctab	운영 체제 프로세스 정보를 PostgreSQL에서 조회합니다.
pg_repack	테이블과 인덱스의 bloat을 줄이고 공간을 회수합니다. 서비스 중단을 최소화하면서 테이블을 정리할 때 사용합니다.
pg_trgm	트라이그램 기반 유사 문자열 검색과 인덱싱을 지원합니다. LIKE '%검색어%' 형태의 검색 성능 개선에 유용합니다.
pg_stat_statements	쿼리별 실행 횟수, 실행 시간, I/O 등 SQL 실행 통계를 수집합니다. 자주 실행되거나 부하가 큰 쿼리를 식별할 때 사용합니다.

확장 기능 상세

사용 방법에 대한 추가 설명이 필요한 확장 기능을 소개합니다.

postgres_fdw

postgres_fdw는 PostgreSQL에서 제공하는 외부 데이터 래퍼(FDW, Foreign Data Wrapper)입니다. 원격 PostgreSQL 데이터베이스의 테이블을 로컬 데이터베이스에서 조회하거나, 원격 테이블을 참조하는 외부 테이블을 로컬에 생성할 수 있습니다.
카카오클라우드에서 제공하는 postgres_fdw를 사용하려면 create_fdw_server 프로시저를 사용해야 합니다. create_fdw_server 프로시저는 template1에 생성되어 있으므로, 신규 데이터베이스 생성 시 기본으로 제공됩니다.

postgres_fdw는 다음과 같이 프로시저를 호출하여 원격 서버를 생성할 수 있습니다. 프로시저를 실행하면 사용자 계정이 자동으로 매핑되며, 서버 생성 후 외부 테이블을 만들어 사용할 수 있습니다.

사용 절차는 다음과 같습니다.

1. 데이터베이스 생성이 가능한 관리자 또는 사용자 계정을 이용해서 데이터베이스를 생성합니다.
2. 접근하려는 데이터베이스에 대한 보안 그룹의 아웃바운드 규칙(포트 허용)이 설정되어 있는지 확인합니다.
3. 생성한 데이터베이스에서 create_fdw_server 프로시저를 사용해 FDW 수행을 위한 서버를 생성합니다.
4. 외부 데이터베이스를 조회합니다.

postgres_fdw 사용 예시

[create_fdw_server 생성 및 사용자 정보 연결]
SELECT * FROM create_fdw_server(
    'fdw_server',                                 -- 서버 이름
    'pg_endpoint',                                -- 외부 데이터베이스 엔드포인트
    'foreign_database',                           -- 외부 데이터베이스 이름
    5432,                                         -- 외부 데이터베이스 접속 포트(숫자 형식)
    'test_database',                             -- 외부 데이터베이스를 조회할 사용자 데이터베이스
    'admin',                                      -- 사용자 계정 ID
    'foreign_admin',                              -- 외부 데이터베이스 계정 ID
    'foreign_pass'                                -- 외부 데이터베이스 계정 비밀번호
);

예시)
 SELECT * FROM create_fdw_server(
    'db_nameserver',
    'db_name-pg-001.csnzphcqgi72.ap-northeast-2.rds.amazonaws.com',
    'postgres',
    5432,
    'grey',
    'admin',
    'remote_user',
    'remote_password'
);

[외부 데이터베이스 조회]
create_fdw_server를 통해 생성한 서버 이름 조회 예시
SELECT srvname, srvtype, srvversion, srvoptions FROM pg_foreign_server;

원격 테이블 매핑 예시)
CREATE FOREIGN TABLE remote_table (
    id SERIAL,
    name TEXT,
    value NUMERIC
)
SERVER fdw_server_example -- FDW 서버 이름
OPTIONS (schema_name 'public', table_name 'remote_table_name');

pg_cron

pg_cron은 데이터베이스의 유지 보수와 운영 효율성을 위해 cron 기반 작업 스케줄링을 실행할 수 있게 하는 확장 기능입니다. 카카오클라우드에서는 기본 데이터베이스인 postgres에 pg_cron이 생성되어 있으며, pg_cron의 시간대는 UTC±00:00을 기준으로 합니다.

지원하지 않는 pg_cron 작업

서비스의 안정적인 운영을 위해 cron.schedule, cron.alter_job, UPDATE cron.job 등 cron.job을 직접 생성하거나 변경하는 작업은 지원하지 않습니다.

사용 절차는 다음과 같습니다.

1. pg_cron이 설정된 postgres 데이터베이스에 접근합니다.
2. cron 형태의 스케줄을 등록합니다.
3. 등록된 시간에 cron이 수행되었는지 조회합니다.
4. 스케줄을 삭제합니다.

pg_cron 사용 예시

postgres=# \c postgres {관리자/사용자 ID}
You are now connected to database "postgres" as user "{관리자/사용자 ID}".

[스케줄 등록]
# 매일 새벽 4시(UTC)에 특정 데이터베이스에서 VACUUM이 수행되는 'weekly-vacuum'이라는 이름의 스케줄 등록
# postgres=> CALL create_cronjob('{job name}', '{schedule}', '{command}', '{DB name}', {active});

postgres=> CALL create_cronjob('weekly-vacuum', '* 4 * * *', 'VACUUM', 'test', true);

[스케줄 조회]
postgres=> select * from cron.job ;
 jobid |  schedule   | command | nodename  | nodeport | database | username | active |     jobname
-------+-------------+---------+-----------+----------+----------+----------+--------+------------------
     1 |  * 4 * * *  | VACUUM  |           |     5432 | test     | admin    | t      | weekly-vacuum


[스케줄 수행 조회]
postgres=> select * from cron.job_run_details ;
 jobid | runid | job_pid | database | username | command |  status   | return_message |          start_time           |           end_time

-------+-------+---------+----------+----------+---------+-----------+----------------+-------------------------------+---------------------
----------
     8 |     1 |  379928 | db_name   | postgres | VACUUM  | succeeded | VACUUM         | 2024-11-11 11:01:00.066025+00 | 2024-11-11 11:01:05.
821539+00
(1 rows)


[스케줄 삭제]
# 기존에 등록한 스케줄 삭제
# postgres=> select cron.unschedule('{Job name or Job ID}') ;

postgres=> select cron.unschedule('weekly-vacuum') ;
 unschedule
------------
 t
(1 row)

작업 수정

작업(Job)을 수정하려면 기존 작업을 삭제한 후 새로운 설정으로 작업을 등록해 주세요.

pgaudit

pgaudit은 데이터베이스 내에서 발생하는 다양한 활동을 감사(Audit)하기 위한 확장 기능입니다.
set_pgaudit_param 프로시저를 사용하여 ROLE 또는 DATABASE 단위로 설정을 변경할 수 있으며, 이 작업은 postgres 데이터베이스에서 실행할 수 있습니다.

set_pgaudit_param 프로시저 사용시 주의사항

• 변경된 설정은 신규 세션부터 적용되며 동일한 파라미터가 모두 설정된 ROLE과 DATABASE에 접속하는 경우 ROLE 설정이 우선 적용됩니다.
• pgaudit.* 파라미터만 변경할 수 있으며 내부 관리용 ROLE 및 DATABASE는 파라미터를 변경할 수 없습니다.
• 본 프로시저를 통해 변경한 설정은 파라미터 그룹에 설정된 값보다 우선하여 적용됩니다. 이에 따라 파라미터 그룹 조회 결과와 실제 세션에서 조회되는 값이 다를 수 있습니다.

1. pgaudit 설정 변경 프로시저를 실행할 수 있는 데이터베이스에 접근합니다.
2. set_pgaudit_param 프로시저를 호출하여 설정을 변경합니다.

pgaudit 사용 예시

postgres=# \c postgres {관리자/사용자 ID}
You are now connected to database "postgres" as user "{관리자/사용자 ID}".

[프로시저 호출 양식]
# public.set_pgaudit_param('{적용 단위}', '{대상 이름}', '{파라미터 명}', '{설정 값}');
# 적용 단위: ROLE 또는 DATABASE 중 선택
# 대상 이름: 설정할 ROLE 또는 DATABASE 중 선택
# 파라미터 명: 변경할 pgaudit 파라미터 이름
# 설정 값: 적용할 파라미터 설정 값

postgres=> CALL public.set_pgaudit_param(
    '{role|database}', 
    '{target_name}', 
    '{parameter_name}', 
    '{parameter_value}'
);

[ROLE 단위 설정 예시]
# 'audit_user'라는 이름을 가진 ROLE에 read, write 로그를 남기도록 설정
postgres=> CALL public.set_pgaudit_param(
    'role',
    'audit_user',
    'pgaudit.log',
    'read,write'
);

[DATABASE 단위 설정 예시]
# 'audit_db'라는 이름을 가진 DATABASE에 모든(all) 로그를 남기도록 설정
postgres=> CALL public.set_pgaudit_param(
    'database',
    'audit_db',
    'pgaudit.log',
    'all'
);