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로 설정되지 않으면 사용할 수 없습니다.
 

오픈스택 수동 설치 실습 #.4

오픈스택 수동 설치 실습 #.4 - Controller 노드에 PostgreSQL DB 설치


자! openstack.org 공식 문서에도 없는 부분이 왔습니다! 

오픈스택의 공식 설치 문서에는 오픈소스 DB인 MariaDB를 사용해서 설치하는 법이 나와 있습니다. 그러나 오픈스택은 역시 오픈소스 DB인 PostgreSQL도 지원하고 있습니다. 그러나 설치는 사용자의 몫. 아쉽게도 공식 문서에는 PostgreSQL을 사용하여 설치하는 법 따위는 소개 하고 있지 않습니다.


이론적으로 OpenStack Compute는 SQL-Alchemy가 지원하는 모든 데이터베이스를 지원합니다. 일반적으로 테스트에는 SQLite3를 개발작업에는 MySQL, PostgreSQL을 사용합니다.


그러면 왜 저는 PostgreSQL을 설치 하느냐? 일단 제 업무 환경이 Postgres로 구성 되어 있습니다. 두번째, 성능면에서 MariaDB보다 뛰어납니다. 세번째, MariaDB보다 설정이 쉽습니다.



※ SQL DB는 controller 노드에만 설치를 합니다.


PostgreSQL Download 홈페이지


홈페이지에 가서 yum repo를 통해 원하는 버전의 패키지를 내려 받아야 합니다.



저는 9.6 버전을 선택했고, 플랫폼은 CentOS7을 설정했습니다.

설정을 하면 나오는 4번 항목의 주소를 복사해서 controller 노드에서 실행합니다.

# yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm


그리고 postgresql을 yum으로 설치해야 하는데, 오픈스택에서 사용하는 driver인 python-psycopg2를 같이 설치해줘야 합니다.


※ python-psycopg2 패키지는 controller 노드 뿐만 아니라 모든 노드에 설치를 해줘야 controller 노드에 설치되어 있는 DB에 접근 할 수 있습니다.


예전에 원하는 경로에 PostgreSQL을 설치하는 포스팅을 한적이 있는데 (http://db.necoaki.net/225) 오늘은 기본값과 최소한의 설치만 가지고 진행하겠습니다. 

# yum -y install postgresql96 postgresql96-server python-psycopg2


yum으로 설치가 완료되면 DB 클러스터를 구성합니다.


# /usr/pgsql-9.6/bin/postgresql96-setup initdb


그리고 DB가 서버 구동시 자동으로 올라오도록 구성해줍니다.


# systemctl enable postgresql-9.6

# systemctl start postgresql-9.6


그럼 DB가 구동 되었는지 확인하기 위해 su - postgres 하여 유저 변환을 합니다.


root@controller:~]# su - postgres

-bash-4.2$


프롬프트 기본값이 영 보기가 좋지 않네요. .bash_profile을 수정합니다. (안해도 됩니다. 취향입니다...)

$ vi .bash_profile

# .bash_profile


# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi


# User specific environment and startup programs


PATH=$PATH:$HOME/.local/bin:$HOME/bin


export PATH


[ -f /etc/profile ] && source /etc/profile

PGDATA=/var/lib/pgsql/9.6/data

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile


export PS1="\[\e[27;1m\]\u@\[\e[32;1m\]\h:\[\e[31;1m\]\w]$ \[\e[0m\]"

export TERM=linux


PATH=$PATH:$HOME/bin:/usr/pgsql-9.6/bin

export PATH


export PSQL_HOME=/var/lib/pgsql


alias vi='vim $*'

프롬프트 설정이 완료 되었고, postgres 마스터 계정의 패스워드를 바꿔줍니다. pg_hba.conf 에서 로컬의 접속도 md5로 변환 해줄 것이기 때문에 미리 바꿔줘야 나중에 마스터 계정으로 접속이 가능합니다.



$ vi $PGDATA/pg_hba.conf


맨 밑으로 내려보면 아래와 같은 설정이 있습니다.

# TYPE  DATABASE        USER            ADDRESS                 METHOD


# "local" is for Unix domain socket connections only

local   all             all                                     peer

# IPv4 local connections:

host    all             all             127.0.0.1/32            ident

# IPv6 local connections:

host    all             all             ::1/128                 ident

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local   replication     postgres                                peer

#host    replication     postgres        127.0.0.1/32            ident

#host    replication     postgres        ::1/128                 ident

다음과 같이 바꿔 줍니다.

# TYPE  DATABASE        USER            ADDRESS                 METHOD


# "local" is for Unix domain socket connections only

#local   all             all                                     peer

local   all             all                                     md5

# IPv4 local connections:

host    all             all             127.0.0.1/32            ident

host    all             all             10.0.0.0/24            md5

# IPv6 local connections:

host    all             all             ::1/128                 ident

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local   replication     postgres                                peer

#host    replication     postgres        127.0.0.1/32            ident

#host    replication     postgres        ::1/128                 ident


postgresql.conf 파일을 열어 리스너 부분의 주석을 해제하고 * 옵션을 줘서 모든 ip에 대해 접속 가능하게 리스너 설정을 합니다.


 $ vi $PGDATA/postgresql.conf

# - Connection Settings -


listen_addresses = '*'                  # what IP address(es) to listen on;

저장 후 DB를 재구동 해줍니다.

$ pg_ctl restart

PostgreSQL DB 설치는 완료 되었습니다.


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

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



  지금까지 오라클 데이터베이스 엔지니어로 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

PostgreSQL 커널 리소스 관리



공유 메모리 및 세마포어


 - 공유 메모리 및 세마포어는 통칭 "System V IPC"라고 합니다. 윈도우 외에, PostgreSQL이 이러한 기능에 대한 자체적인 구현을 제공하는 경우 PostgreSQL을 실행하기 위해 이러한 기능이 요구됩니다.


 - PostgreSQL은 서버 사본별로 System V 공유 메모리 수 바이트가 필요합니다. (64비트 플랫폼의 경우 보통 48바이트)


 - 서버 사본은 다수 실행 중이거나 다른 애플리케이션도 System V 공유 메모리를 사용중인 경우 바이트 단위의 공유 메모리 최대 크기인 SHMMAX를 늘려야 하거나 시스템 차원(system-wide)의 System V 공유 메모리인 SHMALL을 늘려야 할 수 있습니다. SHMALL은 여러 시스템에서 바이트 단위가 아니라 페이지 단위로 처리된다는 점에 유의해야 합니다.


 - PostgreSQL은 16개 한 세트로, 허용된 연결당 (max_connections) 및 autovacuum worker 프로세스당(autovacuum_max_workers) 1개의 세마포어를 사용합니다. 시스템에서 세마포어 최대 수는 SEMMNS에 의해 설정되며, 따라서 최소한 max_connection + autovacuum_max_workers + 각각 허용된 16개의 연결에 1추가 + worker여야 합니다. 각각의 세트마다 다른 애플리케이션에서 사용되는 세마포어 세트와의 충돌을 감지하기 위한 "매직 넘버"가 17번째 세마포어에 포함되어 있습니다.


 ceil((max_connection + autovacuum_max_workers +4)/16)*17외 다른 애플리케이션의 여유분.




리눅스에서의 세마포어


 - 최대 세그먼트 크기 기본값은 32MB 이며, 최대 총 크기 기본값은 2097152 페이지 입니다. "huge page"를 이용한 특수 커널 구성일 때 외에는 페이지는 거의 항상 4096바이트 입니다. (확인하려면 getconf PAGE_SIZE 사용).


 - 공유 메모리 크기설정은 sysctl 인터페이스를 통해 변경이 가능합니다. 예를 들어, 16GB를 허용하려면 다음과 같이 합니다.


 # sysctl -w kernel.shmmax=17179869184

 # sysctl -w kernem.shmall=4194304


 또한, /etc/sysctl.conf 파일에서 리부팅 사이에서도 이 설정을 보존할 수 있습니다. 



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

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
PostgreSQL 권한 부여 및 해제  (0) 2018.12.31

PostgreSQL 점검

PostgreSQL 점검



1. 실시간 쿼리 점검


 postgres=# select * from pg_stat_activity;


 현재 DB에서 돌고 있는 전체적인 쿼리 및 접속정보등을 확인할 수 있습니다.


 xbackend_min 


 pg_stat_activity의 컬럼 중에 가장 중요한 정보는 xbackend_min 입니다. 

 이 값이 있는 쿼리는 현재 해당 쿼리 작업을 처리해야 진행된다는 의미입니다. 따라서 현재 xbackend_min이 있는 쿼리 중에서   현재 진행이 되지 않고 있는 쿼리가 있다면 앞의 쿼리 때문에 다음 쿼리가 진행되지 않는 경우를 의미하죠. 이런 경우는 앞에서   update 문 때문에 해당 row의 lock이 결린 경우가 대표적으로 나타납니다. 물론 update가 완료되어 lock이 금방 풀리면 해당   값이 금방 없어지기도 하죠.




2. 현재 테이블에 Lock을 확인하는 쿼리


SELECT t.relname,

       l.locktype,

       page,

       virtualtransaction,

       pid,

       mode,

       granted

  FROM pg_locks l,

       pg_stat_all_tables t

  WHERE l.relation = t.relid

  ORDER BY relation ASC;


 이 쿼리는 현재 테이블에 lock을 확인할 수 있는 쿼리 입니다. 일반적으로 어지간한 lock은 괜찮지만, RowExclusiveLock 같은 것이 검색된다면 해당 테이블에 접근하는 Row에 update등이 현재 지연되어 다른 쿼리에도 영향을 미친다고 판단하시면 됩니다.




3. 해당 작업을 Kill 하는 쿼리


PostgreSQL은 위에서 검색된 pid를 죽일 수 있습니다. 두가지 방법이 있죠.


select pg_cancel_backend(4678249);


SELECT pg_terminate_backend(32519) FROM pg_stat_activity ;


pg_cancel_backend는 해당 PID만 중지시키려고 하고, pg_terminate_backend는 해당 PID와 연계된 모든 상위 Query Process를 모두 중지시킵니다. 따라서 pg_cancel_backend로 해당 작업이 중지되는지 먼저 확인해 보고, 그래도 중지되지 않는다면 pg_terminate_backend를 수행해 보시면 됩니다.




4. 해당 작업들의 주의점


위의 모니터링 쿼리 및 작업을 Kill하는 쿼리도 사실 Transaction을 탈 수 있습니다. 따라서 Rollack, Commit이 되는 DB도구를 활용하시거나, 터미널에서 transaction으로 처리하시고, 이상없는지 확인하신 다음 Commit을 하시는 것이 DB를 안전하게 사용하시고, 데이터를 잘 관리할 수 있는 방법이라고 할 수 있습니다.

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

PostgreSQL 커널 리소스 관리  (0) 2019.01.01
PostgreSQL 모니터링  (0) 2018.12.31
PostgreSQL 점검  (0) 2018.12.31
PostgreSQL 권한 부여 및 해제  (0) 2018.12.31
PostgreSQL 유저 생성  (0) 2018.12.28
PostgreSQL 스키마 생성  (0) 2018.12.28

PostgreSQL 권한 부여 및 해제

Grant & Revoke



Grant - user,group 혹은 모든 user들에게 해당 객체에 대한 사용권한을 승인합니다.


