달력

5

« 2024/5 »

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

'Elick의 낙서'에 해당되는 글 116건

  1. 2014.07.31 Postgresql Pivot
  2. 2014.07.09 function을 이용한 select
  3. 2014.07.01 pl/r 설치
  4. 2014.06.25 External table
  5. 2013.03.15 Backup & Restore
  6. 2013.03.13 Postgresql 9.2 Install 및 pg_hint_plan 적용
  7. 2013.03.13 PostgreSQL Streaming Replication
  8. 2012.11.08 Install MySql 5.5 on CentOS 6.3
2014. 7. 31. 10:26

Postgresql Pivot Work/PostgreSQL2014. 7. 31. 10:26

집계 테이블을 표현 할 때 흔히 격자형 형태로 표현하기를 원하는 경우들이 있다.

이런 경우 엑셀에서는 pivot 이라는 기능을 이용한다.

데이터베이스에서도 비슷한 상황이 존재할 수 있는데, 이를 위해서 DBMS마다 약간씩 다른 방법을 제공한다. Postgresql 의 경우는 아래은 방법을 제공한다.


SELECT a::date AS cdate,

       b.desc AS TYPE,

       (random() * 10000 + 1)::int AS val

FROM generate_series((now() - '10 days'::interval)::date, now()::date, '1 day'::interval) a,

  (SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b;

위의 쿼리 결과는 아래와 같다.

cdate type val
2014-07-21 OSX 3128
2014-07-21 Windows 8118
2014-07-21 Linux 6699
2014-07-22 OSX 6364
2014-07-22 Windows 4439
2014-07-22 Linux 3727
2014-07-23 OSX 6010
2014-07-23 Windows 5006
2014-07-23 Linux 2249
2014-07-24 OSX 3664
2014-07-24 Windows 2377
2014-07-24 Linux 6907
2014-07-25 OSX 6145
2014-07-25 Windows 728
2014-07-25 Linux 5920
2014-07-26 OSX 6704
2014-07-26 Windows 1331
2014-07-26 Linux 1472
2014-07-27 OSX 5668
2014-07-27 Windows 1408
2014-07-27 Linux 6162
2014-07-28 OSX 6805
2014-07-28 Windows 4762
2014-07-28 Linux 5977
2014-07-29 OSX 8917
2014-07-29 Windows 9468
2014-07-29 Linux 7261
2014-07-30 OSX 1913
2014-07-30 Windows 1495
2014-07-30 Linux 5639
2014-07-31 OSX 5195
2014-07-31 Windows 4623
2014-07-31 Linux 3756

이런 결과의 격자형 형태를 위해 아래처럼 case를 이용한 쿼리를 작성하는 방법이 일반적이다.

select cdate

, sum(case when type = 'OSX' then val else 0 end) OSX

, sum(case when type = 'Windows' then val else 0 end) Windows

, sum(case when type = 'Linux' then val else 0 end) Linux

from (

SELECT a::date AS cdate,

  b.desc AS TYPE,

  (random() * 10000 + 1)::int AS val

FROM generate_series((now() - '10 days'::interval)::date, now()::date, '1 day'::interval) a,

 (SELECT unnest(ARRAY['OSX', 'Windows', 'Linux']) AS DESC) b

) a

group by cdate

order by 1;

결과는 아래와 같다.

cdate osx windows linux
2014-07-21 7095 287 1101
2014-07-22 9741 3417 3237
2014-07-23 6321 7028 9647
2014-07-24 569 8523 7655
2014-07-25 7371 4659 2482
2014-07-26 8352 9015 559
2014-07-27 604 5552 8965
2014-07-28 79 4691 1138
2014-07-29 3355 9816 2113
2014-07-30 4707 1285 2997
2014-07-31 2029 8379 3283


Postgresql 에서 제공하는 또다른 방법은 crosstab을 이용하는 벙법이다. 쿼리는 아래와 같다.

-- crosstab 을 이용하기 위한 extension 설치

CREATE EXTENSION tablefunc;


-- Query

SELECT *

FROM crosstab(

'SELECT

a cdate,

b.desc AS type,

(random() * 10000 + 1)::int AS val

FROM generate_series((now() - ''10 days''::interval)::date, now()::date, ''1 day''::interval) a,

(SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux'']) AS DESC) b ORDER BY 1,2'

,'SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux''])'

) 

AS ct(cdate date, OSX int, Windows int, Linux int);

결과는 위의 case를 이용한 Query와 동일하다.


출처 : http://www.craigkerstiens.com/2013/06/27/Pivoting-in-Postgres/

참고 : http://www.postgresql.org/docs/9.1/static/tablefunc.html


저작자표시 비영리 변경금지
:
Posted by Elick
2014. 7. 9. 14:34

function을 이용한 select Work/PostgreSQL2014. 7. 9. 14:34

Description & Report

