Postgresql  Replication 구현 및 테스트

1. 테스트 서버

    RH9.
    
    AMD 3800+ , 2G RAM
    Pgsql Version   : 8.2.3
    Slony-I Version : 1.2.8
    
    slony-master, slony-slave 두대 모두 동일한 사양, 동일한 원판.
    
    slony-master -  192.168.3.50/16
    slony-slave  -  192.168.3.175/16
    

2. 설치

    1) Postgresql
    
        configure:
            configure --prefix=/home/postgresql --enable-debug --enable-integer-datetimes --enable-cassert --enable-thread-safety --with-tcl --with-perl --with-pam --with-ldap --with-openssl --with-python
        
        make:    
            gmake && gmake install
        
        DB initialize:
            su - postgres -c '/home/postgresql/bin/initdb -D /home/postgresql/data -E EUC_KR'
                
        postgresql.conf :
        
            listen_addresses = '*'
            port = 9999
            max_connections = 500
            work_mem = 16MB
            maintenance_work_mem = 32MB
            max_fsm_pages = 2048000
            max_fsm_relations = 80000
            
            log_line_prefix = '%t %p %x %i'
            
            
        pg_hba.conf :
            
            # IPv4 local connections:
            host    all         all         192.168.0.0/16       trust
            
        pgsql 구동
        
        
        pgbench 설치 :
        
            cd $PGSQL_SRCDIR/contrib/pgbench ; gmake && gmake install
        
        
    2) Slony-I
    
        configure :
        
            configure --prefix=/home/postgresql/slony --enable-debug --with-gnu-ld --with-pgconfigdir=/home/postgresql/bin --with-pgbindir=/home/postgresql/bin --with-pglibdir=/home/postgresql/lib --with-perltools=/home/postgresql/slony/tools --with-perlsharedir=/home/postgresql/lib
            
        make :
        
            gmake && gmake install
            
        
        create directories :
        
            cd /home/postgresql/slony
            mkdir logs archive scripts check
            
        check scripts copy :
        
            cp -a $SRCDIR/tools/*.sh  $PREFIXDIR/scripts
            

            
3. Replication 구현

    1) test DB 생성
        
        psql -h slony-master -U postgres -d template1 -p 9999
        db=#CREATE ROLE pgbench LOGIN;
        db=#CREATE DATABASE pgbench OWNER pgbench;
        
    2) test table 생성 ( slony-master )
    
        pgbench -i -s 1000 -U pgbench -d pgbench -p 9999
        
    3) slony-slave 에 table 및 기타 해당 DB 동기화
    
        psql -h slony-slave -U postgres -d template1 -p 9999
        db=#CREATE ROLE pgbench LOGIN;
        db=#CREATE DATABASE pgbench OWNER pgbench;
        db=# \q
        
        pg_dump -h slony-master -U postgres -d pgbench -p 9999 -s > schema_nodata.sql ( DDL 생성정보 dump )
        psql -h slony-slave -U postgres -d template1 -p 9999 < schema_nodata.sql
        
    4) slony-i  설정.
    
        set_pgbench_replicate.sh
        
        -----------------------------------------------------------------------------------------------------------                
            #!/bin/sh
            # Set up environment vars for slony: pg replication
            
            
            SLONIK=/home/postgresql/slony/bin/slonik
            
            
            $SLONIK <<_EOF_
            
            
                    # Replication의 이름 지정.  해당 DB에 _pgbench 와 같이 생성됨
                    
                    cluster name = pgbench;   
                    
                    # Replication Node 생성
                    node 1 admin conninfo = 'dbname=pgbench host=slony-master port=9999 user=postgres';
                    node 2 admin conninfo = 'dbname=pgbench host=slony-slave port=9999 user=postgres';
            
            
                    # Replication Init
                    init cluster ( id=1, comment = 'pgbench MASTER Node' );
            
                    # Uniq Constraint 또는 Primary key 가 없는 테이블에 키 지정.
                    # 해당 테이블에 _Slony-I_$CLUSTERNAME_rowID  bigint 컬럼 생성.
                    # primary key 가 없는 테이블을 replication 지정할때 반드시 작업해 주어야함.
                    
                    table add key ( node id = 1, fully qualified name = 'public.history' );
                    
            
                    # Replication 될 Set 및 table 설정.
            
                    create set ( id=1, origin=1, comment='tables in pgbench');
                    set add table ( set id=1, origin=1, id=1, fully qualified name = 'public.accounts', comment='account');
                    set add table ( set id=1, origin=1, id=2, fully qualified name = 'public.branches', comment='branches');
                    set add table ( set id=1, origin=1, id=3, fully qualified name = 'public.history', comment='history');
                    set add table ( set id=1, origin=1, id=4, fully qualified name = 'public.tellers', comment='tellers');
            
            
                    # Table set Ends here.
                    
                    # Create the second node ( SLAVE ) tell the 2 nodes how to connect to each other.
            
                    store node ( id=2, comment = 'pgbench SLAVE Node');
                    store path ( server = 1, client = 2, conninfo='dbname=pgbench host=slony-master port=9999 user=postgres');
                    store path ( server = 2, client = 1, conninfo='dbname=pgbench host=slony-slave port=9999 user=postgres');
                    store listen ( origin=1, provider = 1, receiver = 2);
                    store listen ( origin=2, provider = 2, receiver = 1);
            
            _EOF_        
        
        -----------------------------------------------------------------------------------------------------------                
        
    5) slonik setting 및 확인.
    
        sh set_pgbench_replicate.sh   
        
        # err 발생 유무 확인  에러가 발생하였다면
        psql -h slony-master -U postgres -d pgbench -p 9999 -c "DROP SCHEMA _$CLUSTERNAME CASCADE"
        로 스키마를 DROP 후 스크립트를 수정하여 다시 반복한다.
        
        err가 없었다면 master 및 slave DB 모두에 _$CLUSTERNAME 과 같은 스키마가 생성되었는지 확인한다.
        
        
    6) slon daemon 구동
        
        slony-master:
        /home/postgresql/slony/bin/slon pgbench "dbname=pgbench user=postgres host=slony-master port=9999" -a /home/postgresql/slony/archive -x /home/postgresql/slony/logs -d 2 2&>1 &
        
        slony-slaver:
        /home/postgresql/slony/bin/slon pgbench "dbname=pgbench user=postgres host=slony-slave port=9999" -a /home/postgresql/slony/archive -x /home/postgresql/slony/logs -d 2 2&>1 &
        
        /home/postgresql/slony/archive 에  slony 로 replication 된 sql 파일을 로그로 쌓는다.
        실제 서비스에서는 I/O 감소를 위해 hdc 또는 sdb 와 같은 백업 디스크로 경로를 바꿔 주어야 한다
        
        
        
    7) subscribe replication.
    
        start_pgbench_replication.sh 의 스크립트 제작.
        
        -----------------------------------------------------------------------------------------------------------                
            #!/bin/sh
            
            /home/postgresql/slony/bin/slonik <<_EOF_
            
                    cluster name = pgbench;
            
                    node 1 admin conninfo = 'dbname=pgbench host=slony-master port=9999 user=postgres';
                    node 2 admin conninfo = 'dbname=pgbench host=slony-slave port=9999 user=postgres';
            
                    subscribe set ( id = 1, provider = 1, receiver =2 , forward = no );
            
            _EOF_
        -----------------------------------------------------------------------------------------------------------                
        
    8)  vmstat 및 iptraf 등으로 각 테이블들이 master -> slave 로 sync 되고있는지 확인한다.
    
    
    
4. 관리

    /home/postgresql/slony/tools 에 보면 replication 관리에 필요한 스크립트들이 있다.
    
    .../slony/etc/slon_tools.conf 파일이 있는지 확인하고 없다면 slony의 소스 압축을 푼 디렉토리에서 찾아 해당 위치에 복사해 준다.
    
    
    slon_tools.conf 파일 수정.
    
        $CLUSTER_NAME = 'pgbench';
        $LOGDIR = '/home/postgresql/slony/logs/slony';
        $MASTERNODE = 1;
        
            add_node(node     => 1,
                      host     => 'slony-master',
                      dbname   => 'pgbench',
                      port     => 9999,
                      user     => 'postgres',
                      password => '');
        
    
        $SLONYSET 설정.
        
            "set_id" => 1,
            
            "table_id"    => 1,
            "sequence_id" => 1,
            
            # Primary key가 지정되어있는 테이블 리스트설정.
            "pkeyedtables" => [
                                   'TABLE1',
                                   'table2',
                                 ],
                                 
            # Primary key 는 없으나 Uniq not null 키를 가지고 있는 테이블 및 인덱스 지정.
            "keyedtables" => {
                                'table3' => 'index_on_table3',
                                'table4' => 'index_on_table4',
                            },
                            
                            
            # Primary Key 또는 uniq 키 모두 없는 테이블지정.
            # 지정하지 않고 키를 생성해도 된다.
            
            "serialtables" => ["table5"],
            
            # Replication 되어야할 sequence 지정
            
            "sequences" => ['sequence1',
                            'sequence2',
                           ],
                           
                           
                         
     
    사용 방법
    
        /home/postgresql/slony/tools 의 스크립트를 이용
        
        일부 스크립트는 직접 수행되는 것도 있고 slonik 용 스크립트를 생성해 주는 것도 있다.
        
        직접 수행되는것들로는
        
            slon_*
            
            
        slonik 용 스크립트 생성 툴은
        
            slonik_*  
            
        로 보면 된다.
        
        
        Usage는 잘 설명 되어있으나  --config-file 은 스크립트 수행시 넣어주지 않아도 된다.
        
            ex)  ./slonik_drop_node 1 > /home/postgresql/slony/scripts/drop_node1.slonik
            
                 /home/postgresql/slony/bin/slonik < /home/postgresql/slony/scripts/drop_node1.slonik
                
                
        
        replication 장애시.        
         ./slonik_failover 1 2 > /home/postgresql/slony/scripts/fail_over_masternode_to_2.slonik
        
         /home/postgresql/slony/bin/slonik < /home/postgresql/slony/scripts/fail_over_masternode_to_2.slonik
        
        
    
    
    

주로 장애처리를 하다 보니까 글 내용도 그쪽으로 많이 치중되는것 같다.

일단 오늘은

/home/postgresql/bin/pg_dump -Uec_user -f bienworks.sql -n bienworks mall -p5432
pg_dump: schema with OID 5231294 does not exist

이따위 경우에 대해 당해 본적 있는가 해서 끄적거린다.

좀 뒤벼 보자면

=# select oid, * from pg_namespace where oid='5231294';
oid | nspname | nspowner | nspacl
-----+---------+----------+--------
(0 rows)

읍다.  써글..

=# select oid, * from pg_namespace where nspname='bienworks';
oid | nspname | nspowner | nspacl
---------+--------------+----------+--------
1489275   | bienworks   | 16384 |

근데 얜 있다..


이거이 어찌된 일인고..   bienworks 스키마의 테이블 또는 인덱스 또는 role 또는 type 또는 관련 pg_toast
등에 대한 oid가 분실되어 덤프받을 수 없음을 의미 한다.

문제는 이게 pg_catalog 의 index가 쫑나서 발생하는 경우가 상당히 빈번하다는 것.

득단의 조치를 내리게 된다 ㅡㅡ;;

reindexdb -U user -d database -p 9999 -s 

리인덱싱이 끝나고도 고대로 덤프 안되고 나를 약올려 버리는 써글 pg_dump.

찾아야 한다.  pg_dump 가 참조하는 이 써글놈의 oid가 어디서 읽혀지는지.



oid를 찾기 위해 삽질고투중.

select typname,typnamespace from pg_type where typnamespace=5231294;
        typname         | typnamespace
------------------------+--------------
 member_group           |      5231294
 category_access_group  |      5231294
 member                 |      5231294
 member_addr_seq        |      5231294
 member_addr            |      5231294
 regist_config          |      5231294
 mileage_access_log_seq |      5231294
 mileage_access_log     |      5231294
(8 rows)


# select relname,relnamespace from pg_class where relnamespace=5231294;
           relname            | relnamespace
------------------------------+--------------
 member_group_pk              |      5231294
 member_group                 |      5231294
 category_access_group_pk     |      5231294
 category_access_group        |      5231294
 category_access_group_index1 |      5231294
 member_pk                    |      5231294
 member                       |      5231294
 member_ssn_key               |      5231294
 member_index1                |      5231294
 member_index2                |      5231294
 member_index3                |      5231294
 member_addr_seq              |      5231294
 member_addr_pk               |      5231294
 member_addr                  |      5231294
 member_addr_index1           |      5231294
 regist_config                |      5231294
 mileage_access_log_seq       |      5231294
 mileage_access_log_pk        |      5231294
 mileage_access_log           |      5231294
(19 rows)



=# select conname, connamespace from pg_constraint where connamespace=5231294;
         conname          | connamespace
--------------------------+--------------
 member_group_pk          |      5231294
 category_access_group_pk |      5231294
 member_pk                |      5231294
 member_ssn_key           |      5231294
 member_addr_pk           |      5231294
 mileage_access_log_pk    |      5231294
(6 rows)


몇군데서 찾아 내었다 써글놈

모두 delete 조치 후 다시 dump 시도.
아, 물론 서비스 서버에서 한건 아니다.  당연히 온라인 백업으로 wal 복구 한 서버에서 작업.

Grrrrrrrrrrr...  안된다.

어디가 잘 못된 것일까...

모든 스키마에서 참조하는 oid라..

생각보다 답이 간단할거 같은데 커피에 취한 내머리는 굴러갈 생각을 않는다.... 써글


일단 노가다. ㅡㅡ;;
해당 DB 전부 reindex 중..  물론!!  테스트 서버에서. ㅡ0ㅡ;

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

8시간 후...

reindex 는 엄했다. ㅡㅡ;;  역시 catalog가 꼬인게야...

그토록 수많은 삽질 끝에...

dump 중 나오는 oid 를  pg_type, pg_class, pg_constraint 이 세개의 테이블에서 지워준다.
일단 덤프는 된다 ㅡㅡ;;


delete  from pg_type where typnamespace=5274363;
delete  from pg_class where relnamespace=5274363;
delete  from pg_constraint where connamespace=5274363;

pg_conversion
pg_opclass
pg_operator
pg_proc

5234792
5276285
5261129
5268899
5274363

요점은, pg_namespace 에  해당 스키마가 없는데, pg_type 에 올라가 있는 oid는 delete 해준다..
또는 그 반대의 경우도 마찬가지. 라고 보면 된다.


역으로 pg_dump.c 의 소스를 쫒아 가는 방법도 있겠지.


/* subquery used to convert user ID (eg, datdba) to user name */
static const char *username_subquery;

