Docker를 이용해 PostgreSQL 설치

Docker를 이용해 PostgreSQL 설치


도커가 설치 되어 있는 상태에서 아래의 명령으로 설치 해줍니다.

우선 컨테이너를 PostgreSQL 공식 도커 허브에서 내려 받습니다.

# docker pull postgres:9.6.11
그리고 도커를 실행 해줍니다.

# docker run \
-e PGDATA=/data/pgdata \
--volume /home/postgres/pgdata:/data \
-e POSTGRES_INITDB_ARGS="--data-checksums -E utf8 --no-locale" \
--name postgres96 \
--publish 5432:5432 \
postgres:9.6.11

PGDATA는 도커안에서 postgresql 의 클러스터가 생성되는 경로입니다.


--volume $A:$B


$A - 실제 OS의 클러스터 데이터가 올라가는 경로

$B - 컨테이너 안에 생성되는 경로


이걸 잡아줘야 DB 생성 후 설정파일을 수정하기 쉽습니다.

더 다양한 옵션을 줘서 바로 DB를 생성 할 수도 있습니다.


그리고 docker run 으로 생성하고 나면 로그화면에서 빠져 나오지 않을 겁니다. 그냥 ctrl+c 로 중지하고,

# docker ps -a

로 ID를 확인한 후에

# docker start <ID>
해주시면 됩니다.



PostgreSQL 시간 조회



현재 시간 조회
 postgres=# select now();
현재 타임존 조회
 postgres=# show timezone;
타임존 변경 방법
 postgres=# SET TIME ZONE 'Asia/Seoul';
시스템 일자
 postgres=# select current_date, current_time, timeofday();
postgres=# select now(), current_timestamp, timestamp 'now';
년도 추출
 postgres=# select date_part('year', current_timestamp);
월 추출
 postgres=# select date_part('month', current_timestamp);
일 추출
 postgres=# select date_part('day', current_timestamp);
분 추출
postgres=# select date_part('minute', current_timestamp);
초 추출
 postgres=# select date_part('second', current_timestamp);
요일/일차 추출
 postgres=# select extract('dow' from timestamp '2013-07-30 20:38:40');    -- 일요일(0), 토요일(6)
result: 5
 postgres=# select extract('isodow' from timestamp '2013-07-30 20:38:40'); -- 월요일(1), 일요일(7)
result: 5
 postgres=# select extract('doy' from timestamp '2013-07-30 20:38:40');


PostgreSQL 주기적인 유지관리 Vacuuming



주기적인 유지관리 Vacuuming

Vacuum 기초

 - PostgreSQL의 Vacuum 명령은 다음과 같은 이유로 각 테이블마다 정기적으로 프로세스 해야합니다.

 a. 업데이트 또는 삭제된 행이 점유한 디스크 공간의 복구 또는 재사용.
 b. PostgreSQL 쿼리 플래너가 사용하는 데이터 통계 업데이트 (DB통계정보)
 c. 인덱스 전용 스캔 속도를 높이는 가시도 맵(visibility map) 업데이트
 d. 트랜잭션 ID 랩어라운드 또는 multixact ID 랩어라운드에 의한 아주 오래된 데이터가 손실되지 않도록 보호
 
 Vacuum에는 두가지 변이인 표준 Vacuum과 Vacuum Full이 있습니다. Vacuum Full은 디스크 공간을 더 많이 회수하는 대신 실행이 느립니다. 또한, 표준형 Vacuum은 데이터베이스 작업과 병렬로 실행할 수 있습니다. (Vacuum 진행중에 DDL (alter) 명령은 안되나 DML (select,insert,delete,update) 명령은 정상 작동함)
 Vacuum Full 작업은 작업중인 테이블에 대해 배타적 잠금이 필수이므로, 테이블의 다른 작업과 병렬로 사용할 수 없습니다.
 Vacuum은 상당한 I/O 트래픽이 발생되며, 다른 활성 세션의 성능을 저하시키는 원인이 됩니다.
 