Synopsis :

 

GRANT privilege [,...] ON object [,...]

    TO { PUBLIC | GROUP group | username}


privilege

SELECT : 특정 TABLE/VIEW 의 column에 대한 access 을 승인

INSERT : 특정 TABLE의 모든 column 에 데이타의 삽입에 대한 권한 승인

UPDTAE : 특정 TABLE의 모든 column 의 갱신에 대한 권한 승인

DELETE : 특정 TABLE 의 row 의 삭제에 대한 권한 승인

RULE : 특정 TABLE/VIEW에 대한 rule 을 정의하는 권한에 대한 승인

ALL : 모든 권한을 승인한다.


object

 access 를 승인하는 객체의 이름으로서 다음과 같은 객체들이 있다.


Table

Sequence

View

Index


PUBLIC

 모든 유저를 승인


GROUP group

 사용 권한을 획득할 group을 지정, group 을 명시적으로 생성되어져 있어야 함.


username

 사용권한을 획득할 사용자명. PUBLIC 은 모든 유저에 대해서 적용된다.



Notes

psql 에서 "\z" 를 사용하여 존재하는 객체에 대한 permission 등을 참조할 수 있다.


permission 정보의 형식


username=arwR : 유저에게 승인된 사용권한

group gname=arwR : GROUP 에게 승인된 사용권한 

=arwR : 모든 유저에게 승인된 사용권한


a : INSERT privilege

r : SELECT privilege

w : UPDATE/DELETE privilege

R : RULE privilege

arwR : ALL privilege



예)

postgres=# GRANT INSERT ON imsi_table TO PUBLIC;

postgres=# GRANT ALL ON imsi_table TO test_user;




Revoke - user,group 혹은 모든 user로부터 객체에 대한 사용권한을 무효화합니다.


Synopsis :

 

REVOKE privilege [,...]

    ON object [,...]

    FROM { PUBLIC | GROUP gname | username }


privilege

 SELECT ,INSERT ,UPDATE, DELETE, RULE, ALL


object

 적용될 수 있는 객체 : table, view, sequence, index


group

 privilege 를 취소할 그룹명


username


PUBLIC



예)

postgres=# REVOKE INSERT ON imsi_table FROM PUBLIC;

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

PostgreSQL 모니터링  (0) 2018.12.31
PostgreSQL 점검  (0) 2018.12.31
PostgreSQL 권한 부여 및 해제  (0) 2018.12.31
PostgreSQL 유저 생성  (0) 2018.12.28
PostgreSQL 스키마 생성  (0) 2018.12.28
PostgreSQL Tablespace 생성  (0) 2018.12.27

PostgreSQL 유저 생성

유저 생성


 - DATABASE에서 USER는 DATABASE를 사용하는 주체로서 OS를 운영하는 USER와는 분리되어 있습니다. USER는 소유하고 있는 DATABASE안에 있는 OBJECT의 권한을 변경하고 제어할 수 있습니다.


 - 유저를 생성하기 위해서는 먼저 DATABASE에서 SUPERUSER권한을 가지고 있어야 합니다. PostgreSQL에 SUPERUSER의 default 계정은 postgres입니다.



유저 조회


postgres=# SELECT * FROM PG_SHADOW;


or


postgres=# \du



 - \du를 입력하면 USER들이 가지고 있는 ROLE들을 확인 할 수 있습니다. SUPERUSER인 postgres는 SUPERUSER, CREATE ROLE, CREATE DB, REPLICATION기능을 가지고 있습니다.


SUPERUSER : USER들을 생성하고 권한을 부여해 주는 USER

CREATE ROLE : USER가 새로운 ROLE을 정의하는 기능을 생성

CREATE DB : USER가 DB를 생성하는 권한을 부여하는 기능

REPLICATION : USER가 DB를 실시간으로 복사하는 기능



Synopsis :


CREATE USER username [[ WITH ] option [ ... ]]

where option can be:



SUPERUSER  | NOSUPERUSER   - 해당 USER를 SUPERUSER권한을 주는 것입니다. 

                             따로 지정하지 않을 경우 DEFAULT값으로 NOSUPERUSER가 됩니다.

CREATEDB   | NOCREATEDB    - DATABASE를 생성하는 권한을 정의합니다. 

                             CREATEDB를 선택할 경우 USER는 DATABASE를 생성할 권한이 부여됩니다. 

                             NOCREATEDB를 선택할 경우 USER는 DATABASE를 생성할 권한이 거부됩니다. 

       따로 정의 되어있지 않을 경우 NOCREATEDB값이 default 설정되어 있습니다.

CREATEUSER | NOCREATEUSER  - 스스로 새로운 유저를 생성하는 권한을 부여하는 것을 정의합니다.

                             CREATEUSER를 선택할 경우 USER를 생성할 수 있는 권한이 부여됩니다.

                             NOCREATEUSER를 선택할 경우 USER를 생성할 권한이 거부됩니다.

INHERIT    | NOINHERIT    - DATABASE의 권한을 다른 구성원들에게 상속하는 역할을 합니다.

                             따로 정의 되어있지 않을 경우 INHERIT 값이 default값으로 설정 되어 있습니다.

LOGIN      | NOLIGIN    - USER가 LOGIN을 하는 역할을 부여합니다.

CONNECTION LIMIT connlimit - 로그인 할 때 동시연결을 지원 하는 기능으로 default값으로 -1(제한없음)로 

                             설정 되어 있습니다.

[ENCRYPTED | UNCRYPTED ] PASSWORD 'password' - ‘password’를 입력하고 인증이 필요 없는 경우 옵션을 생                                                 략이 가능합니다.



생성 예제


postgres=# create user TEST_USER with password 'test01';


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

PostgreSQL 점검  (0) 2018.12.31
PostgreSQL 권한 부여 및 해제  (0) 2018.12.31
PostgreSQL 유저 생성  (0) 2018.12.28
PostgreSQL 스키마 생성  (0) 2018.12.28
PostgreSQL Tablespace 생성  (0) 2018.12.27
PostgreSQL DB 생성 및 삭제  (0) 2018.12.27

PostgreSQL 스키마 생성

SCHEMA 생성


 - SCHEMA는 Object들의 논리적 집합체 입니다. 

 - TABLE, VIEW, SEQUENCE, SYNONYM, DOMIAN, FUNCTION 등으로 구성되어 있습니다.

 - SCHEMA를 사용하는 이유는 논리적 집합체를 만들어서 관리의 편의성을 높이고, 여러 USER들 간의 간섭 없이 접속 할 수 있게 합니다.



Synopsis :


1. CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]

2. CREATE SCHEMA AUTHORIZATION user_name [3. schema_element [ ... ] ]

4. CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ]

5. CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name


1. CREATE SCHEMA ‘schemaname’을 입력하여 SCHEMA를 생성합니다. 이름을 입력하지 않을 경우 USER의 이름이 SCHEMA이름으로 사용됩니다. (단 Pg_로 시작하는 이름은 스키마로 불가능 합니다.)


2. AUTHORIZATION ‘username’ 스키마를 소유한 USER의 이름을 입력합니다. 이를 생략할 경우 접속되어 있던 USER가 

default값으로 저장되고 SUPERUSER만이 다른 USER가 소유한 SCHEMA를 만들 수 있습니다.


3. schema_element [...]을 입력하여 SCHEMA 내에서 객체를 정의하는 SQL문을 작성합니다. CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER, GRANT등이 포함될 수 있습니다.


4. CREATE SCHEMA IF NOT EXISTS ‘schema_name’ 을 입력하면 특정 이름이 SCHEMA에 없을 경우 그 SCHEMA를 생성합니다.


5. CREATE SCHEMA IF NOT EXISTS AUTHORIZATION ‘user_name’ 은 USER가 소유한 SCHEMA가 존재하지 않을 때 SCHEMA를 생성합니다.




SCHEMA 생성 예제


postgres=# CREATE SCHEMA test01 AUTHORIZATION test_user;


postgres=# GRANT ALL ON SCHEMA test01 TO test_user;



- 오라클에서 SCHEMA = USER의 개념으로 하나의 User가 하나의 Schema를 소유합니다. 히지만 Postgres나 Mysql 같은 DB에서는 User와 Schema 는 분리된 개념이며,하나의 유저가 여러개의 스키마를 소유 할 수도 있습니다.



PostgreSQL Tablespace 생성

Tablespace


- 데이터베이스에서 Tablespace는 오라클과 PostgreSQL에서만 존재하는 개념입니다.

- 테이블스페이스가 존재 함으로 각 schema의 오브젝트 관리가 용이해지며, 데이터파일 관리 및 용량 관리에 있어서, 또는 성능 관리에 있어서 효과적인 관리가 가능해 집니다.


- 테이블 스페이스 확인


postgres=# select * from pg_tablespace;

  spcname   | spcowner | spcacl | spcoptions 

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

 pg_default |       10 |        | 

 pg_global  |       10 |        | 

(2 rows)


postgres=# \db

       List of tablespaces

    Name    |  Owner   | Location 

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

 pg_default | postgres | 

 pg_global  | postgres | 

(2 rows)


postgres=# \db+

                                  List of tablespaces

    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description 

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

 pg_default | postgres |          |                   |         | 29 MB  | 

 pg_global  | postgres |          |                   |         | 497 kB | 

(2 rows)


postgres=# 


- 특별히 유저나 스키마에 테이블 스페이스를 지정하지 않고 pg_default 테이블스페이스를 이용 할 수 있습니다.

- 해당 디렉토리는 postgres의 권한을 가지고 있어야 합니다.



테이블스페이스 생성


Synopsis :

CREATE TABLESPACE tablespace_name

    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]

    LOCATION ’directory’

    [ WITH ( tablespace_option = value [, ... ] ) ]



생성 예제


postgres@pgsqldb:~]$ psql -d postgres -U postgres

psql (9.6.11)

Type "help" for help.

postgres=# CREATE TABLESPACE mydb01 LOCATION '/postgresql/tbs';

CREATE TABLESPACE

postgres=#



Tablespace 조회


postgres=# \db

           List of tablespaces

    Name    |  Owner   |    Location     

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

 mydb01     | postgres | /postgresql/tbs

 pg_default | postgres | 

 pg_global  | postgres | 

(3 rows)


postgres=# select * from pg_tablespace;

  spcname   | spcowner | spcacl | spcoptions 

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

 pg_default |       10 |        | 

 pg_global  |       10 |        | 

 mydb01     |       10 |        | 

(3 rows)


postgres=# \q

postgres@pgsqldb:~]$ ls -l /postgresql/tbs/

total 0

drwx------ 2 postgres postgres 6 Dec 27 14:03 PG_9.6_201608131

postgres@pgsqldb:~]$ ls -l $PGDATA/pg_tblspc

total 0

lrwxrwxrwx 1 postgres postgres 15 Dec 27 14:03 16392 -> /postgresql/tbs

postgres@pgsqldb:~]$ 


- tablespace owner를 지정해서 생성 할 수도 있습니다.




- 경로를 지정하여 테이블스페이스를 생성하면, $PGDATA/pg_tblspc 밑에 OID로 심볼릭 링크가 생성됩니다.

- 실제 파일은 경로에 있고, 클러스터 홈 밑에 pg_tblspc에 링크가 생성되어 DB에 정보를 전달하는것 입니다.



Tablesapce 이름 변경


