PGSQL 9.0 streaming replication + pgpool-II

개요

• pgsql 9.0부터는 replication 기능이 기본으로 포함되어있다. 이전 버전에서는 서드파티 모듈을 사용하거나, 8.3부터 제공되던 warm standby를 활용하여 standby db로 설정할 수 있었다. slave node는 master node에 replication mode로 접속하고 wal 로그를 stream으로 전송받아 redo작업을 수행한다.

• streaming replication의 장점

1. 별도의 replication 관리 DB가 필요없고 복제 대상 테이블에 trigger를 설치할 필요가 없다.
2. 아카이브 로그 파일을 읽을 필요가 없다
3. warm standby의 단점이었던 recovery중인 slave db에 대한 엑세스 제한이 없다.
4. multi-slave 설정이 가능하다.
5. streaming replication과 pgpool-II를 조합하면 소스에서 타겟DB를 변경하지 않고도 read/write db를 구분하여 select 쿼리를 slave db로 분산시킬 수 있다.

테스트 환경

• OS : CentOS 5.5 (kernel 2.6.18-194)

• master (write db) :

192.168.100.76 / 8GB / core2duo 6320 @ 1.86GHz

• slave (read db) :

192.168.100.79 / 4GB / core2duo E4500 @ 2.20GHz
192.168.10.53 / 1GB / core2duo 6300 @ 1.86GHz

설치

pgsql 9.0 설치

• 모든 서버에서 동일한 과정으로 설치

• flex 설치

소스 설치시 flex 버전 확인 필요 (2.5.31 버전 이상 필요). CentOS의 flex 패키지 버전은 2.5.4이므로 최신 소스를 받아서 설치해야함

wget "http://downloads.sourceforge.net/project/flex/flex/flex-2.5.35/flex-2.5.35.tar.gz?r=http%3A%2F%2Fflex.sourceforge.net%2F&ts=1285637727&use_mirror=cdnetworks-kr-1"
tar xvfz flex-2.5.35.tar.gz
cd flex-2.5.35
./configure
make && make install && ldconfig

• pgsql 설치

9.0 버전부터는 thread-safety옵션이 기본으로 들어가있으므로 configure시 명시할 필요가 없다.

tar xvfz postgresql-9.0.0.tar.gz
cd postgresql-9.0.0
./configure --prefix=/home/pgsql9 --with-perl --with-python --with-openssl --with-pgport=54321 --enable-nls
make && make install

• contrib 모듈들도 같이 설치해준다

cd postgresql-9.0.0/contrib
make && make install

• 디렉토리 owner 변경 및 심볼릭 링크

useradd postgres -d /home/pgsql9
chown -R postgres. /home/pgsql9
ln -s /home/pgsql9 postgres

pgpool-II 설치

• pgpool-II 3.0.1 이하 버전(즉, 현재까지 release된 모든 버전)에서 pgpool을 통해 md5 인증을 받기 위해선 pool_auth.c 소스를 패치해야한다. 패스워드 길이에 대한 정의가 없어 pgsql 서버로 정확한 패스워드 길이를 전달하지 못해 pgsql 서버에서 packet size 에러가 발생한다. (http://abdulyadi.wordpress.com/2010/09/25/pgpool-ii-3-0-bug-fixes/)

--- pool_auth.c.old    2010-12-15 17:59:23.000000000 +0000
+++ pool_auth.c    2010-12-15 18:17:32.000000000 +0000
@@ -48,7 +48,7 @@
 static int do_crypt(POOL_CONNECTION *backend, POOL_CONNECTION *frontend, int reauth, int protoMajor);
 static int do_md5(POOL_CONNECTION *backend, POOL_CONNECTION *frontend, int reauth, int protoMajor);
 static int send_md5auth_request(POOL_CONNECTION *frontend, int protoMajor, char *salt);
-static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor,     char *password);
+static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor,     char *password, int *pwdSize);
 static int send_password_packet(POOL_CONNECTION *backend, int protoMajor, char *password);
 static int send_auth_ok(POOL_CONNECTION *frontend, int protoMajor);
 
