|
Master DB |
|
[ 수행중인 DB의 Data Directory 확인 ]
[enterprisedb@EXAVM ~]$ ps -ef | grep postgres enterpr+ 25088 1 0 11:09 pts/0 00:00:00 /opt/PostgresPlus/9.4AS/bin/edb-postgres -D /opt/PostgresPlus/9.4AS/data enterpr+ 25089 25088 0 11:09 ? 00:00:00 postgres: logger process enterpr+ 25091 25088 0 11:09 ? 00:00:00 postgres: checkpointer process enterpr+ 25092 25088 0 11:09 ? 00:00:00 postgres: writer process enterpr+ 25093 25088 0 11:09 ? 00:00:00 postgres: wal writer process enterpr+ 25094 25088 0 11:09 ? 00:00:00 postgres: autovacuum launcher process
[ postgresql.conf 파일 편집 ]
[enterprisedb@EXAVM ~]$ vi /opt/PostgresPlus/9.4AS/data/postgresql.conf
wal_level = hot_standby archive_mode = on archive_command = 'cp %p /odata/PG/PramaryARC/%f' hot_standby = on max_wal_senders = 4
# /odata/PG/PramaryARC/ : 미리 생성 시켜 놓은 Archive Directory
[ pg_hba.conf 파일 편집 ]
[enterprisedb@EXAVM ~]$ vi /opt/PostgresPlus/9.4AS/data/pg_hba.conf
# replication privilege. add 2016.06.14 host replication enterprisedb 127.0.0.1/32 trust host replication enterprisedb ::1/128 trust
[ 환경 적용을 위하여 Restart ]
[enterprisedb@EXAVM data]$ pg_ctl restart -mf [enterprisedb@EXAVM data]$ ps -ef | grep postgres enterpr+ 25088 1 1 11:09 pts/0 00:00:00 /opt/PostgresPlus/9.4AS/bin/edb-postgres -D /opt/PostgresPlus/9.4AS/data enterpr+ 25089 25088 0 11:09 ? 00:00:00 postgres: logger process enterpr+ 25091 25088 0 11:09 ? 00:00:00 postgres: checkpointer process enterpr+ 25092 25088 0 11:09 ? 00:00:00 postgres: writer process enterpr+ 25093 25088 0 11:09 ? 00:00:00 postgres: wal writer process enterpr+ 25094 25088 0 11:09 ? 00:00:00 postgres: autovacuum launcher process enterpr+ 25095 25088 0 11:09 ? 00:00:00 postgres: archiver process enterpr+ 25096 25088 0 11:09 ? 00:00:00 postgres: stats collector process
[ Standby DB 생성 ]
Standby DB는 /odata/PG/data_str_replication directory에 생성 시킬 것임.
[enterprisedb@EXAVM data]$ pg_basebackup -h localhost -D /odata/PG/data_str_replication -U enterprisedb -v -P 1768160/1768160 kB (100%), 1/1 tablespace NOTICE: pg_stop_backup complete, all required WAL segments have been archived
|
|
Standby DB |
|
[ postgresql.conf 파일 편집 ]
[enterprisedb@EXAVM ~]$ vi /odata/PG/data_str_replication/postgresql.conf
port = 5447
[ recovery.conf 파일 생성 ]
[enterprisedb@EXAVM ~]$ vi /odata/PG/data_str_replication/recovery.conf
standby_mode = 'on' primary_conninfo = 'host=localhost port=5444 user=enterprisedb' trigger_file = '/tmp/edb.trigger.5447'
[ Standby DB 시작 ]
[enterprisedb@EXAVM ~]$ chown -R 700 /odata/PG/data_str_replication [enterprisedb@EXAVM ~]$ pg_ctl start -D /odata/PG/data_str_replication server starting [enterprisedb@EXAVM ~]$ 2016-06-14 11:18:20 KST LOG: redirecting log output to logging collector process 2016-06-14 11:18:20 KST HINT: Future log output will appear in directory "pg_log".
[enterprisedb@EXAVM ~]$ ps -ef | grep posgres enterpr+ 25395 25342 0 11:18 pts/1 00:00:00 grep --color=auto posgres [enterprisedb@EXAVM ~]$ ps -ef | grep postgres enterpr+ 25088 1 0 11:09 pts/0 00:00:00 /opt/PostgresPlus/9.4AS/bin/edb-postgres -D /opt/PostgresPlus/9.4AS/data enterpr+ 25089 25088 0 11:09 ? 00:00:00 postgres: logger process enterpr+ 25091 25088 0 11:09 ? 00:00:00 postgres: checkpointer process enterpr+ 25092 25088 0 11:09 ? 00:00:00 postgres: writer process enterpr+ 25093 25088 0 11:09 ? 00:00:00 postgres: wal writer process enterpr+ 25094 25088 0 11:09 ? 00:00:00 postgres: autovacuum launcher process enterpr+ 25095 25088 0 11:09 ? 00:00:00 postgres: archiver process last was 000000010000000000000050.00000028.backup enterpr+ 25389 25088 0 11:18 ? 00:00:00 postgres: wal sender process enterprisedb ::1[2781] streaming 0/51000138 enterpr+ 25096 25088 0 11:09 ? 00:00:00 postgres: stats collector process enterpr+ 25385 1 0 11:18 pts/1 00:00:00 /opt/PostgresPlus/9.4AS/bin/edb-postgres -D /odata/PG/data_str_replication enterpr+ 25386 25385 0 11:18 ? 00:00:00 postgres: logger process enterpr+ 25387 25385 0 11:18 ? 00:00:00 postgres: startup process recovering 000000010000000000000051 enterpr+ 25388 25385 0 11:18 ? 00:00:00 postgres: wal receiver process streaming 0/51000138 enterpr+ 25390 25385 0 11:18 ? 00:00:00 postgres: checkpointer process enterpr+ 25391 25385 0 11:18 ? 00:00:00 postgres: writer process enterpr+ 25392 25385 0 11:18 ? 00:00:00 postgres: stats collector process
[ Standby Log 파일 확인 ]
[enterprisedb@EXAVM ~]$ cd /odata/PG/data_str_replication/pg_log [enterprisedb@EXAVM pg_log]$ ls enterprisedb-2016-05-26_095001.log enterprisedb-2016-06-01_000000.log enterprisedb-2016-06-07_000000.log enterprisedb-2016-06-13_000000.log enterprisedb-2016-05-27_000000.log enterprisedb-2016-06-02_000000.log enterprisedb-2016-06-08_000000.log enterprisedb-2016-06-13_192325.log enterprisedb-2016-05-28_000000.log enterprisedb-2016-06-03_000000.log enterprisedb-2016-06-09_000000.log enterprisedb-2016-06-14_000000.log enterprisedb-2016-05-29_000000.log enterprisedb-2016-06-04_000000.log enterprisedb-2016-06-10_000000.log enterprisedb-2016-06-14_110901.log enterprisedb-2016-05-30_000000.log enterprisedb-2016-06-05_000000.log enterprisedb-2016-06-11_000000.log enterprisedb-2016-06-14_111820.log enterprisedb-2016-05-31_000000.log enterprisedb-2016-06-06_000000.log enterprisedb-2016-06-12_000000.log startup.log [enterprisedb@EXAVM pg_log]$ cat enterprisedb-2016-06-14_111820.log 2016-06-14 11:18:20 KST LOG: database system was interrupted; last known up at 2016-06-14 11:09:23 KST 2016-06-14 11:18:20 KST LOG:
** EnterpriseDB Dynamic Tuning Agent ******************************************** * System Utilization: 66 % * * Database Version: 9.4.1.3 * * Database Size: 1.7 GB * * RAM: 125.9 GB * * Shared Memory: 95367 MB * * Max DB Connections: 112 * * Autovacuum: on * * Autovacuum Naptime: 60 Seconds * *********************************************************************************
2016-06-14 11:18:20 KST LOG: entering standby mode 2016-06-14 11:18:20 KST LOG: started streaming WAL from primary at 0/50000000 on timeline 1 2016-06-14 11:18:21 KST LOG: redo starts at 0/50000028 2016-06-14 11:18:21 KST LOG: consistent recovery state reached at 0/500000F0 2016-06-14 11:18:21 KST LOG: database system is ready to accept read only connections
|
|
Streaming Test : Master DB에서 작업한 내용이 Standby DB에 제대로 적용 되는지 확인
|
|
Master DB |
|
[enterprisedb@EXAVM ~]$ psql -p 5444 psql.bin (9.4.1.3) Type "help" for help.
edb=# edb=# \connect tpch tpch Password for user tpch: You are now connected to database "tpch" as user "tpch". tpch=> \dt List of relations Schema | Name | Type | Owner --------+----------+-------+------- public | customer | table | tpch public | lineitem | table | tpch public | nation | table | tpch public | orders | table | tpch public | part | table | tpch public | partsupp | table | tpch public | region | table | tpch public | supplier | table | tpch (8 rows)
tpch=> create table test (id int) ; CREATE TABLE tpch=> commit ; COMMIT tpch=> insert into test values (1) ; INSERT 0 1 tpch=> insert into test values (2) ; INSERT 0 1 tpch=> commit ; COMMIT tpch=> \conninfo You are connected to database "tpch" as user "tpch" via socket in "/tmp" at port "5444". tpch=> exit |
|
Standby DB |
|
[enterprisedb@EXAVM ~]$ psql -p 5447 Password: psql.bin (9.4.1.3) Type "help" for help.
edb=# \connect tpch tpch Password for user tpch: You are now connected to database "tpch" as user "tpch". tpch=> \dt List of relations Schema | Name | Type | Owner --------+----------+-------+------- public | customer | table | tpch public | lineitem | table | tpch public | nation | table | tpch public | orders | table | tpch public | part | table | tpch public | partsupp | table | tpch public | region | table | tpch public | supplier | table | tpch public | test | table | tpch (9 rows)
tpch=> select * from test ; id ---- 1 2 (2 rows)
tpch=> \conninfo You are connected to database "tpch" as user "tpch" via socket in "/tmp" at port "5447". tpch=> exit |
|
Monitoring Query : pg_stat_replication 뷰를 이용하여 streaming 전송 상태를 모니터링 Gap을 모니터링 |
|
Master DB |
|
edb=# SELECT usename , application_name , state , sent_location , replay_location , sync_state FROM pg_stat_replication ;
usename | application_name | state | sent_location | replay_location | sync_state --------------+------------------+-----------+---------------+-----------------+------------ enterprisedb | walreceiver | streaming | 0/51016AE0 | 0/51016AE0 | async (1 row)
edb=# SELECT pg_xlog_location_diff(sent_location, replay_location) FROM pg_stat_replication; pg_xlog_location_diff ----------------------- 0 (1 row)
|
|
Standby DB |
|
edb=# SELECT usename , application_name , state , sent_location , replay_location , sync_state FROM pg_stat_replication ; usename | application_name | state | sent_location | replay_location | sync_state ---------+------------------+-------+---------------+-----------------+------------ (0 rows)
edb=# SELECT pg_xlog_location_diff(sent_location, replay_location) FROM pg_stat_replication; pg_xlog_location_diff ----------------------- (0 rows) |