postgres=# ALTER TABLESPACE mydb RENAME TO mydb01;



Tablespace Owner 변경


postgres=# ALTER TABLESPACE mydb01 OWNER TO POSTGRES;

PostgreSQL DB 생성 및 삭제

PostgreSQL을 관리하거나 운영하는 방법은 크게 두가지로 분류 할 수 있습니다.


- 터미널을 이용한 커맨드라인 사용

- pgadmin을 이용한 GUI 사용



DB생성


Synopsis :

CREATE DATABASE name

[ [ WITH ] [ OWNER [=] user_name ]

        [ TEMPLATE [=] template ]

        [ ENCODING [=] encoding ]

        [ LC_COLLATE [=] lc_collate ]

        [ LC_CTYPE [=] lc_ctype ]

        [ TABLESPACE [=] tablespace_name ]

        [ ALLOW_CONNECTIONS [=] allowconn ]

        [ CONNECTION LIMIT [=] connlimit ] ]

        [ IS_TEMPLATE [=] istemplate ]


터미널에서 su - postgres 계정 접속 후에


$ createdb mydb


간단하게 만들수 있습니다.

그리고 psql 명령으로 접속 할 수 있습니다.


$ psql mydb

psql (9.6.11)

Type "help" for help.


mydb=#


이렇게 나오면 DB에 접속이 된것입니다.



DB에서 빠져나가려면 \q 하면 OS로 돌아갑니다.



이 방법 말고도 postgres db에 접속해서 생성하는 방법이 있습니다.


$ psql -d postgres -U postgres

psql (9.6.11)

Type "help" for help.


postgres=# create database mydb2;

CREATE DATABASE

postgres=#


오라클에 비하면 너무나 간단한 DB 생성 방법입니다.



반대로 DB를 삭제하려면


$ dropdb mydb


또는


postgres=# drop database mydb2;


이렇게 하시면 됩니다.



Database 목록 확인


\l


명령으로 데이터베이스 리스트를 확인 할 수 있습니다.


postgres-# \l

                                  List of databases

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

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

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

 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

(4 rows)



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

PostgreSQL 스키마 생성  (0) 2018.12.28
PostgreSQL Tablespace 생성  (0) 2018.12.27
PostgreSQL DB 생성 및 삭제  (0) 2018.12.27
CentOS 7 에서 방화벽에 PostgreSQL 리스너 포트 등록하기  (0) 2018.12.27
초간단 postgresql.conf 설정  (0) 2018.12.27
pg_hba.conf  (0) 2018.12.27

CentOS 7 에서 방화벽에 PostgreSQL 리스너 포트 등록하기

접근제어


- PostgreSQL은 pg_hba.conf를 통해 접근제어를 할 수 있으나, 성능상의 이슈로 인해 OS단이나 방화벽 장비, 보안장비에서 제어하는 것을 추천 한다고 했습니다.


- CentOS 7 에서는 새로나온 Firewalld 와 기존의 iptables 모두 사용 가능 합니다.



firewalld 의 설정



# firewall-cmd --permanent --zone=trusted --add-source=<Client IP address>/32


# firewall-cmd --permanent --zone=trusted --add-port=5432/tcp


# firewall-cmd --reload



iptables 설정


# iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d <Server IP address> --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT


# iptables -A OUTPUT -p tcp -s <Server IP address> --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT




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

PostgreSQL Tablespace 생성  (0) 2018.12.27
PostgreSQL DB 생성 및 삭제  (0) 2018.12.27
CentOS 7 에서 방화벽에 PostgreSQL 리스너 포트 등록하기  (0) 2018.12.27
초간단 postgresql.conf 설정  (0) 2018.12.27
pg_hba.conf  (0) 2018.12.27
Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25

초간단 postgresql.conf 설정

postgresql.conf


 - PostgreSQL의 환경변수를 지정해주는 설정파일. 오라클의 파라미터 파일과 비슷한 역할을 합니다.


postgres.conf 파일 안에는 다양하고 많은 설정 값들이 있는데, 아래의 설정 값 정도만 설정하면 싱글 DB를 운영하는데 있어 크게 문제될 사항은 없습니다.



listen_addresses = '*'                       # 로컬 호스트 밖에서의 접속 허용

shared_buffers = 3GB                         # 물리 메모리  2/3 ~ 1/4

checkpoint_segments = 128                    # 2GB redo 로그, 9.4 이하에서

max_wal_size = 2GB                           # 2GB redo 로그, 9.5 이상에서

min_wal_size = 2GB                           # 2GB redo 로그, 9.5 이상에서

wal_level = logical                          # 일단 최대 자세하게

archive_mode = on                            # 아카이빙 기능은 켜두고,

archive_command = 'true'                     # 아카이빙을 임시로 사용 안함

log_destination = 'stderr'                   # pg_log 에 로그 남김

logging_collector = on                       # 자체 로그 프로세스 사용

log_line_prefix = '%t %u@%r/%d(%c 또는 %p)'   # 좀 더 자세히

stats_temp_directory = '/run/shm'            # 실시간 통계 정보는 공유 메모리로

effective_cache_size = 4GB                   # 물리 메모리 1/2 , 9.4 이하



- 더 자세한 설정 값들에 대해서는 나중에 따로 정리 하도록 하겠습니다.

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

PostgreSQL DB 생성 및 삭제  (0) 2018.12.27
CentOS 7 에서 방화벽에 PostgreSQL 리스너 포트 등록하기  (0) 2018.12.27
초간단 postgresql.conf 설정  (0) 2018.12.27
pg_hba.conf  (0) 2018.12.27
Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25
PostgreSQL의 기본 개념  (0) 2018.12.12

pg_hba.conf

pg_hba.conf



- PostgreSQL의 인증관련 설정 파일 ( HBA : host-based authentication 호스트 기반의 인증 약어 )


1) $PGDATA 에 존재. (클러스터홈)

2) PostgreSQL의 pg_hba.conf 파일을 통해 외부접근에 대한 처리는 되도록 배재하는것이 좋습니다.

   pg_hba.conf 설정을 통하여 외부접근을 하게 되면, PostgreSQL 인증체크처리 부하가 발생하여 전체적인 성능에 부하가 걸릴 수 있기 때문에, OS단의 iptables에서 통제 하거나, 그 앞단에서 방화벽, 보안장비에서 통제하는 편이 좋습니다.

3) 설정은 간단하며, 접근 Host나 Host의 데이터 전송방식과 암호화 전송방식에 대한 설정을 가지고 있습니다.

4) 실제적인 계정에 대한 정보는 PostgreSQL의 카탈로그 테이블인 pg_user에서 관리하기 때문에 계정 권한, 패스워드 변경 등의 작업은 실시간 적용이 가능하나, 클라이언트의 접근 방식이나, 암호 전달 방식은 pg_ctl reload 또는, pg_ctl restart 명령을 통해 pg_hba.conf를 다시 로드해야 합니다. restart는 불편함이 있지만, 불법 접근에 대한 처리에 있어 빠른 응답으로 PostgreSQL 서버 부담을 줄이고, 최대한 성능을 끌어내기 위해 채택한 방법입니다.



- pg_hba.conf 내용



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#host    replication     enterprisedb        127.0.0.1/32            md5

#host    replication     enterprisedb        ::1/128                 md5




- 환경설정



1. Host Type


Host Type은 접근자의 접근위치와 통신의 암호화 관련 설정입니다.

local, host, hostssl, hostnossl을 지원합니다.

 

local은 자칫 localhost로 생각할 수 있겠지만, local은 Unix Domain Socket을 통한 접속에 해당되는 것으로 다소 햇갈릴수 있으니 주의 하시기 바랍니다.

 

hostssl은 ssl인증서를 통한 암호화 통신만 지원하며 localhost, 127.0.0.1식의 즉 TCP/IP접속에 해당된다.

hostnossl은 ssl접속은 불가능하며, TCP/IP통신을 지원합니다.

 

host나 hostssl로 설정한 상태에서 SSL기능을 사용하시려면 Postgres컴파일시 --with-openssl옵션을 주어야 하며, postgresql.conf에 ssl=true로 설정을 해주셔야만 합니다.

 

 

2. Database Name


특정한 디비에 대한 접속을 제한할 수 있으며 ,(콤마)로 여러 개의 DB로 접근 제어가 가능합니다.  모든 DB에 대한 접근을 풀려면 all로 설정하시면 됩니다. 만약에 설정할 DB가 수십개라면 기재하기 불편하실경우 @dblist.txt 식으로 설정하고 dblist.txt을 PGDATA로 설정한 폴더의 안에 넣어 두시면됩니다.

 


3. User Name


계정설정으로 ,(콤마)구분으로 할 수 있으며, Database의 @파일명 식으로 따로 파일을 만들어서 처리하실수도 있습니다. PostgreSQL의 계정 그룹 카탈로그 테이블인 pg_group 또는 create_group 명령으로 그룹을 만들어 계정들은 하위(SYSID)에 묶어두었을때는 +(플러스) 키를 붙인 그룹명으로 설정하면 해당 그룹에 대한 모든 접근이 가능해집니다.

 


4. CIDR-ADDRESS or IP-Mask


IPv4 CIDR구분으로 해당 C Class에 대해 모두 접근처리를 할 경우는 : xxx.xxx.xxx.0/24

해당 IP에 대한 접근처리를 할 경우는 : xxx.xxx.xxx.xxx/32

 


5. Authentication Method


이 부분은 실제적인 계정의 패스워드에 대한 서버로의 전송을 어떻게 할 것인가를 정하는 것입니다.

PostgreSQL Server와 Client와의 접속에는 처음 Client가 접속을 하게 되면 pg_hba.conf에 대해 검색해서 해당 접속에 대한 접근허용을 확인하고 확인이 되면 이 Auth.Method에 설정된 암호화 방식으로 패스워드를 전송하라고 응답메시지를 보내고 다시 Client가 Server로 로그인을 하게 되는 방식입니다.


trust : 패스워드 없이 접근 가능

reject : 거부

md5 : 패스워드를 md5로 암호화해서 전송

crypt : crypt로 암호화 해서 전송 Postgres 7.2이후부터는 사용 안함. (이전버전설정 호환용)

password : text로 패스워드를 전송하는 것.

krb4, krb5 : KerberOS V4, 5를 지원한다.

ident : 접속 ClientOS User이름을 확인하는 방법?

pam : PAM(Pluggable Authentication Modules)서비스를 사용한 인증




- 설정 예제



# 로컬 시스템상의 모든 유저가 임의의 데이터베이스에 

# 임의의 데이터베이스 유저명으로 Unix 도메인 소켓을 사용해 접속하는 것을 허가 

# (로컬 접속에서는 디폴트). 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  local   all         all                               trust 



# 로컬 loopback의 TCP/IP 접속을 사용하는 것은 위와 같다. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    all         all         127.0.0.1/32          trust    



# 분리된 netmask 열을 사용하고 있는 것을 제외하고 위와 같다. 

# TYPE    DATABASE    USER        IP-ADDRESS    IP-MASK            METHOD 

  host    all         all         127.0.0.1     255.255.255.255    trust 



# IP주소 192.168. 93. x를 가지는 모든 호스트의 모든 유저가, 

# ident가 그 접속에 대해 보고하는 것과 같은 유저명(전형적으로는 Unix 유저명)으로 