@@ -855,7 +855,7 @@
             }
 
             /* Read password packet */
-            if (read_password_packet(frontend, protoMajor, password))
+            if (read_password_packet(frontend, protoMajor, password, &size))
             {
                 pool_error("do_md5: read_password_packet failed");
                 return -1;
@@ -944,7 +944,7 @@
         }
 
         /* Read password packet */
-        if (read_password_packet(frontend, protoMajor, password))
+        if (read_password_packet(frontend, protoMajor, password, &size))
         {
             pool_error("do_md5: read_password_packet failed");
             return -1;
@@ -1017,7 +1017,7 @@
 /*
  * Read password packet from frontend
  */
-static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor,     char *password)
+static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor,     char *password, int *pwdSize)
 {
     int size;
 
@@ -1056,6 +1056,7 @@
         pool_error("read_password_packet: failed to read password (size: %d)", ntohl(size) - 4);
         return -1;
     }
+    *pwdSize = size;
     return 0;
 }

• pgpool-II 설치 위치는 웹서버여도 되고, 기존의 타겟서버여도 된다.

wget http://pgfoundry.org/frs/download.php/2798/pgpool-II-3.0.tar.gz
tar xvfz pgpool-II-3.0.tar.gz
cd pgpool-II-3.0
./configure --prefix=/home/pgpool-II --with-pgsql=/home/postgres
make && make install

• 디렉토리 owner 변경

chown -R postgres /home/pgpool-II

설정

master 설정

• pg_hba.conf에 streaming replication slave 등록

host    replication     repl    192.168.100.79/32       md5
host    replication     repl    192.168.10.53/32        md5

• postgresql.conf 수정

wal_level = hot_standby # archive로 지정하면 slave에 엑세스가 안됨
max_wal_senders = 10 # multi slave node 댓수를 지정

• 기동 스크립트 작성

[root@of-client1 postgres]# cat pgsql.sh
#!/bin/sh

if [ "$1" == "start" ];
then
        /home/postgres/bin/pg_ctl -D /home/postgres/data start -l /home/postgres/data/postgres.log
elif [ "$1" == "stop" ];
then
        /home/postgres/bin/pg_ctl -D /home/postgres/data stop -m fast
else
        echo "pgsql.sh [ start | stop ]"
        exit 0;
fi

• pgsql 기동 (postgres 유저로 기동)

pgsql.sh start

• replication 유저 생성

postgres=# create USER repl SUPERUSER LOGIN;
postgres=# alter user repl with password '******';

slave 설정

• postgresql.conf 수정

hot_standby = on # slave에대한 엑세스를 허용

• recovery.conf 생성

standby_mode = 'on'
#restore_command = 'cp /path/to/archive/%f %p' # slave에서 archive를 남길 경우 지정
trigger_file = '/home/postgres/trigger.txt'
primary_conninfo = 'host=192.168.100.76 port=54321 user=repl password=*****'

• 기동 스크립트 작성

[root@of-client1 postgres]# cat pgsql.sh
#!/bin/sh

if [ "$1" == "start" ];
then
        /home/postgres/bin/pg_ctl -D /home/postgres/data start -l /home/postgres/data/postgres.log
elif [ "$1" == "stop" ];
then
        /home/postgres/bin/pg_ctl -D /home/postgres/data stop -m fast
else
        echo "pgsql.sh [ start | stop ]"
        exit 0;
fi

• pgsql 기동 (postgres 유저로 기동)

pgsql.sh start

• 설정이 정상적으로 완료되면 master 및 slave에 다음과 같은 프로세스가 기동된다

master (2 slave node인 경우)

postgres  8688  8680  0 10:25 ?        00:00:00 postgres: wal sender process repl 192.168.100.79(40352) streaming 1A/7A98A808
postgres  8774  8680  0 10:41 ?        00:00:01 postgres: wal sender process repl 192.168.10.53(38377) streaming 1A/7A98A808

