달력

3

« 2024/3 »

  • 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
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