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/