slave

postgres  8318  8317  0 Oct12 ?        00:00:32 postgres: startup process   recovering 000000010000001A0000007A
postgres 28558  8317  0 10:25 ?        00:00:02 postgres: wal receiver process   streaming 1A/7A98A808

pgpool-II 설정

• pgpool-II 3.0 버전은 pgsql의 streaming replication을 지원한다. master/slave mode에서 load_balance를 지원하여 select 쿼리를 slave node로 분산시킬 수 있다.

• etc/pgpool.conf.sample-stream 파일을 pgpool.conf로 복사

cp pgpool.conf.sample-stream pgpool.conf

• pgpool.conf 수정

load_balance_mode = true
master_slave_mode = true
master_slave_sub_mode = 'stream' # slony 와 stream 모드를 지원
backend_hostname0 = 'master ip or hostname'
backend_port0 = 54321
backend_weight0 = 0.2 # master db의 가중치를 줄여 select 쿼리가 write DB로 가는 기회를 차단
backend_data_directory0 = '/home/postgres/data'
backend_hostname1 = 'slave1 ip or hostname'
backend_port1 = 54321
backend_weight1 = 5 # slave node에 가중치를 더 부여하여 select 쿼리가 slave node로 분산하도록 한다.
backend_data_directory1 = '/home/postgres/data'
backend_hostname2 = 'slave2 ip or hostname'
backend_port2 = 54321
backend_weight2 = 5 # slave node에 가중치를 더 부여하여 select 쿼리가 slave node로 분산하도록 한다.
backend_data_directory2 = '/home/postgres/data'

• pgpool-II 기동 / 중지

bin/pgpool -f etc/pgpool.conf
bin/pgpool -f etc/pgpool.conf stop

• pgpool-II가 기동하면 backend로 설정된 모든 DB에 기본적인 connection pool을 생성한다.

postgres  4844     1  0 14:54 ?        00:00:00 bin/pgpool -f etc/pgpool.conf
postgres  4973  4844  0 14:54 ?        00:00:00 pgpool: PCP: wait for connection request
postgres  4974  4844  0 14:54 ?        00:00:00 pgpool: worker process
postgres  4910  4844  0 14:54 ?        00:00:00 pgpool: wait for connection request
postgres  4911  4844  0 14:54 ?        00:00:00 pgpool: wait for connection request
.
.
.
.
.

테스트

테스트용 스키마 생성

test=# \d test_scheme;
                                                             Table "public.test_scheme"
   Column    |            Type             |                                               Modifiers
-------------+-----------------------------+--------------------------------------------------------------------------------------------------------
 idx         | integer                     | not null default nextval('test_scheme_idx_seq'::regclass)
 regdate     | timestamp without time zone | not null
 userid      | character varying(30)       | not null
 host        | character varying(50)       | not null
 ip          | character varying(20)       | not null
 status      | character(1)                | not null default 'F'::bpchar
 regdate_end | timestamp without time zone |
 static      | character(1)                | not null default 'F'::bpchar
 idc         | smallint                    |
 opt         | character varying(255)      | not null default 'true|true^Cache-Control|true|true|true|true|true|false^'::character varying
 proxy       | character varying(20)       |
 section     | smallint                    | not null default 1
 ext         | character varying(100)      | not null default 'gif|png|jpg|js|css|jpeg|jpeg|ico|pdf|doc|ppt|xls|vsd|txt|swf|bmp'::character varying
 pip         | character varying(150)      | not null default ''::character varying
 is_director | boolean                     | not null default false
 type        | smallint                    | default 1
Indexes:
    "wp_setting_pk1" PRIMARY KEY, btree (idx)
    "idx1" btree (idc)

insert 테스트

• insert 쿼리가 write db로만 날아가는지 확인

• 테스트 코드 수행후 master/slave 노드의 streaming 프로세스의 아카이브 offset이 동일한지 확인. master/slave 양쪽에 insert 쿼리가 날아가면 offset이 어긋남.

