컴퓨터이야기

공개용 데이터베이스 서버 PostgreSQL (2)

한동훈 (하이텔 리눅스동 대표시삽)

1. 들어가는 말

얼마 전에 PostgreSQL 6.2가 나왔고, 곧이어 며칠후에 6.2.1이 발표되었다.
PostgreSQL 6.2부터는 표준SQL 문법을 본격적으로 지원하겠다고 한다.
PostgreSQL 6.2에서는 많은 부분들이 수정되었는데, SQL 질의어와 관련하여 눈에 띄는 부분은 CREATE TRIGGER구분의 지원과 테이블 생성 시 특정 필드에 대한 NOT NULL과 DEFAULT값 지정, 필드의 값을 제한하고 체크하는 CONSTRAINT정도가 될 것이다. 이전에 이러한 작업은 인덱스나 룰을 통하여 충분히 처리할 수 있었으나, 어찌되었건 한결 편리해진 것만은 틀림없을 것이다. PostgreSQL 6.2.1은 6.2에 대한 패치 정도라고 보면 될 것같다. 특별한 기능 추가보다는 약간의 수정이 주를 이루고 있다. 아울러 PostgreSQL6.2나 6.2.1의 설치는 앞서 설명한 6.1.1의 설치 방법과 동일하다. 올해안에 표준 SQL문법을 완벽히 지원하기를 기대해 보면서 시작하도록 하자.

2.PostgreSQL 관련 문서들

PostgreSQL 을 배우는 데 도움이 되는 문서는 비교적 적다. 대부분의 문서들은 사용자가 SQL이나 다른 DBMS에 어느 정도 익숙하다고 가정하고 쓰여진 것 같다.
PostgreSQL에서 제공하는 사용자 매뉴얼은 95년의 POSTGRES95 1.0에 관해 설명하고 있다. 따라서 지금의 PostgreSQL과는 약간의 차이가 있다. 아울러 현재 PostgreSQL에서 새롭게 구현되거나 변경된 특징들은 POSTGRES95 1.0 매뉴얼에는 나와 있지 않다. 이러한 문서 부분의 부족함을 해결하기 위해 PostgreSQL 사이트에서 새롭게 이와 관련된 프로젝트가 진행되고 있으니 반가운 일이 아닐 수 없다.
PostgreSQL에서 아주 쓸만한 문서는 역시 매뉴얼 페이지이다. 표준 SQL명령 뿐만이 아니라 PostgreSQL에서 제공하는 UNIX 명령어들과 데이터 타입, ORACLE 호환 함수들, 라이브러리 루틴들에 대한 내용을 맨 페이지 형식으로 가장 최신의 정보를 담고 있다. 표준적인 설치가 끝났다면 /usr/local/pgsql/man 에 여러 맨 페이지들이 있으니 맨 페이지 경로를 추가한 이후 다음과 같이 온라인으로 활용할 수 있다.

$ man l create_sequence

그 위에도 PostgreSQL에서 제공하는 예제 소스들을 참고 하거나 PostreSQL사이트를 방문하여 메일링 리스트에 가입하면 여러 자세한 정보를 구할 수 있다.

3. PostgreSQL 외부 명령어

PostgreSQL은 데이터베이스 시스템의 관리를 위한 외부적인 툴을 제공한다.
이들 툴은 /usr/local/pgsql/bin에 있으며, 일반 UNIX명령어처럼 사용할 수 있다. 이 명령어들을 사용하려면 /usr/local/pgsql.bin이 당연히 검색 경로에 포함하는 것이 편리할 것이다. 이중 데이터베이스 관리와 직접적인 관련이 있는 명령은 대개 접속 포트, 데이터베이스 명, 인증 시스템 등 을 지정할 수 있는 옵션을 가지고 있다. postmaster에서 기본적으로 사용하는 포트는 5432 이지만, postmaster를 여러 개 띄운다던지 다른 특별한 이유 때문에 다른 포트를 사용할 경우에는 PGPORT 환경 변수를 이에 맞게 설정해 주던지, 아니면 일일이 명령행에서 -p옵션을 사용하여 포트를 지정해주어야 한다. 여기에서는 일반적으로 5432포트를 사용하는 것으로 간주한다. 이제 하나씩 살펴보도록 하자.

