postgresql database 관리

postgresql 은 multi database 를 지원한다.

CREATE DATABASE

database 를 생성하기 위해서는 role 에 createdb attiribute 가 있어야 한다. (superuser 제외)

데이터베이스를 생성할때 template 에서 생성되는 데이터베이스에 복사를 하는 개념이다.

postgresql 에서는 이런한 template 이 두개 제공된다.

• template0
  • 1과 달리 변경이 안되며, 기본값을 가지는 template 이다. pg_dump 로 부터 restoring 할때 유용하다.
• template1
  • 기본적으로 제공되는 template
  • site-local 추가가 가능하다. 추가된 속성은 다음 생성되는 데이터베이스에 기본값이 된다.

-- 기본 적인 데이터베이스 설정
CREATE DATABASE test2
-- 속성값을 명시한 데이터베이스 설정
CREATE DATABASE test2
    OWNER chlee              -- 오너
    ENCODING utf8            -- 인코딩
    LC_COLLATE 'ko_KR.utf8'  -- 정렬 순서 , 문자 분류
    LC_CTYPE 'ko_KR.utf8'     -- 대문자, 소문자간의 변환 형식
    ALLOW_CONNECTIONS true   -- 접근허용 flag
    CONNECTION_LIMIT 1024    -- 최대접속허용수
    TABLESPACE testspace     -- 사용할 테이블스페이스
    TEMPLATE template0;      -- 사용할 template

bnf

https://www.postgresql.org/docs/12/sql-createdatabase.html

encoding

supported character set

collation (LC_COLLATE, LC_CTYPE)

postgresql 서버가 운영되는 운영체제에 의존적이며 다음 쿼리로 지원되는 collation 확인이 가능하다.

select * from pg_collation;

connection_limit

파라미터 max_connection 이 우선될 것으로 추정되며 테스트 필요.

ALTER DATABASE

alter 구문 를 통해 데이터베이스의 attribute 를 변경할 수 있다.

구문을 실행하기 위해서는 role에 createdb attribute 가 있어야 한다.

-- 데이터베이스 chlee 접근을 금지시킨다.
ALTER DATABASE chlee ALLOC_CONNECTIONS false;
-- 데이터베이스 이름 변경
ALTER DATABASE chlee RENAME TO chlee2;
-- 오너변경
ALTER DATABASE chlee OWNER TO chlee2;
-- 테이블스페이스 변경
ALTER DATABASE chlee SET TABLESPACE chleespace;
-- 파라미터 변경
ALTER DATABASE chlee SET configuration_parameter TO value;

bnf

https://www.postgresql.org/docs/12/sql-alterdatabase.html

DROP DATABASE

drop 구문을 통해 데이터베이스를 삭제할 수 있다.

superuser , owner 만 수행할 수 있다.

drop 구문은 rollback 이 불가능하기 때문에 사용시 주의해야 한다.

drop 구문 수행시 해당 데이터베이스에 connected 된 것이 있다면 수행이 불가능하다.

DROP DATABASE [IF EXISTS] chlee2;

bnf

https://www.postgresql.org/docs/12/sql-dropdatabase.html

issue

한글 정렬에 문제가 있다. 데이터베이스를 UTF8로 생성하면 LC_COLLATE 도 utf8 기준으로 설정 되는데

한글을 정렬할때 문제가 되기 때문에 데이터베이스 생성시 목적에 맞게 설정해야 한다.

https://jupiny.com/2016/12/12/sort-korean-in-postgresql/

Reference

• https://www.joinc.co.kr/w/Site/Linux/Locale
• https://www.postgresql.org/docs/10/manage-ag-templatedbs.html
• https://www.postgresql.org/docs/12/collation.html