PostgreSQL 15에서 pg_dbms_metadata 설치 및 사용하기

Oracle 데이터베이스에서 DDL(데이터 정의 언어) 객체를 추출할 때 사용되는 get_ddl 함수는 많은 개발자와 데이터베이스 관리자에게 유용한 도구로 자리 잡고 있습니다.

그러나 PostgreSQL에서는 이러한 기능을 기본적으로 제공하지 않습니다. 그렇다면 PostgreSQL에서 Oracle의 get_ddl 함수와 유사한 기능을 구현할 수 있을까요?

다행히도 PostgreSQL에는 pg_dbms_metadata라는 확장 모듈이 있어, DDL을 쉽게 추출할 수 있는 기능을 제공합니다. 이 확장 모듈은 Oracle의 get_ddl과 유사한 역할을 수행하며, 테이블, 인덱스, 사용자 권한 등의 DDL 정보를 가져오는 데 유용합니다.

본 포스트에서는 PostgreSQL 15에서 pg_dbms_metadata를 설치하고 사용하는 방법을 자세히 설명합니다. Docker 환경을 활용하여 간단하게 설치하는 과정을 살펴보겠습니다.

도커 환경 구축

1. Docker 컨테이너 실행

먼저, PostgreSQL 15가 설치된 Docker 컨테이너를 실행합니다. 아래 명령어를 사용하여 PostgreSQL 15 컨테이너를 생성합니다.

docker run --name pg15 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres 
-e POSTGRES_DB=mydb -p 5432:5432 -d postgres:15

이 명령어는 pg15라는 이름의 컨테이너를 생성하고, 데이터베이스 mydb와 사용자를 설정합니다.

2. 컨테이너에 접속

docker exec -it pg15 bash

3. 필요한 패키지 설치

컨테이너에 접속한 후, 필요한 패키지를 설치합니다. 이 단계에서 git, build-essential, postgresql-server-dev-15 패키지를 설치합니다.

apt-get update
apt-get install -y git build-essential postgresql-server-dev-15

pg_dbms_metadata 소스 코드 컴파일 및 설치

전체 과정 예시

• 소스 코드 다운로드: 소스 코드를 GitHub 등에서 다운로드합니다.
• 소스 코드 빌드: 터미널에서 make 명령어를 실행하여 소스 코드를 컴파일합니다.
• 설치: make install 명령어를 실행하여 컴파일된 바이너리 파일이나 라이브러리를 시스템에 설치합니다.
  요약
• make: 소스 코드를 컴파일하여 실행 파일 또는 라이브러리를 생성합니다.
• make install: 생성된 파일을 시스템의 특정 디렉토리에 복사하여 사용 가능하게 합니다.

다음으로, pg_dbms_metadata 리포지토리를 클론합니다.

git clone https://github.com/HexaCluster/pg_dbms_metadata.git
cd pg_dbms_metadata

5. 확장 모듈 빌드 및 설치

클론한 리포지토리에서 pg_dbms_metadata를 빌드하고 설치합니다.
make
make install

make와 make install은 소프트웨어를 컴파일하고 설치하는 데 사용되는 명령어입니다.

이 명령어들은 주로 C/C++와 같은 프로그래밍 언어로 작성된 소스 코드의 빌드 및 설치 과정에서 사용됩니다. 아래에서 각각의 명령어에 대해 설명하겠습니다.

1. make

make는 Makefile이라는 파일을 읽어 소스 코드를 컴파일하는 도구입니다. Makefile은 프로젝트의 빌드 규칙과 의존성을 정의한 파일로, make 명령어는 이 파일에 따라 소스 코드를 컴파일하고 링크하여 실행 가능한 프로그램이나 라이브러리를 생성합니다.

사용 예: 소스 코드가 수정되었을 때, make는 변경된 부분만 재컴파일하여 시간을 절약해 줍니다.

2. make install

make install은 make 명령어로 생성된 실행 파일이나 라이브러리를 시스템의 지정된 디렉토리에 설치하는 명령어입니다. 일반적으로 /usr/local/bin, /usr/local/lib 등의 경로에 파일을 복사합니다.

역할: 이 명령어는 사용자가 작성한 프로그램이나 라이브러리를 시스템에서 사용할 수 있도록 설정합니다. 즉, 빌드 후 해당 소프트웨어를 시스템의 PATH에 추가하거나 다른 프로그램에서 사용할 수 있게 만드는 과정입니다.