1) 디스크 공간 복구

 - PostgreSQL에서 행의 UPDATE 또는 DELETE는 행의 오래된 버전을 즉각 제거하지 않습니다. 이러한 접근법은 MVCC의 장점을 누리기 위한것으로, 다른 트랜잭션에서 계속 확인될 가능성이 있을 경우에는 행 버전을 삭제해서는 안됩니다. 그러나 오래되었거나 삭제된 행 버전은 트랜잭션 대상이 아니기 때문에 Vacuum으로 회수 가능합니다.
 - 표준형 Vacuum은 테이블과 인덱스에서 Deed row 버전을 삭제하고, 나중에 재사용할 수 있도록 가용 공간으로 표시합니다. 하지만 테이블에 쓸수 있는 공간이 있는 경우는 오래된 공간을 반환하지 않습니다.
 - Vacuum Full은 Dead space가 일절 없도록 모든 공간을 반환합며, 테이블을 새버전으로 작성함으로 테이블이 최소화되지만, 시간이 오래걸리는 단점이 있습니다.
 - 일상적인 Vacuuming의 목표는 Vacuum Full이 불필요 하도록 표준형 Vacuum을 충분히 실행하는 것입니다. autovacuum데몬은 이와 같은 방식을 작동 되며, 사실상 Vacuum Full을 절대 수행하지 않습니다.
 
2) 실행 계획 통계 업데이트

 - PostgreSQL 쿼리 플래너는 쿼리에 대해 괜찮은 플랜을 생성하기 위해, 테이블 내용에 대한 통계정보에 의존합니다. 이러한 통계는 자체적으로 호출하거나 Vacuum에서 옵션으로 처리하거나 ANALYZE 명령으로 생성됩니다.
 - autovacuum 데몬이 활성화 되면 테이블 내용이 바뀔때마다 ANALYZE 명령이 자동으로 실행됩니다. 업데이트가 빈번하더라도, 데이터의 통계적 분포가 많이 바뀌지 않을때는 통계를 업데이트를 할 필요가 없습니다.

3) visibility map 업데이트

 - Vacuum은 모든 활성 트랜잭션에 보이는 것으로 알려진 튜플만 포함된 페이지를 추적하기 위하여 각 테이블에 대한 visibility map을 관리합니다.
 - 첫번째 목적은 Vacuum 자체는 cleanup 할 것이 없으므로 다음 실행에서 해당 페이지를 스킵합니다.
 - 두번째 목적은 기저 테이블을 참조하지 않고 인덱스만 이용하여 PostgreSQL이 일부 쿼리에 응답할 수 있게 합니다. 테이블을 참조 하지 않고 인덱스만 스캔하는 경우 visibility map을 먼저 확인하기 때문에 페이지에 튜플이 보이는 경우 heep fetch를 스킵할 수 있습니다. 거대 데이터 세트에서는 visibility map이 디스크 액세스를 막을 수 있는 가장 확실한 방법입니다. visibility map은 heep 보다 훨씬 작기때문에 heep이 매우 클 경우에도 쉽게 캐쉬 됩니다.

4) 트랜잭션 ID 랩어라운드 실패 방지

 - PostgreSQL의 MVCC 트랜잭션 시멘틱은 트랜잭션 ID(XID) 번호의 비교 가능 여부에 달려있습니다. 트랜잭션 ID의 크기는 제한되어 있으므로(32비트) 장시간 (40억 트랜잭션 이상) 동안 실행되는 클러스터는 트랜잭션 ID 랩어라운드가 발생합니다. XID 카운트가 0으로 랩어라운드 되고 과거에 있었던 모든 트랜잭션이 미래에 나타나게 됩니다. 즉 데이터의 소실이 발생합니다. 이 것을 피하려면 피하려면 20억 트랜잭션마다 모든 데이터베이스의 모든 케이블을 Vacuum해야 합니다.
 - 주기적으로 Vacuuming으로 문제가 해결되는 이유는 이 행이 과거에 커밋된 트랜잭션에 의해 삽입되었고 삽입 트랜잭션의 결과로 MVCC 관점으로 부터 현재와 미래 트랜잭션에서 모두 보이는 것이 확실하도록 Vacuum이 행에 동결 표시를 한다는 것입니다.
 
5) Multixact 및 랩어라운드

 - Multixact ID는 복수 트랜잭션에 의한 행 잠금을 지원할 때 사용됩니다.
 - Vacuum 테이블 스캔 중에, 테이블 부분적으로 또는 전체적으로 vacuum_multixact_freeze_min_age보다 오래된 multixact ID는 서로 다른 값으로 교체되는데, 이 값은 0, 단일 트랜잭션ID 또는 신규 multixact ID일 수 있습니다. 테이블 별로 pg_class.relminmxid는 해당 테이블에 계속 나타날 가능성이 있고 가장 오래된 multixact ID를 저장합니다. 이 값이 vacuum_multixact_freeze_min_age보다 오래된 경우 전체 테이블 스캔이 강제됩니다.
 - 전체 테이블 Vacuum 스캔은 사용된 멤버 저장소 공간이 할당된 저장소 공간의 50%를 초과 할 경우 multixact 연령이 가장 오래된 것을 시작으로 모든 테이블에 대해 순처적으로 일어납니다.
 
