DB 인사이드 | PostgreSQL Extension - Introduction

 

시작하며

PostgreSQL은 Oracle, MySQL과 같은 다른 DBMS에 비해 기본적으로 제공하는 내장 함수나 부가적인 기능들이 부족한 편입니다. 이를 보완하기 위해 PostgreSQL에서는 Extension 기능을 제공하고 있습니다. PostgreSQL Extension은 PostgreSQL의 기능을 확장해 주는 역할을 하며 이를 통하여 PostgreSQL Database에서 제공하지 않는 기능을 Plug-in 형태로 연결하여 손쉽게 사용할 수 있습니다.

 

 

Extension 구성 요소

Extension을 사용하기 위해서는 {PostgreSQL 엔진 경로}/share/extension 디렉토리에 다음과 같이 컨트롤 파일(.control)과 하나 이상의 SQL 스크립트 파일(.sql)이 있어야 합니다.

[root@tech-231 extension]$ pwd
/home/postgresdt/postgresql2/share/extension

[root@tech-231 ~] ls | grep pg_stat_monitor
pg_stat_monitor--1.0--2.0.sql
pg_stat_monitor--2.0.sql
pg_stat_monitor.control

{extension_name}.control 파일

• Extension의 설명 및 버전 등의 정보 등 메타 정보를 포함
• {extension_name}.control 파일은 {PostgreSQL 엔진 경로}/share/extension 위치
• 해당 파일의 파일명으로 사용된 extension_name은 Extension 사용하기 위해 수행하는 명령(CREATE EXTENSION {extension_name})의 extension_name과 일치해야 함

{extension_name}--version.sql 파일

• Extension의 Source Code 파일로 Extension에서 제공하는 기능 및 동작들에 대한 실제 구현을 하는 파일
• {extension_name}--version.sql 파일은 .control 파일이 있는 위치에 존재하거나 .control 파일에 .sql 파일의 위치를 정의
• .sql 파일은 최소 한 개 이상 존재해야 함

 

 

Extension 설치

PostgreSQL Database를 설치한 방법에 따라 Extension 설치도 다릅니다. PostgreSQL Database를 Source Code로 설치한 경우와 Package Manager를 통하여 설치한 경우에 대해서 각각 Extension 설치 과정이 어떻게 다른지 확인해 보겠습니다.

📢 본 문서에서는 PostgreSQL에서 제공하는 기본 Extension 중 pg_stat_statements 설치에 대해서만 설명합니다.
모든 기본 Extension은  pg_stat_statements 와 동일 방법으로 구현됩니다.

 

Source Code 설치

Source Code로 PostgreSQL Database를 설치한 경우, PostgreSQL에서 기본으로 제공하는 Extension은 Source File의 압축을 푼 경로에 contrib 하위 디렉토리에 존재합니다.

contrib 하위 디렉토리에서 설치하고자 하는 Extension을 확인 후 해당 디렉토리로 이동하여 Source File을 컴파일(make)한 후 설치(make install)할 수 있습니다.

Extension 종류에 따라서 Configuration 파일(postgresql.conf) 수정이 필요할 수 있으며, PostgreSQL Database 재기동이 필요할 수 있습니다.

📢 앞으로 테스트를 진행할 PostgreSQL에 대한 환경 정보는 다음과 같습니다.
PostgreSQL 엔진 경로 : /home/postgres/PostgreSQL_Engine
기본 Extension 경로 : /home/postgres/PostgreSQL_Engine/contrib
PostgreSQL Data 경로 : /home/postgres/PostgreSQL_Data

 

Step 1. Extension이 위치하는 경로 이동

[postgres@tech-233 ~]$ cd /home/postgres/PostgreSQL_Engine/contrib/pg_stat_statements

[postgres@tech-233 pg_stat_statements]$ ls -al
drwxrwxr-x.  4 postgres postgres   106 Sep 21 16:01 .
drwxrwxr-x. 61 postgres postgres  4096 Sep 21 12:12 ..
drwxrwxr-x.  2 postgres postgres     6 Sep 21 12:12 expected
lrwxrwxrwx.  1 postgres postgres    66 Sep 21 12:12 Makefile -> /home/postgres/postgresql-15.4/contrib/pg_stat_statements/Makefile
drwxrwxr-x.  2 postgres postgres     6 Sep 21 12:12 sql

 