PostgreSql에서는 Stored Procedure 라는 것을 제공하지 않고, Function 만 제공하는 것으로 파악된다.
Return Value를 적당히 결정해 주면 되므로 어찌되었든 종합적인 Query 저장 기능 및 pre-compile 을 할 수 있으므로 이정도면 충분하다.
Oracle에서는 PL/SQL, SQL Server에서는 TSQL, Postgresql에서는 plpgsql 로 부르는 것으로 판단된다.

생성 문법은 Oracle과 같이 CREATE OR REPLACE 를 지원하며 변수선언도 유사하고, SQL Server와 같이 function body에 DDL(Date Define Language)을 사용해도 Compile이 Invalid 되지 않는 것으로 Test 되었다. *(*여기까지는 Oracle의 장점과 SQL Server의 장점을 더한 것으로 보여 매우 만족스럽다)
어느 정도까지 사용할 수 있을지는 다양한 형태로 사용해 보야하겠지만, 지금 수준에서는 사용할만 하다.

Create plpgsql
pgAdmin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE OR REPLACE FUNCTION agt.usp_jttr_jtpr_YEAR_CNT() RETURNS int AS $$
DECLARE
--  v_Return int;
BEGIN
    truncate table agt.tb_jttr_01_YEAR_CNT;
     
    insert into agt.tb_jttr_01_YEAR_CNT
    select case when a.cyear is null then b.cyear else a.cyear end cyear
        , coalesce(a.app_cnt, 0) app_cnt
        , coalesce(b.pub_cnt, 0) pub_cnt
    from (select substr(app_date,1,4) CYEAR, COUNT(*) app_cnt FROM tb_jttr_01 GROUP BY substr(app_date,1,4)) A
        full outer join (select substr(pub_date,1,4) CYEAR, COUNT(*) pub_cnt FROM tb_jttr_01 GROUP BY substr(pub_date,1,4)) B
            on A.cyear = B.cyear;
 
    truncate table agt.tb_jptr_01_YEAR_CNT;
 
    insert into agt.tb_jptr_01_YEAR_CNT
    select case when a.cyear is null then b.cyear else a.cyear end cyear
        , coalesce(a.app_cnt, 0) app_cnt
        , coalesce(b.pub_cnt, 0) pub_cnt
    from (select substr(app_date,1,4) CYEAR, COUNT(*) app_cnt FROM tb_jptr_01 GROUP BY substr(app_date,1,4)) A
        full outer join (select substr(pub_date,1,4) CYEAR, COUNT(*) pub_cnt FROM tb_jptr_01 GROUP BY substr(pub_date,1,4)) B
            on A.cyear = B.cyear;
    RETURN 0;
END;
$$ LANGUAGE plpgsql;
Execute plpgsql
pgAdmin
select agt.usp_jttr_jtpr_YEAR_CNT();

 

Return RecordSet

SQL Server와 같이 Recordset을 return 하게 하는 방법.

pgAdmin
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Definition
CREATE OR REPLACE FUNCTION db_xml_kr.usp_test_rs(v_seq_xml bigint)
  RETURNS TABLE (
    seq_xml bigint,
  filepath_converted character varying(256)
  )
LANGUAGE SQL AS $$
    SELECT seq_xml
        , filepath_converted
    FROM db_xml_kr.x_filepath
    WHERE seq_xml = $1
    limit 10;
$$;
-- Execute
SELECT * FROM db_xml_kr.usp_test_rs(13);

위와 같이 간단히 Parameter 만 받아서 처리하는 경우는 비교적 쉽게 작성할 수 있지만, 조건문 변수 선언 및 할당 등 plpgsql에서 제공하는 기능을 모두 사용하려면 조금 다르게 작성해야 한다.

Return 받을 recordset 이 Table의 Column을 다 받는다면 역시 비교적 쉽게 작성할 수 있다. (예:http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html)

만일 생성되어 있는 Table과 Return 받을 Recordset 의 형태가 다르다면 아래처럼 Type 을 생성한 후 받는 방법을 사용한다.

pgAdmin (203.242.170.203 db_patent)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- create type
create type rs_info_table as (ordinal_position integer, column_name character varying, data_type character varying, character_maximum_length  integer);
 
-- create function
CREATE OR REPLACE FUNCTION asp_info_table(IN v_tname character varying)
  RETURNS SETOF rs_info_table AS
$BODY$
DECLARE
    rs rs_info_table%rowtype;
BEGIN
    IF position('.' in $1 ) > 0 THEN
        for rs in
            SELECT A.ordinal_position, A.column_name, A.data_type, A.character_maximum_length
            FROM information_schema.columns A
            WHERE table_schema = substring($1, 1, position('.' in $1) - 1)
                AND table_name = substring($1, position('.' in $1) + 1, character_length($1))
            ORDER BY ordinal_position      
        loop
            return next rs;
        end loop;
    ELSE
        for rs in
            SELECT A.ordinal_position, A.column_name, A.data_type, A.character_maximum_length
            FROM information_schema.columns A
            WHERE table_schema = 'public'
                AND table_name = $1
            ORDER BY ordinal_position
        loop
            return next rs;
        end loop;
    END IF;
    return;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION asp_info_table(IN v_tname character varying) OWNER TO nips;
 
