[PostgreSQL] Postgresql 강좌 - 2.DATABASE/SCHEMA 관리

이번 시간에는 2번째 postgreSQL 시간인 데이터베이스 관리와 수정 삭제 조회 그리고 SCHEAM까지 

배워보도록 하겠습니다.

 

1.DATABASE의 구조

postgrSQL의 DATABASE는 여러 개의 SCHEMA(스키마)들로 구성된 데이터베이스 입니다. 

SCHEMA는 OBJECT(오브젝트)들의 논리적인 집합을 말합니다.

 

SCHEMA는 TABLE(테이블), VIEW(뷰),SEQUENCE(수열), DOMAIN(도메인), FUNCTION(함수)

등의 OBJECT들로 구성되어 있습니다.

 

이러한 DATABASE들과 USER/GROUPS, TABLESPACES들이 모여 CLUSTER(쿨러스터)를 구성합니다.

데이터 베이스의 구조

2.DATABASE 소유

DATABASE를 생성하기 위해서는 SUPERUSER(슈퍼유저)이거나 

CREATEDB권한이 있는 USER여야 합니다.

 

SUPERUSER는 다른 USER가 만든 DATABASE를 소유할 수도 있습니다. SUPERUSER 이외 일반

USER들은 자신이 생성한 DATABASE만을 소유할 수 있습니다.

 

3.DATABASE 조회

현재 존재하고 있는 DATABASE를 조회하기 위해 pdAdmin의 Object Browser에서 검색하거나 

psql의 \l 명령어로 조회할 수 있습니다.

4.CREATE DATABASE

CREATE DATABASE 'name'을 입력해 DATABASE를 생성합니다.

CREATE DATABASE name

DATABASE 생성 option은 다음과 같습니다.

Option	기능
[[WITH] [OWNER [=] dbowner]	DATABASE를 소유하는 USER를 지정합니다. 따로 지정하지 않을 경우 명령을 실행시킨 USER가 주인이 됩니다.
[TEMPLATE [=] template]	지정한 TEMPLATE를 복제해 미리 정의 된 표준객체를 포함한  DATABASE를 생성할 수 있습니다.
[ENCODING [=] encoding]	DATABASE에서 사용한 ENCODING을 설정합니다. EX) SQL_ASCII
[ LC_COLLATE [=] lc_collate ]	컬럼과 인덱스, ORDER BY절에서 문자열에 적용되는 정렬 순서에 영향을 줍니다.
[LC_CTYPE [=] lc_ctype	문자 분류로 대 소문자 숫자분류를 설정합니다.
[TABLESPACE [=] tablespace]]	지정한 TABLESPACE가 DATABASE에서 만든 개체에 사용되는 기본 테이블이 됩니다.
[CONNECTION LIMIT [=] connlimit ] ]	DATABASE에 동시접속 할 수 있는 것을 제한합니다. DEFAULT로 -1(제한없음)으로 설정 되어 있습니다.

[DATABASE 생성 예제]

CREATE DATABASE T_D;

[DATABASE 생성 결과]

[DATABASE OWNER 지정 후 생성 예제]

CREATE DATABASE T_D2 OWNER TEST1;

5.ALTER DATABASE

ALETER DATABASE 'name'을 입력해 DATABASE를 변경합니다.

ALTER DATABASE name [ [ WITH ] option [ ... ] ]
  where option can be;

option은 다음과 같습니다.

Option	기능
CONNECTION LIMIT connlimit	DATABASE에 동시접속을 몇 개 까지 가능하게 할 것인지 설정 (-1: 일경우 무한대)

 

[DATABASE OWNER 지정 변경 예제]

ALTER DATABASE T_D2 OWNER TO TEST;

[DATABASE 이름 변경 예제]

ALTER DATABASE T_D RENAME TO T_D3;

[DATABASE INDEX SCAN 사용 변경 예제]

ALTER DATABASE T_D3 SET ENABLE_INDEXSCAN TO OFF;

[DATABASE  삭제]

DATABASE를 삭제할때는 DROP DATABASE 'name을' 입력해 DATABASE를 삭제합니다.