Step 2. Source File 컴파일(make)

[postgres@tech-233 pg_stat_statements]$ make

[postgres@tech-233 pg_stat_statements]$ ls -al
drwxrwxr-x.  4 postgres postgres   106 Sep 21 16:01 .
drwxrwxr-x. 61 postgres postgres  4096 Sep 21 12:12 ..
drwxrwxr-x.  2 postgres postgres     6 Sep 21 12:12 expected
lrwxrwxrwx.  1 postgres postgres    66 Sep 21 12:12 Makefile -> /home/postgres/postgresql-15.4/contrib/pg_stat_statements/Makefile
-rw-rw-r--.  1 postgres postgres 49048 Sep 21 16:01 pg_stat_statements.o
-rwxrwxr-x.  1 postgres postgres 44800 Sep 21 16:01 pg_stat_statements.so
drwxrwxr-x.  2 postgres postgres     6 Sep 21 12:12 sql

 

Step 3. 컴파일된 파일 설치(make install)

[postgres@tech-233 pg_stat_statements]$ make install

 

Step 4. PostgreSQL Configuration 파일 설정 후 PostgreSQL 재기동

[postgres@tech-233 pg_stat_statements]$ vi /home/postgres/PostgreSQL_Data/postgresql.conf
...
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
...

[postgres@tech-233 pg_stat_statements]$ /home/postgres/PostgreSQL_Engine/bin/pg_ctl -D /home/postgres/PostgreSQL_Data restart

 

Step 5. Database에 접속하여 pg_stat_statements Extenstion 생성 및 생성 확인

postgres=# CREATE EXTENSION pg_stat_statements ;
postgres=# \dx (또는 SELECT * FROM pg_extension ;)
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

 

Package Manager 설치

Package Manager를 통해 PostgreSQL Database를 설치한 경우, 기본 Extension을 가지는 contrib는 설치되지 않기 때문에 Package Manager를 통해 contrib를 설치한 후 사용할 수 있습니다. Extension 종류에 따라서 Configuration 파일(postgresql.conf) 수정이 필요할 수 있으며, PostgreSQL Database 재기동이 필요할 수 있습니다.

📢 PostgreSQL PGDG yum Repository가 설치가 되어 있다는 전제에서 진행합니다.

 

Step 1. PostgreSQL 버전에 맞는 contrib 설치

[root@tech-233 ~] yum install -y postgresql15-contrib

 

Step 2. PostgreSQL Configuration 파일 설정 후 PostgreSQL 재기동

[root@tech-233 ~] vi /var/lib/pgsql/15/data/postgresql.conf
...
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
...

[root@tech-233 ~]$ systemctl restart postgresql-15

 

Step 3. Database에 접속하여 pg_stat_statements Extension 생성 및 생성 확인

postgres=# CREATE EXTENSION pg_stat_statements ;
postgres=# \dx (또는 SELECT * FROM pg_extension ;)
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

 

 

 

기본 제공 Extension 종류

PostgreSQL Database에서 기본으로 제공하는 Extension 종류는 pg_available_extensions Catalog에서 확인할 수 있습니다. 기본 Extension의 종류는 PostgreSQL의 Version에 따라서 다를 수 있습니다.

