2. DBMS이야기/01. PostgreSQL

PostgreSQL PPAS Streaming Replication

OSSW(Open Source System SoftWare 2014. 11. 26. 17:28

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 김득은

 

자료 출처