6) Autovacuum 데몬
 
 - Autovacuum 데몬의 목적은 Vacuum과 ANALYZE 명령의 실행을 자동화하는 것입니다.
 - autovacuum이 활성화 되면 다수의 튜플이 삽입, 업데이트 또는 삭제된 테이블을 검사합니다. 이 검사는 통계 수집 기능을 사용합니다. 따라서 track_counts가 true로 설정되지 않으면 사용할 수 없습니다.
 

클라우드 컴퓨팅에서 데이터베이스 전문가의 방향

클라우드 컴퓨팅에서 데이터베이스 전문가의 방향



  지금까지 오라클 데이터베이스 엔지니어로 10년 가까이 일해 왔지만, 앞으로 오라클만 할 줄 아는 DB 엔지니어 혹은 DBA는 시장에서 살아 남기가 힘들거라고 예상을 하고 있습니다. 장비가 가격이 내리고, 성능이 좋아져서 SQL 쿼리 튜닝에 투자하는 비용이 점점 줄어들고 있어 튜너들이 설자리가 점점 없어지고 있습니다. 또, 오라클의 비싼 라이센스 정책에 반하여 기업들은 어떻게든 비용 절감을 위해 오픈 소스를 도입한다거나, 클라우드를 구축한다거나, 아니면 다른 회사가 제공하는 클라우드 플랫폼을 이용하는 경우도 있습니다. 

 

 물론 오라클의 안정성과 성능은 인정합니다. 하지만 지금 시대는 예전에 오라클을 대체하지 못하던 시절과 많이 다릅니다. 오픈소스 DB의 대표적인 DB인 PostgreSQL의 성능은 이미 오라클의 바짝 뒤쫓고 있으며, 오픈 소스인 만큼 가격적인 측면에서 뛰어난 가성비를 제공합니다. EDB 같은 업체를 통해 PPAS의 유지보수 기술지원도 제공 받을수 있으며, 그 비용이 오라클과의 유지보수 계약에 비하면 정말 싼편이기 때문에, 이젠 충분히 대체 선택을 할 만하다고 생각합니다. 




 또, 앞으로는 DB 시장이 빅데이터와 클라우드를 중심으로 움직일 것이기에 HPE에서도 하드웨어 시장에 집중하기 보다는 빅데이터 분석을 위한 데이터베이스인 Vertica 같은 회사의 제품을 인수하여 시장에 내놓고 있으며, 새로운 환경에 특화된 데이터베이스들이 많이 나오고 있습니다. 오픈소스 기반의 DB들도 빠른 속도로 발전하고 있으며, 빅데이터에 특화 되어 있거나, 클라우드의 컨테이너 환경에 더 어울리는 DB가 발전 가능성이 크고 좀 더 많은 사용자가 유입 될 것이라고 예상합니다.


 클라우드 환경에서는 자체적으로 이중화 삼중화가 되어 있기 때문에, 오라클 같은 RAC 환경의 Active-Active 구성이 아니더라도, DB가 설치된 VM이 장애가 발생하더라도, 시스템이 자동으로 다른 컴퓨트 노드에 동일한 VM을 구동시켜 빠르게 장애 상황을 대체 할 수 있습니다. 이러한 기술은 RAC에 들어가는 라이선스와 장비의 비용 절감 효과가 엄청나기 때문에 기업 입장에서는 구미가 당길수 밖에 없는 상황이죠.



 DB의 부하가 걸려 자원이 더 필요한 상황이 오더라도, 스케일아웃을 통해 자동으로 노드를 추가하여 VM들의 클러스터링 통해 성능을 높일수 있고, 또 부하가 줄어든 시점에 자동으로 노드를 제거하여, 자원 효율을 극대화 시킬 수도 있습니다. 일반적인 상황에서 스케일아웃은 하드웨어 장비가 들고, 상면 랙이 늘고 하는 상황이 발생하지만, 클라우드 환경에서는 VM만 늘어나면 되기에 관리도 어렵지 않고, 필요없는 자원에 대한 회수도 용이합니다. 




 요즘은 MS도 자사의 클라우드 플랫폼인 Azure에 대한 홍보를 적극적으로 하고 있죠. 클라우드 환경에서의 관계형 DB로 시장이 크게 넘어가고 있는 중입니다. 당장 하던일을 그만 두라는 것은 아니지만, 계속 IT쪽에서 일을 해야 한다면, 앞으로 다가올 미래에 대한 대비 정도는 해야하지 않을까 생각해 봅니다.