# 데이터베이스 "postgres"에 접속하는 것을 허가. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    postgres    all         192.168.93.0/24       ident sameuser 



# 유저의 패스워드가 올바르게 입력되었을 경우, 

# 호스트 192.168. 12.10부터의 유저가 데이터베이스 "postgres"에 접속하는 것을 허가 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    postgres    all         192.168.12.10/32      md5 



# 선행하는 "host"행이 없으면, 이 2행에 의해 192.168. 54.1으로 접속 시도는 

# 모두 거부(이 항목이 최초로 일치되기 때문에). 

# 다만, 인터넷상의 다른 모든 장소로부터의 Kerberos 5 접속은 허가. 

# 제로 마스크는, 호스트 IP주소의 비트를 고려하지 않고 

# 어느 호스트라도 조합할 수 있는 것을 의미합니다. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    all         all         192.168.54.1/32       reject 

  host    all         all         0.0.0.0/0             krb5 

 


# 192.168. x.x 호스트로부터의 유저가, ident 검사를 통과하는 경우, 

# 어느 데이터베이스라도 접속을 허가. 만약, 예를 들면, ident가 "bryanh"라고 인정해 

# "bryanh"가 PostgreSQL의 유저 "guest1"로서 

# 접속 요구를 내는 경우, "bryanh"는 "guest1"로 접속이 허가된다고 합니다. 

# 맵 "omicron"에 대한 기재사항이 pg_ident.conf에 있으면 접속을 허가. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  host    all         all         192.168.0.0/16        ident omicron 



# 로컬 접속에 대해서, 이하의 단 3행 밖에 기재가 없는 경우, 로컬 유저는 

# 자신의 데이터베이스(데이터베이스 유저명과 같은 이름의 데이터베이스)에게만 접속 허가. 

# 다만 관리자와 롤 "support"의 멤버는 모든 데이터베이스에 접속 가능. 

# $PGDATA/admins 파일은 관리자의 리스트를 포함한다. 

# 모든 경우에 패스워드가 필요. 

# TYPE    DATABASE    USER        CIDR-ADDRESS          METHOD 

  local  sameuser      all                              md5 

  local  all           @admins                          md5 

  local  all           +support                         md5 



# 위의 마지막 2행은 1개의 행으로 정리하는 것이 가능. 

  local  all           @admins,+support                 md5 


# 데이터베이스의 열에는 리스트나 파일명도 사용할 수 있지만, 그룹은 사용할 수 없다. 

  local  db1,db2,@demodbs  all                          md5 



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

CentOS 7 에서 방화벽에 PostgreSQL 리스너 포트 등록하기  (0) 2018.12.27
초간단 postgresql.conf 설정  (0) 2018.12.27
pg_hba.conf  (0) 2018.12.27
Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25
PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03

Linux에 Postgres 9.6 버전 설치

PostgreSQL 9.6 설치


현재 가장 많이 사용하는 버전이 9.6버전이기 때문이기도 하고, Postgres가 성능적인 부분에서 많은 발전을 이뤄낸 버전이기도 하기 때문에 9.6버전의 설치하는 법을 안내 해보고자 합니다.


기존의 run 파일을 올려서 설치하는 방식이 아닌, PostgreSQL 홈페이지에서 yum repo를 제공하고 있으며, 누구나 쉽게 설치를 할 수 있으나, 처음부터 yum으로 설치 하게 되면, 설치 기본 경로라던가 엔진의 경로, datafile의 경로등 자신이 원하는 대로 설치가 용이 하지않고 나중에 수정해주는 작업은 매우 번거롭기도 하기 때문에 설치 단계에서부터 원하는 입맛대로 설치를 위한 방법을 알아보도록 하겠습니다.



CentOS 7 64bit 설치


PostgreSQL DB는 일반적으로 같은 오픈소스 OS인 CentOS를 많이 이용합니다.

psql을 사용하는 이유는 명백히 단가 절감을 위함인데, 라이센스 비용이 들어가는 OS, 즉, 윈도우나 유닉스에 설치하는 것은 그 의미가 퇴색된다고 봅니다. 그래서 CentOS 7 버전에 PostgreSQL을 설치합니다.



CentOS의 설치는 간단합니다. 오라클 설치 때와는 확연히 다르게, 미니멀 설치를 합니다.

설치를 하면서 중요한 부분입니다. 


Compatibility Library, Development Tools


두 패키지는 추가로 설치하도록 합니다. gcc나 openssh 등 기본적으로 개발자들이나 운영하는데 있어 추가적인 무언가 하기위해 필요한 기본적인 것들이 많이 들어있습니다.


그리고 설치가 완료 되면 yum으로 추가적인 것들을 설치 해줍니다.


# yum -y install net-tools elinks wget bind-utils vim sysstat


서버 네트워크를 Bonding 한다거나, 그 밖의 다른 설정이 필요하다면 다른 패키지들을 충분히 설치합니다.



그룹 및 유저 추가


yum으로 postgresql를 설치하면 기본적으로 postgres 그룹과 postgres 유저를 설치해줍니다.

유저 홈은 /var/lib/pgsql 으로 설정이 되고, 데이터의 기본 경로는 /var/lib/pgsql/9.6/data 밑으로 가게됩니다.

이게 참 애매한 부분이 있기도 하고 운영하는데 불편하기도 하고 해서 그룹과 유저를 기본적으로 세팅을 해놓고 시작을 해보겠습니다.


# groupadd -g 26 postgres


# useradd -u 26 -g postgres -d /home/postgres postgres


# passwd postgres


# su - postgres



프로파일 설정


$ vi .bash_profile


# .bash_profile


# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi


# User specific environment and startup programs


PATH=$PATH:$HOME/.local/bin:$HOME/bin


export PATH


[ -f /etc/profile ] && source /etc/profile

PGDATA=/usr/local/pgsql/data              <----- 데이터가 설치될 경로입니다.

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile


export PS1="\[\e[36;1m\]\u@\[\e[32;1m\]\h:\[\e[31;1m\]\w]$ \[\e[0m\]"

export TERM=linux


PATH=$PATH:$HOME/bin:/usr/pgsql-9.6/bin

export PATH


alias vi='vim $*'


:wq!



유저가 보기도 좋고 관리도 편하게 세팅이 됩니다.



PostgreSQL 설치


데이터를 설치할 경로, 로그를 쌓을 경로를 만들어주고 권한을 부여합니다. 데이터 경로나, 로그의 경로는 다르게 지정을 해도 됩니다. 스스로가 관리하기 편한 경로에 지정 해주는것이 좋습니다. /usr/local/pgsql/data 이 경로는 이전 버전에서 사용하던 경로 입니다. 예를 들면 /app/db/pgsql/data 이렇게 설치를 하고, /app/db/pgsql/diag/log 라는 경로를 만들어 로그를 쌓고 싶다면, 만들어주면 됩니다. DB 관리의 용이함을 위해 data 파티션을 따로 지정해준다거나 하는 것도 좋습니다. 


저는 usr과 var의 의미를 살리기 위해 저는 아래와 같이 설정하고 설치를 진행합니다.

 


# mkdir -p /usr/local/pgsql/data

# chown -R postgres:postgres /usr/local/pgsql


# mkdir -p /var/postgresql/log

# chown -R postgres:postgres /var/postgresql


yum을 이용해 설치


# yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

# yum -y install postgresql96 postgresql96-server


설치가 끝났습니다.

너무 간단하죠? 오라클에 비하면 할게 없습니다.



클러스터 초기화


PostgreSQL에서 클러스터란 database 집합체를 뜻한다고 설명한 적이 있습니다. 클러스터를 초기화 해야만 Database를 사용 할 수 있습니다. initdb 명령은 클러스터를 생성하거나 초기화 하는데 사용됩니다.


$ initdb -D /usr/local/pgsql/data



서버 실행


클러스터가 초기화 되면 Database를 시작 할 수 있습니다. PostgreSQL의 기본이 되는 postgres, template0, template1을 시작하는 것 입니다. 


$ pg_ctl start -D /usr/local/pgsql/data -l /var/postgresql/log/postgresql.log


서버를 실행하는 명령은 pg_ctl 뿐만 아니라 다른 명령도 있습니다.


$ postgres -D /usr/local/pgsql/data


이렇게만 실행해도 서버가 시작됩니다. postgresql의 공식 메뉴얼에는 백그라운드로 실행시키는 것은 권장하고 있습니다.


$ postgres -D /usr/local/pgsql/data >/var/postgresql/log/postgresql.log 2>&1 &


pg_ctl 명령으로는 백그라운드로 실행해도 포그라운드로 나오는데, 이건 좀 더 체크를 해볼 필요가 있을것 같습니다.


OS에서 Jobs를 확인해보면 백그라운드에서 실행중임을 알수 있습니다.


$ jobs

[1]+  Running                 postgres -D /usr/local/pgsql/data > /var/postgresql/log/postgresql.log 2>&1 &




설치 확인


$ psql -d postgres -U postgres


접속을 하면 sql 프롬프트가 나옵니다.


postgres=# 

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          +

(3 rows)


클러스터를 구성하는 3개의 DB가 시작된 것을 볼 수 있습니다.


postgres=# \q


하면 OS로 나갈 수 있습니다.

이 것으로 설치 및 확인은 완료 되었습니다.





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

초간단 postgresql.conf 설정  (0) 2018.12.27
pg_hba.conf  (0) 2018.12.27
Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25
PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03

PostgreSQL의 기본 개념

PostgreSQL의 기본개념


 오라클과 psql의 차이점 때문에 용어 선택에서 상당히 헷갈리는 부분이 존재합니다.



1) 용어 차이

 

- 오라클은 DB 엔진이 올라간 후, 인스턴스를 생성하면서 데이터베이스가 생성됩니다. 즉, 오라클에서는 하나의 인스턴스가 하나의 DB를 나타냅니다. 하나의 데이터베이스 엔진으로 여러개의 인스턴스를 생성하면 하나의 서버에 여러개의 DB를 생성 할 수 있습니다. 인스턴스 = DB의 개념이지만, RAC 환경에서는 다수의 인스턴스가 하나의 DB를 이루는 경우도 있습니다. 


- PostgreSQL에서 인스턴스란, 서버 단위의 개념입니다. 서버 단일 인스턴스라고 부르는 PostgreSQL의 인스턴스는 서버 = 인스턴스 개념입니다. 


- 오라클에서 클러스터는 RAC 환경의 공유 노드 개념입니다. 하나의 스토리지를 바라보는 여러대의 노드, 오라클 클러스터웨어인 grid instructure 를 통한 active-active의 환경을 조성하고 클러스터웨어를 통해 이루어진 공유 노드를 클러스터라고 합니다. 


- Psql에서의 클러스터는 Psql DB 집합체를 이야기 합니다. PostgreSQL은 처음 DB를 설치하면 기본적으로 설치되는 DB가 있는데, postgres database, template0, template1 생성하는데, 이 3가지의 DB 집합체를 클러스터라고 합니다.



2) 데이터베이스 구조


- PostgreSQL의 물리적 구조는 매우 단순합니다.

- Shared Memory, 적은 수의 백그라운드 프로세스, 데이터 파일로 구성되어 있습니다.



2-1) Shared Memory


