Contrib Extensions – 보조기능
Contrib Extension 에서 PostgreSQL의 기능을 보조하는 Extension을 모아서 정리해봤습니다.
- adminpack
- earthdistance
- file_fdw
- fuzzystrmatch
- old_snapshot
adminpack
adminpack은 pgadmin 및 기타 관리도구가 서버 로그 파일의 원격 관리와 같은 추가 기능을 제공하는 데 사용할 수 있는 다양한 지원 기능을 제공합니다.
슈퍼유저에게만 기능 사용이 허용되지만, grant로 다른 사용자에게 허용될 수 있습니다.
제공 함수
| 함수 | 리턴 값 | 설명 |
|---|---|---|
| pg_catalog.pg_file_write ( filename text, data text, append boolean ) | bigint | 텍스트 파일에 쓰거나 추가합니다. data 파일을 지정된 파일에 씁니다. |
| pg_catalog.pg_file_sync ( filename text ) | void | 파일 또는 디렉토리를 디스크로 flush, 플러시 못하더라도 panic 오류 발생하지 않습니다. |
| pg_catalog.pg_file_rename ( oldname text, newname text , archivename text ) | boolean | 파일 이름을 변경합니다. 성공 시 true, 실패 시 false가 return 됩니다. |
| pg_catalog.pg_file_unlink ( filename text ) | boolean | 파일을 제거합니다. 성공 시 true, 실패 시 false가 return 됩니다. |
| pg_catalog.pg_logdir_ls () | setof record | 디렉토리 모든 로그파일을 나열합니다. postgresql.conf의 log_filename 매개변수 설정이 활성화 되어있어야 합니다(postgresql-%Y-%m-%d_%H%M%S.log). |
예시
pg_file_write 함수의 간단한 사용 예시입니다.
extentdb=# select pg_file_write(‘testfile’,’data12345′,true);
pg_file_write
———————–
9
(1개 행)
-bash-4.2$ ls -alrt
합계 80
drwx——. 2 postgres postgres 6 6월 8 15:24 pg_twophase
drwx——. 2 postgres postgres 6 6월 8 15:24 pg_snapshots
drwx——. 2 postgres postgres 6 6월 8 15:24 pg_serial
drwx——. 2 postgres postgres 6 6월 8 15:24 pg_replslot
drwx——. 2 postgres postgres 6 6월 8 15:24 pg_notify
drwx——. 4 postgres postgres 36 6월 8 15:24 pg_multixact
drwx——. 2 postgres postgres 6 6월 8 15:24 pg_dynshmem
drwx——. 2 postgres postgres 6 6월 8 15:24 pg_commit_ts
-rw——-. 1 postgres postgres 3 6월 8 15:24 PG_VERSION
-rw——-. 1 postgres postgres 88 6월 8 15:24 postgresql.auto.conf
-rw——-. 1 postgres postgres 1636 6월 8 15:24 pg_ident.conf
drwx——. 2 postgres postgres 18 6월 16 13:40 pg_xact
drwx——. 4 postgres postgres 51 6월 16 20:33 ..
drwx——. 2 postgres postgres 45 7월 25 03:08 pg_tblspc
-rw——-. 1 postgres postgres 4590 7월 26 01:49 pg_hba.conf
drwx——. 2 postgres postgres 188 8월 4 14:54 log
-rw——-. 1 postgres postgres 28893 8월 10 17:39 postgresql.conf
-rw——-. 1 postgres postgres 58 8월 11 15:20 postmaster.opts
-rw——-. 1 postgres postgres 96 8월 11 15:20 postmaster.pid
drwx——. 2 postgres postgres 37 8월 11 15:20 pg_stat
-rw——-. 1 postgres postgres 30 8월 12 00:00 current_logfiles
drwx——. 2 postgres postgres 18 8월 12 02:56 pg_subtrans
drwx——. 8 postgres postgres 84 8월 12 16:58 base
drwx——. 2 postgres postgres 4096 8월 12 16:58 global
drwx——. 3 postgres postgres 188 8월 12 17:38 pg_wal
-rw——-. 1 postgres postgres 9 8월 12 17:52 testfile
drwx——. 20 postgres postgres 4096 8월 12 17:52 .
drwx——. 4 postgres postgres 68 8월 12 17:53 pg_logical
drwx——. 2 postgres postgres 155 8월 12 17:54 pg_stat_tmp
-bash-4.2$ pwd
/var/lib/pgsql/14/data
-bash-4.2$ cat testfile
data12345
pg_logdir_ls () 함수의 사용 예시입니다.
extentdb=# select pg_logdir_ls();
pg_logdir_ls
—————————————————————————————
(“2022-08-12 17:57:04”,log/postgresql-2022-08-12_175704.log)
(1개 행)
earthdistance
earthdistance 모듈은 지구 표면에서 대원(great circle) 거리를 계산하는 두가지 접근 방식을 제공합니다.
첫번째는 cube 모듈로 접근합니다.
두번째는 point 좌표에 경도와 위도를 사용하는 내장 데이터 유형을 기반으로 접근합니다.
따라서 cube extension이 선행 설치가 되어있어야 합니다. 또한 이 모듈은 지구는 완전한 구형이라 가정합니다.
cube 기반 earth distance 접근
데이터는 지구 중심에서 x, y, z 거리를 나타내는 3개의 좌표를 사용하여 점(두 모서리 모두 동일)인 큐브에 저장됩니다.
cube 위에 지구의 실제 표면에 합리적으로 가까운지 확인하는 제약 조건 검사를 포함하는 도메인 earth 가 제공됩니다.
earth() 함수에서 지구의 반지름을 얻습니다(미터 단위).
다른 단위나 다른 반경 값을 사용가능합니다.
제공 함수
| 함수 | 리턴 값 | 설명 |
|---|---|---|
| earth () | float8 | 지구의 추정 반경을 반환합니다. |
| sec_to_gc ( float8 ) | float8 | 지구 표면의 두 점 사이의 직선 거리(secant)를 두 점 사이의 대원 거리로 변환합니다. |
| gc_to_sec ( float8 ) | float8 | 지구 표면의 두 점 사이의 대원 거리를 두 점 사이의 직선(secant) 거리로 변환합니다. |
| ll_to_earth ( float8, float8 ) | earth | 위도(인수 1)와 경도(인수 2)가 도 단위로 주어졌을 때 지구 표면에서 점의 위치를 반환합니다. |
| latitude ( earth ) | float8 | 지구 표면에 있는 한 지점의 위도를 도 단위로 반환합니다. |
| longitude ( earth ) | float8 | 지구 표면에 있는 점의 경도를 도 단위로 반환합니다. |
| earth_distance ( earth, earth ) | float8 | 지구 표면의 두 점 사이의 대원 거리를 반환합니다. |
| earth_box ( earth, float8 ) | cube | 위치에 지정된 대원 거리 내에 있는 점에 대해 cube의 @> 연산자를 사용하여 인덱싱된 검색에 적합한 상자를 반환합니다. 이 상자의 일부 점은 위치에서 지정된 대원 거리보다 더 멀리 떨어져 있으므로, earth_distance를 사용하는 두번째 확인이 쿼리에 포함되어야합니다. |
point 기반 earth distance 접근
지구 위치를 point 유형의 값으로 나타내는데 의존합니다.
여기서 첫번째 구성요소는 경도를 도단위로, 두번째 구성요소는 위도를 도 단위로 나타냅니다.
경도는 x축, 위도는 y축 개념에 잡혀있기 때문에 점은 (경도, 위도)로 간주됩니다.
(위도, 경도 순으로는 허용하지 않습니다.)
제공 연산자
| 연산자 | 리턴 값 | 설명 |
|---|---|---|
| point <@> point | float8 | 지구 표면의 두 지점 사이의 거리를 마일(miles) 단위로 계산합니다. |
earthdistance 예시
earthdistance가 제공하는 두가지 접근방식에 대한 예제입니다.
–지구 반지름
SELECT earth()::numeric(20,5);earth
6378168.00000
(1 row)SELECT sec_to_gc(0)::numeric(20,5);
sec_to_gc
0.00000
(1 row)SELECT sec_to_gc(2*earth())::numeric(20,5);
sec_to_gc
20037605.73216
(1 row)SELECT gc_to_sec(0)::numeric(20,5);
gc_to_sec
0.00000
(1 row)SELECT gc_to_sec(sec_to_gc(2*earth()))::numeric(20,5);
gc_to_sec
12756336.00000
(1 row)SELECT cube_ll_coord(ll_to_earth(0,0),1)::numeric(20,5),
cube_ll_coord(ll_to_earth(0,0),2)::numeric(20,5),
cube_ll_coord(ll_to_earth(0,0),3)::numeric(20,5);
cube_ll_coord | cube_ll_coord | cube_ll_coord
—————+—————+—————
6378168.00000 | 0.00000 | 0.00000
(1 row)SELECT latitude(ll_to_earth(45,0))::numeric(20,10);
latitude
45.0000000000
(1 row)SELECT longitude(ll_to_earth(0,90))::numeric(20,10);
longitude
90.0000000000
(1 row)SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,180))::numeric(20,5);
earth_distance
20037605.73216
(1 row)SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
cube_ll_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
cube_ll_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5),
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
cube_ur_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5);
cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
—————+—————+—————+—————+—————+—————
6266169.43896 | -111998.56104 | -111998.56104 | 6490166.56104 | 111998.56104 | 111998.56104
(1 row)SELECT (‘(0,0)’::point <@> ‘(180,0)’::point)::numeric(20,5);
numeric
12436.77274
(1 row)
file_fdw
file_fdw 모듈은 서버의 파일 시스템에 있는 데이터 파일에 액세스하거나, 서버에서 프로그램을 실행하고 출력을 읽는 데 사용할 수 있는 외부 데이터 wrapper를 제공합니다.
데이터 파일 또는 프로그램 출력은 copy from 절에서 읽을 수 있는 형식이어야 합니다.
(데이터 파일에 대한 액세스는 현재 읽기 전용)
이 wrapper를 사용하여 생성된 foreign table에는 다음 옵션을 적용할 수 있습니다.
foreign table 적용 옵션 목록
| 옵션 | 설명 |
|---|---|
| filename | 읽을 파일을 지정합니다. 상대 경로는 데이터 디렉토리에 상대적입니다. |
| program | 실행할 명령을 지정합니다. 이 명령의 표준 출력은 copy from program이 사용된 것처럼 읽힙니다. filename or program 둘 중 하나만 지정 가능합니다. |
| format | copy의 format 옵션과 동일한 데이터 형식을 지정합니다. |
| header | copy의 header 옵션과 마찬가지로 데이터에 헤더가 있는지 여부를 지정합니다. |
| delimiter | copy의 delimiter 옵션과 동일한 데이터 구분 문자를 지정합니다. |
| quote | copy의 quote 옵션과 동일한 데이터 인용 문자를 지정합니다. |
| escape | copy의 escape 옵션과 동일하게 데이터 이스케이프 문자를 지정합니다. |
| null | copy의 null 옵션과 동일한 데이터 null 문자열을 지정합니다. |
| encoding | copy의 enconding 옵션과 동일하게 데이터 인코딩을 지정합니다. |
foreign table의 column에도 다음 옵션을 적용할 수 있습니다.
foreign table의 column 적용 옵션 목록
| 옵션 | 설명 |
|---|---|
| force_not_null | boolean 옵션입니다. true면 column 값이 null 문자열과 일치하지 않아야함을 지정합니다(테이블 수준 null 옵션). 이는 copy의 force_not_null 옵션에 column을 나열하는 것과 동일한 효과를 가집니다. |
| force_null | boolean 옵션입니다. true인 경우 null 문자열과 일치하는 column의 null 값이 따옴표로 묶인 경우에도 반환되도록 지정합니다. 이 옵션이 없으면 null 문자열과 일치하는 인용 부호가 없는 값믄 NULL로 반환됩니다. 이는 copy의 force_null 옵션에 열을 나열하는 것과 동일한 효과를 가집니다. |
file_fdw의 용도 중 하나는 postgresql 활동 로그를 쿼리용 테이블로 사용할 수 있도록 하는 것입니다.
예시
pglog를 csv로 로깅하여 쿼리용 테이블로 만들기 위해 file_fdw를 활용하는 예제입니다.
–foreign 서버를 file_fdw wrapper로 생성합니다
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;–foreign 테이블 생성합니다. filename의 경로를 지정하고 반드시 pglog.csv 파일이 있어야 합니다.
CREATE FOREIGN TABLE pglog (
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
backend_type text,
leader_pid integer,
query_id bigint
) SERVER pglog
OPTIONS ( filename ‘log 경로입력/pglog.csv’, format ‘csv’ );–로그를 직접 쿼리할 수 있음을 확인합니다.
select * from pglog;log_time | user_name | database_name | process_id | connection_from | session_id | session_line_num | command_tag |
session_start_time | virtual_transaction_id | transaction_id | error_severity | sql_state_code | message | detail |
hint | internal_query | internal_query_pos | context | query | query_pos | location | application_name | backend_ty
pe | leader_pid | query_id
———-+———–+—————+————+—————–+————+——————+————-+
——————–+————————+—————-+—————-+—————-+———+——–+
——+—————-+——————–+———+——-+———–+———-+——————+———–
—+————+———-
(0 rows)
fuzzystrmatch
fuzzystrmatch 모듈은 문자열 간의 유사성과 거리를 결정하는 여러 기능을 제공합니다.
- soundex
- levenshtien
- metaphone
- double metaphone(dmetaphone)
soundex
소리가 비슷한 이름을 동일한 코드로 변환하여 일치시키는 방법입니다. 영어가 아닌 이름에는 유용하지 않습니다.
soundex 코드 작업을 위해 두가지 기능을 제공합니다.
| 함수 | 리턴 값 | 설명 |
|---|---|---|
| soundex(text) | text | 문자열을 soundex 코드로 변환합니다. |
| difference(text, text) | int | 두 문자열을 soundex 코드로 변환 한 다음 일치하는 코드 위치 수를 보고. 결과를 0 ~ 4까지 반환합니다. 0은 불일치에 가깝고, 4는 정확히 일치에 가깝습니다. |
예시
soundex의 예시입니다.
SELECT soundex(‘hello world!’);
soundex
H464
(1 row)SELECT soundex(‘Anne’), soundex(‘Ann’), difference(‘Anne’, ‘Ann’);
soundex | soundex | difference
———+———+————
A500 | A500 | 4
(1 row)SELECT soundex(‘Anne’), soundex(‘Andrew’), difference(‘Anne’, ‘Andrew’);
soundex | soundex | difference
———+———+————
A500 | A536 | 2
(1 row)SELECT soundex(‘Anne’), soundex(‘Margaret’), difference(‘Anne’, ‘Margaret’);
soundex | soundex | difference
———+———+————
A500 | M626 | 0
(1 row)
levenshtien
두 문자열 사이의 levenshtien 거리를 계산합니다.
Levenshtein 거리는 한 string s1 을 s2 로 변환하는 최소 횟수를 두 string 간의 거리로 정의합니다.
예를 들어 s1 = ‘꿈을꾸는아이’ 에서 s2 = ‘아이오아이’ 로 바뀌기 위해서는 (꿈을꾸 -> 아이오) 로 바뀌고, 네번째 글자 ‘는’ 이 제거되면 됩니다.
다음과 같은 기능들을 제공합니다.
| 함수 | 리턴 값 | 설명 |
|---|---|---|
| levenshtein(text source, text target, int ins_cost, int del_cost, int sub_cost) | int | source, target 모두 최대 255자의 null이 아닌 문자열일 수 있고, cost는 각각 문자 삽입, 삭제 또는 대체에 대한 비용입니다. |
| levenshtein(text source, text target) | int | levenshtein 함수에서 매개변수가 일부 생략된 함수입니다. |
| levenshtein_less_equal(text source, text target, int ins_cost, int del_cost, int sub_cost, int max_d) | int | 작은 거리만 관심이 있을 때 사용합니다. |
| levenshtein_less_equal(text source, text target, int max_d) | int | levenshtein_less_equal 함수에서 매개변수가 일부 생략된 함수입니다. |
예시
levenshtein의 예시입니다.
SELECT levenshtein(‘GUMBO’, ‘GAMBOL’);
levenshtein
2(1 행)
SELECT levenshtein(‘GUMBO’, ‘GAMBOL’, 2, 1, 1);
levenshtein
3(1 행)
SELECT levenshtein_less_equal(‘extensive’, ‘exhaustive’, 2);
levenshtein_less_equal
3(1행)
SELECT levenshtein_less_equal(‘extensive’, ‘exhaustive’, 4);
levenshtein_less_equal
4(1 행)
metaphone
soundex와 마찬가지로 입력 문자열에 대한 대표 코드를 구성합니다.
제공되는 기능은 다음과 같습니다.
| 함수 | 리턴 값 | 설명 |
|---|---|---|
| metaphone(text source, int max_output_length) | text | 소스는 최대 255자의 null이 아닌 문자열이어야합니다. max_output_length는 최대길이로, 이보다 길면 잘립니다. |
예시
metaphone의 예시입니다.
SELECT metaphone(‘GUMBO’, 4);
metaphone
KM
(1 row)
double metaphone(dmetaphone)
주어진 입력 문자열에 대해 “기본” 문자열, “대체” 문자열의 두가지 “음향 유사” 문자열을 계산합니다. 영어가 아닌 이름의 경우 발음에 따라 약간 다를 수 있습니다.
제공되는 기능은 다음과 같습니다.
| 함수 | 리턴 값 | 설명 |
|---|---|---|
| dmetaphone(text source) | text | 기본 문자열을 계산합니다. 입력 문자열 길이에 제한 없습니다. |
| dmetaphone_alt(text source) | text | 대체 문자열을 계산합니다. 입력 문자열 길이에 제한 없습니다. |
예시
dmataphone의 예시입니다.
SELECT dmetaphone(‘gumbo’);
dmetaphone
———————–
KMP
(1 row)SELECT dmetaphone_alt(‘gumbo’);
dmetaphone_alt
—————————
KMP
(1 row)
old_snapshot
old_snapshot은 old_snapshot_threshold를 구현하는데 사용되는 서버 상태를 검사할 수 있습니다.
- old_snapshot_threshold
비동기 동작중 하나로, 스냅샷을 사용할 때 “snapshot too old” 오류가 발생 하지 않고 쿼리 스냅샷을 사용할 수 있는 최소 시간을 설정합니다.
이 임계값보다 오래된 죽은 데이터는 vacuum처리 될 수 있습니다. 이렇게 하면 오랫동안 사용된 스냅샷의 부풀림을 방지할 수 있습니다.
이 단위의 default 단위는 “분” 단위이며 -1이 기본값으로, 기능을 비활성화하여 스냅샷 수명 제한을 무한대로 효과적으로 설정합니다.
이 매개변수는 서버 시작 시에만 설정할 수 있습니다.
이 기능이 활성화되면 relation 종료 시 사용 가능한 공간을 운영 체제에 해제할 수 없습니다. 이렇게 하면 “snapshot too old” 상태를 감지하는데 필요한 정보가 제거될 수 있기 때문입니다.
relation에 할당된 모든 공간은 명시적으로 해제되지 않는 한(예-VACUUM FULL 명령) 해당 relation 내에서만 재사용을 위해 해당 relation과 연결된 상태로 유지됩니다.
제공 함수
| 함수 | 리턴 값 | 설명 |
|---|---|---|
| pg_old_snapshot_time_mapping(array_offset OUT int4, end_timestamp OUT timestamptz, newest_xmin OUT xid) | setof record | XID 매핑에 대한 서버 timestamp의 모든 항목을 반환합니다. 각 항목은 해당 분에 찍은 모든 스냅샷의 최신 xmin을 나타냅니다. |
예시
postgresql.conf 의 old_snpashot_threshold를 30분으로 주고, pg_old_snapshot_time_mapping 함수를 사용했을 때의 결과 예시입니다.
show old_snapshot_threshold;
old_snapshot_threshold
30min
(1 row)SELECT * FROM pg_old_snapshot_time_mapping() ;
array_offset | end_timestamp | newest_xmin
————–+————————+————-
0 | 2022-09-29 01:44:00-04 | 1354
1 | 2022-09-29 01:45:00-04 | 1354
2 | 2022-09-29 01:46:00-04 | 1354
3 | 2022-09-29 01:47:00-04 | 1354
(4 rows)
지금까지 PostgreSQL의 Contrib Extensions – 보조기능에 관해 알아보았습니다
‘PostgreSQL의 Interface(C++)’을 바로 이어서 확인해보세요!