DROP DATABASE name

[DATABASE  삭제]

DROP DATABASE T_D2;

6.SCHEMA 구조

SCHEMA는 OBJECT들의 논리적인 집합을 말합니다. SCHEMA는 TABLE, VIEW, SEQUENCE, SYNONYM, DOMIAN, FUNCTION동의 OBJECT들로 구성되어 있습니다.

 

SCHEMA를 사용하는  이유는 논리적 집합체를 만들어 괸리의 편의성을 높이고 여러 USER들 간의 간섭 없이

접속할 수 있게 합니다.

7.SCHEMA 조회

현재 존재하고 있는 SCHEMA를 조회하기 위해 pgAdmin의 Object Browser에서 검색하거나 psql에서 \dn 

명령어로 조회할 수 있습니다.

\du

8. CREATE SCHEMA

1. CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]
 
2. CREATE SCHEMA AUTHORIZATION user_name [3. schema_element [ ... ] ]
 
4. CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ]
 
5. CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name

1. CREATE SCHEMA ‘schemaname’을 입력하여 SCHEMA를 생성합니다. 이름을 입력하지 않을 경우 USER의 이름이 SCHEMA이름으로 사용됩니다. (단 Pg_로 시작하는 이름은 스키마로 불가능 합니다.)

 

2. AUTHORIZATION ‘username’ 스키마를 소유한 USER의 이름을 입력합니다. 이를 생략할 경우 접속되어 있던 USER가 default값으로 저장되고 SUPERUSER만이 다른 USER가 소유한 SCHEMA를 만들 수 있습니다.

 

3. schema_element [...]을 입력하여 SCHEMA 내에서 객체를 정의하는 SQL문을 작성합니다. CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER, GRANT등이 포함될 수 있습니다.

 

4. CREATE SCHEMA IF NOT EXISTS ‘schema_name’ 을 입력하면 특정 이름이 SCHEMA에 없을 경우 그 SCHEMA를 생성합니다.

 

5. CREATE SCHEMA IF NOT EXISTS AUTHORIZATION ‘user_name’ 은 USER가 소유한 SCHEMA가 존재하지 않을 때 SCHEMA를 생성합니다.

 

 

[SCHEMA 생성 예제]

CREATE SCHEMA TEST_S;

USER를 지정해준 SCHEMA 생성 예제

CREATE SCHEMA TEST_S2 AUTHORIZATION TEST1;

TABLE과 VIEW를 추가해 SCHEMA 생성 예제

CREATE SCHEMA TEST_S3
  CREATE TABLE comtecopseq(table_name varchar(20));

• [그림] SCHEMA 생성 결과 pgAdmin3 화면

 

 9, ALTER SCHEMA

1. ALTER SCHEMA name RENAME TO newname
 
2. ALTER SCHEMA name OWNER TO newowner

1.ALTER SCHEMA name RNAME TO 'newname'을 입력해 SCHEMA의 이름을 변경합니다. (단 Pg_로 시작하는 이름은 스키마

로 불가능 합니다.)

2.ALTER SCHEMA name OWNER TO 'newowner'을 입력해 스키마를 소유한 USER의 이름을 변경합니다.

 

SCHEMA 이름 변경 예제

ALTER SCHEMA TEST_S2 RENAME TO ALT_TEST_S2;

SCHEMA 이름 변경 예제

ALTER SCHEMA TEST_S OWNER TO TEST1;

10.SCHEMA 삭제

SCHEMA 삭제 예제

DROP SCHEMA TEST_S;

11.SCHEMA SEARCH PATH

DATABASE에서 원하는 SCHEMA OBJECT에 접근하기 위해서는 'SCHEMA명.OBJECT명'의 형식으로 사용해야 합니다.

하지만 이 방식은 불편하기 때문에 SCHEMA를 DATABASE의 USER명과 동일하게 햐여 OBJECT를 바로 찾을 수 있도록

하는 것이 SCHEMA SEARCH PATH입니다.

 

SCHEMA SEARCH PATH 예

SHOW SEARCH_PATH;

강의 자료

SCHEMA 수정 및 삭제