::: IT인터넷 :::

PostgreSQL의 Database 관리

곰탱이푸우 2022. 5. 5. 08:20
오픈소스 데이터베이스인 PostgreSQL의 Database를 관리하는 방법에 대해 정리한다.
 
PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.
PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.
GitHub과 DockerHub 사이트는 다음과 같다.
 
PostgreSQL은 다음과 같은 구조를 가지고 있다.
각 기능은 다음과 같다.
  • Users/Groups - 사용자 정보 관리
  • Databases - 실제 레코드가 저장되는 Database 관리
  • Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
  • Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
  • Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
  • Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간
 
 

Database 관리

PostgreSQL의 데이터가 저장되는 공간이다. 
 
앞서 다룬 PostgreSQL 구조의 Databases 기능에 해당하며, 물리적으로 구분 된 저장 공간으로 이해하면 된다.
 

Database 목록 확인

psql 사용법의 'Database 전체 목록 확인' 항목을 참고한다.

Database 생성

사용자의 권한이 Superuser이거나 Create DB인 경우에만 Database를 생성할 수 있다.
Superuser는 다른 사용자의 Database를 소유할 수 있다. 일반 사용자는 불가능하다.
 
다음 명령으로 생성한다.
CREATE DATABASE name;
 
대표적인 option은 다음과 같다.
Default 값은 Docker로 제공되는 PostgreSQL 14.1 버전을 기준으로 한다.
Option
Default
Description
[[WITH] [OWNER [=] dbowner]
명령을 실행한 사용자
Database를 소유하는 사용자 지정
[TEMPLATE [=] template]
template1
지정한 Template를 복제하여 Database 생성
[ENCODING [=] encoding]
UTF-8
Database에서 사용할 Encoding 타입 설정
init_db 명령으로 UTF8 지정 필요
[ LC_COLLATE [=] lc_collate ]
en_US.utf8
문자 정렬 규칙 지정
컬럼, 인덱스, ORDER BY 정렬 순서에 영향
PostgreSQL 서버에 의존적
[ LC_CTYPE [=] lc_ctype ]
en_US.utf8
문자 분류
대문자, 소문자, 숫자의 변환 형식
PostgreSQL 서버에 의존적
[TABLESPACE [=] tablespace]
pg_default
Database의 테이블을 저장할 디스크의 물리 경로
[ CONNECTION LIMIT [=] connlimit ]
-1 (제한없음)
Database의 동시 접속 제한
 
 
다음과 같이 Database를 생성할 수 있다.
# CREATE DATABASE DB이름;
# 옵션을 생략하면 Default 값으로 지정
postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=# \l
                                List of databases
  Name     |  Owner   | Encoding |  Collate   |  Ctype     |  Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
template0  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
template1  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
testdb     | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
(4 rows)
 
Collate와 Ctype이 en_US.utf8로 표기된다.
PostgreSQL 이미지가 기본적으로 영문 버전만 제공하기 때문에 한글 적용이 어렵다.
EUC-KR을 사용할 수는 있지만, UTF8 기반의 지원을 위해 ko_KR.utf8로 설정하고자 하는 경우 아래 포스팅을 참고한다.
Owner를 지정하여 생성하는 경우 다음 명령을 실행한다.
postgres=# CREATE DATABASE testdb OWNER testuser;
CREATE DATABASE
postgres=# \l
                                List of databases
  Name     |  Owner   | Encoding |  Collate   |  Ctype     |  Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
template0  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
template1  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
testdb     | testuser | UTF8     | en_US.utf8 | en_US.utf8 |
 
Owner가 testuser로 변경 된 것을 확인할 수 있다.

 

 

Database 수정

Database 생성 후 설정을 변경해야 하는 경우가 있다.
SuperUser 또는 해당 Database의 Owner는 설정 변경이 가능하다.
이름 변경과 소유주 변경은 CreateDB 권한도 가능하다.
 
다음 명령으로 생성한다.
ALTER DATABASE name [ [ WITH ] 옵션 [ ... ] ];
 
대표적인 option은 다음과 같다.
Default 값은 Docker로 제공되는 PostgreSQL 14.1 버전을 기준으로 한다.
Option
Default
Description
[ RENAME TO newname ]
 
Database의 이름 변경
현재 사용중인 Database는 변경 불가
[ OWNER TO
{ newdbowner | CURRENT_USER | SESSION_USER } ]
 
Database 소유자 변경
현재 사용중인 Database는 변경 불가 (SuperUser 제외)
[ SET TABLESPACE newtablespace]
 
신규 TABLESPACE는 비어 있는 상태 필요
다른 사용자의 연결이 없어야 함
[ ALLOW_CONNECTIONS [=] allowconn]]
true
false면 아무도 접속할 수 없음 (template인 경우 적용)
[ CONNECTION LIMIT [=] connlimit ] ]
-1
(제한없음)
Database의 동시 접속 제한
[ IS_TEMPLATE [=] istemplate ]
false
Database을 template으로 사용하려면 true 적용
 
다음과 같이 Database 설정을 변경할 수 있다.
# DB Owner 변경
postgres=# ALTER DATABASE testdb OWNER TO postgres;
ALTER DATABASE
postgres=# \l 
                                List of databases 
  Name     |  Owner   | Encoding |  Collate   |  Ctype     |  Access privileges 
-----------+----------+----------+------------+------------+----------------------- 
postgres   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
... 생략 ...
testdb     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(4 rows)

# DB 이름 변경
postgres=# ALTER DATABASE testdb RENAME TO dbtest;
ALTER DATABASE
postgres=# \l
                                List of databases
  Name     |  Owner   | Encoding |  Collate   |  Ctype     |  Access privileges
-----------+----------+----------+------------+------------+-----------------------
dbtest     | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
postgres   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 
 

Database 삭제

사용하지 않는 Database는 삭제해야 한다.
 
SuperUser 또는 해당 Database의 Owner만 삭제할 수 있다.
Database의 데이터가 모두 삭제되고 복구가 되지 않으므로 주의해야 한다.
 
다음 명령으로 삭제한다.
DROP DATABASE [ IF EXISTS ] name;
 
IF EXISTS를 사용하면 삭제할 Database가 없어도 오류 대신 메시지만 출력한다.
 
다음과 같이 Database를 삭제할 수 있다.
# DB 삭제
postgres=# DROP DATABASE dbtest
DROP DATABASE
postgres=# \l
                                List of databases
  Name     |  Owner   | Encoding |  Collate   |  Ctype     |  Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
template0  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
template1  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

# IF EXISTS 사용
postgres=# DROP DATABASE testdb;
ERROR:  database "testdb" does not exist

postgres=# DROP DATABASE IF EXISTS testdb;
NOTICE:  database "testdb" does not exist, skipping
DROP DATABASE
postgres=#
 

'::: IT인터넷 :::' 카테고리의 다른 글

PostgreSQL의 Table 관리  (0) 2022.05.12
PostgreSQL의 Schema 관리  (0) 2022.05.09
PostgreSQL의 사용자 관리  (0) 2022.05.02
PostgreSQL의 psql 사용법  (0) 2022.04.28
WSL의 우분투 OS 경로 변경  (0) 2022.04.25