Initbd

이 명령은 직접적으로 사용할 기회가 없을 것이다. PostgreSQL 데이터베이스 시스템을 초기에 구축할 목적으로 설치 시에 한번 사용했을 것이다. 바이너리 패키지를 설치한다면 이미 데이터베이스 시스템이 구축되어 있을 것이기 때문에 이 조차도 사용할 필요가 없다.

Cleardbdir

이 명령은 PostgreSQL의 모든 데이터베이스를 파괴하는데 사용된다. 이 역시도 일반 사용자는 사용할 필요도 없고, 사용해서도 안된다. PostgreSQL 슈퍼 유저가 PostgreSQL데이터베이스 시스템을 다시 초기화하기 위해 데이터베이스를 모두 제거할 때 사용한다. 이 명령을 수행할 때에는 postmaster 프로세스가 돌아가지 않도록 하여야 한다.

pg_dump,pg_dumpall

이들 명령은 데이터베이스 형식이 다른 현재의 데이터베이스를 다른 버전으로 옮길 때 SQL 스크립트로 갈무리할 목적으로 사용된다. PostgreSQL에서는 버전이 다르면 데이터베이스의 형식이 달라진다. 즉, 몇주일 정도 흐르면 데이터베이스 형식이 달라지기 때문에 업그레이드를 하려면 먼저, 데이터베이스를 이들 프로그램을 사용하여 SQL 스크립트로 갈무리한 다음, 새로운 PostgreSQL에서 실행시켜 줘야 한다. SQL스크립트를 받아낼 때는 반드시 신버젼의 pg_dump, pg_dumpall프로그램을 사용해야 한다. 버전이 달라지면 대개 SQL문법도 약간 달라지게 되므로 구버젼의 dump 프로그램을 사용하여 갈무리하였다면 신버젼의 PostgreSQL에서 SQL스크립트가 제대로 작동하지 않을 것이다. 즉, 신버젼의 PostgreSQL를 설치하기 이전에 dump프로그램만 풀어낸 다음, 이 dump프로그램으로 구버젼의 PostgreSQL데이터베이스에서 SQL을 받아내야 한다. 6.2와 6.2.1에서는 이런 고생을 하지 않고, 그냥 사용하면 된다.

Pg_dump를 사용하면 특정 테이블이나 데이터베이스만 SQL로 받아낼 수 있다.
Pg_dumpall은 시스템의 전체 데이터베이스를 한꺼번에 받아낼 때 사용한다.
모든 데이터베이스를 dump하여 새롭게 설치한다면 다음과 같이 하면 된다.

$ pg_dumpall-o>유.out
$ psql-e templatel<유.out

이들 dump프로그램은 뷰와 룰, 부분 인덱스, 거대 객체는 제대로 처리하지 못한다는 것을 알아두자.

Createdb, destroydb

말 그대로 데이터베이스를 생성하고 제거하는 유틸리티이다. 특별히 데이터베이스를 뒷부분에 지정하지 않으면 사용자의 아이디와 동일한 이름의 데이터베이스를 생성하거나 제거한다. 물론, PostgreSQL슈퍼유저가 해당 사용자에게 데이터베이스 생성 권한을 부여 하여야 한다.

$ createdb test
$ destroydb test

만일 데이터베이스 생성 권한이 없는 사용자가 데이터베이스를 만들려고 한다면 다음의 에러가 나타날 것이다.

$ createdb test
WARN:user "ddoch" is not allowed to create/destroy databases
Createdb: database creation failed on test.
$

createuser, destroyuser

데이터베이스를 사용할 사용자를 추가하거나 제거하는 유틸리티이다. 이 명령을 사용하려면 PostgreSQL 슈퍼유저이거나 데이터베이스 사용자를 추가할 권한이 있어야 한다.

$createuser linuxer
$destroyuser linuxer

만일 데이터베이스 사용자 추가 권한이 없는 사용자가 데이터베이스를 사용할 사용자를 추가하려고 한다면 다음의 에러가 나타날 것이다.

$createuser linuxer
createuser: ddoch cannot create users.
$