• 100개의 동시접속 세션을 생성하여 동일한 테이블에 각각 1만건의 레코드 insert 수행. 총 1백만건의 레코드 삽입

• 테스트 코드

#!/usr/bin/perl

use strict;
use warnings;


use threads('yield', 'stack_size' => 64*4096, 'exit' => 'threads_only', 'stringify');
use Term::ANSIColor qw(:constants);

use DBI;

my $user    = "postgres";
my $passwd  = "*****";
my $db      = "test";
my $host    = "localhost";
my $port    = 9999;
my $sql;
my $ssh;
my $thrd;

my $valLimit            = 10;
my $thrdStartCount      = 0;
my $maxThrd             = 100;

print "test start!!\n";

while ($thrdStartCount <= $maxThrd) {
        my $thr = threads->create('doQuery');
                print "thread id ==> " . $thr->tid()  . "\n";
                $thrdStartCount++;
}

while (my(@list) = threads->list()) {
        grep { $_->join } @list;
}

print "test is done!\n";

exit(0);

sub doQuery {

        my $val = 0;
        my $fieldVal;

        my $dbh = Connect($host, $db, $user, $passwd);
        while ( $val < $valLimit ) {
                #$fieldVal = int(rand(999999));
                #$sql = "insert into test values ($fieldVal)";
                my $userid      = getRandomString(rand(30));
                my $host        = getRandomString(rand(15));
                my $ip1         = getRandomNum(3);
                my $ip2         = getRandomNum(3);
                my $ip3         = getRandomNum(3);
                my $ip4         = getRandomNum(3);
                my $ip          = $ip1 . "." . $ip2 . "." . $ip3 . "." . $ip4;
                my $idc         = int(rand(10));
                my $proxy       = getRandomString(rand(20));
                my $sec         = int(rand(10));
                my $ext         = getRandomString(rand(100));
                my $pip         = getRandomString(rand(150));
                my $type        = getRandomNum(1);

                $sql = "INSERT INTO test_scheme (regdate, userid, host, ip, status, regdate_end, static, idc, proxy, section, ext, pip, is_director, type)
                                values (now(), '$userid', '$host', '$ip', 'T', now(), 'T', '$idc', '$proxy', '$sec', '$ext', '$pip', 'true', '$type' )";
                #print "$sql \n";
                $dbh->do($sql);
                $val++;
        }
}

sub Connect {
    my ($host, $db, $user, $pass) = @_;

    return DBI->connect("dbi:Pg:database=$db;port=$port;host=$host", $user, $pass, {PrintError => 1}) || die "$DBI::errstr";
}

sub runQuery {

        my $dbh;
    my $sql = $_[0];

    my $sth = $dbh->prepare($sql) || die "Can't prepare the statement" . $dbh->errstr;
    $sth->execute || die "Can't execute the statement" . $dbh->errstr;

    return $sth;
}

sub getRandomString {
        my $units = shift;
        my @str = qw /a b c d e f g h i j k l m n o p q r s t u v w x y z/;
        my $rtnString = '';

        my $unitsCount = 1;
        while ($unitsCount <= $units) {
        $rtnString .= $str[rand(25)];
        $unitsCount++;
        }

        return $rtnString;

}

sub getRandomNum {
    my $units = shift;
    my @str = qw /0 1 2 3 4 5 6 7 8 9/;
    my $rtnString = '';

    my $unitsCount = 1;
    while ($unitsCount <= $units) {
        $rtnString .= $str[rand(9)];
        $unitsCount++;
    }

    return $rtnString;

}

• 테스트 결과 1 - 레코드 카운트

master

test=# select count(*) from test_scheme;
  count
---------
 1000000
(1 row)

slave1 (192.168.100.79)

test=# select count(*) from test_scheme;
  count
---------
 1000000
(1 row)

slave2 (192.168.10.53)

test=# select count(*) from test_scheme;
  count
---------
 1000000
(1 row)

• 테스트 결과 2 - 아카이브 offset

master

