Object - 데이터베이스,테이블스페이스,스키마

데이터베이스

 

Postgres에서 데이터베이스는 여러 스키마의 집합입니다.

Oracle과 다르게 데이터베이스는 스키마와 분리된 개념입니다.

데이터베이스 -> 스키마 순서로 접속 후 해당 스키마에 Object를 구성합니다.

 

postgreSQL은 최초에 template0,template1,postgres 3개의 데이터베이스를 생성합니다.

- template0 : template1이 문제가 생겼을 때 사용되는 데이터베이스

- template1 : 데이터베이스 생성 시 참조되는 기본 데이터베이스. 사용자의 필요에 따라 오브젝트 생성 가능

- 특정 template 옵션을 사용하지 않으면 template 1을 복제하여 데이터베이스를 생성

 

데이터베이스 생성을 위해서는 Superuser 또는 CreateDB 권한이 필요합니다.

 

생성구문

CREATE DATABASE name [ [ WITH ] OWNER [=] user_name . . . option [ . . . ] ]
	[ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LOCALE [=] locale ]
	[ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] 
	[ TABLESPACE [=] tablespace_name ] [ ALLOW_CONNECTIONS [=] allowconn ]
	[ CONNECTION LIMIT [=] connlimit ] [ IS_TEMPLATE [=] istemplate ] ]

파라미터 명 설명
name 생성할 데이터베이스의 이름
user_name 새 데이터베이스를 소유할 사용자의 Role 이름 
template 새 데이터베이스를 만들 template의 이름 
encoding 새 데이터베이스에서 사용할 문자 집합 인코딩
locale LC_COLLATE와 LC_CTYPE를 한번에 설정하는 shortcut
lc_collate 새 데이터베이스에서 사용할 데이터 정렬 순서
lc_ctype 새 데이터베이스에서 사용할 문자 분류
tablespace_name 새 데이터베이스와 연관될 테이블스페이스의 이름
allowconn true:연결 허용(default)/false:누구도 해당 데이터베이스에 연결 불가
connlimit 데이터베이스에 연결할 수 있는 동시 연결 수 (default -1,제한 없음)
istemplate true : CREATEDB권한이 있는 모든 사용자가 데이터베이스 복제 가능./ false : SUPERUSER 또는 데이터베이스 소유자만 복제 가능

 

생성예제

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)

postgres=# create database test1;
CREATE DATABASE

 

template값을 따로 지정하지 않으면 template1을 기반으로 데이터베이스가 생성됩니다.

 

postgres=# \l test1
                                             List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges
-------+----------+----------+-------------+-------------+------------+-----------------+-------------------
 test1 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |            | libc            |
(1 row)

 

template0을 기반으로 locale도 따로 설정한 데이터베이스 생성

postgres=# create database test2 locale 'sv_SE.utf8' template template0;
CREATE DATABASE

postgres=# \l test2
                                            List of databases
 Name  |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider | Access privileges
-------+----------+----------+------------+------------+------------+-----------------+-------------------
 test2 | postgres | UTF8     | sv_SE.utf8 | sv_SE.utf8 |            | libc            |
(1 row)

 

데이터베이스 변경예제

test1 -> test7로 변경

postgres=# alter database test1 rename to test7;
ALTER DATABASE

 

test2의 설정변수 변경

postgres=# alter database test2 set search_path='"$user",public';
ALTER DATABASE

postgres=# \c test2
You are now connected to database "test2" as user "postgres".
test2=# show search_path;
    search_path