PostgreSQL 테이블스페이스 및 오브젝트 사용량 확인



PostgreSQL 테이블스페이스 및 오브젝트 사용량 확인

 - 테이블스페이스 총량

  postgres=# select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;


 - Table Size (Index 미포함)

 postgres=# select pg_relation_size('TableName');


 - Table Size (Index 포함) 

 postgres=# select pg_total_relation_size('TableName');


 - Index size

 postgres=# select pg_relation_size('IndexName');

 

 - Total Size ( 데이터 + 인덱스)

 postgres=# select pg_total_relation_size('TableName');


 ※ 단위적용 - pg_size_pretty()


 - DB Size

 postgres=# select pg_size_pretty(pg_database_size('DBName'));

PostgreSQL 백업 및 복원



PostgreSQL 백업 및 복원


 - PostgreSQL 데이터를 백업하는 3가지 방법

 

 1. SQL 덤프

 2. 파일 시스템 레벨 백업

 3. 연속 아카이빙


 

1. SQL 덤프


pg_dump 사용


 덤프 백업


 $ pg_dump dbname > outfile


 - pg_dump 는 최신 버전에서 다시 로드 할 수 있습니다.

 - 32bit -> 64bit 서버로 이동할 수 있는 유일한 방법입니다.

 - 한번에 하나의 데이터베이스만 백업 가능합니다.


 덤프 복원


 $ psql dbname < infile


 - 새로운 환경에서 복원하게 되면 dbname 부분의 들어가는 데이터베이스가 자동으로 생성 되지 않기 때문에, 미리 사용자가 같은 이름의 데이터베이스를 생성 해줘야 합니다.

 - SQL 덤프를 복원하기 전에 덤프된 데이터베이스의 개체를 소유하고 있거나 개체에 대한 권한이 부여된 모든 사용자가 이미 존재해야 합니다.

 - 덤프 복원은 에러가 나도 계속 진행하기 때문에 --set ON_ERROR_STOP=on 옵션을 주면 에러 발생시 psql을 종료하게 할 수 있습니다.

 - pg_dump로 생성된 덤프는 template0에 상대적입니다. 이것은 template1을 통해 추가된 언어, 프로시저 등도 pg_dump에 의해 덤프된다는 것을 의미합니다. 결과적으로 복원시, 커스터마이즈된 template1을 사용하는 경우 template0으로부터 비어있는 데이터베이스를 생성해야 합니다. 

 - 파이프에 쓰거나, 파이프에서 읽어 오는 pg_dump 및 psql의 기능은 데이터베이스를 특정 서버에서 다른 서버로 직접 덤프를 가능하게 합니다.


 $ pg_dump -h host1 dbname | psql -h host2 dbname



pg_dumpall 사용


 - pg_dump는 한 번에 하나의 데이터베이스만 덤프하며, role 또는 테이블스페이스에 대한 정보는 덤프하지 않습니다. 


 백업

 

 $ pg_dumpall > outfile


 복원


 $ psql -f infile postgres


 - 실제로 시작한 기존 데이터베이스 이름을 지정할 수 있지만 비어있는 클러스터로 로딩한 경우 postgres를 일반적으로 사용해야 합니다.

 - 클러스터 차원의 데이터는 pg_dumpall --globals-only 옵션을 사용하여 단독 덤프가 가능합니다.