Postgresql 접속 후 진행

6. PostgreSQL에 접속하여 확장 모듈 설치

이제 PostgreSQL에 접속하여 pg_dbms_metadata 확장 모듈을 설치합니다.

psql -U postgres -d mydb

mydb=# CREATE EXTENSION pg_dbms_metadata;

CREATE EXTENSION 명령어는 PostgreSQL에 새로운 확장 기능을 추가합니다. 여기서는 pg_dbms_metadata를 데이터베이스에 설치하고 있습니다.

7. 테이블 생성

이제 employees라는 이름의 간단한 테이블을 생성합니다.

mydb=# CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50) NOT NULL,
    salary NUMERIC(10, 2)
);
CREATE INDEX idx_employee_name ON employees(name);

설명
CREATE TABLE 명령어는 새로운 테이블을 생성합니다.
SERIAL 데이터 타입은 자동 증가하는 정수를 생성합니다.
CREATE INDEX 명령어는 특정 열에 대한 인덱스를 생성하여 검색 성능을 향상시킵니다.

8. 새로운 사용자 생성 및 권한 부여

이제 새로운 사용자를 생성하고 데이터베이스에 대한 권한을 부여합니다.

mydb=# CREATE USER test_user WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE mydb TO test_user;

설명
CREATE USER 명령어는 새로운 사용자를 생성합니다.
GRANT CONNECT 명령어는 특정 데이터베이스에 대한 연결 권한을 부여합니다.

9. 역할 생성 및 부여
새로운 역할을 생성하고 test_user에게 부여합니다.

mydb=#CREATE ROLE manager;
GRANT manager TO test_user;

설명
CREATE ROLE 명령어는 새로운 역할을 생성합니다.
GRANT 명령어는 특정 역할을 사용자에게 부여합니다.

10. 변환 매개변수를 설정

mydb=#CALL dbms_metadata.set_transform_param('SQLTERMINATOR', true);
CALL dbms_metadata.set_transform_param('DEFAULT', true);

설명
CALL 명령어는 함수나 프로시저를 호출합니다. 여기서는 SQL 명령어의 끝을 처리하는 변환 매개변수를 설정하고 있습니다.

함수 설명

11. get_ddl

mydb=# SELECT dbms_metadata.get_ddl('TABLE', 'employees', 'public');

결과

mydb=# SELECT dbms_metadata.get_ddl('TABLE', 'employees', 'public');
                                                                                                  get_ddl
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 -- Table definition
 CREATE TABLE public.employees 
 (id integer DEFAULT nextval('employees_id_seq'::regclass) NOT NULL,
 name character varying(100)  NOT NULL,
 ,"position" character varying(50)  NOT NULL,
 salary numeric(10,2) );

 -- Constraints
 ALTER TABLE public.employees
 ADD CONSTRAINT employees_pkey PRIMARY KEY (id);

설명
SELECT 문은 dbms_metadata.get_ddl 함수를 호출하여 지정된 객체의 DDL을 반환합니다. 여기서는 employees 테이블의 DDL을 요청하고 있습니다.

12.get_granted_ddl

SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', 'test_user');

결과

mydb=# SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT', 'test_user');
       get_granted_ddl
-----------------------------
 GRANT my_role TO test_user;+

설명
이 쿼리는 test_user 사용자에게 부여된 역할 및 권한을 DDL 형식으로 추출합니다.

13.get_dependent_ddl

mydb=# SELECT dbms_metadata.get_dependent_ddl('INDEX', 'employees', 'public');

결과

mydb=# SELECT dbms_metadata.get_dependent_ddl('INDEX', 'employees', 'public');

                        get_dependent_ddl
-----------------------------------------------------------------
 CREATE INDEX idx_employee_name ON employees USING btree (name);+

설명

이 쿼리는 public 스키마에 있는 employees 테이블에 대한 인덱스 DDL을 추출합니다.

결론

이렇게 하면 PostgreSQL 15에서 pg_dbms_metadata를 설치하고 사용할 준비가 완료됩니다. 이 확장 모듈을 통해 DDL을 쉽게 추출하고 관리할 수 있어 데이터베이스 작업이 훨씬 수월해질 것입니다. 추가적인 질문이 있으시면 언제든지 말씀해 주세요!

참고 링크
https://github.com/HexaCluster/pg_dbms_metadata
https://www.postgresql.org/about/news/pg_dbms_metadata-v100-released-2778/