-- Execute
select * from asp_info_table('rdb_jt.t_publish');

위 방법만 가능한 줄 알았다..

정말 다른 방법이 없는 것으로 확인되었는데... 최근 입수한 PostgreSQL Server Programming 이라는 책에서 아래와 같은 아주 간단한 방법을 제공하는 것을 확인했다.

CREATE OR REPLACE FUNCTION db_pas.usp_inven_select_with_proj_id_tech_id_stream(p_proj_id character varying, p_tech_id_stream character varying)
  RETURNS TABLE(id_kipi character varying, dt_sys   character varying, query_info   character varying) AS
$BODY$
DECLARE
    rs db_pas.t_inven%rowtype;
BEGIN
    IF length(p_tech_id_stream) = 0 THEN
        return query
        select distinct b.id_kipi, b.dt_sys, coalesce(b.query_info, '')
        from db_pas.t_inven_tech a inner join db_pas.t_inven b on a.id_kipi = b.id_kipi and a.proj_id = b.proj_id
        where a.proj_id = '{0}';
    ELSE
        return query
        select distinct b.id_kipi, b.dt_sys, coalesce(b.query_info, '')
        from db_pas.t_inven_tech a inner join db_pas.t_inven b on a.id_kipi = b.id_kipi and a.proj_id = b.proj_id
        where a.proj_id = p_proj_id
            and a.tech_id in (select unnest(string_to_array(p_tech_id_stream, ',')));
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

함수를 생성할 때마다 타입을 같이 생성해 주어야 하는 번거로움과 loop을 해야 하는 두려움을 모두 해결할 수 있는 좋은 방법을 찾았다.

postgresql-9.2 에서 test 되었다.

저작자표시 비영리 변경금지
:
Posted by Elick
2014. 7. 1. 10:35

pl/r 설치 Work/PostgreSQL2014. 7. 1. 10:35

R 이라고... SAS나 SPSS 같은... 통계쪽에서 사용하는 패키지가 있다. 

PL/R은 R 통계 컴퓨팅 언어로 PostgreSQL의 함수들과 집계 함수를 작성 할 수있는 PostgreSQL의 언어 확장이다.


CentOS에 설치된 Postgresql-9.2 에서는 기본적으로 제공하지 않지만... yum을 이용하여 pl/r을 설치할 수 있다.


[root@syslog ~]# yum search plr

.

.

.

[root@syslog ~]# yum install plr92.x86_64

.

.

.

[root@syslog ~]# su - postgres

[postgres@syslog ~]$ cd /usr/pgsql-9.2/share/extension/

[postgres@syslog extension]$ ls

.

.

.

plr--8.3.0.15.sql

plr.control

plr.sql

plr--unpackaged--8.3.0.15.sql

.

.

.

extension을 설치할 준비는 끝났다.


설치한다.

[postgres@syslog extension]$ psql -d mgtdb 

psql (8.4.20, server 9.2.8)

WARNING: psql version 8.4, server version 9.2.

         Some psql features might not work.

Type "help" for help.


mgtdb=# 

mgtdb=# create extension plr;

CREATE EXTENSION


설치가 잘 되었는지 간단한 테스트!

mgtdb=# SELECT * FROM plr_environ();

        name        |                                                 value                                                 

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

 HOSTNAME           | syslog.nips.local

 SHELL              | /bin/bash

 TERM               | linux

 HISTSIZE           | 1000

 QTDIR              | /usr/lib64/qt-3.3

 QTINC              | /usr/lib64/qt-3.3/include

 USER               | postgres

 TMOUT              | 900

 MAIL               | /var/spool/mail/postgres

 PATH               | /usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/postgres/bin

 PWD                | /home/postgres

 LANG               | en_US.UTF-8

 HISTCONTROL        | ignoredups

 SHLVL              | 1

 HOME               | /home/postgres

 LOGNAME            | postgres

 QTLIB              | /usr/lib64/qt-3.3/lib

 CVS_RSH            | ssh

 PGDATA             | /var/lib/pgsql/9.2/data

 LESSOPEN           | |/usr/bin/lesspipe.sh %s

 HISTTIMEFORMAT     | %F %T IP: ID:postgres  

 G_BROKEN_FILENAMES | 1

 _                  | /usr/pgsql-9.2/bin/postmaster

 PGLOCALEDIR        | /usr/pgsql-9.2/share/locale

 PGSYSCONFDIR       | /etc/sysconfig/pgsql

 LC_COLLATE         | en_US.UTF-8

 LC_CTYPE           | en_US.UTF-8

 LC_MESSAGES        | en_US.UTF-8

 LC_MONETARY        | C

 LC_NUMERIC         | C

 LC_TIME            | C

(31 rows)


mgtdb=# 


reference : http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut01

저작자표시 비영리 변경금지
:
Posted by Elick
2014. 6. 25. 16:47

External table Work/PostgreSQL2014. 6. 25. 16:47

Oracle에서는 External table 이라는 기능을 잘 사용했었다.