--------------------
 """$user"",public"
(1 row)

 

 

테이블스페이스

 

테이블스페이스란 오브젝트(테이블,인덱스 등)가 저장되는 파일시스템의 위치입니다.

물리적인 저장구조는 $PGDATA/base/database_oid/object_id로 구성됩니다.

 

두개의 default 테이블스페이스가 존재합니다.

pg_default : 모든 사용자 데이터의 저장위치 ($PGDATA/base)

pg_global : 모든 global데이터(클러스터) 저장위치($PGDATA/global)

pg_tblspc : data디렉토리가 아닌 다른 특정 디렉토리를 지정하여 테이블스페이스를 생성하면 해당디렉토리의 경로가 pg_tblspc디렉토리에 생성된 심볼릭 링크를 통해 연결됩니다.

 

생성예제

CREATE TABLESPACE tablespace_name
[ OWNER { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory’
[ WITH ( tablespace_option = value [, ... ] ) ]

 

 

생성테스트

다른 경로에 테이블스페이스를 생성하고, pg_tblspc의 심볼릭링크를 확인

[postgres@ysbae2 ~]$ pwd
/home/postgres
[postgres@ysbae2 ~]$ mkdir pg_ts

 

[postgres@ysbae2 ~]$ psql
psql (15.5)
Type "help" for help.

postgres=# create tablespace dbspace location '/home/postgres/pg_ts';
CREATE TABLESPACE
postgres=# \db dbspace
            List of tablespaces
  Name   |  Owner   |       Location
---------+----------+----------------------
 dbspace | postgres | /home/postgres/pg_ts
(1 row)

[postgres@ysbae2 /postgres]$ cd pg_tblspc
[postgres@ysbae2 /postgres/pg_tblspc]$ ls
16411
[postgres@ysbae2 /postgres/pg_tblspc]$ ls -al
total 4
drwx------.  2 postgres dba   19 Jan  9 02:48 .
drwx------. 19 postgres dba 4096 Jan  9 01:51 ..
lrwxrwxrwx.  1 postgres dba   20 Jan  9 02:48 16411 -> /home/postgres/pg_ts

 

pg_tblspc의 안에 /home/postgres/pg_ts로 심볼릭링크가 걸려있습니다.

 

테이블스페이스 이름변경

postgres=# alter tablespace dbspace rename to new_dbspace;
ALTER TABLESPACE

 

테이블스페이스 소유주 변경

--디렉토리 생성
[postgres@ysbae2 ~]$ mkdir pg_ts2

--소유주용 유저 생성
postgres=# create user super_ts superuser;
CREATE ROLE

--테이블스페이스 생성
postgres=# create tablespace dbspace2 owner super_ts location '/home/postgres/pg_ts2';
CREATE TABLESPACE
--확인
postgres=# \db dbspace*
             List of tablespaces
   Name   |  Owner   |       Location
----------+----------+-----------------------
 dbspace  | postgres | /home/postgres/pg_ts
 dbspace2 | super_ts | /home/postgres/pg_ts2
(2 rows)

--다시 변경
postgres=# alter tablespace dbspace2 owner to postgres;
ALTER TABLESPACE
postgres=# \db *dbspace*
              List of tablespaces
    Name     |  Owner   |       Location
-------------+----------+-----------------------
 dbspace2    | postgres | /home/postgres/pg_ts2
 new_dbspace | postgres | /home/postgres/pg_ts
(2 rows)

 

스키마

스키마랑 다양한 오브젝트들의 집합을 의미합니다.

하나의 데이터베이스는 다수의 스키마를 소유할 수 있습니다.

스키마별로 동일한이름의 오브젝트가 존재할 수 있습니다.

스키마소유자는 스키마를 제어할 수 있습니다.

public 스키마는 create권한을 포함한 모든 권한이 부여된 기본 스키마입니다.

각 데이터베이스별로 각각의 public 스키마를 별개로 가지고 있습니다.

 

생성예제

postgres=# create database test1;
CREATE DATABASE

postgres=# \c test1
test1=# create schema myschema;
CREATE SCHEMA
test1=# \dn
       List of schemas
   Name   |       Owner
----------+-------------------
 myschema | postgres
 public   | pg_database_owner
(2 rows)

 

**추가테스트

데이터베이스의 스키마가 하나도 없는상태에서 object를 생성하려고 하면 에러가 발생합니다.

test1=# \c test1

test1=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

test1=# drop schema myschema;
DROP SCHEMA

test1=# create table t1(a int);
ERROR:  no schema has been selected to create in
LINE 1: create table t1(a int);

 

***추가테스트

test1의 public 스키마가 없는 상태에서 test2의 public 스키마의 여부를 확인

test1=# \c test2
You are now connected to database "test2" as user "postgres".
test2=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

각 데이터베이스별로 public 스키마는 따로 존재하는것을 확인할 수 있습니다.

 

제가 현재 운영중인 postgresql은 업무별로 데이터베이스를 따로 관리를 하고 있으며, 각 데이터베이스에는 하나의 스키마(public)만 존재하게 하여 데이터베이스 = 스키마를 동일하게 설정하여 관리하고 있습니다.