PostgreSQL의 Schema 관리

오픈소스 데이터베이스인 PostgreSQL의 Schema를 관리하는 방법에 대해 정리한다.

 

PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.

Docker로 PostgreSQL 사용하기

 

PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.

공식 PostgreSQL Docker 이미지에 한글 적용하기

 

GitHub과 DockerHub 사이트는 다음과 같다.

• DockerHub - https://hub.docker.com/_/postgres
• GitHub - https://github.com/docker-library/postgres

 

PostgreSQL은 다음과 같은 구조를 가지고 있다.

각 기능은 다음과 같다.

• Users/Groups - 사용자 정보 관리
• Databases - 실제 레코드가 저장되는 Database 관리
• Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
• Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
• Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
• Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간

 

 

Schema 관리

PostgreSQL의 데이터가 저장되는 공간이다. 

 

앞서 다룬 PostgreSQL 구조의 Schemas 기능에 해당한다.

Databases가 물리적으로 구분 된 공간이라면, Schemas는 논리적으로 구분 된 공간으로 이해하면 된다.

 

Schema에 대한 자세한 내용은 psql 사용법의 'Schema 확인' 항목을 참고한다.

 

Schema 목록 확인

Schema에 대한 자세한 내용은 psql 사용법의 'Schema 확인' 항목을 참고한다.

PostgreSQL의 psql 사용법

 

Schema 생성

다음 명령으로 생성한다.

CREATE SCHEMA [ IF NOT EXISTS ] [ schema_name ]
    [ AUTHORIZATION user_name ] [ schema_element [ ... ] ];

 

대표적인 option은 다음과 같다.

Default 값은 Docker로 제공되는 PostgreSQL 14.1 버전을 기준으로 한다.

Option	Default	Description
IF NOT EXISTS		중복으로 인한 오류 발생 회피 (중복이면 메시지만 출력)
schema_name	USER 이름	Schema의 이름으로 pg_로 시작하는 이름 사용 불가
AUTHORIZATION username	접속 중인 USER 이름	SuperUser만 다른 USER 소유의 Schema 생성 가능
schema_element		Schema 내에서 객체를 정의하는 SQL문 작성 CREATE [ TABLE, VIEW, INDEX, SEQUENCE, TRIGGER ] 와 GRANT 명령 포함 가능

 

다음과 같이 Schema를 생성할 수 있다.

# DB Schema 생성
postgres=# CREATE SCHEMA test_schema;
CREATE SCHEMA
postgres=# \dn
    List of schemas
    Name     |  Owner
-------------+----------
public       | postgres
test_schema  | postgres
(2 rows)

# DB Schema 생성 (소유자 지정)
postgres=# CREATE SCHEMA schema_test AUTHORIZATION testuser;
CREATE SCHEMA
postgres=# \dn
    List of schemas
    Name     |  Owner
-------------+----------
public       | postgres
schema_test  | testuser
(2 rows)

 

 

Schema 수정

다음 명령으로 생성한다.

ALTER SCHEMA name [ RENAME | OWNER ] TO newname;

 

Schema 수정은 Schema의 이름과 소유자 변경만 가능하다.

Schema의 이름이 pg_로 시작할 수 없는 점에 주의한다.

 

다음과 같이 Schema를 수정할 수 있다.

# DB Schema 이름 변경
postgres=# ALTER SCHEMA test_schema RENAME TO rename_schema;
ALTER SCHEMA
postgres=# \dn
    List of schemas
    Name       |  Owner
---------------+----------
public         | postgres
rename_schema  | postgres
(2 rows)

# DB Schema 소유자 변경
postgres=# ALTER SCHEMA schema_test OWNER TO postgres;
CREATE SCHEMA
postgres=# \dn
    List of schemas
    Name     |  Owner
-------------+----------
public       | postgres
schema_test  | postgres
(2 rows)

 

Schema의 SEARCH_PATH

Table과 같은 Schema 하위의 객체들은 Schema 마다 동일한 이름을 사용할 수 있다.

