PostgreSQL PPAS Streaming Replication

PostgreSQL Streaming Replication

 

설명 :

 스트리밍 복제 (Streaming Replication)은 PostgreSQL 9.0 이상에서 사용 가능한 복제 기능.
Insert/Update/Delete, Create/Drop 가능한 1 개의 Master DB에 SELECT만 할 수 있는 여러 Standby DB에 복제하는 것
Standby DB의 변경 결과가 반영되기까지 약간의 지연이 있지만 비교적 지연은 적게, 마스터 DB에 영향 작은 복제 방식.

 

 

목적 :

1. 많은 SELECT Query의 Server 간 분산
2. Master DB 이상시 신속한 Fail over
3. Master DB의 Disk 장애에 대비한 실시간 Backup

 

 

9.0 에 비해 강화된 9.1의 기능

1. 동기화 복제 (변경 결과 도착 보장)
2. 기반 Backup의 간소화 (초기 Data 복제 도구)
3. 복제 상태를 확인하기위한 System view
4. Fail Over 지원 강화 (전용 명령 다른 Master Server에 다시 연결)

 

 

 

- 구축절차

 

1. 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$

 2. 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$

 

 

 

 

Posted By 김득은

 

자료 출처