사실 지금까지의 프로그램들은 본쉘 스크립트들이다. 살펴보면 알겠지만 psql을 사용하여 templatel 데이터베이스에 접속한 다음, 적절한 질의어를 실행하고 있다. 데이터베이스나 사용자를 추가하는 작업은 SQL 질의어에 기본을 두고 있다는 것을 알 수 있다.

pg_version

별 실용성이 없는 프로그램이다. Pg_version에 인자를 주어 실행하면 해당 디렉토리에 PG_VERSION 이라는 파일이 만들어지고, 버전정보가 간단히 들어간다.

Pg_id

이 역시도 잘 사용하지 않는 프로그램이다. 실행하면 자신의 계정에 해당하는 uid를 출력한다. /etc/passwd 의 세번째 항목과 동일하다.

Postmaster

postmaster는 PostgreSQL 데몬 프로세스이고, postgres는 PostgreSQL 백엔드 프로세스이다. 사실 이들 프로그램은 링크된 같은 프로그램이지만, 보통 postmaster를 실행시키면 postmaster가 postgres를 수행한다. 따라서 postgres를 바로 띄울 수도 있지만, 보통은 postmaster를 사용한다.
Postmaster는 frontend와 backend프로세스 사이의 통신을 관리한다.
아울러 공유 메모리 버퍼 풀과(test-and-set 명령을 지원하지 않는 기계에서) 세마포어를 할당한다. postmaster는 그 자체로는 사용자와 상호대화적으로 수행되지 않으며, 후 위 프로세스로 기동되어야 한다.

명령어 형식:
postmaster [-B n_buffers] [-D data_dir] [-S] [_a systerm]
[-b backend_pathname] [-d[debug_level]][-n]
[-o backend_options][-p port][-s]

⊙ -B n_bufferw

n_buffers는 postmaster가 backend 서버 프로세스를 위해 할당하고 관리할 공유 메모리 버퍼의 개수이다. 기본겂은 64이다.

⊙ -D data_dir

데이터베이스 디렉토리 트리의 루트로 사용할 디렉토리를 명시한다. 이 디렉토리는 PGDATA 환경 변수 값을 사용한다. 만일 PGDATA가 설정되어 있지 않다면, 사용되는 디렉토리는 $POSTGRESQLHOME/data이다. 환경 변수 값도 설정되지 않았고, 이러한 옵션도 지정되지 않았다면, 컴파일 시간에 설정된 기본 데렉토리가 사용될 것이다.

⊙ -S

postmaster 프로세스로 하여금 기동시에 침묵모드(?)가 되도록 지정한다.

이것은 사용자의 (제어) tty와는 상관없이 그 자신의 프로세스 그룹으로 기동 된다. 이 옵션은 디버깅 옵션과 함께 사용할 수는 없는데, 그 이유는 표준출력과 표준에러출력을 통해서 나오는 메시지는 모두 취소되기 때문이다.

⊙ -a system

frontend 응용프로그램이 postmaster 프로세스로 접속할 때 'system' 인증시스템을 사용할 것인지를 지정한다. 'system'을 명시하여 인증 시스템을 유효화 시킬 수 있으며,'nosystem'을 명시하여 인증 시스템을 취소할 수 있다. 예를 들어, 사용자로 하여금 Kerberos 인증을 사용하도록 허용하려면, '-a kerberos'처럼 사용할 수 있다. 인증을 거치지 않은 모든 접속을 거부하려면, '-a nounauth'처럼 사용할 수 있다.

⊙ -b backend_pathname

backend_pathname은, postmaster가 frontend 응용프로그램의 접속을 요청 받을 때, 호출할 PostgreSQL backend서버의 시행가능한 파일의 전체 경로명이다. 이 옵션이 지정되지 않았다면, postmaster는 이 실행가능한 파일을 postmaster가 호출된 디렉토리에서 찾으려고 할 것이다.(이 것은 postmaster가 호출된 경로명에서 찾는다는 것을 의미한다. 경로명이 지정되지 ㅇ낳았다면, "postgres"라 불리우는 실행가능한 파일을 찾기위해 PATH환경변수를 사용한다).

⊙ -d [debug_level]