거대 데이터베이스 처리


 - 일부 운영체제는 거대 pg_dump 출력 파일을 생성할 때 최대 파일 크기 제한이 있습니다. pg_dump는 표준 출력으로 사용할 수 있어, 유닉스 툴로 이런 문제의 가능성을 피할 수 있습니다.


 ◆ 압축 덤프 사용

  

  gzip을 이용한 덤프

  $ pg_dump dbnam | gzip > filename.gz


  리로드

  $ gunzip -c filename.gz | psql dbname


 ◆ split 사용

  

  1GB 단위로 분할 백업

  $ pg_dump dbname | split -b 1024m - filename


  리로드

  $ cat filename* | psql dbname


 ◆ pg_dump의 커스텀 덤프 형식 사용

  

  - 설치된 zlib 압축 라이르러리를 사용하여 PostgreSQL을 시스템에서 빌드한 경우 출력 파일에 쓸 때 커스텀 덤프 형식이 데이터를 압축합니다.

 - 덤프파일 크기는 gzip과 비슷하지만 테이블을 선택적으로 복원할 수 있다는 장점이 있습니다.


  백업

  $ pg_dump -Fc dbname > filename


  복원 : psql이 아닌 pg_restore를 이용해 복원합니다.

  $ pg_restore -d dbname filename


 ◆ pg_dump의 병렬 덤프 기능 사용


  - 거대 데이터베이스의 덤프 속도를 높이기 위하여 병렬 모드를 사용할 수 있습니다.

  - 병렬 덤프는 "디렉토리" 아카이브 형식에 대해서만 지원합니다.

 

 백업

 $ pg_dump -j <number> -F d -f out.dir dbname


 복원

 pg_resote -j 를 이용하여 복원 할 수 있습니다.




2. 파일 시스템 레벨 백업


 - 시스템 레벨에서 백업을 하는 것인데, 정상적인 백업을 위해서는 반드시 DB를 shutdown 해야 합니다. 복원도 마찬가지.


 tar -cf backup.tar /usr/local/pgsql/data


 - 파일 시스템 백업은 전체 데이터베이스 클러스터의 완전한 백업 및 복원에 대해서만 동작합니다.



 

3. 연속 아카이빙 및 PITR


 - PostgreSQL은 클러스터의 데이터 디렉토리의 pg_xlog/ 서브 디렉토리에 WAL를 항상 유지관리합니다. 이 로그에는 데이터 파일에서 일어난 모든 변경 내용이 기록 됩니다.

 - 복구가 필요한 경우, 파일 시스템 백업을 복원한 이후, 백업된 WAL 파일로부터 리플레이로 시스템을 현재 상태로 불러옵니다. (오라클 Hot backup 복원 후 archive 적용하는 것과 비슷한 개념입니다.)

 - 일반 파일 시스템 백업과 마찬가지로, 전체 데이터베이스 클러스터 복원만 지원합니다.


 베이스 백업


 - pg_base 를 이용한 백업

 

 1) WAL 아카이빙이 활성화 되어 있는지 확인한다.

 2) 슈퍼유저로 데이터베이스에 연결하고 다음 명령을 실행한다.

   SELECT pg_start_backup('label');

   여기서 label은 백업 작업의 식별을 위한 string이다.

   기본적으로 pg_start_backup은 완료 되는데 체크포인트를 수행하기 때문에 시간이 오래걸린다.

   백업을 가능한 빨리 시작하고 싶다면, 다음을 사용하는 것이 좋다.

   SELECT pg_start_backup('label',ture);

   체크포인트를 강제한다.

 3) tar 또는 cpio 같은 편리한 파일 시스템 백업툴을 사용하여 백업을 수행한다.

 4) 슈퍼유저로 데이터베이스에 접속후에 명령을 실행한다.

   SELECT pg_stop_backup();

 5) 백업을 아카이브 하는 중에 WAL 세그먼트가 활성화 되면 백업이 끝이 난다.

 

 ※ 오라클 Hot backup과 동일한 개념으로 보면 됩니다.


 연속 아카이브 백업을 사용한 복구


 1) 서버가 실행중인 경우 서버를 중지한다.

 2) 공간에 여유가 잇는 경우 필요할 때를 대비하여 전체 클러스터 데이터 디렉토리와 테이블스페이스를 임시 위치에 복사한다. 시스템 다운 정에 아카이브되지 않은 로그가 포함되었을 수 있는 클러스터의 pg_xlog 서브디렉토리의 내용을 최소한이라도 따로 저장해두어야 한다.

 3) 사용 중인 클러스터 데이터 디렉토리 아래 및 데이블스페이스의 root 디렉토리 아래의 모든 기본 파일 및 서브 디렉토리를 삭제한다.

 4) 데이터베이스 파일을 파일 시스템 백업으로 부터 복원한다. 올바른 소유권 확인 및 pg_tblspc의 심볼릭 링크가 바른지 확인.

 5) pg_xlog/ 밑에 오래된 파일 시스템 백업에서 온 로그를 삭제한다.

 6) pg_xlog/ 2단계에서 저장된 아카이브되지 않은 WAL 세그먼트 파일이 있을 경우 pg_xlog/에 복사한다.

 7) 클러스터 데이터 디렉토리에서 복구 명령 파일 recovery.conf를 생성한다. pg_hba.conf를 수정하여 복구하는 동안 접속을 막는다.

 8) 서버를 시작한다. 서버가 복구모드로 들어가고 아카이브된 WAL 파일을 통해 읽기가 진행 된다. 복구가 완료 되면 recovery.conf의 이름을 recovey.done 으로 변경하고 다시 복구 모드로 들어가지 않게 바꿔준다.

 9) 데이터베이스의 내용을 확인하고 pg_hba.conf 를 복원하여 사용자 연결을 확인한다.