-bash-3.2$ ps -ef |grep post |grep stream
postgres  8688  8680  0 10:25 ?        00:00:01 postgres: wal sender process repl 192.168.100.79(40352) streaming 1A/9D1E92C8
postgres  8774  8680  0 10:41 ?        00:00:02 postgres: wal sender process repl 192.168.10.53(38377) streaming 1A/9D1E92C8

slave1 (192.168.100.79)

-bash-3.2$ ps -ef |grep post |grep stream
postgres 28558  8317  0 10:25 ?        00:00:04 postgres: wal receiver process   streaming 1A/9D1E92C8

slave2 (192.168.10.53)

-bash-3.2$ ps -ef |grep post |grep stream
postgres 13004 13000  0 11:06 ?        00:00:08 postgres: wal receiver process   streaming 1A/9D1E92C8

select 분산 테스트

• select 쿼리가 각 slave로 노드로 고르게 분산되는지 확인

• write db로 select 쿼리가 날아가지 않는지 확인

• 100개의 세션을 생성하고 각 세션당 100번, 총 1만회 select 쿼리 수행.

• 테스트 코드

-bash-3.2$ cat pgsql_select_test.pl
#!/usr/bin/perl

use strict;
use warnings;


use threads('yield', 'stack_size' => 64*4096, 'exit' => 'threads_only', 'stringify');
use Term::ANSIColor qw(:constants);

use DBI;

my $user    = "postgres";
my $passwd  = "";
my $db      = "test";
my $host    = "localhost";
my $port    = 9999;
my $sql;
my $ssh;
my $thrd;

my $valLimit            = 100;
my $thrdStartCount      = 0;
my $maxThrd             = 99;
my $serverCount = {};

print "test start!!\n";

while ($thrdStartCount <= $maxThrd) {
        my $thr = threads->create('doQuery', $thrdStartCount);
        print "thread id ==> " . $thr->tid()  . "\n";

        $thrdStartCount++;
}


while (my(@list) = threads->list()) {
        grep { $_->join } @list;
}

print "test is done!\n";

exit(0);

sub doQuery {

        my $thrdID = shift;
        my $val = 0;
        my $fieldVal;

        my $dbh = Connect($host, $db, $user, $passwd);
        while ( $val < $valLimit ) {
                $sql = "SELECT (select inet_server_addr())as server, idx FROM test_scheme limit 1";
                my $sth =  runQuery($dbh, $sql);
                while (my @data = $sth->fetchrow_array()) {
                        $serverCount->{$thrdID}->{$data[0]}->{count}++;
                        #print "$thrdID   :  $data[0] ==> $data[1]    " .  $serverCount->{$thrdID}->{$data[0]}->{count} . "\n";
                }

                $val++;
        }

        foreach my $thread (sort keys %{$serverCount}) {
                foreach my $server (keys %{$serverCount->{$thread}}) {
                        foreach my $count (keys %{$serverCount->{$thread}->{$server}}) {
                                print  $thread . " " . $server . " ". $serverCount->{$thread}->{$server}->{count}  . "\n";
                        }
                }
        }
        $dbh->disconnect;
}

sub Connect {
    my ($host, $db, $user, $pass) = @_;

    return DBI->connect("dbi:Pg:database=$db;port=$port;host=$host", $user, $pass, {PrintError => 1}) || die "$DBI::errstr";
}

sub runQuery {

    my $dbh = $_[0];
    my $sql = $_[1];

    my $sth = $dbh->prepare($sql) || die "Can't prepare the statement" . $dbh->errstr;
    $sth->execute || die "Can't execute the statement" . $dbh->errstr;

    return $sth;
}

sub getRandomString {
        my $units = shift;
        my @str = qw /a b c d e f g h i j k l m n o p q r s t u v w x y z/;
        my $rtnString = '';

        my $unitsCount = 1;
        while ($unitsCount <= $units) {
        $rtnString .= $str[rand(25)];
        $unitsCount++;
        }

        return $rtnString;

}