2-1-1) Shared Buffer


 - Shared Buffer의 목적은 DISK I/O를 최소화 하는 것입니다.

 - 그러기 위해서는 아래 항목을 만족해야 합니다.


  ● 매우 큰 버퍼를 빠르게 엑세스해야 한다. (수백GB 단위)

  ● 많은 사용자가 동시에 접근할 때 경합을 최소화해야 한다.

  ● 자주 사용되는 블록은 최대한 오랫동안 버퍼 내에 있어야 한다.


2-1-2) WAL 버퍼


 - WAL 버퍼는 데이터베이스의 변경 사항은 잠시 저장하는 버퍼입니다.

 - WAL 버퍼 내에 저장된 내용은 정해진 시점에서 WAL 파일로 기록됩니다.

 - 백업 및 복구 관점에서 WAL 파일은 매우 중요하고, 오라클에서 Redo의 개념과 비슷한 부분이 있습니다.



2-2) 프로세스 유형


 - Postmaster (Daemon) 프로세스

 - Background 프로세스

 - Backend 프로세스

 - Client 프로세스


2-2-1) Postmaster 프로세스


 - PostgreSQL를 구동 할 때 가장 먼저 시작되는 프로세스 입니다.

 - 초기 기동시 복구 작업, Shared Memory 초기화 작업, 백그라운드 프로세스 구동작업을 수행합니다

 - 클라이언트 프로세스의 접속 요청이 있을 때 Backend 프로세스를 생성합니다.


2-2-2) Background 프로세스


 - Autovacuum launcher를 제외하면, 오라클과 비슷한 백그라운드 프로세스들이 존재합니다.


 ● logger : 에러메세지를 로그 파일에 기록한다.

 ● checkpointer : 체크포인트 발생시, dirty 버퍼를 파일에 기록한다.

 ● writer : 주기적으로 dirty 버퍼를 파일에 기록한다.

 ● wal writer : WAL버퍼 내용을 WAL 파일에 기록한다.

 ● autovacuum launcher : Vacuum이 필요한 시점에 autovacuum worker를 fork 한다.

 ● archiver : Archive Log 모드일 때, WAL 파일을 지정된 디렉토리에 복사한다.

 ● stats collector : 세션 수행 정보 (pg_stat_activity)와 테이블 사용 통계 정보 (pg_stat_all_tables)와 같은 DBMS 사용 통계 정보를 수집한다.


2-2-3) Backend 프로세스


 - Backend 프로세스의 최대 개수는 max_connections 파라미터로 설정 가능하며, 기본값은 100입니다.

 - Backend 프로세스는 사용자 프로세스의 쿼리 요청을 수행한 후, 결과를 전송하는 역할을 수행합니다.

 - 쿼리 수행에 몇가지 메모리 구조가 필요한데, 이 것을 통칭해서 로컬 메모리라고 합니다.

 

 - 로컬 메모리 관련 주요 파라미터


 ◈ work_mem 파라미터

  - 정렬 작업, Bitmap 작업, 해시 조인과 Merge조인 작업시에 사용되는 공간. 기본값은 4MB

 

 ◈ maintenance_work_mem 파라미터

  - Vacuum 및 create index 작업 시에 사용되는 공간. 기본값은 64MB


 ◈ temp_buffers 파라미터

  - Temporary 테이블을 저장하기 위한 공간. 기본값은 8MB



 



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

pg_hba.conf  (0) 2018.12.27
Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25
PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03
PostgreSQL 백업 및 복구  (0) 2018.11.24

pgadmin4 설치

pgadmin 은 웹에서 postgresql을 관리 해줄수 있게 해주는 관리 툴입니다.

오라클 EM 같은거라고 보시면 됩니다.



SELINUX 해제


setenforce 0

sed -i 's/^SELINUX=.*/SELINUX=permissive/g' /etc/selinux/config



Yum으로 pgadmin 설치


# yum install  https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm -y

# yum install epel-release


# yum install pgadmin4



pgadmin4 환경설정


# vi /usr/lib/python2.7/site-packages/pgadmin4-web/config_distro.py

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'

SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'

SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'

STORAGE_DIR = '/var/lib/pgadmin4/storage'



# python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py

NOTE: Configuring authentication for SERVER mode.


Enter the email address and password to use for the initial pgAdmin user account:


Email address: <이메일 주소>

Password: <패스워드>

Retype password: <패스워드 확인>

pgAdmin 4 - Application Initialisation

======================================



# mkdir -p /var/lib/pgadmin4/

# mkdir -p /var/log/pgadmin4/


# chown -R apache:apache /var/lib/pgadmin4

# chown -R apache:apache /var/log/pgadmin4


# chcon -R -t httpd_sys_content_rw_t "/var/log/pgadmin4/"

# chcon -R -t httpd_sys_content_rw_t "/var/lib/pgadmin4/"




virtual host 만들기


# vi /etc/httpd/conf.d/pgadmin4.conf


<VirtualHost *>

    ServerName 192.168.0.9/pgadmin


    WSGIDaemonProcess pgadmin processes=1 threads=25

    WSGIScriptAlias / /usr/lib/python2.7/site-packages/pgadmin4-web/pgAdmin4.wsgi

 

    <Directory "/usr/lib/python2.7/site-packages/pgadmin4-web/">

        WSGIProcessGroup pgadmin

        WSGIApplicationGroup %{GLOBAL}

        Require all granted

    </Directory>

</VirtualHost>



# apachectl configtest

Syntax OK



# systemctl restart httpd


# setsebool -P httpd_can_network_connect 1


# firewall-cmd --permanent --add-service=http

# firewall-cmd --reload

success



웹 브라우저에서 192.168.0.9/pgadmin 으로 접속해보면 아래와 같은 화면이 나오고 위에서 입력한 메일 주소로 로그인이 가능합니다.




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

Linux에 Postgres 9.6 버전 설치  (0) 2018.12.25
PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03
PostgreSQL 백업 및 복구  (0) 2018.11.24
PostgreSQL 소스 코드로 설치  (0) 2018.04.03

PostgreSQL 11 설치

Linux Cent OS 7.5 x64


PostgreSQL 11



우선 yum repository를 설치합니다. 


# yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm



그 후에 yum으로 postgresql을 설치 합니다.


# yum -y install postgresql11-server postgresql11



설치하고 나면 postgres 라는 계정이 생성됩니다.


# su - postgres

-bash-4.2$


프로토콜이 보기 불편하니 변경해줍니다.


$ vi .bash_profile


export PS1="[\u@\h:\w]\\$ "   

추가


root에서 DB를 서비스에 init 합니다.


[root@psql-db ~]# /usr/pgsql-11/bin/postgresql-11-setup initdb

Initializing database ... OK


systemctl 로 DB를 구동하고, 서버 실행시 자동 실행되게 등록해줍니다.


[root@psql-db ~]# systemctl start postgresql-11

[root@psql-db ~]# systemctl enable postgresql-11

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.

[root@psql-db ~]#


방화벽에 PostgreSQL을 차단하지 않게 정책을 추가해줍니다.


[root@psql-db ~]# firewall-cmd --add-service=postgresql --permanent

success

[root@psql-db ~]# firewall-cmd --reload

success

[root@psql-db ~]#



PostgreSQL의 conf 파일은 /var/lib/pgsql/11/data/postgresql.conf 있습니다.


vi로 열어서 listener  설정을 해줍니다.



listen_addresses = '192.168.0.9'



remote 접속 허용 설정을 해줍니다.


[root@psql-db ~]# vi /var/lib/pgsql/11/data/pg_hba.conf


# Accept from anywhere  (아무나 다)

host all all 0.0.0.0/0 md5


or


# Accept from trusted subnet (특정 대역대만)

host all all 192.168.18.0/24 md5



예>


# IPv4 local connections:

host    all             all             0.0.0.0/0               md5

#host    all             all             127.0.0.1/32            ident


Postgres 재구동


# systemctl restart postgresql-11



TEST 유저와 TEST DB를 만들고 접속 해봅니다.


[postgres@psql-db:~]$ createuser test_user

[postgres@psql-db:~]$ createdb test_db -O test_user

[postgres@psql-db:~]$ psql -c "alter user test_user with password 'test'"


권한을 주기위해 admin 유저로 접속을 합니다. postgres/postgres


[postgres@psql-db:~]$ psql -U postgres -d postgres

psql (9.2.24, server 11.1)

WARNING: psql version 9.2, server version 11.0.

         Some psql features might not work.

Type "help" for help.


postgres=# 

postgres=# 

postgres=# grant all privileges on database test_db to test_user;

GRANT

postgres=# \q


터미널에서 TEST DB에 접속


[postgres@psql-db:~]$ psql -U test_user -h 192.168.0.9 -d test_db

psql (9.2.24, server 11.1)

WARNING: psql version 9.2, server version 11.0.

         Some psql features might not work.

Type "help" for help.


test_db=>


설치가 완료 되었습니다.

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

PostgreSQL의 기본 개념  (0) 2018.12.12
pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03
PostgreSQL 백업 및 복구  (0) 2018.11.24
PostgreSQL 소스 코드로 설치  (0) 2018.04.03
PostgreSQL 버그 리포팅 가이드 라인  (0) 2018.04.03

PostgreSQL 백업 및 복구

DATABASE 및 TABLE 백업 / 복구



백업


superuser 계정으로 접속



문법>


pg_dump [DATABASE명] -t [TABLE명] > [저장 될 DUMP FILE명]



예)


[postgres@test data]$ pg_dump pgsqldb -t tb_test > PGSQL_tb_test_20090830.dump

[postgres@test data]$



복구


superuser 계정으로 접속



문법>


psql -f [FILE명] [DATABASE명]



예)

[postgres@test data]$ psql -f PGSQL_tb_test_20090830.dump pgsqldb

SET

SET

SET

SET

SET

SET

SET

SET

CREATE TABLE

ALTER TABLE

[postgres@test data]$




전체 DATABASE 백업 / 복구



백업


superuser 계정으로 접속



문법>


pg_dumpall > [저장 될 DUMP FILE명]



예)


[postgres@test data]$ pg_dumpall > PGSQL_all_20090830.dump

[postgres@test data]$



복구


superuser 계정으로 접속



문법>


psql -f [FILE명] template1




예)


[postgres@test data]$ psql -f PGSQL_all_20090830.dump template1

SET

SET

SET

CREATE TABLE

ALTER TABLE

...

[postgres@test data]$



백업 스크립트


#!/bin/bash

#This script creates backup of your postgresql databases,

#you can select databases you want/dont want,

#script will send e-mail to you when needed.


#I need user and password

export PGUSER=postgres

export PGPASSWORD=


DBNAME=abcd


#Where should i save pg dump-s?

TARGET=/mnt/seeme


#Give pg_dump location

PGDUMP=/usr/lib/postgresql/9.3/bin/pg_dump

#Give me psql location

PSQL=/usr/lib/postgresql/9.3/bin/psql

#Give me psql connection port

PORT=5432


BEFOREDATE=$(date '+%Y-%m-%d' -d '10 day ago')

rm -rf $TARGET/abcd-$BEFOREDATE.sql


$PGDUMP -h 127.0.0.1 -p $PORT -U $PGUSER -d $DBNAME -f $TARGET/abcd-`date +%Y-%m-%d`.sql

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

pgadmin4 설치  (0) 2018.12.03
PostgreSQL 11 설치  (0) 2018.12.03
PostgreSQL 백업 및 복구  (0) 2018.11.24
PostgreSQL 소스 코드로 설치  (0) 2018.04.03
PostgreSQL 버그 리포팅 가이드 라인  (0) 2018.04.03
PostgresSQL 이란?  (0) 2018.04.03