※ recovery.conf


 예제 파일. /usr/pgsql-9.6/share/recovery.conf.sample을 이용하여 만들수 있다.


 아카이브 복구 설정


 restore_command (string)

  - WAL 파일 시리즈의 아카이브된 세그먼트의 검색을 실행하는 로컬 쉘 명령.

  - 아카이브 복구에 필요하지만 스트리밍 복제의 경우는 옵션.

  - string의 %f 는 아카이브에서 검색할 파일 이름으로 교체되고, %p 는 서버의 복사 대상 경로로 교체된다.

 

    restore_command = 'cp /data/pgsql/archvie/%f "%p"'


 archive_cleanup_command (string)

  -모든 restartingpoint에서 실행되는 쉘 명령을 지정한다.

  - 스탠바이 서버에서 더 이상 필요로 하지 않는 오래된 아카이브 WAL 파일을 클린업하는 메카니즘을 제공하는 것.

  - %r 은 마지막 유효 재시작 지점이 있는 파일의 이름으로 교체된다.

  

   archive_cleanup_command = 'pg_archivecleanup /data/pgsql/archive %r'


 recovery_end_command (string)

  - 복구 종료 시에 한 번만 실행되는 쉘 명령을 지정한다. 이 매개변수는 옵션이다.


복구 타깃 설정


recovery_target = 'immediate'

  - 온라인 백업에서 복원하는 경우 이것은 백업이 종료된 지점을 의미한다.

  - 현재 다른 매개변수는 없다.


recovery_target_name (string)

  - 이 매개변수는 지명된 복원 지점(pg_create_restore_point()를 사용하여 생성)을 복구가 진행되는 지점으로 지정.


recovery_target_time (timestamp)

  - 이 매개변수는 타임스탬프를 복구가 진행되는 지점으로 지정한다.

  - 정밀한 정지 지점은 recovery_target_inclusive의 영향도 받는다


recovery_target_xid (string)

  - 트랜잭션 ID를 복구가 진행되는 지점으로 지정한다.

  - 복구되는 트랜잭션은 지정된 것 이전에 커밋된 트랜잭션이다.


다음 옵션은 복구 타깃을 추가로 지정하며, 타깃에 도달 했을때, 발생하는 것에 영향을 끼친다.


recovery_target_inclusive (boolean)

  - 지정된 복구 타깃 직후에 중지 할 것인지, 직전에 중지 할 것인지 지정한다.

  - 기본값은 ture.


recovery_target_timeline (string)

  - 특정한 타임라인으로의 복구를 지정한다.

  - 기본값은 베이스 백업을 가져왔을 때 현재였던 동일한 타임라인을 따라 복구하는 것이다.

  - 이것을 lastest로 설정하면 아카이브의 최신 타임라인으로 복구 되며, 스탠바이 서버에 유용하다.

 







PostgreSQL 로컬라이제이션



PostgreSQL 로컬라이제이션


 - 로케일 지원은 initdb를 이용해 클러스터를 구성하면 자동으로 초기화 됩니다. 

 - 특별히 옵션을 넣지 않으면 en_US.UTF8로 설정이 됩니다.

 

 LC_COLLATE

 String 정렬 순서

 LC_CTYPE

 문자 분류 (어떤글자인지, 대문자도 동일한지)

 LC_MESSAGES

 메세지 언어

 LC_MONETARY

 통화 형식

 LC_NUMERIC

 숫자 형식

 LC_TIME

 날짜 및 시간 형식


 예)

 로케일을 한국으로 설정하되 통화 형식은 달러를 쓴다면,

 initdb --locale=ko_KR --lc-monetary=en_US 로 클러스터를 구성하면 됩니다.


 - 시스템에 로케일 지원이 안되는 것처럼 하고 싶으면 특수한 로케일 이름인 C 또는 동등하게 POSIX를 사용해야 합니다.

 - 일부 로케일 카테고리는 데이터베이스가 생성될 때 고정된 값이어야 합니다. 

 - 서로 다른 데이터베이스에 대해 서로 다른 설정을 사용할 수는 있지만, 데이터베이스가 생성된 다음에는 설정을 변경 할 수 없습니다.

 - 인덱스 정렬 순서에 영향을 미치므로 고정된 상태로 유지되어야 하며, 데이터베이스 운영 중 변경하면 인덱스 손상이 발생합니다.

 - initdb 에서 선택된 값은 postgresql.conf에 작성되어 서버 시작시 기본값으로 사용됩니다. 이 값을 postgresql.conf에서 제거하면 서버가 실행 환경에서 설정을 상속 받습니다.

 - 리눅스가 처음부터 ko_KR.UTF8로 설정 되어 있다먼, initdb 시 옵션을 넣지 않아도 ko_KR.UTF8로 설정됩니다.



