PostgreSQL의 설치와 설정, 간단한 튜닝에 대해 데비안의 PostgreSQL 설치와 설정에서 다루었다. 이 페이지에서는 PostgreSQL의 실제적인 사용인 데이터베이스와 사용자 생성, 백업 및 복구에 대해 살펴본다.
데이터베이스와 사용자 생성
PostgreSQL은 데이터베이스와 사용자 생성을 위한 createdb, createuser 명령어를 제공한다. 여기서는 SQL문을 사용하여 데이터베이스와 사용자를 생성하는 방법을 볼 것이다. 먼저 사용자 생성을 보자. PostgreSQL은 데이터베이스에 대한 접근 권한을 롤(role) 개념으로 다룬다. 롤은 어떻게 설정하느냐에 따라 사용자 또는 사용자들의 그룹으로 생각할 수 있다. 롤은 롤들을 포함할 수 있다. 클러스터 개념과 비슷하게 대규모 조직을 위한 것이다.
사용자를 만들기 위해 선호하는 pgAdmin, DataGirp 등과 같은 PostgreSQL 클라이언트에서 다음의 SQL문을 실행한다. 예제를 쉽게 보여주고 세션의 개념을 설명하기 위해 나는 psql 클라이언트를 사용할 것이다.
# psql -h localhost -U postgres -d postgres psql (9.6.17) SSL 연결정보 (프로토콜: TLSv1.2, 암호화기법: ECDHE-RSA-AES256-GCM-SHA384, 비트: 256, 압축: off) 도움말을 보려면 "help"를 입력하십시오. postgres=# CREATE USER mydb; CREATE ROLE postgres=# CREATE ROLE developer LOGIN; CREATE ROLE postgres=# SELECT * FROM pg_roles; ... postgres=# DROP ROLE mydb; DROP ROLE postgres=# DROP ROLE developer; DROP ROLE postgres=# CREATE ROLE testdb_user WITH LOGIN PASSWORD '/root/.pgpass 파일에서 설정한 개발이나 응용프로그램에서 사용할 암호 입력'; CREATE ROLE
라인 8에 있는 SQL문처럼 CREATE ROLE에 LOGIIN이 들어가면 사용자를 생성한다는 의미이다. 라인 6의 CREATE USER는 디폴트로 로그인을 가정하기 때문에 LOGIN을 생략할 수 있다. 즉, 위의 라인 6과 8은 사용자를 만든다는 점에서 같은 SQL문이다. 라인 10의 SELECT * FROM pg_roles;로 롤을 확인할 수 있다. 라인 12와 14에서 DROP ROLE로 롤을 삭제한다. 라인 16의 마지막의 SQL문처럼 보통은 암호를 설정하여 사용자를 생성한다.
.pgpass 파일 설정은 데비안의 PostgreSQL 설치와 설정에서 다루었다. .pgpass 파일은 로컬시스템에서 ‘su -s /bin/bash postgres’ 명령문 없이 psql을 사용하기 위한 것이다. pgAdmin, DataGrip 등의 클라이언트를 사용하여 원격에서 접근하는 경우는 다른 암호를 사용할 수 있다.
다음으로 testdb_user 사용자가 사용할 데이터베이스를 생성해 보자.
postgres=# CREATE DATABASE testdb WITH ENCODING='UTF8' OWNER=testdb_user;
CREATE DATABASE
postgres=# \l
데이터베이스 목록
이름 | 소유주 | 인코딩 | Collate | Ctype | 액세스 권한
-----------+-------------+--------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | testdb_user | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
(4개 행)
postgres=# \c testdb
SSL 연결정보 (프로토콜: TLSv1.2, 암호화기법: ECDHE-RSA-AES256-GCM-SHA384, 비트: 256, 압축: off)
접속정보: 데이터베이스="testdb", 사용자="postgres".
testdb=# \c testdb testdb_user
SSL 연결정보 (프로토콜: TLSv1.2, 암호화기법: ECDHE-RSA-AES256-GCM-SHA384, 비트: 256, 압축: off)
접속정보: 데이터베이스="testdb", 사용자="testdb_user".
testdb=>
라인 1의 첫 번째 SQL문에서 CREATE DATABASE로 testdb 데이터베이스를 생성하였다. 라인 3처럼 \l 명령어로 데이터베이스의 목록을 확인할 수 있다. psql은 역슬래시(\)를 가지는 명령문을 제공한다. \? 실행으로 명령문에 대한 도움말을 볼 수 있다. 라인 15처럼 \c 명령어로 testdb로 연결하는 세션을 만든다. PostgreSQL은 세션 연결로 데이터베이스를 이동한다. MySQL, Microsoft SQL Server에서 데이터베이스 변경에 사용하는 ‘USE database_name;’문이 없다. Oracle과 비슷하게 세션의 개념을 사용한다. 이러한 세션의 연결과 종료로 데이터베이스를 이동하려면 클라이언트에서 기능을 제공해야 하는데 psql에서는 \c 명령어이다.
라인 15의 첫 번째 \c 명령은 postgres 관리자에서 사용자 지정 없이 \c testdb 명령을 실행하였다. 라인 17의 접속정보를 보면 사용자 postgres로 접속한 상태다. 이 상태에서 테이블을 만들면 그 테이블의 소유주는 postgres이고 testdb_user 사용자는 그 테이블에 접근할 수 없다. 따라서 라인 18의 \c testdb testdb_user처럼 사용자를 지정하여 testdb_user로 세션을 연결해야 한다.
백업 및 복구 예제를 위해 testdb 데이터베이스에 테이블을 만들고 몇몇 값을 입력해 보자.
testdb=> CREATE TABLE students(id INTEGER, l_name VARCHAR(20), f_name VARCHAR(20));
CREATE TABLE
testdb=> INSERT INTO students(id, l_name, f_name) VALUES (1, '홍', '길동');
INSERT 0 1
testdb=> INSERT INTO students(id, l_name, f_name) VALUES (2, '김', '길상');
INSERT 0 1
testdb=> SELECT * FROM students;
id | l_name | f_name
----+--------+--------
1 | 홍 | 길동
2 | 김 | 길상
(2개 행)
testdb=> \dt
릴레이션(relation) 목록
스키마 | 이름 | 종류 | 소유주
--------+----------+--------+-------------
public | students | 테이블 | testdb_user
(1개 행)
라인 1의 첫 번째 SQL문에서 CREATE TABLE로 students 테이블을 만들었다. 라인 3과 5에서 INSERT INTO를 사용하여 students 테이블에 데이터를 넣어주었다. 라인 7에서 SELECT를 사용하여 students 테이블의 값을 출력하였다. 라인 14의 \dt 명령어는 테이블 목록을 보여준다. 라인 18에서 보듯이 testdb_user 사용자의 세션에서 students 테이블을 만들었기 때문에 소유주는 testdb_user이다.
백업 및 복구
PostgreSQL은 백업 및 복구를 위해 pg_dump와 pg_restore 명령어를 제공한다. testdb 데이터베이스를 백업하기 위해 다음의 명령어를 실행한다.
# pg_dump -Fc -h localhost -U postgres -d testdb -f /root/testdb.dump
pg_dump에서 -F 옵션은 백업 파일의 포맷을 선택하는 것으로 c(custom)를 사용해야 pg_restore 명령어로 복구할 수 있다. -Fc 옵션은 디폴트로 파일을 압축한다.
복구를 테스트하기 위해 psql에서 기존에 존재하는 testdb 데이터베이스를 삭제하고 다시 생성한다.
testdb=> \c postgres postgres
SSL 연결정보 (프로토콜: TLSv1.2, 암호화기법: ECDHE-RSA-AES256-GCM-SHA384, 비트: 256, 압축: off)
접속정보: 데이터베이스="postgres", 사용자="postgres".
postgres=# DROP DATABASE testdb;
DROP DATABASE
postgres=# \l
데이터베이스 목록
이름 | 소유주 | 인코딩 | Collate | Ctype | 액세스 권한
-----------+----------+--------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3개 행)
postgres=# CREATE DATABASE testdb WITH ENCODING='UTF8' OWNER=testdb_user;
CREATE DATABASE
postgres=# \l
데이터베이스 목록
이름 | 소유주 | 인코딩 | Collate | Ctype | 액세스 권한
-----------+-------------+--------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | testdb_user | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
(4개 행)
postgres=# \c testdb testdb_user
SSL 연결정보 (프로토콜: TLSv1.2, 암호화기법: ECDHE-RSA-AES256-GCM-SHA384, 비트: 256, 압축: off)
접속정보: 데이터베이스="testdb", 사용자="testdb_user".
testdb=> \dt
릴레이션(relation) 없음.
첫 번째 줄에서 \c 명령어로 postgres 데이터베이스-postgres 사용자의 세션으로 변경하여 testdb에 대한 세션을 끊었다. 세션을 연결한 상태에서는 데이터베이스를 삭제할 수 없다. 라인 4에서 DROP DATABASE를 사용하여 testdb 데이터베이스를 삭제했다. 라인 6에서 \l 명령어로 testdb 데이터베이스를 삭제하였는지 확인하였다. 라인 17에서 CREATE DATABASE로 복구에 사용할 testdb 데이터베이스를 새로 생성하였다. 라인 31에서 \c 명령어로 testdb 데이터베이스- testdb_user 사용자의 세션으로 이동하였다. 마지막으로 라인 34에서 \dt 명령어를 사용하여 테이블이 없는 것을 확인한다.
testdb 데이터베이스를 복구하기 위해 다음의 명령어를 실행한다.
# pg_restore -v -h localhost -U postgres -d testdb /root/testdb.dump pg_restore: 리스토어 작업을 위해 데이터베이스에 접속합니다 pg_restore: SCHEMA "public" 만드는 중 pg_restore: COMMENT "SCHEMA public" 만드는 중 pg_restore: EXTENSION "plpgsql" 만드는 중 pg_restore: COMMENT "EXTENSION plpgsql" 만드는 중 pg_restore: TABLE "public.students" 만드는 중 pg_restore: "public.students" 테이블의 자료를 처리 중
pg_restore에서 -v 옵션은 진행 상황을 보기 위한 것이다. 이제 psql에서 복구가 되었는지 확인해 보자.
testdb=> \dt
릴레이션(relation) 목록
스키마 | 이름 | 종류 | 소유주
--------+----------+--------+-------------
public | students | 테이블 | testdb_user
(1개 행)
testdb=> SELECT * FROM students;
id | l_name | f_name
----+--------+--------
1 | 홍 | 길동
2 | 김 | 길상
(2개 행)
라인 1과 8에서 \dt 명령어로 테이블이 존재는지와 SELECT로 students 테이블의 값을 확인하였다. 복구에 성공한 것을 볼 수 있다.
백업 및 복구는 복잡하고 어려운 주제이다. 백업한 시점 이후에 추가하거나 변경한 데이터는 복구가 힘들 수 있다. 또한, 아주 큰 데이터베이스를 복구한다면 사용자에 대한 서비스를 오랜 시간 동안 중단해야 한다. 이런 문제를 해결하려면 많은 노력을 들여야 하고 전문적인 상용 제품들도 있다. 개인적인 사용이라면 백업 주기를 짧게 하여 데이터 손실을 적게 하는 것이 차선의 선택일 것이다.