PostgreSQL | 스키마(Schema) | 스키마 검색 경로를 설정하기

Tags:

• PostgreSQL
• Database

스키마 이름을 생략하여 테이블 등의 객체를 지정하는 경우에 실제로는 어떤 스키마 안의 객체인지 찾기 위해서 사용되는 스키마 검색 경로 설정 방법과 사용법에 대해 설명한다.

스키마 검색 경로란?

테이블 등의 객체는 스키마에 생성된 스키마마다 같은 이름의 테이블을 만들 수 있기에, 어떤 테이블을 명확히 하려면 “스키마명.테이블명"과 같이지정해야 한다.

예를 들어, schemaA 스키마에 staff 테이블이 있고, schemaB 스키마에 같은 이름의 staff 테이블이 있는 경우에 단순히 staff 테이블을 지정하면 두 스키마 중 staff 테이블인지 모르게 된다.

select * from staff;

테이블을 명확히 지정하려면 schemaA.staff과 schemaB.staff 등과 같이 “스키마명.테이블명"으로 지정해야 한다.

select * from schemaA.staff;

매번 스키마명을 넣어야 한다면 귀찮아 지기에 스키마명을 생략하기 위해 어떤 스키마인지를 설정할 수 있다. 이 설정이 스키마 검색 경로이다. 스키마 검색 경로에는 여러 스키마를 설정해 둘 수 있다.

스키마명1, 스키마명2 ...

예를 들어, 스키마가 생략하고 테이블명만 작성되면, 스키마 검색 경로의 시작부터 스키마에 테이블명이 존재하는지 여부를 확인하고 있다. 일치하는 테이블을 찾게 되면 그 스키마에 테이블명으로 판단하게 된다.

그럼 구체적으로 스키마 검색 경로의 사용법과 설정 방법에 대해 알아 보자.

스키마 검색 경로의 현재 설정 값을 확인하기

스키마 검색 경로에 설정되어 있는 값을 확인하려면 다음과 같이 수행한다.

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


devkuma=#

다음과 같이 조회하였다.

 "$user", public

스키마 검색 경로는 스키마를 쉼표(,)로 구분하고 있다. 앞에 언급되어 있는 "$user"는 현재의 역할과 동일한 이름의 스키마를 나타낸다. 현재는 postgres 역할로 연결되어 있어서 다음과 같이 기술되어 있던 것과 동일하다.

postgres, public

스키마 검색 경로가 사용되는 방법

스키마 검색 경로는 SELECT 등의 기존의 테이블을 찾는 경우와 CREATE 등의 새로운 테이블을 생성하는 경우에 사용되는 방법이 조금 다르다.

먼저 테이블을 찾는 경우이다. 테이블에서 데이터를 검색하는 경우에 스키마명을 생략하고 테이블명만을 작성하면 스키마 검색 경로에 나열된 스키마를 처음부터 순서대로 테이블이 존재하는지 여부를 확인하게 된다.

예를 들어, 스키마 검색 경로는 다음과 같이 되어 있다고 하자.

schemaA, schemaB, schemaC

여기에서 예를 들어 다음과 같이 스키마를 생략하고 테이블 이름을 지정하고 데이터를 검색하게 되면

select * from mytbl;

먼저 schemaA 스키마에 mytbl이 있는지 여부를 확인한다. 발견되면 schemaA.mytbl로 데이터를 가져온다. 발견되지 않으면 schemaB 스키마에 mytbl이 있는지 여부를 확인하게 된다. 이 과정을 이 스키마 검색 경로에 설정되어 있는 스키마의 수 만큼 반복하게 된다.

마지막까지 발견되지 않았던 경우는 에러가 발생하게 된다. 스키마 검색 경로에 기재되지 않은 스키마가 따로있더라도 체크되지 않는다.

–

다음은 테이블을 생성할 경우이다. 스키마명을 생략하고 테이블명을 작성하게 되면 스키마 검색 경로에 나열된 스키마가 존재하는지 여부를 확인할 것이다. 실재하는 스키마가 발견된 경우 그 스키마에서 테이블을 만든다.

예를 들어, 스키마 검색 경로는 다음과 같이 되어 있다고 하자.

schemaA, schemaB, schemaC

먼저 schemaA부터 확인하고 있고, 실제로 존재하는 스키마가 발견되면 경우에는 그 스키마가 현재 스키마되 된다.

여기에서 다음과 같이 스키마명을 생략하여 테이블을 작성하게 되면, 현재의 스키마에 테이블이 만들어 진다.