RMAN 으로 Raw device 백업 복구하기

테스트 1 : Raw device 상태의 데이터파일을 RMAN 의 backupset 으로 백업 받은 후 정상적으로 복구 되는지 확인


Step 1. 현재상태 확인


[oracle@localhost ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 1 11:05:19 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> !vi dd.sql


set line 200

col tablespace_name for a10

col file_name for a45

select tablespace_name,bytes/1024/1024 MB,file_name from dba_data_files

/

:wq!


SQL> @dd <- Data file 상태 확인


TABLESPACE MB FILE_NAME

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

USERS 5 /dev/raw/raw4

SYSAUX 250 /dev/raw/raw2

UNDOTBS1 30 /dev/raw/raw3

SYSTEM 440 /dev/raw/raw1

EXAMPLE 100 /dev/raw/raw5


SQL> !vi log.sql <- Redo log file 상태 확인


set line 200

col group# for 999

col member for a20

col mb for 999

col seq# for 999

col archived for a4

col status for a8

select a.group#,a.member,b.bytes/1024/1024 MB,b.sequence# "SEQ#" ,b.archived,b.status

from v$logfile a, v$log b

where a.group#=b.group#

order by 1,2

/

:wq!


SQL> @log


GROUP# MEMBER MB SEQ# ARCH STATUS

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

1 /dev/raw/raw10 50 4 NO CURRENT

1 /dev/raw/raw9 50 4 NO CURRENT

2 /dev/raw/raw11 50 2 YES INACTIVE

2 /dev/raw/raw12 50 2 YES INACTIVE

3 /dev/raw/raw13 50 3 YES INACTIVE

3 /dev/raw/raw14 50 3 YES INACTIVE

6 rows selected.


SQL> select name from v$controlfile;


NAME

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

/dev/raw/raw6

/dev/raw/raw7

/dev/raw/raw8


SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /data/arc2

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4


Step 2. RMAN 을 사용하여 전체 백업(백업경로는 /data/backup/rman/ 으로 하겠습니다)


[oracle@localhost ~]$ rman target /


Recovery Manager: Release 10.2.0.5.0 - Production on Wed Feb 1 10:47:50 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2557423915)


RMAN> backup as compressed backupset

2> format '/data/backup/rman/%U_%T'

3> database;

Starting backup at 01-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=144 devtype=DISK

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/dev/raw/raw1

input datafile fno=00003 name=/dev/raw/raw2

input datafile fno=00005 name=/dev/raw/raw5

input datafile fno=00002 name=/dev/raw/raw3

input datafile fno=00004 name=/dev/raw/raw4

channel ORA_DISK_1: starting piece 1 at 01-FEB-12

channel ORA_DISK_1: finished piece 1 at 01-FEB-12

piece handle=/data/backup/rman/01n27hl4_1_1_20120201 tag=TAG20120201T104907

comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 01-FEB-12

channel ORA_DISK_1: finished piece 1 at 01-FEB-12

piece handle=/data/backup/rman/02n27hm8_1_1_20120201 tag=TAG20120201T104907

comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 01-FEB-12


[oracle@localhost ~]$ ls -lSh /data/backup/rman/

합계 112M

-rw-r----- 1 oracle dba 111M 2월 1 10:49 01n27hl4_1_1_20120201 <- 전체 backupset

-rw-r----- 1 oracle dba 1.1M 2월 1 10:49 02n27hm8_1_1_20120201 <- controlfile backupset


Step 3. 테스트용 테이블 scott.gogak 테이블 생성 후 장애 발생시킴


[oracle@localhost ~]$ sqlplus scott/tiger


SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 1 11:14:21 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> create table gogak (no number, name varchar2(10)) tablespace users;

Table created.


SQL> insert into gogak values (1,'AAA');

1 row created.


SQL> insert into gogak values (2,'BBB');

1 row created.


SQL> commit;

Commit complete.


SQL> select * from gogak;


NO NAME

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

1 AAA

2 BBB


SQL> conn / as sysdba

Connected.


SQL> @dd


TABLESPACE MB FILE_NAME

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

USERS 5 /dev/raw/raw4 <- 이 파일에 장애 발생시킴

SYSAUX 250 /dev/raw/raw2

UNDOTBS1 30 /dev/raw/raw3

SYSTEM 440 /dev/raw/raw1

EXAMPLE 100 /dev/raw/raw5


SQL> !dd if=/dev/zero of=/dev/raw/raw4 bs=8k

dd: writing `/dev/raw/raw4': 장치에 남은 공간이 없음

12801+0개의 레코드를 입력하였습니다

12800+0개의 레코드를 출력하였습니다


SQL> alter tablespace users offline immediate;

Tablespace altered.


SQL> alter tablespace users online;

alter tablespace users online

*

ERROR at line 1:

ORA-01122: database file 4 failed verification check

ORA-01110: data file 4: '/dev/raw/raw4'

ORA-01210: data file header is media corrupt


SQL> select * from scott.gogak;

select * from scott.gogak

*

ERROR at line 1:

ORA-00376: file 4 cannot be read at this time

ORA-01110: data file 4: '/dev/raw/raw4'


Step 4. RMAN 으로 해당 장애 복구하기


[oracle@localhost ~]$ rman target /


Recovery Manager: Release 10.2.0.5.0 - Production on Wed Feb 1 11:19:20 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2557423915)

RMAN> run {

2> sql "alter tablespace users offline immediate";

3> restore tablespace users;

4> recover tablespace users;

5> sql "alter tablespace users online";

6> }

sql statement: alter tablespace users offline immediate

Starting restore at 01-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to /dev/raw/raw4

channel ORA_DISK_1: reading from backup piece /data/backup/rman/01n27hl4_1_1_20120201

channel ORA_DISK_1: restored backup piece 1

piece handle=/data/backup/rman/01n27hl4_1_1_20120201 tag=TAG20120201T104907

channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished restore at 01-FEB-12

Starting recover at 01-FEB-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 01-FEB-12

sql statement: alter tablespace users online


RMAN> exit

Recovery Manager complete.


[oracle@localhost ~]$ sqlplus scott/tiger


SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 1 11:21:53 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select * from gogak;


NO NAME

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

1 AAA

2 BBB


위에서 테스트 해 본 바와 같이 Raw device 도 RMAN 으로 백업 및 복구가 가능합니다.



테스트 2. Raw device 상태의 데이터 파일을 backupset 으로 증분 백업 가능한지와 복구 가능한지 테스트



Step 1. 현재 상태 확인


[oracle@localhost ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 1 11:24:25 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> @dd


TABLESPACE MB FILE_NAME

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

USERS 5 /dev/raw/raw4

SYSAUX 250 /dev/raw/raw2

UNDOTBS1 30 /dev/raw/raw3

SYSTEM 440 /dev/raw/raw1

EXAMPLE 100 /dev/raw/raw5


SQL> @log


GROUP# MEMBER MB SEQ# ARCH STATUS

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

1 /dev/raw/raw10 50 4 NO CURRENT

1 /dev/raw/raw9 50 4 NO CURRENT

2 /dev/raw/raw11 50 2 YES INACTIVE

2 /dev/raw/raw12 50 2 YES INACTIVE

3 /dev/raw/raw13 50 3 YES INACTIVE

3 /dev/raw/raw14 50 3 YES INACTIVE


SQL> select name from v$controlfile;


NAME

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

/dev/raw/raw6

/dev/raw/raw7

/dev/raw/raw8

6 rows selected.



Step 2. RMAN 을 사용하여 증분 백업을 여러 차례 수행합니다.


[oracle@localhost ~]$ rman target /


Recovery Manager: Release 10.2.0.5.0 - Production on Wed Feb 1 11:28:05 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TESTDB (DBID=2557423915)


RMAN> backup as compressed backupset

2> incremental level 0

3> format '/data/backup/rman/%U_%T'

4> database;

Starting backup at 01-FEB-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=145 devtype=DISK

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/dev/raw/raw1

input datafile fno=00003 name=/dev/raw/raw2

input datafile fno=00005 name=/dev/raw/raw5

input datafile fno=00002 name=/dev/raw/raw3

input datafile fno=00004 name=/dev/raw/raw4

channel ORA_DISK_1: starting piece 1 at 01-FEB-12

channel ORA_DISK_1: finished piece 1 at 01-FEB-12

piece handle=/data/backup/rman/03n27jur_1_1_20120201 tag=TAG20120201T112827

comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting compressed full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 01-FEB-12

channel ORA_DISK_1: finished piece 1 at 01-FEB-12

piece handle=/data/backup/rman/04n27jvu_1_1_20120201 tag=TAG20120201T112827

comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 01-FEB-12

-- 다른 터미널에서 데이터 변경을 위해 테이블 생성하고 데이터 입력합니다.


SQL> create table scott.gogak2 (no number,name varchar2(10)) tablespace users;

Table created.


SQL> insert into scott.gogak2 values (1,'CCC');

1 row created.


SQL> insert into scott.gogak2 values (2,'DDD');

1 row created.


SQL> commit;

Commit complete.


-- RMAN 터미널에서 증분 백업을 수행합니다.

RMAN> backup as compressed backupset

2> incremental level 2

3> format '/data/backup/rman/%U_%T'

4> tablespace users;

Starting backup at 01-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed incremental level 2 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00004 name=/dev/raw/raw4

channel ORA_DISK_1: starting piece 1 at 01-FEB-12

channel ORA_DISK_1: finished piece 1 at 01-FEB-12

piece handle=/data/backup/rman/05n27knu_1_1_20120201 tag=TAG20120201T114150

comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 01-FEB-12



Step 3. 장애를 발생 시킵니다


SQL> @dd


TABLESPACE MB FILE_NAME

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

USERS 5 /dev/raw/raw4

SYSAUX 250 /dev/raw/raw2

UNDOTBS1 30 /dev/raw/raw3

SYSTEM 440 /dev/raw/raw1

EXAMPLE 100 /dev/raw/raw5


SQL> !dd if=/dev/zero of=/dev/raw/raw4 bs=8k <- 장애를 발생시킵니다

dd: writing `/dev/raw/raw4': 장치에 남은 공간이 없음

12801+0개의 레코드를 입력하였습니다

12800+0개의 레코드를 출력하였습니다


SQL> alter tablespace users offline immediate;

Tablespace altered.


SQL> alter tablespace users online;

alter tablespace users online

*

ERROR at line 1:

ORA-01122: database file 4 failed verification check

ORA-01110: data file 4: '/dev/raw/raw4'

ORA-01210: data file header is media corrupt


SQL> select * from scott.gogak2;

select * from scott.gogak2

*

ERROR at line 1:

ORA-00376: file 4 cannot be read at this time

ORA-01110: data file 4: '/dev/raw/raw4'



Step 4. RMAN 으로 복구 후 확인합니다.


RMAN> run {

2> restore tablespace users;

3> recover tablespace users;

4> sql "alter tablespace users online" ;

5> }

Starting restore at 01-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to /dev/raw/raw4

channel ORA_DISK_1: reading from backup piece /data/backup/rman/05n27knu_1_1_20120201

channel ORA_DISK_1: restored backup piece 1