Postgresql에서도 비슷한 기능이 9.1 부터 지원된다고 해서 테스트 해보았다.


-- 일단 extention을 설치. 

CREATE EXTENSION file_fdw;


-- 서버 생성.

CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw


-- FOREIGN TABLE 생성.

CREATE FOREIGN TABLE work_01.fdw_test (

id_kipi varchar(32)

) SERVER file_fdw_server

OPTIONS (format 'text', filename '/mnt/splee/fdw_test.txt', delimiter ',', null '');


select * from work_01.fdw_test;

/*

결과 잘 나옴.!!!

*/


file_fdw 라는 확장을 설치 후 위처럼 테스트를 진행했는데... 굉장히 간단하게 성공...

쓸만하다...

저작자표시 비영리 변경금지
:
Posted by Elick
2013. 3. 15. 13:49

Backup & Restore Work/PostgreSQL2013. 3. 15. 13:49

대부분 데이터베이스가 그렇듯이... Postgresql도 백업과 복구가 존재한다.

백업은 크게 논리백업과 물리백업으로 나누어지고... 물리백업은 pg_basebackup 툴을 이용한 백업과 저수준 API를 이용한 백업으로 나누어진다.

 

논리 백업은 os 상에서 pg_dump 라는 uitl 로 하면 된다. 여러가지 옵션이 있어서 다르지만, 복구에 필요한 script가 파일로 생성되는 형태이다.

물리백업은 오라클의 hot backup 과 유사하다. pg_basebackup 을 이용한 간단한 방법이 있다. 검색하면 내용이 많이 나오는 듯.... 저수준 API를 이용한 방법은 psql 상에서 select pg_start_backup (...), select pg_stop_backup (...) 을 이용하게 된다. 마찬가지로 자세한 것은 google에...

 

이외에... PostgreSQL 8.3 이상 버전이면 BARMAN 이라는 백업복구툴을 이용할 수 있을 듯 하다.