Extension Name	Description
adminpack	pgAdmin 및 기타 관리 도구에서 Server 로그 파일 원격 관리 등의 관리 기능을 제공
amcheck	Object에 구조적 손상과 논리적 손상 여부 확인을 통해 관계 무결성을 검증하는 기능을 제공
autoinc	Sequence의 필드 자동 증가 기능을 제공
bloom	블륨 필터를 기반으로 인덱스가 집합에 속하는 지를 확인하여 인덱스 Access 방법 제공
btree_gin	GIN 테스트 및 다른 GIN 연산자 클래스 개발을 위한 GIN 연산자 관련 인덱스 제공
btree_gist	GiST 테스트 및 다른 GiST 연산자 클래스 개발을 위한 GiST 연산자 관련 인덱스 제공
citext	대소문자를 구분하지 않는 문자열 Data 유형 지원
bloom	다차원 큐브 형태의 Data 유형 지원
cube	블륨 필터를 기반으로 인덱스가 집합에 속하는 지를 확인하여 인덱스 Access 방법 제공
dblink	Database 내에서 다른 Database로 연결하는 기능 제공
dict_int	정수의 고유단어 텍스트 검색 사전 템플릿 제공(최대 자릿수, 매개변수 길이, 기호)
dict_xsyn	확장된 동의어 처리를 위한 텍스트 검색 사전 템플릿 제공
earthdistance	지구 표면의 대원 거리 계산할 수 있는 기능 제공
file_fdw	Server의 파일시스템에 저장된 Data에 액세스를 위한 외부 Data 래퍼 기능 제공
fuzzystrmatch	문자열 사이의 유사성과 거리를 결정할 수 있는 기능 제공. 비슷한 소리의 이름을 동일한 코드로 변경하거나 입력 문자열에 대한 대표 코드를 구성해서 유사도를 체크하는 기능 제공
hstore	(키, 값) 쌍 세트를 저장하기 위한 Data 유형 제공
insert_username	누가 테이블을 변경했는지 사용자의 이름을 추적하는 기능 제공
intagg	공급되는 정수를 정확히 포함하는 정수 배열을 생성하는 집계 함수 지원
intarray	1차원 정수 배열에 대한 함수, 연산자 및 인덱스 지원
isn	EAN13, UPC, ISBN과 같은 ISN(Interational Standard Number)의 Data 유형 제공
lo	LO, BLOB와 같은 대형 객체 유지 관리 기능 제공
ltree	계층적 트리형 구조의 라벨트리 검색 제공 및 Data 유형 제공
moddatetime	테이블 내의 특정 Row의 마지막 수정 시간을 추적하는 기능 제공
old_snapshot	[Since. 14] old_snapshot_threshold를 지원하는 유틸리티 제공
pageinspect	디버깅 목적으로 유용한 낮은 수준에서 Database 페이지의 내용을 검사 기능 제공
pg_buffercache	Shared Buffer Cache 실시간 모니터링 기능 제공
pg_freespacemap	여유 공간을 반환하는 FSM(Free Space Map) 검사 기능 제공
pg_prewarm	Data를 Buffer Cache에 로드하는 방법 제공
pg_stat_statements	실행된 모든 SQL 문의 계획 및 실행 통계를 추적 기능 제공
pg_surgery	[Since. 14] 손상된 관계에 대한 조치를 위하여 강제 제거, 강제 고정 등의 함수 제공
pg_trgm	트라이그램 기반으로 영문 및 숫자 텍스트 유사성 측정 및 인덱스 검색 기능 제공
pg_visibility	비트 값 반환을 통해 VM 및 페이지 레벨의 가시성 정보를 검사하는 유틸리티 제공
pg_walinspect	[Since. 15] 낮은 수준의 PostgreSQL WAL(Write-Ahead Log) 내용을 검사하는 함수 지원
pgcrypto	암호화 함수 제공
pgrowlocks	Row 수준 잠금 정보 표시 기능 제공
pgstattuple	튜플 수준 통계 표시 기능 제공
plpgsql	PL/pgSQL 절차적 언어 지원
postgres_fdw	원격 PostgreSQL Server 외부 Data 래퍼 기능 지원
refint	참조 무결성 구현을 위한 함수 제공(외래 키 제약 조건을 확인)
seg	선분 또는 부동 소수점 간격을 나타내는 Data 유형 제공
sslinfo	PostgreSQL에 클라이언트가 제공한 SSL 인증서에 대한 정보 제공
tablefunc	Crosstab을 포함하여 전체 테이블을 조작하는 다양한 함수 지원
tcn	연결된 테이블에 대하여 테이블 내용 변경시, 리스너에게 알림 기능 제공
tsm_system_rows	테이블 샘플링 시, 반환할 Row 수를 제한하는 기능 제공
tsm_system_time	테이블 샘플링 시, 밀리초 단위의 시간 내로 출력 가능한 샘플을 반환하도록 시간 제한하는 기능 제공
unaccent	텍스트를 필터링하여 악센트를 제거하는 텍스트 검색 사전 제공
uuid-ossp	UUID(Universally Unique Identifier) 생성 기능 제공
xml2	XPath Query 및 XSLT 제공