Gentoo Linux에 Postgresql 8.3.1 설치

1. 설치 가능여부 확인
# emerge -pv =postgresql-8.3.1
!!! All ebuilds that could satisfy "=dev-db/postgresql-8.3.1" have been masked.
!!! One of the following masked packages is required to complete your request:
- dev-db/postgresql-8.3.1 (masked by: ~amd64 keyword)
위과 같은 에러가 나온다면 /etc/make.conf에 ACCEPT_KEYWORDS="~amd64" 을 추가한다.
해당 아키텍처에서 충분히 테스트 되지 않았거나 다른 문제로 아직 안정화되지 않았다는 의미다.

2. emerge
# emerge =postgresql-8.3.1
# passwd postgres

3. /etc에 심볼릭 링크 추가
아래와 같이 추가적인 설정을 한다.
# mkdir -p /var/lib/postgresql/data
# chown -R postgres:postgres /var/lib/postgresql/data
# su - postgres
# initdb -E UTF8 -D /var/lib/postgresql/data
# exit
# vi /etc/conf.d/postgresql
--------------------------------------------------------------------------------
PGDATA="/var/lib/postgresql/data"
--------------------------------------------------------------------------------
PostgreSQL 설정파일을 빨리 찾을 수 있도록 심볼릭 링크를 추가한다.
(대부분의 사람들은 /etc/postgresql 디렉토리에서 설정파일을 찾을 것이다)
# mkdir /etc/postgresql
# ln -s /var/lib/postgresql/data/pg_hba.conf /etc/postgresql/pg_hba.conf
# ln -s /var/lib/postgresql/data/postgresql.conf /etc/postgresql/postgresql.conf

4. 외부 접속 설정
리슨할 IP를 설정 (아래 두개중에 적당한 정책을 선택)
listen_addresses = '192.168.1.200'  # 192.168.1.200로 접속할 경우에만 접속허용
listen_addresses = '*'              # 서버에 할당된 모든 아이피로의 접속 허용
아래를 참고해서 적절히 설정
(인터넷을 통해 접속이 허용된다면 SSL이나 Kerberos를 사용할 것을 권장)
# vi /var/lib/postgresql/data/postgresql.conf
--------------------------------------------------------------------------------
#--------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#--------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
#port = 5432
max_connections = 100
# note: increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).  You
# might also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777         # octal
#bonjour_name = ''                      # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60            # 1-600, in seconds
ssl = on
#password_encryption = on
#db_user_namespace = off
--------------------------------------------------------------------------------

5. 내부 접속 설정
아래를 참고해 적절히 수정
# vi /var/lib/postgresql/data/pg_hba.conf
--------------------------------------------------------------------------------
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         postgres                          trust
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
hostssl all         all         *           md5
--------------------------------------------------------------------------------

6. 로그설정
# mkdir /var/log/pglog
# chown postgres:postgres -R /var/log/pglog
아래를 참고해 적절히 설정
# vi /var/lib/postgresql/data/postgresql.conf
--------------------------------------------------------------------------------
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
    # - Where to Log -
#log_destination = 'stderr'
# This is used when logging to stderr:
redirect_stderr = on
# These are only used if redirect_stderr is on:
log_directory = '/var/log/pglog'
log_filename = 'pgsql-%Y-%m-%d.log'
#log_truncate_on_rotation = off
#log_rotation_age = 1440
#log_rotation_size = 10240

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -
#client_min_messages = notice
#log_min_messages = notice
#log_error_verbosity = default
log_min_error_statement = error
#log_min_duration_statement = -1
#silent_mode = off
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
log_connections = on
#log_disconnections = off
#log_duration = off
log_line_prefix = '<%t> '
log_statement = 'ddl'
#log_hostname = off
--------------------------------------------------------------------------------

7. Log Rotation
# emerge logrotate
# vi /etc/logrotate.d/postgresql
--------------------------------------------------------------------------------
# Compress postgresql log files
# Provided by DouweQuerty
/var/log/pglog/pglog.tar {
  missingok
  ifempty
  rotate 7
  weekly
  sharedscripts
  prerotate
    cd /var/log/pglog/
    tar -C /var/log/pglog/ -cp --remove-files --wildcards -f pglog.tar --exclude *$(date +%Y-%m-%d)* *.log
  endscript
  postrotate
  endscript
}
--------------------------------------------------------------------------------
# echo "" > /var/log/pglog/pglog.tar
아래 명령을 실행해서 loglotate가 정성적으로 설정되었는지 확인한다.
# logrotate -f /etc/logrotate.conf

8. Install a Procedural Language (postgresql가 구동중인 상태에서 실해해야 한다.)
# /etc/init.d/postgresql start
# su - postgres
# createlang plpgsql template1

9. Installing JDBC driver (생략)
# emerge -av jdbc-postgresql

10. Finish and Start
# /etc/init.d/postgresql start
부팅시 로딩되도록 한다.
# rc-update add postgresql default

11.Postgresql Login
# su - postgres
# mkdir /var/lib/postgresql/data/bbs
# psql -U postgres
* Create user
CREATE USER bbs PASSWORD '<password>'
CREATEDB
VALID UNTIL 'infinity';
* Create tablespace
CREATE TABLESPACE bbs OWNER bbs LOCATION '/var/lib/postgresql/data/bbs';
* Create database
CREATE DATABASE bbs
WITH ENCODING='UNICODE'
OWNER=bbs
TABLESPACE=bbs;