([출처] PostgresDBA - http://www.postgresdba.com/bbs/board.php?bo_table=B13&wr_id=5)

 

 

 

 

저작자표시 비영리 변경금지
:
Posted by Elick
2013. 3. 13. 10:24

Postgresql 9.2 Install 및 pg_hint_plan 적용 Work/PostgreSQL2013. 3. 13. 10:24

Overview

 

Installation

USER
Commad
Description
root  
 yum updateOS Update
 rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpmPostgresql 9.2 repository 등록
 yum install postgresql92 postgresql92-server postgresql92-contribPostgresql 9.2 install
 service postgresql-9.2 initdbPostgresql 데이터베이스 초기화
postgres  
 postgresql.conf, pg_hba.conf 수정기본 설정
root  
 service postgresql-9.2 startPostgresql Servcie 실행
 wget "http://en.sourceforge.jp/frs/redir.php?m=iij&f=%2Fpghintplan%2F57713%2Fpg_hint_plan92-1.0.0-1.el6.x86_64.rpm"pg_hint_plan download
 yum localinstall pg_hint_plan92-1.0.0-1.el6.x86_64.rpmpg_hint_plan install
postgres  
 postgresql.conf 수정 : shared_preload_libraries = 'pg_hint_plan' 추가pg_hint_plan 환경 설정 변경
root  
 service postgresql-9.2 restartPostgresql 재시작
postgres  
 psqlpsql 실행
 

CREATE TABLE tbl (i int, j int);
INSERT INTO tbl SELECT x, x * 2 FROM generate_series (1, 10) x;
CREATE INDEX idx ON tbl (i);
VACUUM ANALYZE tbl;
EXPLAIN SELECT * FROM tbl WHERE i = 7;
/*+ IndexScan (tbl) */ EXPLAIN SELECT * FROM tbl WHERE i = 7;

실행계획 비교.

 

실행계획 확인

실행계획 확인
-bash-4.1$ psql
psql (9.2.3)
Type "help" for help.
postgres=# EXPLAIN SELECT * FROM tbl WHERE i = 7;
                    QUERY PLAN                    
---------------------------------------------------
 Seq Scan on tbl  (cost=0.00..1.12 rows=1 width=8)
   Filter: (i = 7)
(2 rows)
postgres=# /*+ IndexScan (tbl) */ EXPLAIN SELECT * FROM tbl WHERE i = 7;
                          QUERY PLAN                          
---------------------------------------------------------------
 Index Scan using idx on tbl  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (i = 7)
(2 rows)
postgres=# \q
-bash-4.1$


개인적 감상 : 이제 postgresql에서도 hint를 사용할 수 있다!!!


Hint List

  • 스캔 방식
    • SeqScan (table) 
      • 지정된 table에 대해 Seq Scan을 선택합니다.
    • TidScan (table) 
      • 지정된 table에 대해 Tid Scan을 선택합니다. 검색 조건에 ctid을 지정한 경우에만 유효합니다.
    • IndexScan (table index ...) 
      • 지정된 table에 대한 Index Scan을 선택합니다. index를 지정하면, 지정한 index에 비용이 최소가되는 Index Scan을 선택합니다. 
        그러나 index가 존재하지 않거나 지정된 index가 WHERE 절이나 JOIN 조건 등에 관련 않으면 Seq Scan을 선택합니다.
    • IndexOnlyScan (table index ...) 
      • 지정된 table에 대한 Index Onl??y Scan과 Index Scan 중 비용이 최소가되는 스캔 방식을 선택합니다.
         index를 지정하면, 지정한 index에서 Index Onl??y Scan과 Index Scan 중 비용이 최소가되는 스캔 방식을 선택합니다. 
        그러나 index가 존재하지 않거나 지정된 index가 WHERE 절이나 JOIN 조건 등에 관련 않으면 Seq Scan을 선택합니다. 
        또한 이러한 경우 외에도 index가 가지는 값뿐만 아니라 table의 값이 필요한 경우는 Index Scan을 선택합니다. PostgreSQL 9.2 이상이 필요합니다.
    • BitmapScan (table index ...) 
      • 지정된 table에 대해 Bitmap Scan을 선택합니다. 
        index를 지정하면, 지정한 index에 비용이 최소가되는 index를 선택합니다. 
        그러나 index가 존재하지 않거나 지정된 index가 WHERE 절이나 JOIN 조건 등에 관련 않으면 Seq Scan을 선택합니다.
    • NoSeqScan (table) 
      • 지정된 table에 대해 Seq Scan 이외 비용을 최소화하는 스캔 방식을 선택합니다. 
        그러나 다른 검색 방식을 선택할 수없는 경우는 Seq Scan을 선택합니다.
    • NoTidScan (table) 
      • 지정된 table에 대해 Tid Scan 이외 비용을 최소화하는 스캔 방식을 선택합니다.
    • NoIndexScan (table) 
      • 지정된 table에 대한 Index Scan 및 Index Onl??y Scan을 제외한 스캔 방식에서 비용을 최소화하는 스캔 방식을 선택합니다.
    • NoIndexOnlyScan (table) 
      • 지정된 table에 대한 Index Onl??y Scan 이외 비용을 최소화하는 스캔 방식을 선택합니다. (PostgreSQL 9.2 이상)
    • NoBitmapScan (table) 
      • 지정된 table에 대해 Bitmap Scan 이외 비용을 최소화하는 스캔 방식을 선택합니다.
  • 결합 방식
    • NestLoop (table table table ...) 지정된 table 간의 조인에 Nested Loop을 선택합니다.
    • HashJoin (table table table ...) 지정된 table 간의 조인에 Hash Join을 선택합니다.
    • MergeJoin (table table table ...) 지정된 table 간의 조인에 Merge Join을 선택합니다.
    • NoNestLoop (table table table ...) 지정된 table 간의 조인에 Nested Loop 이외의 결합 방식을 선택합니다.
    • NoHashJoin (table table table ...) 지정된 table 간의 조인에 Hash Join 이외의 결합 방식을 선택합니다.
    • NoMergeJoin (table table table ...) 지정된 table 간의 조인에 Merge Join 이외의 결합 방식을 선택합니다.
  • 조인 순서
    • Leading (table table table ...) 지정된 table을 지정한 순서대로 결합합니다.
  • GUC 매개 변수
    • Set (GUC 매개 변수 값) 쿼리 실행 계획을 작성하고있는 동안 만 지정된 GUC 매개 변수 값으로 변경합니다.


저작자표시 비영리 변경금지
:
Posted by Elick
2013. 3. 13. 10:04

PostgreSQL Streaming Replication Work/PostgreSQL2013. 3. 13. 10:04

PostgreSQL Streaming Replication

Overview
  • 스트리밍 복제 (Streaming Replication)은 PostgreSQL 9.0 이상에서 사용 가능한 복제 기능.
  • Insert/Update/Delete, Create/Drop 가능한 1 개의 Master DB에 SELECT만 할 수 있는 여러 Standby DB에 복제하는 것
  • Standby DB의 변경 결과가 반영되기까지 약간의 지연이 있지만 비교적 지연은 적게, 마스터 DB에 영향 작은 복제 방식.
Purpose
  • 많은 SELECT Query의 Server 간 분산
  • Master DB 이상시 신속한 Fail over
  • Master DB의 Disk 장애에 대비한 실시간 Backup
9.0 에 비해 강화된 9.1의 기능
  • 동기화 복제 (변경 결과 도착 보장)
  • 기반 Backup의 간소화 (초기 Data 복제 도구)
  • 복제 상태를 확인하기위한 System view
  • Fail Over 지원 강화 (전용 명령 다른 Master Server에 다시 연결)
구성
  • Master 1EA(192.168.83.146), Slave 1EA(192.168.83.147)
구축 절차
  • Master
    • Postgresql 9.1 설치 (생략)

    • 복제용 사용자 생성

      -bash-4.1$ psql
      psql (9.1.7)
      Type "help" for help.
       
      postgres=# CREATE ROLE repl_user LOGIN REPLICATION PASSWORD 'xxxxxxxx';
      CREATE ROLE
      postgres=# \q
      -bash-4.1$

    • pg_hba.conf 수정

      vi $PGDATA/pg_hba.conf
      host all         all       127.0.0.1/32 trust
      host replication repl_user 127.0.0.1/32 md5
      host all         all       ::1/128      trust
      host replication repl_user ::1/128      md5

    • postgresql.conf 수정

      vi $PGDATA/postgresql.conf
      wal_level = hot_standby
      max_wal_sender = 2 # 대기 DB의 수 + 1
      # Archive 사용시
      archive_mode = on
      archive_command = 'cp "%p" /mnt/pgbackup/archive/"%f"'
      # Archive 미사용시
      archive_mode = off
      wal_keep_segments = 8 # 8-32가 기준

    • pg_basebackup 으로 Master를 Backup

      Base Backup
      -bash-4.1$
      -bash-4.1$ /usr/pgsql-9.1/bin/pg_basebackup -h localhost -U repl_user -D /mnt/pgbackup/cluster/ -xlog -c fast -P
      Password:
      35430/35430 kB (100%), 1/1 tablespace
      -bash-4.1$
  • Slave
    • Postgresql 9.1 설치 (생략)

    • Master의 Backup을 restore

      Master
      -bash-4.1$ rsync -ra /mnt/pgbackup/cluster/* root@192.168.83.147:/mnt/pgbackup/cluster/
      root@192.168.83.147's password:
      -bash-4.1$
      Slave
      -bash-4.1$ cd /mnt/pgbackup/cluster
      -bash-4.1$ cp -rf ./* /$PGDATA/
      -bash-4.1$

    • postgresql.conf 수정

      vi $PGDATA/postgresql.conf
      wal_level = minimal
      max_wal_sender = 0
      archive_command = 'cp "%p" /mnt/pgbackup/archive/"%f"'
      hot_standby = on

    • recovery.conf 생성

      vi $PGDATA/recovery.conf
      standby_mode = 'on'
      primary_conninfo = 'host=192.168.83.146 port=5432 user=repl_user password=xxxxxxxx'
  • Postgresql start

    • Master Start

      [root@localhost mnt]# service postgresql-9.1 start
      Starting postgresql-9.1 service:                           [  OK  ]
      [root@localhost mnt]#

    • Slave Start

      [root@localhost mnt]# service postgresql-9.1 start
      Starting postgresql-9.1 service:                           [  OK  ]
      [root@localhost mnt]#
복제상태 확인
  • Process

    • Master

      wal sender process 있는지 확인
      -bash-4.1$ ps -ef | grep postgres
      postgres  2927     1  0 Jan03 ?        00:00:00 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
      postgres  2930  2927  0 Jan03 ?        00:00:00 postgres: logger process                                       
      postgres  2933  2927  0 Jan03 ?        00:00:01 postgres: writer process                                       
      postgres  2934  2927  0 Jan03 ?        00:00:01 postgres: wal writer process                                   
      postgres  2935  2927  0 Jan03 ?        00:00:00 postgres: autovacuum launcher process                          
      postgres  2936  2927  0 Jan03 ?        00:00:00 postgres: archiver process   last was 000000010000000000000006.00000020.backup
      postgres  2937  2927  0 Jan03 ?        00:00:00 postgres: stats collector process                              
      postgres  3194  2927  0 Jan03 ?        00:00:00 postgres: wal sender process repl_user 192.168.83.147(44356) streaming 0/7028558
      root      3227  2982  0 Jan03 pts/0    00:00:00 su - postgres
      postgres  3228  3227  0 Jan03 pts/0    00:00:00 -bash
      postgres  3695  3228  0 00:11 pts/0    00:00:00 ps -ef
      postgres  3696  3228  0 00:11 pts/0    00:00:00 grep postgres
      -bash-4.1$

    • Slave

      wal receiver process, startup process 있는지 확인.
      -bash-4.1$ ps -ef | grep postgres
      postgres  4212     1  0 Jan03 ?        00:00:00 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
      postgres  4214  4212  0 Jan03 ?        00:00:00 postgres: logger process                                       
      postgres  4215  4212  0 Jan03 ?        00:00:00 postgres: startup process   recovering 000000010000000000000007
      postgres  4216  4212  0 Jan03 ?        00:00:01 postgres: writer process                                       
      postgres  4217  4212  0 Jan03 ?        00:00:00 postgres: stats collector process                              
      postgres  4218  4212  0 Jan03 ?        00:00:02 postgres: wal receiver process   streaming 0/7028558           
      root      4277  2961  0 Jan03 pts/0    00:00:00 su - postgres
      postgres  4278  4277  0 Jan03 pts/0    00:00:00 -bash
      postgres  4565  4278  9 00:12 pts/0    00:00:00 ps -ef
      postgres  4566  4278  0 00:12 pts/0    00:00:00 grep postgres
      -bash-4.1$

Fail Over

  • Master Service 중지

    Maseter Postgresql stop
    -bash-4.1$
    -bash-4.1$ exit
    logout
    [root@localhost mnt]# service postgresql-9.1 stop
    Stopping postgresql-9.1 service:                           [  OK  ]
    [root@localhost mnt]#

  • Slave 에서 DDL 실행 : 실패

    Slave Postgresql 상태 확인 및 DDL 실행
    -bash-4.1$
    -bash-4.1$ exit
    logout
    [root@localhost mnt]# service postgresql-9.1 status
     (pid  4212) is running...
    [root@localhost mnt]# su - postgres
    -bash-4.1$ psql -d db_test_01
    psql (9.1.6)
    Type "help" for help.
    db_test_01=# create table t_test_02 (col1 int);
    ERROR:  cannot execute CREATE TABLE in a read-only transaction
    db_test_01=# \q
    -bash-4.1$

  • pg_ctl promote 실행

    -bash-4.1$
    -bash-4.1$ /usr/pgsql-9.1/bin/pg_ctl promote -D $PGDATA
    server promoting
    -bash-4.1$

  • Slave 에서 DDL 실행 : 성공

    -bash-4.1$
    -bash-4.1$ psql
    psql (9.1.6)
    Type "help" for help.
    postgres=# create table t_test_02 (col1 int);
    CREATE TABLE
    postgres=# \q
    -bash-4.1$


Reference : http://lets.postgresql.jp/documents/technical/replication/1/


개인적 소감 : 생각보다 간단한 복제 설정이었음. 극단적인 환경에서 사용해보지 않아 성능적인 부분은 보장할 수 없지만, 평범한 환경에서라면 충분히 쓸만한 듯 함.

저작자표시 비영리 변경금지
:
Posted by Elick
2012. 11. 8. 15:53

Install MySql 5.5 on CentOS 6.3 Work/MySql2012. 11. 8. 15:53

ref : http://www.tecmint.com/install-mysql-on-rhel-centos-6-5-fedora-17-12/


1. Remi Repository 설치

## Install Remi Repository on RHEL/CentOS 6.7-6.0 ##
rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-7.noarch.rpm
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm


2. MySql version 확인

yum --enablerepo=remi list mysql mysql-server

    ※ 확인 예제

Available Packages
mysql-server.i686 5.5.28-1.el6.remi remi


3. MySql 설치

 yum --enablerepo=remi install mysql mysql-server


4. MySql 실행

service mysqld start


5. MySql version 확인

mysql -u root
select version();



※ 설치 Example

[root@localhost ~]# rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-7.noarch.rpm

Retrieving http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-7.noarch.rpm

warning: /var/tmp/rpm-tmp.Siat48: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY

Preparing...                ########################################### [100%]

   1:epel-release           ########################################### [100%]

[root@localhost ~]# rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm 

Retrieving http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

warning: /var/tmp/rpm-tmp.1EyPOa: Header V3 DSA/SHA1 Signature, key ID 00f97f56: NOKEY

Preparing...                ########################################### [100%]

   1:remi-release           ########################################### [100%]

[root@localhost ~]# 

[root@localhost ~]#  yum --enablerepo=remi list mysql mysql-server

Loaded plugins: fastestmirror, refresh-packagekit, security

Loading mirror speeds from cached hostfile

epel/metalink                                                                                         | 3.6 kB     00:00     

 * base: centos.tt.co.kr

 * epel: ftp.riken.jp

 * extras: centos.tt.co.kr

 * remi: remi-mirror.dedipower.com

 * updates: centos.tt.co.kr

epel                                                                                                  | 4.3 kB     00:00     

epel/primary_db                                                                                       | 4.8 MB     00:05     

remi                                                                                                  | 2.9 kB     00:00     

remi/primary_db                                                                                       | 401 kB     00:04     

Available Packages

mysql.x86_64                                                 5.5.28-1.el6.remi                                           remi

mysql-server.x86_64                                          5.5.28-1.el6.remi                                           remi

[root@localhost ~]# 

[root@localhost ~]# yum --enablerepo=remi install mysql mysql-server

Loaded plugins: fastestmirror, refresh-packagekit, security

Loading mirror speeds from cached hostfile

 * base: centos.tt.co.kr

 * epel: ftp.riken.jp

 * extras: centos.tt.co.kr

 * remi: remirpm.mirror.gymkl.ch

 * updates: centos.tt.co.kr

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package mysql.x86_64 0:5.5.28-1.el6.remi will be installed

--> Processing Dependency: mysql-libs(x86-64) = 5.5.28-1.el6.remi for package: mysql-5.5.28-1.el6.remi.x86_64

---> Package mysql-server.x86_64 0:5.5.28-1.el6.remi will be installed

--> Processing Dependency: perl-DBI for package: mysql-server-5.5.28-1.el6.remi.x86_64

--> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.5.28-1.el6.remi.x86_64

--> Processing Dependency: perl(DBI) for package: mysql-server-5.5.28-1.el6.remi.x86_64

--> Running transaction check

---> Package mysql-libs.x86_64 0:5.1.61-4.el6 will be updated

--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: 2:postfix-2.6.6-2.2.el6_1.x86_64

--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64

--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: 2:postfix-2.6.6-2.2.el6_1.x86_64

--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64

---> Package mysql-libs.x86_64 0:5.5.28-1.el6.remi will be an update

---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed

---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed

--> Running transaction check

---> Package compat-mysql51.x86_64 0:5.1.54-1.el6.remi will be installed

--> Finished Dependency Resolution


Dependencies Resolved


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

 Package                          Arch                     Version                              Repository              Size

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

Installing:

 mysql                            x86_64                   5.5.28-1.el6.remi                    remi                   5.7 M

 mysql-server                     x86_64                   5.5.28-1.el6.remi                    remi                    10 M

Installing for dependencies:

 compat-mysql51                   x86_64                   5.1.54-1.el6.remi                    remi                   1.4 M

 perl-DBD-MySQL                   x86_64                   4.013-3.el6                          base                   134 k

 perl-DBI                         x86_64                   1.609-4.el6                          base                   705 k

Updating for dependencies:

 mysql-libs                       x86_64                   5.5.28-1.el6.remi                    remi                   771 k


Transaction Summary

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

Install       5 Package(s)

Upgrade       1 Package(s)


Total download size: 19 M

Is this ok [y/N]: y

Downloading Packages:

(1/6): compat-mysql51-5.1.54-1.el6.remi.x86_64.rpm                                                    | 1.4 MB     00:07     

(2/6): mysql-5.5.28-1.el6.remi.x86_64.rpm                                                             | 5.7 MB     00:31     

(3/6): mysql-libs-5.5.28-1.el6.remi.x86_64.rpm                                                        | 771 kB     00:03     

(4/6): mysql-server-5.5.28-1.el6.remi.x86_64.rpm                                                      |  10 MB     00:53     

(5/6): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm                                                          | 134 kB     00:00     

(6/6): perl-DBI-1.609-4.el6.x86_64.rpm                                                                | 705 kB     00:00     

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

Total                                                                                        194 kB/s |  19 MB     01:38     

warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 00f97f56: NOKEY

Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi

Importing GPG key 0x00F97F56:

 Userid : Remi Collet <RPMS@FamilleCollet.com>

 Package: remi-release-6-1.el6.remi.noarch (installed)

 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-remi

Is this ok [y/N]: y

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Warning: RPMDB altered outside of yum.

  Installing : perl-DBI-1.609-4.el6.x86_64                                                                               1/7 

  Updating   : mysql-libs-5.5.28-1.el6.remi.x86_64                                                                       2/7 


WARNING : This MySQL RPM is not an official Fedora / Red Hat build and it

overrides the official one. Don't file bugs on Fedora Project nor Red Hat.

Use dedicated forums http://forums.famillecollet.com/


  Installing : mysql-5.5.28-1.el6.remi.x86_64                                                                            3/7 

  Installing : compat-mysql51-5.1.54-1.el6.remi.x86_64                                                                   4/7 

  Installing : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                         5/7 

  Installing : mysql-server-5.5.28-1.el6.remi.x86_64                                                                     6/7 

  Cleanup    : mysql-libs-5.1.61-4.el6.x86_64                                                                            7/7 

  Verifying  : compat-mysql51-5.1.54-1.el6.remi.x86_64                                                                   1/7 

  Verifying  : perl-DBD-MySQL-4.013-3.el6.x86_64                                                                         2/7 

  Verifying  : mysql-libs-5.5.28-1.el6.remi.x86_64                                                                       3/7 

  Verifying  : mysql-5.5.28-1.el6.remi.x86_64                                                                            4/7 

  Verifying  : perl-DBI-1.609-4.el6.x86_64                                                                               5/7 

  Verifying  : mysql-server-5.5.28-1.el6.remi.x86_64                                                                     6/7 

  Verifying  : mysql-libs-5.1.61-4.el6.x86_64                                                                            7/7 


Installed:

  mysql.x86_64 0:5.5.28-1.el6.remi                          mysql-server.x86_64 0:5.5.28-1.el6.remi                         


Dependency Installed:

  compat-mysql51.x86_64 0:5.1.54-1.el6.remi      perl-DBD-MySQL.x86_64 0:4.013-3.el6      perl-DBI.x86_64 0:1.609-4.el6     


Dependency Updated:

  mysql-libs.x86_64 0:5.5.28-1.el6.remi                                                                                      


Complete!

[root@localhost ~]# 

[root@localhost ~]# 

[root@localhost ~]# service mysqld start

Initializing MySQL database:  Installing MySQL system tables...

OK

Filling help tables...

OK


To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:


/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'


Alternatively you can run:

/usr/bin/mysql_secure_installation


which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.


See the manual for more instructions.


You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &


You can test the MySQL daemon with mysql-test-run.pl

cd /usr/mysql-test ; perl mysql-test-run.pl


Please report any problems with the /usr/bin/mysqlbug script!


                                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

[root@localhost ~]# 
[root@localhost ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.28 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.28    |
+-----------+
1 row in set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# 



저작자표시 비영리 변경금지
:
Posted by Elick

티스토리툴바