debug_level 옵션 인자는 backend 서버가 출력할 디버깅 메시지의 양을 결정한다. Debug_level이 1이라면, postmaster는 모든 접속 트래픽을 추적할 것이며, 그외에는 아무런 일도 하지 않는다. 2 이상의 값이라면, 디버깅작업은 backend 프로세스가 담당하게 되고, postmaster는 backend의 환경과 프로세스 트래픽을 포함한 좀 더 많은 정보를 출력한다. Backend서버가 디버깅 메시지를 출력할 파일을 지정하지 않는다면, 디버깅 정보는 이들의 부모인 postmaster의 제어 tty상에 나타날 것이다.

⊙ -n , -s

-s와 -n은 backend가 비정상적으로 종료할 때, postmaster의 행동을 제어하는 데 사용된다. 이 옵션은 정상적인 작동에는 사용하지 않는 것이 좋다. 비정상적인 종료가 발생할 때 일반적인 전략은, 다른 모든 backend서버에게 종료할 것을 알리고, 공유 메모리와 세마포어를 다시 초기화 하는 것이다. 이렇게 하는 이유는, 잘못된 backend는 종료하기 이전에, 몇몇 공유된 상태를 망가뜨릴 수 있기 때문이다.

-s 옵션이 지정된다면, postmaster는 다른 모든 backend서버에게 SIGSTOP시그널을 보내서 중도에서 멈추게 할 것이다. 하지만 이 시그널은 프로세스 자체를 종료 시키지는 않을 것이다. 이러한 방법을 사용하면, 시스템 프로그래머는 모든 backend서버로부터 발생한 코아 덤프를 수집할 수 있다.

-n 옵션이 지정된다면, postmaster는 공유된 데이터 구조를 다시 초기화하지 않는다.

⊙ -o backend_options

postgres(unix)에서 backend_options에 지정된 옵션은 postmaster에 의해 기동된 모든 backend서버 프로세스에게 전달된다. 옵션 중간에 공백 문자가 들어 간다면 인용 부호로 묶어야 한다.

⊙ -p port
postmaster가 frontend 응용프로그램으로부터의 접속요청을 받아들일 인터넷 TCP포트를 지정한다. 기본 포트값은 5432이지만, PGPORT환경변수를 통하여 얼마든지 변경할 수 있다. 기본 포트값과는 다른 포트를 지정한다면, 다른 모든 frontend 응용프로그램의 사용자는, psql을 포함한 libpq를 사용한 응용프로그램을 기동할 때, 반드시 같은 포트를 (PGPORT 환경변수나 명령행 옵션을 사용하여)지정 해야한다.

Postmaster를 종료시킬 때, 가능하다면 SIGKILL을 사용하지 않는 것이 좋다.
이것은 비단 PostgreSQL에만 그런건 아니다. Httpd에서도 SIGKILL을 사용해서 httpd 서버를 죽이면 자식들은 살아서 돌아다니게 되므로 귀찮아진다. SIGTERM을 대신 사용하도록 하자.

$ps-ax|grep postmaster
3544 p1 S 0:00grep postmaster
386? S 0:00/usr/local/pgsql/bin/postmaster-S-B 512-o-F-D/usr/l
$kill-TERM 386

다시 한번 말하지만, postmaster를 죽이기 위해서 SIGKILL (또는 시그널 번호 9)을 사용하게 되면, postmaster에게 할당되어 있는 공유 메모리와 세마포어등의 시스템 자원을 종료할 때 해제하지 못하게 된다.
다음 명령은 postmaster를 5432기본 포트 상에 침묵 모드(?)로 띄운다.

$postmaster-S

다음 명령은 postmaster를 1234포트상에 띄우고, 공유메모리 버퍼를 4메가(8K*512)를 할당하고, 디스크 캐슁을 켜기 위해 백엔드 옵션으로 '-F'를 전달하고 있다. 데이터베이스 디렉토리로 /usr/local/pgsql/data를 설정하고 있다. psql을 사용하여 이 postmaster에 접속하려면, psql 명령행에서 -p 1234를 지정하거나, PGPORT 환경변수값을 1234로 설정하여야 한다.

$postmaster-S p 1234-B 512-o-F-d/usr/local/pgsql/data

postgres