findNamespace(Oid nsoid, Oid objoid)
{
        int                     i;
        if (g_fout->remoteVersion >= 70300)
        {
                for (i = 0; i < g_numNamespaces; i++)
                {
                        NamespaceInfo *nsinfo = &g_namespaces[i];

                        if (nsoid == nsinfo->dobj.catId.oid)
                                return nsinfo;
                }
                write_msg(NULL, "schema with OID %u does not exist\n", nsoid);
                exit_nicely();
       
       
       
       
"SELECT tableoid, oid, nspname, "
                                          "(%s nspowner) as rolname, "
                                          "nspacl FROM pg_namespace",
                                          username_subquery);
                                         
Binary file ./pg_dump matches
./pg_dump.c:static const char *username_subquery;
./pg_dump.c:            username_subquery = "SELECT rolname FROM pg_catalog.pg_roles WHERE oid =";
./pg_dump.c:            username_subquery = "SELECT usename FROM pg_catalog.pg_user WHERE usesysid =";
./pg_dump.c:            username_subquery = "SELECT usename FROM pg_user WHERE usesysid =";                                         


잘 찾아 보시게나 들..  오늘은 이만 하고  내일은 SELECT 로 뽑는 툴을 만들어야 하니.. ㅋ


눈 멋지게 오는데 이게 무슨짓이람 ㅋ

postgresql -  http://www.postgresql.org

오픈소스 진영의 막강한 RDBMS.

항상 막강한 말이 붙는 소프트 웨어를 웹질을 하면서 많이 만나게 된다.
회사를 옮기고 생소하게 접했던 pgsql.

약 일년여간 다루어 오면서 느낀건 정말 손 많이 가는 데이터베이스라는거다.
회사 사용목적의 특성상 스키마를 잘근잘근 쪼개서 보통 900여개, 많으면 1800여개 까지 만들어대는데
한개의 DB에 특정 tablespace 지정없이 이런식으로 개념없이 계속 스키마를 생성하다 보면
디렉토리 하나에 파일이 70만개.

일단 파일시스템 성능에도 문제가 있고..  data 디렉토리를 왕창 압축해서 한 파일에 몰아 넣는건 별 문제가
되진 않지만 이걸 다시 풀어 버리는데는.... 끔찍했다.

실례로 16시간동안 압축이 해제 되는걸 보고만 있어야 했다는.  주말이었다.

실제 서비스 하는 서버의 사정상 각 스키마별로 tablespace를 재지정 한다는건 개발팀이나 시스템에서도
난색을 표하는( 한차례 schema별 덤프를 받아야 했는데 이건 이틀짜리다 ) 일이어서 결국
파일 시스템 레벨에서 해결을 하기로 했다.

답은...

tune2fs /dev/sdxN -O dir_index

일단 data디렉토리를 백업, archive를 압축 해 두고 나서 파일 제대로 있는지 꼭 확인 한 후에
위의 옵션을 ext3 에 추가 해 주고
시스템을 리붓 하던가 아니면 언마운트 하고 fsck 를 돌려버린다.

아, 물론 새로운 디스크에 해당 옵션을 걸고 미러링을 하는것이 더 안정성 높은 좋은 방법이다.

물론 한 디렉토리 안에 무지막지한 파일 갯수의 문제는 남아있게 되지만...
뭐, 어쩌겠는가  디비 구조 변경 당장 못하겠다는데..

사용중 하나의 스키마에만 문제가 발생해도 복구할때는 전체 압축파일을 다 풀어버려야 한다.
이건 정말 고역이어서..  WAL 백업 프로세스에 들어가게 되면.. dir_index 가 걸려있지 않은 상황에서는
해당 wal 백업과 online 백업 전체를 옮겨서 준비가 된 서버에서 작업을 해주어야 하는..
dir_index 도 좋지만 보다 좋은 성능을 내는건... 훗훗
보시라.

일단 메모리가 32기가 정도 되는 서버를 준비해서
data 디렉토리 사이즈만큼 크기를 할당하고 ( dd if=/dev/zero of=/dev/ram0 bs=1k count=니맘대로 )
파일 시스템을 생성 ( mkreiserfs -f /dev/ram0 ) 하고
loopback으로 마운트 한 다음에 ( mount -t reiserfs -o loop  /dev/ram0  니맘대로 )
여기다가 online 백업을 풀어버리는거다.

12기가의 파일 50만개 정도를 8분에 풀어버렸다. ( 듀얼코어 옵테론 270 X 4 , sata 디스크 )
여기서 덤프는 그다지 빠르진 않지만, 기타 다른 seq scan 이나 sort 등  tmp table 생성이 요구되는
여러가지 작업에서 db 성능은 매우 비약적으로 향상된다.

하지만 돈 많이 든다는거...


또 하나의 문제가 있는데.

어디, 스키마를 만들어 내기만 하는게 아니다.
지우기도 하지 않는가?

여기서 MVCC 의 문제가 발생해 버린다.

postgresql 의 pg_catalog 하위 테이블중 pg_class 및 관련 attribute 등의 해당 db system catalog 들에
문제가 발생한다.  일례로 pg_class 테이블 및 인덱스 파일 사이즈가 1기가가 넘어버리는 사태가 발생하기도
했지만... 
역시 디비 구조 변경은 당분간 불가 합니다 라는 말이므로 어쩌겠는가...
data 디렉토리 크기는 날이 갈 수록 커지지.. 스키마 추가, 삭제의 반복.. 그리고 각 스키마 별로 들어있는
엉망 구조의 게시판 테이블 구조...

vacuumdb -az, reindex -S  등을 열심히 돌려주었다.  가끔 대규모 drop 이나 대규모 insert 가 발생한 경우엔
vacuumdb -azf .

백업에 대해 문제가 발생할 소지도 있어서 참 난감 했다.
vacuum 전 백업, vacuum -avz 로 로그를 뽑아 내어 행여나 잘못된 테이블이나 인덱스때문에 vacuum 이 죽어버
리게 되면..

아무튼... 이런 저런 문제 끝에 지금은 여러가지 절차적, 구조적 수정을 감행하여 실제 서비스에 큰 무리 없도록
하고 있긴 하지만...  언제 터질지 모르는 폭탄같아 좀 무섭긴 하다.