sub getRandomNum {
    my $units = shift;
    my @str = qw /0 1 2 3 4 5 6 7 8 9/;
    my $rtnString = '';

    my $unitsCount = 1;
    while ($unitsCount <= $units) {
        $rtnString .= $str[rand(9)];
        $unitsCount++;
    }

    return $rtnString;

}

• 테스트 결과

slave 가중치 : 5

52 192.168.100.79 slave1
 1 192.168.100.76 master
47 192.168.10.53 slave2

slave 가중치 : 10

59 192.168.100.79
41 192.168.10.53

• 각 세션당 쿼리 정상 수행. 전체 세션의 1% (1개)가 write db에서 select 수행

• 쿼리 분산 문제없는 것으로 판단됨

• 부하

테스트시 스크립트의 실행 부하를 제외한 DB부하는 없었으며 insert 테스트 시 다수의 동시 세션(100개) 수행에 의한 master db의 부하가 다소 있었음.

insert 테스트시 master -> slave로의 replicaton 부하 없었음.

트리거와 함수/프로시저의 수행

• 함수/프로시저는 pgpool.conf의 black_function_list에 등록하면 slave가 아닌 master db로 쿼리가 날아간다. 등록하지 않고 쿼리를 날린 경우 slave로 날아간 쿼리는 트랜잭션 에러를 발생시키며 수행되지 않는다

-bash-3.2$ ~/bin/psql -h localhost -p 9999 -d test -c "SELECT test1();"
ERROR:  cannot execute INSERT in a read-only transaction
:  SQL statement "INSERT INTO tab1(id,name) VALUES(var1,var3)"
PL/pgSQL function "test1" line 6 at SQL statement

• 트리거를 수행하는 경우 SELECT 문에서의 트리거는 허용되지 않기 때문에 고려하지 않아도 될듯

운영중인 DB의 분산 설정

• 실운영중인 DB에서 slave replication을 설정은 mysql의 replication과 유사함.

• pg_start_backup()을 이용해 아카이브 offset 설정후 DB의 data 디렉토리를 rsync/scp등을 이용해 복사한 뒤 pg_stop_backup()을 걸고 slave를 replication mode로 띄우면 설정된 아카이브 offset 이후부터 복제를 시작한다.

• 설정 과정은 현재 eclog2 서버에 걸려있는 warm standby 설정과도 유사함

• master 및 slave 설정은 위에 설명한 대로 설정

• master 서버를 restart 하여 replication 모드 적용

• slave에서 master로 non password ssh 접속이 가능하도록 설정. sshpass를 이용하여 패스워드를 인수로 주고 수행해도 됨

• slave의 모든 설정 파일을 postgres 홈 디렉토리에 저장

• 다음의 스크립트 작성 및 수행

-bash-3.2$ cat applyWARMSTDBY.sh
#!/bin/sh

OPTION="--exclude postmaster.pid --exclude pg_hba.conf --exclude postgresql.conf --exclude postgres.log"

export PGPASSWORD=******

/home/postgres/bin/psql -U postgres -p 54321 -h 192.168.100.76 -c "select pg_start_backup('stdby')"

sleep 1

/usr/bin/rsync -a --progress --delete ${OPTION} 192.168.100.76:/home/postgres/data /home/postgres/

sleep 1

/home/postgres/bin/psql -U postgres -p 54321 -h 192.168.100.76 -c "select pg_stop_backup()"

sleep 1

/bin/rm /home/postgres/data/*.conf
/bin/cp /home/postgres/*.conf /home/postgres/data/.
sleep 1
/home/postgres/pgsql.sh start

 

cat pgsql.sh

#!/bin/sh

if [ "$1" == "start" ];
then
        /db/pgsql9/bin/pg_ctl -D /db/pgsql9/data start -l /db/pgsql9/data/postgres.log
elif [ "$1" == "stop" ];
then
        /db/pgsql9/bin/pg_ctl -D /db/pgsql9/data stop -m fast
else
        echo "pgsql.sh [ start | stop ]"
        exit 0;
fi