piece handle=/data/backup/rman/05n27knu_1_1_20120201 tag=TAG20120201T114150

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 01-FEB-12

Starting recover at 01-FEB-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 01-FEB-12

sql statement: alter tablespace users online


SQL> select * from scott.gogak2;


NO NAME

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

1 CCC

2 DDD



테스트 3. RMAN 으로 Raw device 상태의 데이터 파일을 Raw device 형태로 백업 받은 후 복구 가능한지 테스트



Step 1. 현재 상태 확인


RMAN> report schema;

Report of database schema

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

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

1 440 SYSTEM *** /dev/raw/raw1

2 30 UNDOTBS1 *** /dev/raw/raw3

3 250 SYSAUX *** /dev/raw/raw2

4 5 USERS *** /dev/raw/raw4

5 100 EXAMPLE *** /dev/raw/raw5

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

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

1 20 TEMP 100 /dev/raw/raw15


Step 2. RMAN 으로 Raw device 파일 백업 수행


RMAN> copy

2> datafile 1 to '/data/backup/rman/system' ,

3> datafile 2 to '/data/backup/rman/undotbs1' ,

4> datafile 3 to '/data/backup/rman/sysaux' ,

5> datafile 4 to '/data/backup/rman/users' ,

6> datafile 5 to '/data/backup/rman/example' ;

Starting backup at 01-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/dev/raw/raw1

output filename=/data/backup/rman/system tag=TAG20120201T115814 recid=9

stamp=774100724

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/dev/raw/raw2

output filename=/data/backup/rman/sysaux tag=TAG20120201T115814 recid=10

stamp=774100744

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16

channel ORA_DISK_1: starting datafile copy

input datafile fno=00005 name=/dev/raw/raw5

output filename=/data/backup/rman/example tag=TAG20120201T115814 recid=11

stamp=774100749

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/dev/raw/raw3

output filename=/data/backup/rman/undotbs1 tag=TAG20120201T115814 recid=12

stamp=774100754

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/dev/raw/raw4

output filename=/data/backup/rman/users tag=TAG20120201T115814 recid=13

stamp=774100756

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 01-FEB-12

-- 다른 창에서 백업 파일을 확인합니다


[oracle@localhost rman]$ pwd

/data/backup/rman


[oracle@localhost rman]$ ls –lSh

-rw-r----- 1 oracle dba 441M 2?? 1 11:58 system

-rw-r----- 1 oracle dba 251M 2?? 1 11:59 sysaux

-rw-r----- 1 oracle dba 101M 2?? 1 11:59 example

-rw-r----- 1 oracle dba 31M 2?? 1 11:59 undotbs1

-rw-r----- 1 oracle dba 5.1M 2?? 1 11:59 users


Step 3. 테스트용 테이블 scott.gogak3 을 생성 후 장애를 발생시킵니다.


SQL> create table scott.gogak3 (no number, name varchar2(10))

2 tablespace users;

Table created.


SQL> insert into scott.gogak3 values (1,'EEE');

1 row created.


SQL> insert into scott.gogak3 values (2,'FFF');

1 row created.


SQL> commit;

Commit complete.


SQL> select * from scott.gogak3;


NO NAME

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

1 EEE

2 FFF


SQL> @dd


TABLESPACE MB FILE_NAME

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

USERS 5 /dev/raw/raw4

SYSAUX 250 /dev/raw/raw2

UNDOTBS1 30 /dev/raw/raw3

SYSTEM 440 /dev/raw/raw1

EXAMPLE 100 /dev/raw/raw5

SQL> !dd if=/dev/zero of=/dev/raw/raw4 bs=8k