postgres [-B n_buffers] [-D data_directory] [-E] {-F}
[-P filedes] [-Q] [-S n_buffers] [-e]
[-d debug_level] [-o output_file] [-s] [dbname]

postgres 옵션도 postmaster 와 유사함을 할 수 있다.
postmaster 에서 -o뒤의 인자는 postgres에게 전달된다.

Psql

전월호에서 설명한 바 있다. psql은 정말 유용한 프론트엔드 프로그램이다.
간간이 중요한 옵션을 실전에 사용해 볼 것이다.

pg_passwd

pg_passwd는 PostgreSQL에서 비밀번호 파일을 처리하는 도구이다.
$PGDATA/pg_hba.conf (일반적으로는, /usr/local/pgsql/data/pg_hba.conf)와 관련이 있다. pg_hba.conf는 뒤에서 설명하겠지만 PostgreSQL의 데이터베이스 접근 제어를 처리하는 파일이다. pg_hba.conf 파일의 중간 쯤에 다음과 같은 줄이 있을 것이다.

host all 127.0.0.1 255.255.255.255 trust

이것은 로컬 호스트에서 접속하는 모든 사용자를 일단 믿고, 모든 데이터베이스로의 접속을 허용한다는 뜻이다. 자신의 사이트에 마음에 들지 않는 괴팍한 사용자들이 있어서 비밀번호를 걸어두고 싶다고 하자. 그렇다면 이것을 다음과 같이 바꾸면 된다.

host all 127.0.0.1 255.255.255.255 password passwd

여기에서 다섯번째의 'password'는 127.0.0.1(로컬 호스트)에서 접속하는 사용자에게는 비밀전호 매카니즘을 적용한다는 이야기이며, 마지막의 'passwd'는 $PGDATA/passwd (보통, /usr/local/pgsql/data/passwd)파일을 비밀번호 파일로 사용한다는 뜻이다. 즉, 시스템 전체에는 /etc/passwd가 비밀번호 파일로 사용된다면, PostgrreSQL 내부의 기본적인 비밀번호 파일로는 $PGDATA/passwd 파일을 사용한다는 것이다. 이 파일은 pg_passwd프로그램을 사용하여 PostgreSQL 슈퍼유저가 생성할 수 있다. 백업파일은 같은 디렉토리에 passwd.bk로 저장된다.

$ cd/usr/local/pgsql/data
$ pg_passwd passwd
Username:linuxer
New password: (비밀번호 입력)
Re-enter new password: (재입력)
$ cat passwd
linuxer:XJYDbjVQfFbRs (사용자 아이디와 암호화된 비밀번호)
$

이제 로컬 호스트에서 접속하는 linuxer라는 사용자는 비밀번호를 입력하지 않으면 데이터베이스에 접속할 수 없다.

$ psqlinux
Connection to database 'linux' failed.
Failed to authenticate client as Postgres user 'linuxer' using the default authentication type: be_recvauth:host-based authentication failed
$

비밀번호를 입력하려면 psql에서 '-u' 옵션을 사용하면 된다.

$ psql -u linux
Username: linuxer
Password:
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
Type＼? For help on slash commands
Type＼q to quit
Type＼g or terminate with semicolon to execute query
You are currently connected to the database: linux
Linux=>

로컬 호스트에 굳이 이렇게 비밀번호를 걸어둘 필요는 없을 것이다. 오히려 번거롭기만 하다. 따라서 특정한 호스트에서 접속하는 경우에 사용자의 확인을 거칠 필요가 있다면, pg_passwd 유틸리티를 사용하면 유용하다.

4. 데이터베이스 접근 제어와 원격 접속: pg_hba.conf

PostreSQL 에서 pg_hba.conf는 아주 유용한 파일이다. 보통 PostgreSQL 슈퍼우저만이 읽을 수 있도록 해두는 것이 좋다.

$ Is -l pg_hba.conf
-r--------1 postgres postgres 3776Now 11 08:47 pg_hba.conf

