[PostgreSQL] 데이터베이스 관리(생성, 삭제, 조회, 변경 등)

데이터베이스 관리

 

PostgreSQL DB의 기본 구조에 따라 PostgreSQL는 하나의 클러스터에서 여러 개의 데이터베이스를 가질 수 있습니다.

https://checktech.tistory.com/6

[PostgreSQL] Database 구조 (1)

 

따라서, 시스템 데이터베이스(template0, template1)를 제외한 기본으로 생성되는 데이터베이스인 postgres 이 외 데이터베이스를 생성할 수 있습니다. 기본적인 데이터베이스 생성 및 삭제, 조회, 소유자 변경 방법에 대해 알려드리겠습니다.

 

 

Database 생성 1 (기본)

CREATE DATABASE [database_name] OWNER [username];

CREATE DATABASE pgtest OWNER postgres;

 

위와 같이 별다른 옵션을 사용하지 않고 생성했을 때, template1 데이터베이스를 복사하여 생성되는 방식입니다. 그래서 template1의 문자셋(ENCODING)이나 Collation(LC_COLLATE, LC_CTYPE) 값이 따라오게 되지요. 만약 데이터베이스를 생성할 때 문자셋과 문자정렬방식을 변경하고 싶다면, 아래와 같이 문자셋을 변경하여 데이터베이스를 생성하시면 됩니다.

 

Database 생성 2 (문자셋 변경)

CREATE DATABASE pgtest WITH OWNER postgres ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr’ LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;

 

 

Database 생성 3 (Default 테이블스페이스 지정)

CREATE DATABASE pgtest WITH OWNER postgres TABLESPACE pgdata;

 

 

Database 삭제

DROP DATABASE pgtest;

 

데이터베이스 삭제 시 아래와 같은 에러가 발생하는 경우가 있습니다. 이런 경우는 삭제 대상 데이터베이스를 사용중인 세션이 있기 때문에 나오는 오류로 연결되어있는 세션이 다 종료되어야지만 삭제가 가능합니다.

postgres=# DROP DATABASE pgtest;
ERROR: database “pgtest” is being accessed by other users
DETAIL: There is 1 other session using the database.

 

WITH (FORCE) 옵션을 추가하면 강제로 대상 데이터베이스에 연결되어 있는 세션을 끊고 삭제할 수 있습니다.

DROP DATABASE pgtest WITH (FORCE);

 

또는 아래와 같이 활성연결을 끊는 SQL문을 수행 후 삭제를 진행하여도 됩니다.

-- 아래 SQL문 수행 후 데이터베이스 삭제 시 활성연결이 남아있는 경우 재수행 후 삭제
postgres=# SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'pgtest';
 pg_terminate_backend
----------------------
 t
 t
 t
(3 rows)

postgres=# drop database postgres;
DROP DATABASE

 

 

Database 목록 조회

ALTER DATABASE [database_name] OWNER TO [user_name];

\l
또는
SELECT * FROM pg_database;

 

 

Database Owner 변경

ALTER DATABASE [database_name] OWNER TO [username];

ALTER DATABASE pgdata OWNER TO testuser;

 

Database Name 변경

ALTER DATABASE [current_database_name] RENAME TO [new_database_name];

ALTER DATABASE pgtest RENAME TO pgtest2;

* 단, 사용중인 Database는 이름 변경 불가

 

 

psql 내, 다른 Database 접속

\c [database_name] [user_name]

postgres=# \c pgdata postgres

 

 

데이터베이스 별 OID 값 조회

SELECT oid, datname FROM pg_database;

 

 

데이터베이스 별 사용량 조회

SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

 

 

현재 접속한 데이터베이스명 확인

SELECT CURRENT_DATABASE();