[PostgreSQL] Database 관리

■ PostgreSQL의 데이터베이스 속성

어떤 RDBMS든 스키마를 다루는 방법 혹은 개념이 약간 틀리듯이 PostgreSQL도 약간 스키마의 개념이 틀립니다.

PostgreSQL에서 데이터 베이스는 스키마의 집합이며 이 스키마에는 테이블, 펑션등이 포함됩니다.

전체 구조로 본다면 인스턴스 -> 데이터베이스 -> 스미카 -> 테이블 또는 기타 객체 구조로 되어 있습니다.

 

그림 1 : PostgreSQL 클러스터 구조

 

■ 데이터베이스 생성과정

postgresql 접속 후 데이터베이스를 조회하면 template0,1 이라는 데이터베이스가 있dp습니다.

이 데이터베이스는 템플릿 데이터베이스라고 해서 이름에서도 알수 있듯이 데이터베이스를 만드는 기초 정보들이 들어 있습니다.

그리고 이 템플릿 데이터베이스가 2개가 있다는것은 용도가 다르다는것을 의미합니다.

 

•template1 데이터베이스

create database 호출시 기본적으로 사용되는 템플릿 데이터베이스입니다. 이곳에 필요한 오브젝트들, 즉 사전 필요 테이블이나 PL/Perl등을 만들어 두면

신규 데이터베이스 생성시 이 템플릿을 이용해서 데이터베이스를 만들기 때문에 후속 작업이 생략되게 됩니다.

 

•template0 데이터베이스

완전히, 오로지 초기 오브젝트만으로 구성된 데이터베이스 템플릿입니다. 다른말로 '처녀' 데이터베이스라고도 합니다. 즉 아무런 수정도 가해지지 않은

순수 데이터베이스입니다. 이 템플릿 데이터베이스는 절대 수정해서는 안되며 초기 클러스터 생성시 생성된 그대로 두어야 합니다.

나중에 pg_dump로 덤프를 복원할 때 특히 유용합니다.

그리고 추후 template1의 데이터베이스가 망가지거나 기존 template1으로 만들어야 할때 template0 데이터베이스를 이용해서 만들어야 하기 때문에

절대로 변경을 가해서는 안됩니다.

 

•템플릿 데이터베이스 이용방법

가능하면 template0, 1은 사용하지 말고 새로운 템플릿 데이터베이스를 만들어서 회사만의, 혹은 개인만의 템플릿 데이터베이스를 만들어 사용하시는것을 추천드립니다.

클러스터 초기화로 만들어진 데이터베이스인 만큼 자신만의 데이터베이스를 만들어 관리하는것을 추천합니다.

 

 

■ 데이터 베이스 생성 및 카탈로그 정보

• 데이터베이스 생성 문법

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

 

• 기본 생성 방법

create database <database 이름>;

 

• 템플릿 데이터베이스를 이용해서 생성하는 방법

postgres> create database <database 이름> template template0;
shell> createdb -T template0 <database 이름>

 

• Database생성시 특정 유저에게 위임

postgres> create database <database 이름> owner <rolename>;
shell> createdb -O <rolename> <database name>

 

• pg_database 카탈로그에서 주의깊게 볼 컬럼

datistemplate 컬럼

f : 슈퍼유저나 템플릿을 복사한 유저만이 사용할 수 있음.

t : 어떤 유저든지 이용해서 데이터베이스 생성 가능. createdb 권한이 있어야 가능.

 

■ 데이터 베이스 변경

• 데이터베이스 변경 문법

ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

 

• 데이터 베이스 이름 변경

명령문 : ALTER DATABASE <database name> RENAME TO <database new name)

예제 :

postgres> alter database gamedb rename to herodb;
ALTER DATABASE
postgres> select datname, pg_authid.rolname as dbrollname
from pg_database
join pg_authid on pg_authid.oid = pg_database.datdba;
  datname  | dbrollname 
-----------+------------
 herodb    | postgres     <- gamedb에서 herodb로 변경
 template0 | postgres
 template1 | postgres
 postgres  | postgres
 userdb4   | user4

 

• 데이터 베이스 소유자 변경

명령문 :

ALTER DATABASE name OWNER TO {new_owner | CURRENT_USER | SESSION_USER}

예제 : 

postgres> \l herodb
                              List of databases
  Name  |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
--------+----------+----------+-------------+-------------+-------------------
 herodb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)

postgres> alter database herodb owner to user4;  
ALTER DATABASE
postgres> \l herodb
                             List of databases
  Name  | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
--------+-------+----------+-------------+-------------+-------------------
 herodb | user4 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)

 

■ 데이터 베이스 삭제

postgres> drop database <database 이름>
shell> dropdb <database 이름>

 

■ 데이터 베이스 카탈로그 조회

• 데이터베이스 리스트 조회 - 1

postgres=# \l (마지막에 콜론 붙이면 에러)
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sbtest    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | crtdb    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

 

• 데이터베이스 리스트 조회 - 2

select * from pg_database;
  oid  |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace |               datacl                
-------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------
 13593 | postgres  |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         13592 |          479 |          1 |          1663 | 
 16384 | sbtest    |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         13592 |          479 |          1 |          1663 | 
     1 | template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         13592 |          479 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
 13592 | template0 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         13592 |          479 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres}
 16550 | testdb    |  16545 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         13592 |          479 |          1 |          1663 | 
(5 rows)