※ 로케일 설정은 다음과 같은 SQL 기능에 영향을 줍니다.


 1. order by를 사용한 쿼리에서 정령 순서 또는 텍스트 데이터에서 표준 비교 연산자

 2. upper 및 lower, initcap 함수

 3. 패턴 일치 연산자 (LIKE, SIMILAR TO 및 POSIX 스타일 정규식). 대소문자 비 구분 일치 및 문자 클래스 정규식에 의한 문자 분류에 모두 영향을 미치는 로케일.

 4. TO_CHAR 계열 함수

 5. LIKE 절을 사용한 인덱스 사용 능력


 - PostgreSQL에서 C 또는 POSIX가 아닌 다른 로케일을 사용할 때의 단점은 성능입니다. 문자 처리가 느려지고 LIKE에서 사용 되는 일반 인덱스를 사용하지 못합니다. 이러한 이유로, 실제로 필요한 경우에만 로케일을 사용해야 합니다.


※ 이미 클러스터를 구성해서 운영중인데 다른 언어셋이 필요하다면, create database 명령에서 로케일 옵션을 변경해서 생성 할 수 있습니다.


postgres=# create database test_kr

with

template=template0

encoding='EUC_KR'

LC_COLLATE='POSIX'

LC_CTYPE='ko_KR.euckr'

tablespace=test_kr

connection limit=999;

CREATE DATABASE

postgres=# \l

                                  List of databases

   Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+-----------+----------+-------------+-------------+-----------------------

 postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |           |          |             |             | postgres=CTc/postgres

 template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |           |          |             |             | postgres=CTc/postgres

 test_kr   | postgres  | EUC_KR   | C           | ko_KR.euckr | 

 testdb    | test_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

(5 rows)



 - C가 아닌 로케일 하에서 LIKE 절을 사용한 인덱스를 PostgreSQL이 이용하려면 몇 가지 커스텀 연산자 클래스가 존재해야 합니다. 이것은 로케일 비교 규칙은 무시하면서 엄격한 문자별 비교를 수행하는 인덱스의 생성을 허용합니다. 다른 방법은 C 콜레이션을 사용하여 인덱스를 생성하는 것입니다.

PostgreSQL 아카이브 모드



PostgreSQL 아카이브 모드 (Archive Mode)



- PostgreSQL에서 아카이브 모드를 이해하기전에 WAL을 자세히 알고 넘어가야 할 필요가 있습니다. 

- WAL (Write-Ahead Logging)은 데이터 무결성을 보장하는 표준 방법입니다.

- WAL의 중심개념은 변경 내용을 설명하는 로그 레코드를 영구적 저장소에 먼저 기록한 후에 데이터 파일의 변경 내용을 작성한 다는 것입니다. (오라클의 redo-archive와 비슷한 역할)

- 충돌 발생시 로그를 사용하여 데이터베이스를 복구 할 수 있으므로 트랜잭션 커밋마다 데이터 페이지를 디스크에 쓸 필요가 없습니다. 데이터 페이지에 적용되지 않은 변경 내용은 로그 레코드에서 실행 취소가 가능합니다. (이것은 roll-forward 복구 이며, REDO라고도 합니다.)

- 로그 파일은 순차적으로 작성되며, 로그 파일 동기화 비용은 데이터 페이지 쓰기 비용보다 훨씩 적습니다. 

- 서버가 소규모 도시 트랜잭션을 다수 처리하는 경우 로그 파일의 fsync 하나로 여러가지 트랜잭션을 충분히 커밋할 수 있습니다.