dd: writing `/dev/raw/raw4': 장치에 남은 공간이 없음

12801+0개의 레코드를 입력하였습니다

12800+0개의 레코드를 출력하였습니다


SQL> alter tablespace users offline immediate;

Tablespace altered.


SQL> alter tablespace users online;

alter tablespace users online

*

ERROR at line 1:

ORA-01122: database file 4 failed verification check

ORA-01110: data file 4: '/dev/raw/raw4'

ORA-01210: data file header is media corrupt


SQL> select * from scott.gogak3;

select * from scott.gogak3

*

ERROR at line 1:

ORA-00376: file 4 cannot be read at this time

ORA-01110: data file 4: '/dev/raw/raw4'


Step 4. RMAN 으로 파일을 복원 한 후 복구합니다.


RMAN> restore tablespace users;

Starting restore at 01-FEB-12

using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004

input datafile copy recid=13 stamp=774100756 filename=/data/backup/rman/users

destination for restore of datafile 00004: /dev/raw/raw4

channel ORA_DISK_1: copied datafile copy of datafile 00004

output filename=/dev/raw/raw4 recid=14 stamp=774101682

Finished restore at 01-FEB-12

위에서 진하게 표시된 부분을 보면 RMAN 에서 자동으로 copy 된 raw device 를 찾아서

복원하는 것을 알 수 있습니다.


RMAN> recover tablespace users;

Starting recover at 01-FEB-12

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 01-FEB-12


-- sqlplus 창에서 데이터가 복구되었는지 확인합니다.


SQL> alter tablespace users online;

Tablespace altered.


SQL> select * from scott.gogak3;

NO NAME

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

1 EEE

2 FFF


정상적으로 복구가 된 것을 알 수 있습니다.

위에서 살펴본 바와 같이 Raw Device 로 구성된 database 도 RMAN 으로 동일하게 관리할 수 있습니다.

Oracle 18c 설치 Single

오라클 18c가 릴리즈 되었는데 설치 방법이 약간 바뀌었다.


18c는 2018.08.21 기준, 리눅스와 솔라리스 버전만 릴리즈 되었다.



해당 예제는 리눅스 7버전에 설치를 진행 한다.



리눅스 7의 Oracle 설치를 위한 기본 설치 패키지



Server with GUI

Hardware Monitoring Utilities

Large Systems Performance

Network file system client

Performance Tools

Compatibility Libraries

Development Tools


# vi /etc/hosts


# yum update -y




자동 셋업


# yum install -y install oracle-database-preinstall-18c




수동 셋업


# vi /etc/sysctl.conf


fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500



# vi /etc/security/limits.d/oracle-database-preinstall-18c.conf


oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728



패키지 설치 목록


# OL6 and OL7 (RHEL6 and RHEL7)

yum install -y bc    

yum install -y binutils

yum install -y compat-libcap1

yum install -y compat-libstdc++-33

yum install -y compat-libstdc++-33.i686

yum install -y elfutils-libelf.i686

yum install -y elfutils-libelf

yum install -y elfutils-libelf-devel.i686

yum install -y elfutils-libelf-devel

yum install -y fontconfig-devel

yum install -y glibc.i686

yum install -y glibc

yum install -y glibc-devel.i686

yum install -y glibc-devel

yum install -y ksh

yum install -y libaio.i686

yum install -y libaio

yum install -y libaio-devel.i686

yum install -y libaio-devel

yum install -y libX11.i686

yum install -y libX11

yum install -y libXau.i686

yum install -y libXau

yum install -y libXi.i686

yum install -y libXi

yum install -y libXtst.i686

yum install -y libXtst

yum install -y libgcc.i686

yum install -y libgcc

yum install -y librdmacm-devel

yum install -y libstdc++.i686

yum install -y libstdc++

yum install -y libstdc++-devel.i686

yum install -y libstdc++-devel

yum install -y libxcb.i686

yum install -y libxcb

yum install -y make

yum install -y nfs-utils

yum install -y net-tools

yum install -y python

yum install -y python-configshell

yum install -y python-rtslib

yum install -y python-six

yum install -y smartmontools

yum install -y sysstat

yum install -y targetcli

yum install -y unixODBC



그룹 및 유저 추가


# groupadd -g 54321 oinstall

# groupadd -g 54322 dba

# groupadd -g 54323 oper


# useradd -u 54321 -g oinstall -G dba,oper oracle




추가 세팅



SELinux 설정


# vi /etc/selinux/config


SELINUX=permissive


# setenforce Permissive

※ 리눅스 7 버전은 SELinux 설정 후 재구동 필요없음.



방화벽 Off


# systemctl stop firewalld

# systemctl disable firewalld



디렉토리 생성


# mkdir -p /u01/app/oracle/product/18.0.0/dbhome_1

# mkdir -p /u02/oradata

# chown -R oracle:oinstall /u01 /u02

# chmod -R 775 /u01 /u02


* u01 - 엔진

* u02 - 데이터



프로파일 설정


기존의 방식대로 해도 되고, 

아래는 18c 부터 오라클이 제공하는 방식을 설명한다.


$ mkdir /home/oracle/scripts


$ touch /home/oracle/scripts/setEnv.sh


$ cat > /home/oracle/scripts/setEnv.sh <<EOF

# Oracle Settings

export TMP=/tmp

export TMPDIR=\$TMP


export ORACLE_HOSTNAME=ol7-183.localdomain

export ORACLE_UNQNAME=cdb1

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=\$ORACLE_BASE/product/18.0.0/dbhome_1

export ORA_INVENTORY=/u01/app/oraInvenotry

export ORACLE_SID=cdb1

export PDB_NAME=pdb1

export DATA_DIR=/u02/data


export PATH=/usr/sbin:/usr/local/bin:\$PATH

export PATH=\$ORACLE_HOME/bin:\$PATH


export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib

EOF


$ echo ". /home/oracle/scripts/setEnv.sh" >> /home/oracle/.bash_profile



시작, 종료 스크립트 작성


$ touch /home/oracle/scripts/start_all.sh

$ touch /home/oracle/scripts/stop_all.sh


$ cat > /home/oracle/scripts/start_all.sh <<EOF

#!/bin/bash

. /home/oracle/scripts/setEnv.sh


export ORAENV_ASK=NO

. oraenv

export ORAENV_ASK=YES


dbstart \$ORACLE_HOME

EOF



$ cat > /home/oracle/scripts/stop_all.sh <<EOF

#!/bin/bash

. /home/oracle/scripts/setEnv.sh


export ORAENV_ASK=NO

. oraenv

export ORAENV_ASK=YES


dbshut \$ORACLE_HOME

EOF



$ chown -R oracle.oinstall /home/oracle/scripts

$ chmod u+x /home/oracle/scripts/*.sh


설치 완료후에 /etc/oratab에 넣어주고, 오라클 유저로 시작/종료 할 수 있다.


~/scripts/start_all.sh

~/scripts/stop_all.sh



엔진 설치


Oracle 홈페이지에서 LINUX.X64_180000_db_home.zip 파일을 받을 수 있다.


해당 파일을 반드시 /u01/app/oracle/product/18.0.0/dbhome_1 폴더로 옮겨서 압축을 푼다.


$ cd /u01/app/oracle/product/18.0.0/dbhome_1



X매니저 나 Gnome 환경에서 GUI 방식으로 설치 한다.


$ ./runInstaller




엔진 설치 완료


DBCA는 12c와 동일하다.

오라클에서 ODBC를 이용한 MySQL으로 DB LINK 설정

오라클에서 ODBC를 이용한 MySQL으로 DB LINK 설정



테스트 환경



오라클 DB 서버


Oracle Linux 6.9

Oracle 11gR2 - 11.2.0.4

ODBC Driver 8.0.11 (홈페이지에서 받아서 설치, https://dev.mysql.com/downloads/connector/odbc/)

캐릭터셋 - KO16MSWIN949

ip - 192.168.0.55



MySQL DB 서버


CentOS 7.2

MySQL 5.7 (yum 설치)

Oracle Gateway 11gR2 - 11.2.0.4

캐릭터셋 - euckr

ip - 192.168.0.44

Database - MYSQL



오라클 서버에서 설정


버전에 맞는 ODBC 드라이버를 받아서 yum으로 설치하면 /etc/odbc.ini, /etc/odbcinst.ini 파일이 보인다.


vi로 odbcinst.ini 열어보면


[MySQL ODBC 8.0 Unicode Driver]

Driver = /usr/lib64/libmyodbc8w.so

UsageCount = 1


[MySQL ODBC 8.0 ANSI Driver]

Driver = /usr/lib64/libmyodbc8a.so

UsageCount = 1


이 것이 MySQL 연결에 사용될 드라이버 인데, 주의할점은 각 DB의 캐릭터 셋에 맞춰 드라이버를 설정해줘야 한다.

UTF8를 사용할 것이라면 Unicode 드라이버를, EUCKR을 사용할 것 이라면 ANSI를 선택해야 한다.



# vi /etc/odbc.ini


[MYSQL]

Driver = /usr/lib64/libmyodbc8a.so

trace = off

port = 3306

server = 192.168.0.44

database = MYSQL

option = 2

user = DEMO

password = PASSWORD

charset = euckr



$ vi tnsnames.ora


MYSQL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.44)(PORT = 1521))

      (CONNECT_DATA = (SID = MYSQL))

      (HS = OK)

  )



$ isql -v MYSQL 

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

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

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

SQL>


이렇게 나오면 일단 MySQL DB로 원격접속이 되는것이다.


※ 사전에 MySQL 서버의 방화벽을 내린다거나, MySQL 계정에 원격접속 가능한 권한을 줘야 한다.




MySQL 서버에서의 설정


MySQL은 기호에 맞게 설치하면 되고, 오라클 Gateway를 설치한다. 오라클 GUI 설치 할때와 같은 환경으로 맞춰서 설치한다.


# groupadd oinstall

# groupadd dba

# useradd -g oinstall -G dba oracle

# su - oracle



$ vi .bash_profile


export ODBCINI=/etc/odbc.ini

export ORACLE_HOME=/oracle/product/11.2.0/tg_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/lib64:/lib

export PATH=$ORACLE_HOME/bin:$PATH


GUI에서 gateway를 설치할때 


ODBC 부분을 체크 해서 설치 해준다. (해당 스샷은 설치 후 캡처를 위해 다시 창을 띄운거라 reinstall 이라고 나옴)


Gateway 설치 후 HS 파라미터를 설정해야 한다.


$ cd $ORACLE_HOME/hs/admin



$ vi initMYSQL.ora (initXXXX.ora)    <-- [아래 HS_FDS_CONNECT_INFO = XXXX 의 XXXX 부분과 동일 하게 설정]


# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC


#

# HS init parameters

#

HS_FDS_CONNECT_INFO = MYSQL      <-- [해당부분은 접속 하고자하는 MySQL DB 명 입력]

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so      <-- [사용하고자 하는 ODBC 드라이버]

HS_FDS_TRACE_LEVEL = OFF                <--[접속은 되는데 결과값이 안나온다면 255 로 설정해 trace 확인]

HS_FDS_SUPPORT_STATISTICS = FALSE

HS_LANGUAGE=AMERICAN_AMERICA.KO16MSWIN949     <--[Oracle 서버쪽 DB의 캐릭터셋]

HS_RPC_FETCH_REBLOCKING = OFF

HS_KEEP_REMOTE_COLUMN_SIZE = ALL

HS_NLS_LENGTH_SEMANTICS = VARCHAR                  <-- [VARCHAR or CHAR 등으로 환경에 맞게 설정 가능]

HS_FDS_TIMESTAMP_MAPPING = DATE

HS_NLS_NCHAR=UCS2


#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini


#

# Environment variables required for the non-Oracle system

#



Oracle Gateway를 이용해서 MySQL 서버에 오라클 Listener를 띄운다.

$ vi $ORACLE_HOME/network/admin/listener.ora

# listener.ora Network Configuration File: /oracle/product/11.2.0/tg_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME=MYSQL)
      (ORACLE_HOME=/oracle/product/11.2.0/tg_1)
      (PROGRAM=/oracle/product/11.2.0/tg_1/bin/dg4odbc)
      (ENVS=LD_LIBRARY_PATH = /usr/lib:/usr/lib64:/oracle/product/11.2.0/tg_1/lib:/oracle/product/11.2.0/tg_1/hs/lib)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.44)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


ADR_BASE_LISTENER = /oracle/product/11.2.0/tg_1


$ lsnrctl start

$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 27-JUL-2018 11:35:58

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /oracle/product/11.2.0/tg_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /oracle/product/11.2.0/tg_1/network/admin/listener.ora
Log messages written to /oracle/product/11.2.0/tg_1/diag/tnslsnr/MYSQL/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1192.168.0.44)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.44)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                27-JUL-2018 11:35:58
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/11.2.0/tg_1/network/admin/listener.ora
Listener Log File         /oracle/product/11.2.0/tg_1/diag/tnslsnr/MYSQL/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.44)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "MYSQL" has 1 instance(s).
  Instance "MYSQL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



오라클 서버에서 tnsping을 실행 했을때


$ tnsping mysql


TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 27-JUL-2018 11:19:40


Copyright (c) 1997, 2013, Oracle.  All rights reserved.


Used parameter files:

/app/oracle/product/11.2.0.4/db_1/network/admin/sqlnet.ora



Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = 192.168.0.44)(PORT = 1521)) (CONNECT_DATA = (SID = MYSQL)) (HS = OK))

OK (10 msec)



나오면 접속 준비가 완료




ORACLE DB의 SCOTT 계정에서 DB LINK TEST


DB LINK 생성 

※ DB Link를 생성하고자하는 계정에 create database link 권한을 주고 작업한다.


$ sqlplus scott/tiger


SQL> create database link mysql

connect to DEMO

IDENTIFIED BY "<password>"

USING 'MYSQL';



MySQL DB에 미리 만들어 놓은 DEMO 테이블을 조회 해본다.

※ 테스트용 계정과 테이블은 알아서 만드시길... DEMO 유저의 DEMO 테이블을 조회 했다.


SQL> select * from "demo"@mysql_test;


      col1 col2       col3 col4     col5

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

1 24-JUL-18  0123456789 0123456789 30313233343536373839

2 25-JUL-18  0123456788 0123456788 30313233343536373838



결과 값이 나오면 성공이다.




Oracle 12c R2 RAC 설치 #1

설치 환경


Esxi 5.5, 

Oracle Linux 6.9 x86_64

Oracle 12c R2 (12.2.0.1)


리눅스6 설치 옵션


SWAP 4GB+

방화벽 사용중지

SELinux 설정 변경 (permissive or disabled)

해당 패키지 그룹 설치

  • Base System > Base
  • Base System > Hardware monitoring utilities
  • Base System > Large Systems Performance
  • Base System > Network file system client
  • Base System > Performance Tools
  • Base System > Perl Support
  • Servers > Server Platform
  • Servers > System administration tools
  • Desktops > Desktop
  • Desktops > Desktop Platform
  • Desktops > Fonts
  • Desktops > General Purpose Desktop
  • Desktops > Graphical Administration Tools
  • Desktops > Input Methods
  • Desktops > X Window System
  • Applications > Internet Browser
  • Development > Additional Development
  • Development > Development Tools


자동 설정


# yum install oracle-database-server-12cR2-preinstall -y

# yum install ntp -y



직접 설정


# groupadd oinstall

# groupadd dba

# useradd -g oinstall -G dba oracle

# passwd oracle



# vi /etc/sysctl.conf


fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter = 2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500


# sysctl -p



# vi /etc/security/limits.d/oracle-database-server-12cR2-preinstall.conf


oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728


# yum -y install ksh,libaio-devel,unixODBC, unixODBC-devel

# yum -y update



# vi /etc/hosts


127.0.0.1       localhost.localdomain   localhost

# Public

192.168.56.201   ol6-122-rac1.localdomain        ol6-122-rac1

192.168.56.202   ol6-122-rac2.localdomain        ol6-122-rac2

# Private

192.168.1.201   ol6-122-rac1-priv.localdomain   ol6-122-rac1-priv

192.168.1.202   ol6-122-rac2-priv.localdomain   ol6-122-rac2-priv

# Virtual

192.168.56.203   ol6-122-rac1-vip.localdomain    ol6-122-rac1-vip

192.168.56.204   ol6-122-rac2-vip.localdomain    ol6-122-rac2-vip

# SCAN

#192.168.56.205   ol6-122-scan.localdomain ol6-122-scan

#192.168.56.206   ol6-122-scan.localdomain ol6-122-scan

#192.168.56.207   ol6-122-scan.localdomain ol6-122-scan



박화벽 중지


# service iptables stop

# chkconfig iptables off



NTP 설정


# vi /etc/sysconfig/ntpd

OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid"


# service ntpd restart

# chkconfig ntpd on



Grid 설치 폴더 생성


# mkdir -p /u01/app/12.2.0.1/grid

# mkdir -p /u01/app/oracle/product/12.2.0.1/db_1

# chown -R oracle:oinstall /u01

# chmod -R 775 /u01/



Oracle user 접속 후 프로파일 설정


$ vi .bash_profile


# Oracle Settings

export TMP=/tmp

export TMPDIR=$TMP


export ORACLE_HOSTNAME=ol6-122-rac1.localdomain

export ORACLE_UNQNAME=CDBRAC

export ORACLE_BASE=/u01/app/oracle

export GRID_HOME=/u01/app/12.2.0.1/grid

export DB_HOME=$ORACLE_BASE/product/12.2.0.1/db_1

export ORACLE_HOME=$DB_HOME

export ORACLE_SID=cdbrac1

export ORACLE_TERM=xterm

export BASE_PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$BASE_PATH


export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib


alias grid_env='. /home/oracle/grid_env'

alias db_env='. /home/oracle/db_env'


$ vi grid_env

export ORACLE_SID=+ASM1

export ORACLE_HOME=$GRID_HOME

export PATH=$ORACLE_HOME/bin:$BASE_PATH


export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib


$ vi db_env

export ORACLE_SID=cdbrac1

export ORACLE_HOME=$DB_HOME

export PATH=$ORACLE_HOME/bin:$BASE_PATH


export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib



공유 스토리지 Block device 설정


# fdisk /dev/sdb

 n, p, 1, Enter, Enter, w 



Udev 설정


# vi /etc/scsi_id.config

options=-g


# /sbin/scsi_id -g -u -d /dev/sdb1

36000c299ab492a9c2ff010df22555c9f


# vi /etc/udev/rules.d/99-oracle-asmdevices.rules

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="36000c299ab492a9c2ff010df22555c9f", SYMLINK+="oracleasm/asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"



Block Device 파티션 테이블 불러오기


# partprobe /dev/sdb1



Udev Rule 다시 불러오기


# udevadm control --reload-rules


# ls -al /dev/oracleasm/*

lrwxrwxrwx. 1 root root 7 Mar  8 21:11 /dev/oracleasm/asm-disk1 -> ../sdb1




2번 노드도 똑같이 설정.