pg_hba.conf 파일에서 첫번째 항의 'host'는 해당 레코드의 성격을 나타낸다.
즉, 호스트 접속과 관련된 레코드 임을 알 수 있다.
두번째 항은 접속을 허용할 데이터베이스이다. 'all'은 모든 데이터베이스를 통칭한다. 세번째 항은 접속을 허용하는 호스트의 IP 주소이다. 접속을 요청하는 호스트의 주소는 먼저 pg_hba.conf의 네번째 항의 마스크와 연산되어 IP주소와 비교된다. 따라서 192.168.1.1과 192.168.1.2등의 호스트에서의 접속을 허용하려면 MASK는 255.255.255.0로, IP 주소는 192.168.1.0으로 하면 된다. 다섯번째 항은 사용자인증 방법을 지정한다.
'trust'는 인증 절차를 거치지 않음을 나타낸다. 'ident'는 RFC 1413에 따른 ident 프로토콜에 따라 인증 절차를 거친다는 것을 뜻한다. 마지막항은 인증에서 좀 더 세부적인 사항을 지정한다.

#TYPE DATABASE IP_ADDRESS MASK USERAUTH MAP
host all 127.0.0.1 255.255.255.255 trust
host all 192.168.1.0 255.255.255.0 ident sameuser

만일 허용되지 않는 호스트에서 접속을 하려면 다음과 같은 메시지가 나온다.

$psql-h free.world.co.kr linux
Connection to database 'linux' failed.
Failed to authenticate client as Postgres user 'postgres' using the default
Authentication type : be_recvauth: host-based authentication failed
$

psql에서 -h 옵션은 접속할 호스트 이름을 지정하는 데 사용된다. 만일, 원격 데이터베이스 시스템의 pg_hba.conf에 자신의 호스트가 접속할 수 있도록 되어 있다면, 자신의 시스템에서 원격 데이터베이스에 접속할 수 있다.

[ddoch@han ddoch]$psql-h free.world.co.kr mydb
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type\? For help on slash commands
type＼q to quit
type＼g or terminate with semicolon to execute query
You are currently connected to the database: mydb
mydb=>＼!hostname
han.world.co.kr
mydb=>

물론 psql은 자신의 시스템에서 실행되고, 입력하는 질의는 원격 호스트의 postmaster 포느에 전달되어 그 결과가 다시 자신의 시스템으로 돌아오는 것이다.

따라서, 이것은 telnet으로 원격 시스템에 로긴하여 psql을 실행하는 것과 같은 결과를 가져오기는 하지만, 그 과정은 전혀 다르다. 원격 데이터베이스 접속 기능을 활용하면, 데잍베이스는 하나의 데이터베이스 서버에 두고, 실행은 원격 클라이언트의 프론트엔드 응용프로그램에서 처리함으로써 여러가지 작업 처리를 분산시키고 체계화할 수 있는 장점을 가질 수 있을 것이다.

5. Kerberos 인증 시스템

대부분의 사용자는 이 정도의 인증 방법을 사용하면 무난할 것이다. 하지만, 보안상 주용한 데이터를 다룰 경우에는 이것으로도 부족하다. 전달되는 데이터 자체가 암호화되어야 할 필요성이 있다. 이를 위해 PostgreSQL는 kerberos 인증 시스템을 지원하고 있다. 하지만 PostgreSQL 자체에 Kerberos 패키지가 따라오지는 않는다. Kerberos는 ftp://authena-dist.mit.edu 에서 구할 수 있고, 자세한 문의사항은 info-kerberos@authena.mit.edu 에 해보기 바란다. 메일링리스트는 kerberos@athena.mit.edu 이고, 가입은 kerberos-request@athena.mit.edu 에 편지를 보내면 된다.
USENET뉴스 그룹은 comp.protocols.kerberos 이다.
미국과 캐나다 이외에 살고 있는 사용자의 Kerberos의 실제 암호화 코드의 사용은 미정부 수출 규제법에 의해 제한되므로 유의하는 것이 좋다.

원래 이 번호 부터는 데이터베이스의 실질적인 활용측면에 주력할 생각이였으나 궁리 끝에 PostgreSQL에 관해 처음부터 꼼꼼히 챙기는 방향으로 하는 것이 좋을 것 같아 실질적인 응용프로그램의 제작은 조금 늦추어 질 것 같다. 이점 양해를 바란다.

[출처] 공개용 데이터베이스 서버 PostgreSQL (2)|작성자 황제펭귄