create table mytbl (...);

–

그러면 테이블을 만들어 보도록 하자. 현재 수퍼 유저 postgres 역할로 devkuma 데이터베이스에 연결되어 있다. 스키마 검색 경로는 기본 상태이므로 다음과 같이 되어 있다.

"$user", public

devkuma 데이터베이스에는 역할 이름과 같은 postgres 스키마가 생성되지 않으므로 스키마 검색 경로에서 최초로 실재하는 스키마는 public이다. 따라서 스키마를 생략하여 테이블을 만들게 되면 public 스키마에 테이블이 만들어 진다.

는 다음과 같이 실행하여 테이블을 작성하십시오.

devkuma=# create table memo (id integer, memo text);
CREATE TABLE
devkuma=#

테이블이 생성되었다. 확인을 위해 psql 메타 명령인 \dt를 실행해 본다.

devkuma=# \dt
      릴레이션(relation) 목록
 스키마 | 이름 |  종류  |  소유주
--------+------+--------+----------
 public | memo | 테이블 | postgres
(1개 행)


devkuma=#

public 스키마에 방금 만든 memo 테이블이 생성되어있는 것을 확인할 수 있다.

스키마 검색 경로를 변경하기

스키마 검색 경로를 현재 세션 동안만 일시적으로 변경하려면 SET 명령을 사용하여 변경한다.

SET search_path TO 스키마명1, 스키마명2, ...;

예를 들어 스키마 검색 경로를 myschema, public으로 설정하려면 다음과 같이 실행한다.

devkuma=# set search_path to myschema, public;
SET
devkuma=#

스키마 검색 경로가 변경되었다. 확인을 위해 show search_path을 실행해 보자.

devkuma=# show search_path;
   search_path
------------------
 myschema, public
(1개 행)


devkuma=#

스키마 검색 경로를 지정한 값으로 변경되는 것을 확인할 수 있다.

–

다음은 스키마 검색 경로를 영구적으로 변경하려는 경우에는 ALTER ROLE 명령을 사용하여 변경한다.

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

예를 들어, 스키마 검색 경로를 myschema, public으로 설정하려면 다음과 같이 실행한다.

devkuma=# alter role postgres set search_path to myschema, public;
ALTER ROLE
devkuma=#

스키마 검색 경로를 변경하였다. 확인을 위해 한번 PostgreSQL과의 연결을 종료하고 다시 접속을 실시 show search_path을 실행해 보자.

devkuma=# alter role postgres set search_path to myschema, public;
ALTER ROLE
devkuma=# \q

C:\Users\kimkc>psql -U postgres
postgres 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

postgres=# show search_path;
   search_path
------------------
 myschema, public
(1개 행)


postgres=#

스키마 검색 경로를 지정한 값으로 변경되는 것을 확인할 수 있다.

기본 값으로 되돌리려면 다음과 같이 실행한다.

alter role postgres set search_path to default;

–

모든 사용자의 기본 스키마 검색 경로를 변경하려면, PostgreSQL 설정 파일인 postgresql.conf 파일을 수정한다. (postgresql.conf 파일의 위치 등에 대해서는 postgresql.conf 파일 설정 방법“을 참조하도록 하자)

postgresql.conf 파일을 텍스트 편집기에서 연 후 다음과 같은 위치를 찾는다.

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

# - Statement Behavior -

#client_min_messages = notice		# values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   log
					#   notice
					#   warning
					#   error
#search_path = '"$user", public'	# schema names
#row_security = on
#default_tablespace = ''		# a tablespace name, '' uses the default

#search_path = '"$user", public'	# schema names

현재 search_path에 대한 설정은 주석으로 되어 있기에 첫 번째 #을 제거한다. 그러고 기본 스키마 검색 경로를 설정하고자 하는 값을 설정한다. 여기서는 다음과 같이 설정한다.

search_path = 'myschema, clientschema'    # schema names

변경했으면 postgresql.conf 파일을 저장한다. 그러고 PostgreSQL을 다시 시작한다.

그런 다음 PostgreSQL에 연결하여 show search_path을 실행해 본다.

C:\Users\kimkc>psql -U postgres
postgres 사용자의 암호:
psql (12.2)
도움말을 보려면 "help"를 입력하십시오.

postgres=# show search_path;
   search_path
------------------
 myschema, clientschema
(1개 행)


postgres=#

스키마 검색 경로의 기본 값이 변경되는 것을 확인할 수 있다.

여기까지 스키마 검색 경로의 설정과 사용법에 대해 알아보았다.