- 온라인 백업 및 PIT(point-in-time) 복구를 지원 가능하게 합니다.




 ※ $PGDATA 밑에 있는 postgresql.conf 파일안의 파라미터 값 수정하여 설정 가능합니다.


 ◆ wal_level (enum)

 - wal_level은 WAL에 기록되는 정보의 양을 결정합니다. 기본값은 충돌 또는 즉시 셧다운으로부터 복구하기 위해 필요한 정보만 기록하는 minimal 입니다.

  

  minimal : 기본값

  archive : WAL 아카이브에 필요한 로깅만 추가. 

  hot_standby : 대기 서버에서 읽기전용 쿼리에 필요한 정보를 추가.


 ◆ archive_mode (boolean)

  -archive_mode를 사용하는것으로 설정하면 완료된 WAL 세그먼트가 archive_command 설정에 의하 아카이브 저장소로 전달 됩니다. archive_mode 및 archive_command는 별개의 변수이므로 아카이빙 모드를 해지하지 않고도 archive_command를 변경할 수 있습니다. 이 매개변수는 서버 시작 시 설정됩니다. wal_level이 minimal로 설정된 경우 archive_mode를 사용 할 수 없습니다.

 

 ◆ archive_command (string)

  - 완료된 WAL 파일 세그먼트를 아카이브하기 위해 실행하는 로컬 쉘 명령입니다.

  - String : %p 아카이브할 파일의 경로명으로 대체

             %f 파일명으로만 대체


 ◆ archive_timeout (integer)

  - archive_command는 완료된 WAL 세그먼트를 호출합니다. 그러므로 서버에는 WAL 트래픽이 발생되지 않아서 트랜잭션이 완료되는 시간과 아카이브 저장소에서 안전하게 기록 되는 사이에 긴 지연시간이 발생 할 수 있습니다. 데이터가 아카이브되지 않은 채로 방치되지 않게 하기 위해 서버가 새 WAL 세그먼트 파일로 주기적으로 전환되도록 archive_timeout을 설정할 수 있습니다. 

 - archive_timeout을 매우 짧게 설정하는 것은 저장소를 부풀게 함므로 현명하지 못하며, 1~2분정도로 설정하는 것이 좋습니다.




※ 실제로 설정 해보기


$ vi postgresql.conf


wal_level = archive

archive_mode = on

archive_command = 'cp %p /data/pgsql/archive/arch_%f.arc'

archvie_timeout = 120


:wq!


postgres@psql-db01:/usr/local/pgsql/data]$ pg_ctl stop

waiting for server to shut down.... done

server stopped

[1]+  Done                    postgres -D /usr/local/pgsql/data > /var/postgresql/log/postgresql.log 2>&1  (wd: /var/postgresql/log)

(wd now: /usr/local/pgsql/data)

postgres@psql-db01:/usr/local/pgsql/data]$ postgres -D /usr/local/pgsql/data >/var/postgresql/log/postgresql.log 2>&1 &

[1] 52044

$



※ 아카이브 모드 확인하기


postgres=# select * from pg_settings

           where name in ('archive_mode', 'archive_command', 'archive_timeout', 'wal_level');

postgres=# show wal_level;

postgres=# show archive_mode;

postgres=# show archive_command;



로그 스위치


pg_switch_xlog() / pg_xlogfile_name / pg_xlogfile_name_offset



pg_switch_xlog() - 현재 사용중인 로그 파일을 아카이빙하고 새로운 파일로 스위칭 함.


postgres=# select pg_switch_xlog();

 pg_switch_xlog 

----------------

 0/70000B0

(1 row)


pg_xlogfile_name / pg_xlogfile_name_offset - 아카이빙 된 파일명 출력


postgres=# select pg_xlogfile_name('0/3000078'), pg_xlogfile_name_offset('0/3000078');

     pg_xlogfile_name     |    pg_xlogfile_name_offset     

--------------------------+--------------------------------

 000000010000000000000003 | (000000010000000000000003,120)

(1 row)



현재 사용중인 로그 확인


pg_current_xlog_locatioin() : 현재 사용중인 로그 출력


postgres=# select pg_current_xlog_location();

 pg_current_xlog_location 

--------------------------

 0/41000060

(1 row)




'Database > PostgreSQL' 카테고리의 다른 글

PostgreSQL 로컬라이제이션  (0) 2019.01.01
PostgreSQL 데이터베이스 Role  (0) 2019.01.01
PostgreSQL 아카이브 모드  (0) 2019.01.01
PostgreSQL 커널 리소스 관리  (0) 2019.01.01
PostgreSQL 모니터링  (0) 2018.12.31
PostgreSQL 점검  (0) 2018.12.31