PostgreSQL 설치 및 구성

PostgreSQL은 open source software로 BSD(Berkeley Distribution Software) 라이센스와 유사한 TPL (The PostgreSQL License)입니다. 

1. 소스 다운로드 위치

   http://www.postgresql.org/download/

   1) 소스 설치

      gunzip postgresql-9.4.5.tar.gz

      tar xf postgresql-9.4.5.tar

      configure

      make

      make install

      make install-docs

      make install-world 

   2) OS tool 사용시 (redhat 기준)

      yum install postgresql-server  (repository에 postgresql 버전 확인필)

   

      service postgresql initdb    

      chkconfig postgresql on   

  

    3) os 설정 (/etc/sysctl.conf)

Name	Description	Reasonable values
SHMMAX	Maximum size of shared memory segment (bytes)	at least 1kB (more if running many copies of the server)
SHMMIN	Minimum size of shared memory segment (bytes)	1
SHMALL	Total amount of shared memory available (bytes or pages)	if bytes, same as SHMMAX; if pages, ceil(SHMMAX/PAGE_SIZE)
SHMSEG	Maximum number of shared memory segments per process	only 1 segment is needed, but the default is much higher
SHMMNI	Maximum number of shared memory segments system-wide	like SHMSEG plus room for other applications
SEMMNI	Maximum number of semaphore identifiers (i.e., sets)	at least ceil((max_connections + autovacuum_max_workers + 4) / 16)
SEMMNS	Maximum number of semaphores system-wide	ceil((max_connections + autovacuum_max_workers + 4) / 16) * 17 plus room for other applications
SEMMSL	Maximum number of semaphores per set	at least 17
SEMMAP	Number of entries in semaphore map	see text
SEMVMX	Maximum value of semaphore	at least 1000 (The default is often 32767; do not change unless necessary)

 

2. 환경 설정

   1) Shell 설정

   linux 사용하는 shell에 따라 아래 설정 내용을 포함

   PGHOST or PGHOSTADDR       => postgresql 서버

   PGPORT (default :5432)   => postgresql 서버포트  

   PGUSER                   => User 계정

   PGPASSWORD  (설정파일 .pgpass)    

    

   psql postgresql://myuser:mypasswd@myhost:5432/mydb

   LD_LIBRARY_PATH=/usr/local/pgsql/lib

   export LD_LIBRARY_PATH

/sbin/ldconfig /usr/local/pgsql/lib (root에서 실행)

   PATH=/usr/local/pgsql/bin:$PATH

   export PATH

   MANPATH=/usr/local/pgsql/man:$MANPATH

   export MANPATH

   2) postgresql 설정파일

   - postgresql.conf 

     listen_addresses = '*'

     log_connections = on        (사용자 접속시 로그 남김)

     log_disconnections = on    (사용자 접속 off시 로그 남김)

    

    shared_buffers          : 시스템 메모리에 1/4로 설정

    (effective_cache_size는 전체 메모리에 1/2로 설정)

    wal_buffers               : write량이 많으면 default 값 증가 (shared 1/32 ~ 16MB)

    checkpoint_segments : write량이 많거나 많은 데이터 load 되면 

                                  default 값증가  (48MB (3 * 16MB),  보통 32 ~ 256)     

    checkpoint_timeout   : default 5분

    work_mem               : 많은 쿼리가 수행되면 default 값보다 증가

    autovacuum                 : 리소스 정리

   synchronous_commit :  on/off 설정 (off 설정시 데이터 loss 발생가능)   

   - pg_hba.conf

     type      database      user      cidr-address       method

     Host      all               all        0.0.0.0/0            md5

3. 제약사항

4. Postgresql 내부 구조

1) 전체 구조

2) shared buffer 요소

3) shared buffer 메모리 구조

5. 기본 SQL ( .psqlrc )

pg_ctl  -D datadir  start

pg_ctl -D  datadir  stop                          -- stop after finishing active trans   

pg_ctl -D  datadir -m fast stop                  -- immediate stop   

pg_ctl -D datadir stop -m immediate            -- emergency stop

                (oracle : shutdown abort)

pg_ctl -D datadir restart -m fast                 --  fast restart

pg_ctl -D datadir reload                           -- config reload

select pg_reload_conf();                          -- config reload

-- dirty page를 모두 disk로 내리는 작업

psql -c "CHECKPOINT"

-- 특정 database 접속을 제한

alter database test connection limit 0

-- 특정 사용자 접속을 제한

alter user test1 connection limit 0

-- 설정 파일을 사용한 특정 ip에 db/user 사용자 제한 (pg_hba_lockdown.conf)

type   database   user       cidr-address    method

local    alll          all         localhost         reject (or preer)

-- data pages preload

create extension pgfincore ( http://github.com/klando/pgfincore )

-- config reload된 항목 확인

SELECT name, setting, unit,(source = 'default') as is_default FROM pg_settings

            WHERE context = 'sighup'

            AND (name like '%delay' or name like '%timeout')

            AND setting != '0';

-- kill session 

kill -SIGHUP \`psql -t -c "select procpid from pg_stat_activity limit 1"`

psql -h hostname -p 5432 -d dbname -U username

# help  (\? : psql meta-command help,  \h : sql command help)

# \quit

psql -f exam.sql  ( exam.sql 파일 실행 )

psql postgresql://postgres:password@localhost:5432/dbname 
--command="COPY (SELECT ratingdata FROM test WHERE date='20150710') TO '/tmp/data.txt"

99. PostgreSQL 성능 (9.4 기준)

    PostgreSQL 9.4 can achieve more than 300,000 reads per second     on a 32-CPU server, and it benchmarks at more than 20,000

    write transactions per second with full durability.