특정 객체를 지정하려면 Schema이름.객체명 과 같은 형태로 사용해야 한다.

 

예를들어 employer와 employee라는 Schema가 있고 각각 salary라는 table이 있다고 가정해보자.

 

salary의 전체 데이터를 조회하기 위해서는 다음과 같이 실행한다.

# salary Table의 모든 데이터 조회
postgres=# SELECT * FROM salary;

 

salary라는 Table은 employer와 emploee에 모두 정의 되어 있다.

PostgreSQL은 각각의 Schema를 순서대로 조회하면서 가장 먼저 매칭 되는 Table에서 해당 명령을 수행한다.

 

이렇게 Schema를 조회하는 순서를 SEARCH_PATH라고 한다.

 

employee Schema의 salary Table을 지정해서 조회하려면 다음과 같이 실행해야 한다.

# salary Table의 모든 데이터 조회
postgres=# SELECT * FROM employee.salary;

 

 

SEARCH_PATH 조회

SEARCH_PATH의 설정 상태를 확인하려면 아래와 같이 실행한다.

postgres=# SHOW SEARCH_PATH;
# show search_path;
  search_path
-----------------
"$user", public
(1개 행)

 

$user는 현재 사용자를 의미한다. postgres 계정을 사용하고 있으면 postgres가 된다.

먼저 postgres라는 Schema를 조회하고 그 다음 public Schema를 조회한다는 의미가 된다.

 

Schema 예제에서 생성한 Schema 들은 포함되지 않은 것을 알 수 있다.

 

SEARCH_PATH 설정

현재 세션에 일시적으로 적용하기 위해서는 SET 명령을 사용한다.

세션 종료 후 다시 접속하면 설정 정보는 사라진다.

SET search_path TO Schema1, Schema2, ...;

 

영구적으로 적용하기 위해서는 ALTER ROLE 명령을 사용한다.

ALTER ROLE user SET search_path = 스키마명1, 스키마명2 ...;

 

employee와  employer를 일시적으로 추가하기 위해서는 다음과 같이 설정한다.

postgres=# ALTER ROLE postgres SET search_path = employee, employer, "$user", public;
ALTER ROLE
postgres=# \q

$ psql -U postgres
postgres=# SHOW SEARCH_PATH;
                search_path
---------------------------------------------
rename_schema, schema_test, "$user", public
(1 row)

 

SEARCH_PATH 초기화

SEARCH_PATH의 특정 Schema만 삭제하는 것은 불가능하다.

대신 초기화 하고 재설정하면 된다.

 

SET 명령을 사용해서 일시적으로 적용한 경우 세션을 종료하고 다시 접속한다. RESET 명령을 사용하는 것보다 훨씬 간단하다.

RESET 명령을 사용해야 하는 경우 다음과 같이 실행한다.

postgres=# RESET SEARCH_PATH;
RESET
postgres=# SHOW SEARCH_PATH;
  search_path
-----------------
"$user", public
(1 row)

# 이후 SEARCH_PATH 설정 진행

 

ALTER ROLE 명령으로 영구 적용한 경우 아래와 같이 실행한다.

postgres=# ALTER ROLE postgres RESET search_path;
ALTER ROLE
postgres=# \q

$ psql -U postgres
postgres=# SHOW SEARCH_PATH;
  search_path
-----------------
"$user", public
(1 row)

# 이후 SEARCH_PATH 설정 진행

 

 

Schema 삭제

지정한 Schema를 삭제한다.

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ];

 

대표적인 option은 다음과 같다.

Option	Default	Description
IF EXISTS		Schema가 존재하는 경우만 삭제 (없으면 메시지만 출력)
CASCADE | RESTRICT	RESTRICT	삭제할 Schema 하위에 연결 된 객체에 대한 강제 삭제 여부

 

기본 값은 RESTRICT이므로 Table과 같이 Schema 하위에 연결 된 객체가 있으면 오류가 발생한다.

CASCADE 옵션을 지정하면 모두 삭제되므로, 사용에 주의해야 한다.