1. http://www.postgresql.org
에서 프로그램 소스를 내려받아 압축을 푼다.
2. 다음과 같이 컴파일하고 설치한다.
# ./configure
# --prefix=/usr/local/postgres
# --enable-thread-safety
# --enable-depend
# --enable-multibyte
# --enable-locale
# --enable-nls='ko'
# --enable-odbc
# --with-openssl
# --with-python
# --with-perl
#
# make
# make install
3. postgres 계정을 생성한다.
# groupadd postgres
# adduser -d /home/postgres -g postgres postgres
# passwd postgres
4. directory의 퍼미션을 변경한다.
# chown -R postgres.postgres /usr/local/postgres
# chown -R postgres.postgres /home/postgres
# cd /usr/local/postgres
# chown -R root lib include
5. postgres 계정의 환경을 설정한다.
# su - postgres
# vi .bash_profile
export MANPATH=/usr/local/man
export PGDATA=/home/postgres/data
# source .bash_profile
6. 디비생성및 기초 셋팅작업
# initdb -E EUC_KR -W 또는...
# initdb -E UTF-8 --lc-collate=C -W
7. 서버기동 및 중단
기동 : pg_ctl start
중단 : pg_ctl stop -m smart|fast|immediate
재시작 : pg_ctl restart
재로딩 : pg_ctl reload
상태확인 : pg_ctl status
8. 유저 생성
# createuser username -P
9. DB 생성
# createdb -E EUC_KR -O username DBname
-E option 을 생략하면 initdb 시 설정한 encoding으로 설정됨.
-O option 을 생략하면 현재 접속한 user로 설정됨.
10. PG_HBA.conf 파일설정
# vi $PGDATA/pg_hba.conf
# "local" is for Unix domain socket connections only
local all all md5
11. client를 이용하여 접속한다.
# psql -U username DBname
[참고] postgresql.conf 설정
- FILE LOCATIONS (시스템과 직접 연관된 파일들에 대한 설정) -
data_directory = 'ConfigDir'
실제적인 PostgreSQL의 DB Data 폴더 설정이다.
PostgreSQL는 기본적으로 접근자 Shell의 환경변수중 PGDATA에 대해서 로딩하게 된다.
이걸 설정하면 PGDATA Enviroment가 없을때 잡게 되나 계정별로 주는것이 다소 나을 것 같다.
hba_file = 'ConfigDir/pg_hba.conf'
PostgreSQL의 접근제어 파일.
ident_file = 'ConfigDir/pg_ident.conf'
PostgreSQL의 Ident Authentication Maps 파일.
external_pid_file = '(none)'
PostgreSQL의 Process ID파일위치인데 기본적으로 PGDATA 또는 data_directory 쪽에
postmaster.pid 파일로 생성되고 Shutdown등에 이용되며, 따로 설정하지 않아도 된다.
- CONNECTIONS AND AUTHENTICATION (접속과 인증관련) -
listen_addresses = 'localhost'
PostgreSQl의 Listen Adress설정으로 Default Localhost로 되어 있다.
이경우 PostgreSQL가 장착된 서버 로칼만 가능하며 외부접근이 되지 않는다.
'*'로 설정할 경우 모든 Network Interface를 통한 접근이 가능하다.
만약에 서버 네트웍카드가 여러 개일 경우 접근을 허용할 IP를 설정한다.
listen_addresses = '192.168.0.10, 192.168.0.13'
port = 5432
PostgreSQL의 Service Port를 지정하는 것으로 기본 5432포트로 되어 있으나 변경도 가능하다.
외부에 노출되어 있는경우 바꾸어주는것이 좋을듯 하다.
max_connections = 100
최대 동시접속자의 수를 정하는 부분인데 이는 다소 주의해야 한다.
Shared Memory설정에 민감하며 1명당 최소 500Byte가 필요하다.
superuser_reserved_connections = 2
SuperUser의 접속을 위한 예비용으로 설정하는 것으로 Max Connection설정이 100일때
이를 2로하면 98명만이 동시접근된다.
unix_socket_directory = ''
TCP/IP접속만이아닌 Local에서는 Unix Socket으로 접속이 가능하며 네트웍보다는 좀 나은 성능을 보이긴한다.
이 설정을 해주지 않으면 /tmp에 .s.PGSQL.5432 과 .s.PGSQL.5432.lock 파일이 생성된다.
5432를 보든이 해당포트로 만약에 한 서버에 PostgreSQL를 여러개 띄울때 구분을 위함이다.
unix_socket_group = ''
Unix Socket파일의 Onwer는 역시 PostgreSQL Master User이지만 Group을 정할수 있다.
접근 제어를 시스템단계에서도 할수 있기 위한 배려, 미설정시 PostgreSQL Satart한 Master User의
Owner와 Group을 따라 간다.
unix_socket_permissions = 0777 octal
역시 시스템 Permission차원의 접근제어를 위한 퍼미션 설정이다.
따로 설정하지 않을시 srwxrwxrwx 로 설정된다.
rendezvous_name = ''
Apple사의 기술지원을 위한것이다.
- Security & Authentication -
authentication_timeout = 60
인증 오류시의 재인증까지의 TimeOut으로 기본 60초이며,
초단위 설정이 가능하고 문서를 보면 600초(600/60=10분)까지 되지만,
설정을 할 때 짧게 주되 상황에 따라 조절하면 된다.
너무 길게주면 오히려 리소스만 잡아 먹는다.
ssl = false
SSL 인증처리 여부.
password_encryption = true
create user나 alter user로 계정 생성 변경시에 ENCRYPTED 혹은 UNENCRYPTED로 지정하지 않을 때
자동으로 Password Encryption할지 여부를 주는 것으로 디폴트로 두는 것이 좋다.
krb_server_keyfile = ''
Kerberos 서버 키파일의 위치정보 설정.
db_user_namespace = false
User별 디비설정때 사용하는 옵션으로 Oracle의 SID와 같은 형태라고 보면 쉬울 듯하다.
기존에는 계정하나로 보통 다 접근이 되나 이경우는 계정@디비명 식으로 접근을 모두 해야 한다.
- RESOURCE USAGE (except WAL) PostgreSQL운영 리소스 제어 -
shared_buffers = 16
공유메모리의 설정으로 max_connections수의 2배는 설정을 해주어야 하며,
OS의 Shared Memory 설정까지만 설정이 가능하다.
권장사항은 최소 1,000단위로 설정을 해주는것을 추천한다.
work_mem = 1024
말그대로 작업용 메모리 상한선을 지정하는 것으로 Sorting(Order by),Distinct시나,
In, merge join등 결과를 만들어내기 위해 쿼리에 해당되는 정렬이나 임시 저장을 위한 공간 확보의 상한선을 주는것으로,
이는 모든 합계를 정하는게 아니라 각 쿼리당으로 설정값이 반영되므로 무턱대고 많이 주면 시스템 메모리가 커진다.
작업상에 어느정도를 잡아야 하나 실제 쿼리 수행후 나오는 데이타들의 크기를 대략보고 정하면된다.
KiloByte단위 설정으로 기본값은 1024kb나 최소 64까지가능하나 디폴트나 조금더 주면 충분하나
업무량에 따라 늘력주시되 주의할것은 총합이 아닌 각각의 쿼리질의당이므로 주의요망!!
(메모리가 무리 넉넉하시면 많이 주면 성능의 향상이 있다)
maintenance_work_mem = 16384 min 1024, size in KB
DML즉 Create , Alter등 실제적인 관리 목적의 작업이나 vauum시의 작용 메모리 사용 제한선으로
work_mem 설정보다는 쬐금더 준다. 현재 기본값은 약 16M정도로 많이 사용하는 부분은 아니지만,
복구작업이나 DML쿼리 전송이 많을때 많이 주면 빠른 처리가 된다. 그리 크게 설정할 필요까지는 없으니
디비 엎고 다시 넣거나 할 때에는 높여 주면 성능 향상이 된다.
max_stack_depth = 2048 min 100, size in KB
최대 Stack 수로 아무리 많이 준다해도 Kernel설정을 최대치로 놓으니 ulimit 로 설정시에는 많이 줄수 있으나
이는 너무많이주면 Crash가 일어날수 있다. 재귀호출함수나 복잡한 함수처리에는 조금더 설정해주면 좋으나
부턱대고 늘리는건 비추천. 역시 KiloByte단위이며 기본이나 기본보다 조금더 주는게 좋을것 같다.
- Kernel Resource Usage (PostgreSQL이 Kernel 리소스 이용에 대한 제어) -
max_files_per_process = 1000 min 25
각 PostgreSQL Process(Child Process)가 Open할수 있는 최대 파일수이다.
이는 Kernel설정값 file-max와 연관된것으로, Kernel에서 제한을 했다면 설정을 안해도 되지만
Unlimit로 되어 있거나 file-max로 이미 잡아 놓은 상태에서 그 범위내에서 처리할ㅤ때
조정하는 것으로 너무 많이 설정하면 에러가 난다.
preload_libraries = ''
서버 Start시에 함께 메모리에 적재할 Library를 설정하는 것이다.
PL(Procedure Language) Library에 대해 미리 설정하면 PL 로 작성된 함수들의 처리에 빠른 속도를 낼수 있니다.
문법은 '$libdir/plXXX:plXXX_init' 식으로 python 라이브러리를 Start시점에서 적재하고 싶으면
'$libdir/plpythoni:plpythonu_init' 식으로 하면 된다. $libdir은 PostgreSQL 설치위치에서 자동으로 찾아오나
해당 라이브러리가 다른 곳에 있으면, 위치를 절대 경로 (/usr/local/lib)로 주면 되며 여러 개를 할 때는
,(콤마)로 구분하면 된다.
- Cost-Based Vacuum Delay -
vacuum_cost_delay = 0 0-1000 milliseconds
vacuum_cost_page_hit = 1 0-10000 credits
vacuum_cost_page_miss = 10 0-10000 credits
vacuum_cost_page_dirty = 20 0-10000 credits
vacuum_cost_limit = 200 0-10000 credits
PostgreSQL의 단편작업과 오류수정과 최적화를 위해 vacuum을 실행하는데
이 실행시 잠시 데이타에 Lock이 되거나 Delay가 생겨 본 작업수행에 다소
지장을 주는데 위 설정으로 Delay를 주어 다른 쿼리가 이미 수행중이거나
Transaction처리중에 Delay를 주거나 허용된 범위 이상으로 리소스를 찾지 않게 하는 것이다.
실제적인 update, delete..등이 자주 일어나거나하면 당연히 vauum해주어야만 한다.
이때 select 쿼리가 상당량을 차리할때 vauum을 해주면 딜레이가 생기니
적당성으로 실시간이나 한가한 시간대가 아닌 시간단위등으로 할 경우는 이옵션으로
본래의 Transaction에 최대한 지장을 주지 않도록 셋팅하면 좋다.
- Background writer -
bgwriter_delay = 200 10-10000 milliseconds between rounds
bgwriter_percent = 1 0-100% of dirty buffers in each round
bgwriter_maxpages = 100 0-1000 buffers max per round
역시 성능향상을 위한것으로 write Process의 Delay타임과 전체 리소스 퍼센트와
일정량까지 BackGrount Write가 시행될 Buffer량을 설정하는 부분이다.
이것 역시 시스템 사용량과 용도에 따라 적절히 설정하면되나 주석처리된 상태로 써도
문제될 것은 없다.
- WRITE AHEAD LOG -
fsync = off
fsync on(true)일 경우 Disk Write가 빈번히 일어나고 바로바로 Sync되므로,
성능(빠른 응답)을 원할때는 off(false)가 좋으나 안정성만을 위한다면 on(true)가 좋다.
시스템 Crash로 인한 복구에 롤백시점이 생기지 않기를 원한다면 on(true).
wal_sync_method = fsync
WAL의 File Sync 시의 방법에 관한것으로 디폴트 fsync이다.
fsync이외에 fdatasync , open_sync or open_datasync가 있다.
OS자체의 파일 함수를 쓰는데 open_sync는 O_SYNC로 WAL Log파일을 오픈하고
open_datasync는 O_DSYNC로 WAL Log를 오픈한다.
wal_buffers = 8
WAL의 버퍼 설정으로 최소는 4로 실제 쓰는 메모리의 량은 4*BKbyte이다.
디폴트로 두어도 상관없으니 쿼리속도만을 생각한다면 버퍼를 늘리면 좋겠지만,
버퍼에 너무 많이 가지고 있다가 Power Down이 올경우 그마만큼 복구시점의 손해가 오니
기본값이 일반적으로는 좋을 것 같다.
commit_delay = 0
WAL의 Commit(Disk Write) Delay설정으로 마이크로 세컨드설정으로 최대 10,0000 까지 된다.
WAL Buffer 설정 크기만큼 WAL Log를 담고있다 넘칠 때 실제 물리적 Disk에 Write할 때의 Delay시간이다.
- Checkpoints -
checkpoint_segments = 1
WAL의 체크포인트를 찍는 최대 거리를 정하는 것으로, 1 세그먼크당 약 16MB(16*1024의 텀이 생긴다.
즉 이 값을 많이 주면 복구시점의 PITR이 너무 많은 텀을 가지게 된다.
기본 3이지만 1을 추천한다. Check Point Segments를 너무 많이 주면
그 용량이 찰때까지의 성능 향상은 있지만 짧게 주는 것이 좋다.
checkpoint_timeout = 300
WAL Auto Check Point 간격의 시간을 정하는 것으로, 초단위 설정을 하면 된다.
디폴트는 300초이다. 너무 자주 찍어도 그렇고 너무 넓게 찍어도 그렇지만 시스템의 Transaction
분단위 처리량이 많은지 시간당 처리가 많은지와 최악의 상황의 복구시점들을 생각해서 적어 주되
역시 너무 짧아도 좋지 않고 너무 길어도 좋지 않다.
checkpoint_warning = 30
경고 메세지로 checkpoint_segments에 설정된 크기에 넘칠 때 해당 시간내에서
Write후 Flush 즉 비워지지 않고 계속 흘러 넘칠 때 경고메세지를 에러 로그에 남기에 하는 옵션이다.
0은 off이며 초단위 설정이며, 처음 WAL과 PITR의 주기와 셋팅을 체크할 때 켜두면 어느정도 튜닝에 도움이 된다.
- Archiving -
archive_command = ''
WAL Log파일등의 백업처리등에 쓰는 것으로 DAT나 기타 백업 미디어나 백업 Disk쪽으로
백업하는 일련의 시스템 Command이다. 문법은 다음과 같다.
archive_command = '/bin/cp -i %p /mnt/tape/%f > /var/log/wal-log-archiving.log'
식으로 사용한다. 문자열 포맷팅에는 2가지가 있는데 %p 는 WAL Log Archive 절대경로+파일명이며, %f는 WAL Log
Archive File명이다. Command Line작성시 Systeam 명령을 내리는 것으로 모두 다 절대경로로 사용해야 한다.
QUERY TUNING
- Planner Method Configuration -
이옵션은 자주 analyse나 vacuum등으로 각종 쿼리에 대한 탐색 최적화 plan을 해주는 기능으로 무조건 True로 두는게
좋다. 만약에 쿼리가 100,000만 쿼리를 뒤지는데 첫 실행후 2번째는 최적의 경로를 찾아 1,000레코드만 뒤지는등
성능 향상을 보여준다.
- Genetic Query Optimizer -
geqo = true
Genetic Query Optimizer의 가동여부 결정. 위의 결과적 튜닝이 아닌 유연한 쿼리 튜닝이라고 한다.
geqo_threshold = 12
Query문에서 From 절의 갯수제한으로 12개 이상의 From이 들어간 쿼리에 대해 Genetic Query Optimizing을 한다.
geqo_effort = 5
GEQO의 쿼리 최적화 Plan시의 여러가지를 해보는 그 범위를 설정하는 것이다.
많이주면 그마만큼 최적화는 되겠지만, 괜한 시간 소비가 일어 난다.
최대 10, 기본값(5) 정도가 적당할 듯 싶다.
geqo_pool_size =
GEQO가 사용할 Pool Size로 보통들 100~1,000정도 쓴다고 문서에 되어 있다.
0으로 두면 geqi_effort의 값과 쿼리에서 호출하는 Table의 수를 감안해서 자동 계산하니 귀찮으신분은 0을 추천.
geqo_generations = 0
GEQO 최적화 알고리즘의 최대 루프수이다. 0 설정시 geqo_effort를 참조하게 된다.
- Other Planner Options -
default_statistics_target = 10
위 Query Optimize Planner(,ALTER TABLE SET STATISTICS)에서 처리되지 않는 쿼리에 대한 쿼리최적화로
값 증가는 역시나 많은 시간을 소요하지만 최적화 될가능성도 있기는 하니 적절히 상황에 맞게 사용한다.
from_collapse_limit = 8
이 옵션은 geqo_threshold 보다 적은 수를 주며 geqo_threshold 보다 적은 from 절을 가진 쿼리에 대한 최적화를 단행한다.
join_collapse_limit = 8
위와 같은것으로 쿼리의 Join절대 대한 최적화를 단행한다.
ERROR REPORTING AND LOGGING
- Where to Log -
log_destination = 'stderr'
서버 로그를 어디로 출력할 것인지 설정하는 것이다.
기본이 Standard Error인데 이 경우 화면에 출력된다.
하지만 syslog시에는 아래 syslog_**옵션에 영향을 준다.
따로 로그 서버를 둘 경우 syslog로 놓고 syslog_** 옵션을 통해
외부서버로 전송하는 것도 좋은 방법이다.
redirect_stderr = true
stderr에 대해 아래 설정된 로그파일 처리시에
Standard Error로 출력된것 모두 다 로그파일화 하느냐의 옵션이다.
log_directory = 'pg_log'
Log 파일을 저장할 디렉토리 지정으로 당연히 PostgreSQL 의 Owner와 Group 그리고 Permission이 설정된
디렉토리여야만 한다. 기본적으로 상대경로를 주면 PostgreSQL의 Data Folder에 pg_log식의 폴더가 생기며
따로 저장을 원하면 절대경로 /dep/log/pgsql 식으로 설정해주면 된다.
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
에러 로그파일에 대한 Log Rotate File구성으로 위와 같이 설정하면
postgresql-년(4자리)-월-일_시분초.log 로 생성된다. 만약에 분단위나 일단위로 하고 싶으면
log_filename = 'postgresql-%Y-%m-%d-%H.log' 식이다.
log_truncate_on_rotation = false
이 옵션을 아래 두개의 옵션을 통해 로그파일을 나누는 옵션으로 log_filename 에 지정한 형식이 아닌
파일 생성시간 부터의 시간이나 FIle Size를 통해 로그를 나눌 때 쓴다.
log_rotation_age = 1440
로그파일이 최초 생성시점부터 1440초 이상지나면 다른 파일이 생성되게 한다.
0은 Disable이다.
log_rotation_size = 10240
로그파일의 크기로 제안하는 것으로 10240byte면 1Mb단위로 파일을 나누어 저장하는 것이다.
syslog_facility = 'LOCAL0'
에러 로그를 syslog로 설정시 syslog daemon의 facility를 주는 것으로 LOCA0~7까지 있다.
syslog_ident = 'postgres'
Syslog 저장시 구분용 단어로 PostgreSQL DB 뭐 이런식으로 둘 수도 있다.
식별용 키워드이니 적당한 걸로 설정한다.
- When to Log -
* 에러 로그 레벨 설명 *
DEBUG[1-5] : 실제적으로는 필요없으나 PostgreSQL 개발자들에게 필요한 정보라고 보면 된다.
INFO : 유저레벨로 단순히 유저쪽에 알리는정보.
NOTICE : 유저에게 유용하게 알수 있거나 단순히 이렇게 하는게 좋겠어? 이건 주의해라는 정보.
WARNING : 유저 경고 정도, 이건 어떻게든 처리해라던지 Transaction Start가 안되었는데 Commit을 한다던지하는 경고.
ERROR : 현재의 업무수행을 중단할정도의 에러.
LOG : 관리자에게 필요한 정보류. 체크리스트 활동정보라던지 실질적인 DBA용 메세지.
FATAL : 현재의 쿼리 세션을 중단하게 하는 에러.
PANIC : 모든 세션에 대한 중단이 초래되는 원인에 대한 에러.
client_min_messages = notice
로그 기록상의 로그 Level의 설정하는 것으로 디폴트인 Notice를 두면
Notice,Warning,error 로그에 대해서 Client에 전송한다.
log_min_messages = notice
위와 같으며, 서버에 로그 기록 레벨을 정하는 것이다.
log_error_verbosity = default
이것은 로그 기록시의 해당 오류에 대한 정보에 대한것으로 terse로 설정하면 최대한 많은 에러로그에 대한
정보는 받을수 있으며 default와 verbose는 같은 옵션이다.
log_min_error_statement = panic
사용자의 쿼리에 대한 에러로그로 레벨로 설정값 아래 수위일때 저장된다.
만약에 DB Application에서 쿼리 Fail에 대한 정보를 따로 수집하지 않으면
레벨을 올려 서버에 남겨도 됩니다.
log_min_duration_statement = -1
이 옵션을 지정된 시간이상 Client의 질의 쿼리에 대해 작업할때 로그를 남긴다.
이건 초기 개발이란 런칭한지 얼마 안되거나 Tunning시점에서 품질 기준시간을 설정해
너무 지체되는 쿼리에 대해 Tunning할 때 필요하다. -1은 Disable이며 밀리세컨드로 지정할수 있다.
silent_mode = false
기본옵션으로는 화면에 에러 출력을 하는데 이걸 그냥 true하면 에러나도 아무런 메세지를 볼수 없다.
- What to Log -
debug_print_parse = false
debug_print_rewritten = false
debug_print_plan = false
Debug 레벨 설정시에 문법 Parsing, ReWrite, Plan에 대한 디버깅정보 출력 여부설정으로
레벨을 Debug[1-5]가 아니면 별 의미가 없다.
debug_pretty_print = false
Debug Level에서의 에러 로그시에 좀더 자세히 보기 쉽게 할 때,즉 풀어서 보여준다는 것.
log_connections = false
접속 로그
log_disconnections = false
접속해제 로그
log_line_prefix = ''
로그 기록시 원하는 패턴을 정하면 로그의 각 줄에 정한 패턴의 형식의 데이타가 앞에 붙어
좀더 라인단위 로그분석에 도움이 된다.
%u : 접속유저명
%d : 디비명
%r : 리모트접속자의 HostName (Resolve)또는 IP와 Port
%p : 프로세스 ID
%t : Unix TimeStamp (일반적으로 사람이 보기 쉬운(흔한) 형태의 시간
%i : command tag
%c : session id
%l : session line number
%s : session start timestamp
%x : transaction id
%q : stop here in non-session processes
%% : '%' (문자열 포맷팅 구분자로 %를 쓰니 쓰고 싶으면 %%로 써야만 한다.)
log_statement = 'none'
SQL쿼리 로그시에 원하는 부분만 남기고 싶을 때 none은 아무 것도 하지 않으며
mod(DML)(insert,update,delete,trunscate,copy from,prepare,explain등),
ddl (create,aler,drop),
all은 ALL
log_hostname = false
로그에서는 기본적으로 접속자IP를 기록하나 이걸 true화 하면 resolve또는 hostserver search(/etc/nsswaitch 설정기준으로)를 해서
해당 IP에 대한 Host명을 찾는다.
|
