기존에 만들었던 PG VM을 Copy해서 Replication을 구성 함
###############
2가지 구성 종류
###############
log-shipping (physical replication - archive log file 전달)
pg_xlog 디렉터리 안에 wal 파일 자체를 전달해서 사용하는 방식 (Oracle의 아카이브 파일을 전송 후 반영하는 것과 같은 ??)
wal file이 생성되기 까지 master와 standby의 데이터 갭이 발생 할 수 있다.
streaming replication (logical replication - SQL command 전달)
9.0부터 가능
Master Server의 wal 내용을 standby로 전송하여 반영하는 방식
wal sender, wal receiver 프로세스가 이 작업을 수행
하나 이상의 standby 서버 가능
master
replication user 생성
create user repl with replication password 'repl1122' ;
replication user 접근 설정
pg_hba.conf
host replication repl 0.0.0.0/0 md5
또는
host replication repl 192.168.56.117/32 md5
parameter 설정
postgresql.conf
wal_level = 버전에 맞게 설정 (9.5까지는 hot_standby , 9.6 부터는 replica)
max_wal_senders = 3 (넉넉하게)
wal_keep_segments = 100 (기본이 16M 이므로 ,약 1.6G- 100개 파일) or max_replication_slots = 2
-- replication slot을 사용할 경우 slot을 생성해 줘야 한다.
-- select * from pg_create_physical_replication_slot('slot1') ;
-- select * from pg_replication_slots ;
standby
DB stop
rm -rf $PGDATA
pg_basebackup -h 192.168.56.116 -p 5493 -D /data/pg9.3 -U repl -P -v -X stream
vi $PGDATA/recovery.conf
standby_mode='on'
primary_conninfo='host=192.168.56.116 port=5493 user=repl password=repl1122'
trigger_file='/data/pg9.3/failover_trigger'
-- slot을 사용할 경우 아래 주석 제거
#primary_slot_name='slot1'
######################
Replication 구성 서버
######################
master
192.168.56.116
standby
192.168.56.117
##############
관련 파라미터들
##############
master
wal_level : wal 에 저장되는 데이터의 수준을 지정.
9.3 : minimal, archive, or hot_standby
9.4 : minimal, archive, hot_standby, or logical
9.5 : minimal, archive, hot_standby, or logical
9.6 : minimal, replica, or logical
10.x : minimal, replica, or logical
11.x : minimal, replica, or logical
12.x : minimal, replica, or logical
13.x : minimal, replica, or logical
14.x : minimal, replica, or logical
max_wal_senders : wal 내용을 읽어서 전송할 프로세스 개수 설정. (0은 비활성화, 1이상으로 설정 시 streaming replication, 일반 유저 커넥션과 sender 커넥션을 포함하는 값이 max_connecitons)
standby 서버에서 연결이 잠시 끊어졌다가 다시 접속 될수도 있으므로, standby 개수보다 크게 준다.
wal_keep_segments : pg_xlog 디렉터리에 저장해 놓을 과거 로그 파일의 개수 지정. standby에서 replication이 늦게 따라오고 있을 경우 아직 standby 서버에 적용되지 않은 wal 세그먼트가 삭제 될 수 있으니 잘 설정해 주어야 한다.
(하나의 세그먼트는 기본으로 16M , 기본값은 0)
9.4 부터는 자동으로 standby에서 적용 못 시킨 wal 파일을 보관할 수 있게 replication slot을 지원한다. 이럴 경우 standby 서버가 오래 끊어져 있을 경우 pg_xlog 가 계속 커 질 수 있다.
wal_sender_timeout : 설정 값보다 오래된 비활성화 sender 세션을 종료 시킴 (ms). standby서버와 네트워크 이상이거나, standby 서버 장애를 감지 하는데 유용함. (기본값 60초)
synchronous_standby_names : standby 서버 리스트를 컴마로 구별하여 지정해 준다. 커밋을 기다리는 트랜잭션은 리스트 된 standby 서버 중 첫번째 standby 서버가 데이터를 수신한 것을 확인한 후에 커밋을 진행 한다.
첫 번째 standby 서버가 연결이 끊어지면, 두 번째 standby 서버로 우선순위가 교체 된다.
이 값에 *을 설정하면 모든 standby 서버의 walreceiver의 이름을 뜻하게 된다.
이 값에 아무값도 없으면 synchronous 복제가 비 활성화 된다. synchronous 복제가 활성화 된 상태에서도 "synchronous_commit" 파라미터를 local 또는 off를 시켜서 사용할 수 있다.
여기에 설정해야 하는 이름은 standby 서버 walreceiver의 primary_conninfo값에 application_name으로 설정 한다.
vacuum_defer_cleanup_age : vacuum 이나 hot update시 dead row를 정리 해야 하는데 이를 연기 시키는 트랜잭션 수를 지정 한다.(기본은 0)
이 값을 대신하는 것은 standby 서버에서 hot_standby_feedback 값을 설정 하는 것이다.
Standby
hot_standby : standby 모드에서 커넥션을 받아서 쿼리를 수행가능하게 할지 여부를 결정(기본 OFF). server start시에만 변경 가능. archive recovery 또는 standby 모드에서만 적용 됨
#######
9.3
#######
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg9.3/bin/psql -p 5493 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=hot_standby
max_wal_senders = 3
wal_keep_segments = 100 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5493 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- recovery.conf 파일 생성 ($PGDATA)
echo "
standby_mode='on'
primary_conninfo='host=192.168.56.116 port=5494 user=repl password=repl1122'
#primary_slot_name='repl_slot_01'
trigger_file='/data/pg9.4/failover_trigger'" >> $PGDATA/recovery.conf
stanby start...
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg9.3/bin/psql -p 5493 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1591 | 16384 | repl | walreceiver | 192.168.56.117 | | 39032 | 2022-04-08 12:56:36.515781+09 | streaming | 0/3000528 | 0/3000528 | 0/3000528 | 0/3000528 | 0 | async
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1326 0.0 0.0 115548 2144 pts/0 S 09:14 0:00 -bash
postgres 1665 0.0 0.0 155448 1840 pts/0 R+ 13:39 0:00 \_ ps -u postgres uf
postgres 1571 0.0 0.3 265276 12664 pts/0 S 12:50 0:00 /engine/pg9.3/bin/postgres -D /data/pg9.3
postgres 1573 0.0 0.0 265404 2216 ? Ss 12:50 0:00 \_ postgres: checkpointer process
postgres 1574 0.0 0.0 265276 1764 ? Ss 12:50 0:00 \_ postgres: writer process
postgres 1575 0.0 0.0 265276 752 ? Ss 12:50 0:00 \_ postgres: wal writer process
postgres 1576 0.0 0.0 266140 2092 ? Ss 12:50 0:00 \_ postgres: autovacuum launcher process
postgres 1577 0.0 0.0 120952 932 ? Ss 12:50 0:00 \_ postgres: stats collector process
postgres 1591 0.0 0.0 266088 2392 ? Ss 12:56 0:00 \_ postgres: wal sender process repl 192.168.56.117(39032) streaming 0/3000528
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1327 0.0 0.0 115548 2160 pts/0 S 09:14 0:00 -bash
postgres 1491 0.0 0.0 155448 1840 pts/0 R+ 13:40 0:00 \_ ps -u postgres uf
postgres 1457 0.0 0.3 265316 12656 pts/0 S 12:56 0:00 /engine/pg9.3/bin/postgres -D /data/pg9.3
postgres 1458 0.0 0.0 265416 1544 ? Ss 12:56 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1459 0.0 0.0 265416 2012 ? Ss 12:56 0:00 \_ postgres: checkpointer process
postgres 1460 0.0 0.0 265316 1756 ? Ss 12:56 0:00 \_ postgres: writer process
postgres 1461 0.0 0.0 120952 728 ? Ss 12:56 0:00 \_ postgres: stats collector process
postgres 1462 0.0 0.0 271884 1712 ? Ss 12:56 0:02 \_ postgres: wal receiver process streaming 0/3000528
-- logfile 확인
LOG: database system was interrupted; last known up at 2022-04-08 12:50:26 KST
LOG: entering standby mode
LOG: redo starts at 0/2000028
LOG: consistent recovery state reached at 0/20000F0
LOG: database system is ready to accept read only connections
LOG: started streaming WAL from primary at 0/3000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg9.3/bin/psql -p 5493 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1591 | 16384 | repl | walreceiver | 192.168.56.117 | | 39032 | 2022-04-08 12:56:36.515781+09 | streaming | 0/3013F48 | 0/3013F48 | 0/3013F48 | 0/3013F48 | 0 | async
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1326 0.0 0.0 115548 2148 pts/0 S 09:14 0:00 -bash
postgres 1713 0.0 0.0 155448 1836 pts/0 R+ 14:00 0:00 \_ ps -u postgres uf
postgres 1571 0.0 0.3 265276 12664 pts/0 S 12:50 0:00 /engine/pg9.3/bin/postgres -D /data/pg9.3
postgres 1573 0.0 0.0 265404 2476 ? Ss 12:50 0:00 \_ postgres: checkpointer process
postgres 1574 0.0 0.0 265276 1764 ? Ss 12:50 0:00 \_ postgres: writer process
postgres 1575 0.0 0.0 265276 752 ? Ss 12:50 0:00 \_ postgres: wal writer process
postgres 1576 0.0 0.0 266140 2132 ? Ss 12:50 0:00 \_ postgres: autovacuum launcher process
postgres 1577 0.0 0.0 121084 968 ? Ss 12:50 0:00 \_ postgres: stats collector process
postgres 1591 0.0 0.0 266220 2392 ? Ss 12:56 0:00 \_ postgres: wal sender process repl 192.168.56.117(39032) streaming 0/3013F48
standby
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1327 0.0 0.0 115548 2160 pts/0 S 09:14 0:00 -bash
postgres 1512 0.0 0.0 155448 1840 pts/0 R+ 14:00 0:00 \_ ps -u postgres uf
postgres 1457 0.0 0.3 265316 12656 pts/0 S 12:56 0:00 /engine/pg9.3/bin/postgres -D /data/pg9.3
postgres 1458 0.0 0.0 265492 2912 ? Ss 12:56 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1459 0.0 0.0 265416 2264 ? Ss 12:56 0:00 \_ postgres: checkpointer process
postgres 1460 0.0 0.0 265316 1756 ? Ss 12:56 0:00 \_ postgres: writer process
postgres 1461 0.0 0.0 121084 728 ? Ss 12:56 0:00 \_ postgres: stats collector process
postgres 1462 0.0 0.0 272012 1984 ? Ss 12:56 0:03 \_ postgres: wal receiver process streaming 0/3013F48
echo "select * from test;" | /engine/pg9.3/bin/psql -U app -p 5493 -d test
#######
9.4
#######
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg9.4/bin/psql -p 5494 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=hot_standby
max_wal_senders = 3
wal_keep_segments = 100 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5494 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- recovery.conf 파일 생성 ($PGDATA)
echo "
standby_mode='on'
primary_conninfo='host=192.168.56.116 port=5494 user=repl password=repl1122'
#primary_slot_name='repl_slot_01'
trigger_file='/data/pg9.4/failover_trigger'" >> $PGDATA/recovery.conf
stanby start...
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg9.4/bin/psql -p 5494 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1466 | 16385 | repl | walreceiver | 192.168.56.117 | | 48862 | 2022-04-11 13:43:43.028452+09 | 1896 | streaming | 0/3000138 | 0/3000138 | 0/3000138 | 0/3000138 | 0 | async
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1450 0.0 0.3 270300 13276 pts/0 S 13:39 0:00 /engine/pg9.4/bin/postgres -D /data/pg9.4
postgres 1451 0.0 0.0 123324 624 ? Ss 13:39 0:00 \_ postgres: logger process
postgres 1453 0.0 0.0 270416 2280 ? Ss 13:39 0:00 \_ postgres: checkpointer process
postgres 1454 0.0 0.0 270444 2236 ? Ss 13:39 0:00 \_ postgres: writer process
postgres 1455 0.0 0.1 270300 5132 ? Ss 13:39 0:00 \_ postgres: wal writer process
postgres 1456 0.0 0.0 270704 1840 ? Ss 13:39 0:00 \_ postgres: autovacuum launcher process
postgres 1457 0.0 0.0 125576 1008 ? Ss 13:39 0:00 \_ postgres: stats collector process
postgres 1466 0.0 0.0 270704 2064 ? Ss 13:43 0:00 \_ postgres: wal sender process repl 192.168.56.117(48862) streaming 0/3000138
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1391 0.0 0.3 270332 13272 pts/0 S 13:43 0:00 /engine/pg9.4/bin/postgres -D /data/pg9.4
postgres 1392 0.0 0.0 123324 624 ? Ss 13:43 0:00 \_ postgres: logger process
postgres 1393 0.0 0.0 270448 1580 ? Ss 13:43 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1394 0.0 0.0 270332 920 ? Ss 13:43 0:00 \_ postgres: checkpointer process
postgres 1395 0.0 0.0 270332 1968 ? Ss 13:43 0:00 \_ postgres: writer process
postgres 1396 0.0 0.0 125444 656 ? Ss 13:43 0:00 \_ postgres: stats collector process
postgres 1397 0.0 0.0 274740 1844 ? Ss 13:43 0:00 \_ postgres: wal receiver process streaming 0/3000138
-- logfile 확인
2022-04-11 13:43:42 KST [1393]: [1-1] user=,db=,remote=LOG: database system was interrupted; last known up at 2022-04-11 13:40:25 KST
2022-04-11 13:43:43 KST [1393]: [2-1] user=,db=,remote=LOG: entering standby mode
2022-04-11 13:43:43 KST [1393]: [3-1] user=,db=,remote=LOG: redo starts at 0/2000060
2022-04-11 13:43:43 KST [1393]: [4-1] user=,db=,remote=LOG: consistent recovery state reached at 0/2000128
2022-04-11 13:43:43 KST [1391]: [3-1] user=,db=,remote=LOG: database system is ready to accept read only connections
2022-04-11 13:43:43 KST [1397]: [1-1] user=,db=,remote=LOG: started streaming WAL from primary at 0/3000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg9.4/bin/psql -p 5494 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1466 | 16385 | repl | walreceiver | 192.168.56.117 | | 48862 | 2022-04-11 13:43:43.028452+09 | 1902 | streaming | 0/3012590 | 0/3012590 | 0/3012590 | 0/3012590 | 0 | async
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1450 0.0 0.3 270300 13276 pts/0 S 13:39 0:00 /engine/pg9.4/bin/postgres -D /data/pg9.4
postgres 1451 0.0 0.0 123324 624 ? Ss 13:39 0:00 \_ postgres: logger process
postgres 1453 0.0 0.0 270416 2528 ? Ss 13:39 0:00 \_ postgres: checkpointer process
postgres 1454 0.0 0.0 270444 2236 ? Ss 13:39 0:00 \_ postgres: writer process
postgres 1455 0.0 0.1 270300 5132 ? Ss 13:39 0:00 \_ postgres: wal writer process
postgres 1456 0.0 0.0 270704 1876 ? Ss 13:39 0:00 \_ postgres: autovacuum launcher process
postgres 1457 0.0 0.0 125576 1036 ? Ss 13:39 0:00 \_ postgres: stats collector process
postgres 1466 0.0 0.0 270836 2064 ? Ss 13:43 0:00 \_ postgres: wal sender process repl 192.168.56.117(48862) streaming 0/3012590
standby
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1391 0.0 0.3 270332 13272 pts/0 S 13:43 0:00 /engine/pg9.4/bin/postgres -D /data/pg9.4
postgres 1392 0.0 0.0 123324 624 ? Ss 13:43 0:00 \_ postgres: logger process
postgres 1393 0.0 0.0 270512 3348 ? Ss 13:43 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1394 0.0 0.0 270448 2484 ? Ss 13:43 0:00 \_ postgres: checkpointer process
postgres 1395 0.0 0.0 270332 1968 ? Ss 13:43 0:00 \_ postgres: writer process
postgres 1396 0.0 0.0 125444 656 ? Ss 13:43 0:00 \_ postgres: stats collector process
postgres 1397 0.0 0.0 274868 2008 ? Ss 13:43 0:00 \_ postgres: wal receiver process streaming 0/3012590
echo "select * from test;" | /engine/pg9.4/bin/psql -U app -p 5494 -d test
#######
9.5
#######
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg9.5/bin/psql -p 5495 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=hot_standby
max_wal_senders = 3
wal_keep_segments = 100 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5495 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- recovery.conf 파일 생성 ($PGDATA)
echo "
standby_mode='on'
primary_conninfo='host=192.168.56.116 port=5495 user=repl password=repl1122'
#primary_slot_name='repl_slot_01'
trigger_file='/data/pg9.5/failover_trigger'" >> $PGDATA/recovery.conf
stanby start...
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg9.5/bin/psql -p 5495 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1677 | 16384 | repl | walreceiver | 192.168.56.117 | | 52970 | 2022-04-11 14:07:45.938827+09 | 1835 | streaming | 0/3000060 | 0/3000060 | 0/3000060 | 0/3000060 | 0 | async
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1662 0.0 0.3 270864 13352 pts/0 S 14:04 0:00 /engine/pg9.5/bin/postgres -D /data/pg9.5
postgres 1663 0.0 0.0 123744 680 ? Ss 14:04 0:00 \_ postgres: logger process
postgres 1665 0.0 0.0 270968 2524 ? Ss 14:04 0:00 \_ postgres: checkpointer process
postgres 1666 0.0 0.0 270864 2104 ? Ss 14:04 0:00 \_ postgres: writer process
postgres 1667 0.0 0.1 270864 5000 ? Ss 14:04 0:00 \_ postgres: wal writer process
postgres 1668 0.0 0.0 271300 1892 ? Ss 14:04 0:00 \_ postgres: autovacuum launcher process
postgres 1669 0.0 0.0 125996 1044 ? Ss 14:04 0:00 \_ postgres: stats collector process
postgres 1677 0.0 0.0 271172 2140 ? Ss 14:07 0:00 \_ postgres: wal sender process repl 192.168.56.117(52970) streaming 0/3000060
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1535 0.1 0.3 270896 13340 pts/0 S 14:07 0:00 /engine/pg9.5/bin/postgres -D /data/pg9.5
postgres 1536 0.0 0.0 123744 680 ? Ss 14:07 0:00 \_ postgres: logger process
postgres 1537 0.0 0.0 270968 1472 ? Ss 14:07 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1538 0.0 0.0 270896 832 ? Ss 14:07 0:00 \_ postgres: checkpointer process
postgres 1539 0.0 0.0 270896 1076 ? Ss 14:07 0:00 \_ postgres: writer process
postgres 1540 0.0 0.0 125864 700 ? Ss 14:07 0:00 \_ postgres: stats collector process
postgres 1541 0.3 0.0 275172 1596 ? Ss 14:07 0:00 \_ postgres: wal receiver process streaming 0/3000060
-- logfile 확인
2022-04-11 14:07:45 KST [1537]: [1-1] user=,db=,remote=LOG: database system was interrupted; last known up at 2022-04-11 14:05:56 KST
2022-04-11 14:07:45 KST [1537]: [2-1] user=,db=,remote=LOG: entering standby mode
2022-04-11 14:07:46 KST [1537]: [3-1] user=,db=,remote=LOG: redo starts at 0/2000028
2022-04-11 14:07:46 KST [1537]: [4-1] user=,db=,remote=LOG: consistent recovery state reached at 0/2000130
2022-04-11 14:07:46 KST [1535]: [3-1] user=,db=,remote=LOG: database system is ready to accept read only connections
2022-04-11 14:07:46 KST [1541]: [1-1] user=,db=,remote=LOG: started streaming WAL from primary at 0/3000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg9.5/bin/psql -p 5495 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1677 | 16384 | repl | walreceiver | 192.168.56.117 | | 52970 | 2022-04-11 14:07:45.938827+09 | 1841 | streaming | 0/30162F0 | 0/30162F0 | 0/30162F0 | 0/30162F0 | 0 | async
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1718 0.0 0.0 115548 2076 pts/0 S 14:11 0:00 -bash
postgres 1749 0.0 0.0 155448 1840 pts/0 R+ 14:11 0:00 \_ ps -u postgres uf
postgres 1662 0.0 0.3 270864 13352 pts/0 S 14:04 0:00 /engine/pg9.5/bin/postgres -D /data/pg9.5
postgres 1663 0.0 0.0 123744 680 ? Ss 14:04 0:00 \_ postgres: logger process
postgres 1665 0.0 0.0 270968 2524 ? Ss 14:04 0:00 \_ postgres: checkpointer process
postgres 1666 0.0 0.0 270864 2104 ? Ss 14:04 0:00 \_ postgres: writer process
postgres 1667 0.0 0.1 270864 5000 ? Ss 14:04 0:00 \_ postgres: wal writer process
postgres 1668 0.0 0.0 271300 1924 ? Ss 14:04 0:00 \_ postgres: autovacuum launcher process
postgres 1669 0.0 0.0 125996 1072 ? Ss 14:04 0:00 \_ postgres: stats collector process
postgres 1677 0.0 0.0 271480 2140 ? Ss 14:07 0:00 \_ postgres: wal sender process repl 192.168.56.117(52970) streaming 0/30162F0
standby
[postgres@postgres ~]$ ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1572 0.0 0.0 115548 2080 pts/0 S 14:11 0:00 -bash
postgres 1596 0.0 0.0 155448 1840 pts/0 R+ 14:11 0:00 \_ ps -u postgres uf
postgres 1535 0.0 0.3 270896 13340 pts/0 S 14:07 0:00 /engine/pg9.5/bin/postgres -D /data/pg9.5
postgres 1536 0.0 0.0 123744 680 ? Ss 14:07 0:00 \_ postgres: logger process
postgres 1537 0.0 0.0 271004 3396 ? Ss 14:07 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1538 0.0 0.0 270896 832 ? Ss 14:07 0:00 \_ postgres: checkpointer process
postgres 1539 0.0 0.0 270896 1868 ? Ss 14:07 0:00 \_ postgres: writer process
postgres 1540 0.0 0.0 125864 700 ? Ss 14:07 0:00 \_ postgres: stats collector process
postgres 1541 0.0 0.0 275352 1992 ? Ss 14:07 0:00 \_ postgres: wal receiver process streaming 0/30162F0
echo "select * from test;" | /engine/pg9.5/bin/psql -U app -p 5495 -d test
#######
9.6
#######
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg9.6/bin/psql -p 5496 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=replica
max_wal_senders = 3
wal_keep_segments = 100 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5496 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- recovery.conf 파일 생성 ($PGDATA)
echo "
standby_mode='on'
primary_conninfo='host=192.168.56.116 port=5495 user=repl password=repl1122'
#primary_slot_name='repl_slot_01'
trigger_file='/data/pg9.5/failover_trigger'" >> $PGDATA/recovery.conf
stanby start...
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg9.6/bin/psql -p 5496 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1812 | 16384 | repl | walreceiver | 192.168.56.117 | | 59404 | 2022-04-11 15:17:43.739304+09 | 1762 | streaming | 0/3000060 | 0/3000060 | 0/3000060 | 0/3000060 | 0 | async
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1718 0.0 0.0 115548 2104 pts/0 S 14:11 0:00 -bash
postgres 1817 0.0 0.0 155448 1840 pts/0 R+ 15:18 0:00 \_ ps -u postgres uf
postgres 1799 0.0 0.3 271128 13000 pts/0 S 15:16 0:00 /engine/pg9.6/bin/postgres -D /data/pg9.6
postgres 1800 0.0 0.0 124024 696 ? Ss 15:16 0:00 \_ postgres: logger process
postgres 1802 0.0 0.0 271128 2380 ? Ss 15:16 0:00 \_ postgres: checkpointer process
postgres 1803 0.0 0.0 271128 2124 ? Ss 15:16 0:00 \_ postgres: writer process
postgres 1804 0.1 0.1 271128 5284 ? Ss 15:16 0:00 \_ postgres: wal writer process
postgres 1805 0.0 0.0 271556 1964 ? Ss 15:16 0:00 \_ postgres: autovacuum launcher process
postgres 1806 0.0 0.0 126276 1052 ? Ss 15:16 0:00 \_ postgres: stats collector process
postgres 1812 0.0 0.0 271532 2224 ? Ss 15:17 0:00 \_ postgres: wal sender process repl 192.168.56.117(59404) streaming 0/3000060
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1633 0.0 0.0 115548 2156 pts/0 S 15:13 0:00 -bash
postgres 1674 0.0 0.0 155448 1840 pts/0 R+ 15:18 0:00 \_ ps -u postgres uf
postgres 1664 0.1 0.3 271160 12996 pts/0 S 15:17 0:00 /engine/pg9.6/bin/postgres -D /data/pg9.6
postgres 1665 0.0 0.0 124024 692 ? Ss 15:17 0:00 \_ postgres: logger process
postgres 1666 0.0 0.0 271224 1504 ? Ss 15:17 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1667 0.0 0.0 271160 844 ? Ss 15:17 0:00 \_ postgres: checkpointer process
postgres 1668 0.0 0.0 271160 1352 ? Ss 15:17 0:00 \_ postgres: writer process
postgres 1669 0.0 0.0 126144 916 ? Ss 15:17 0:00 \_ postgres: stats collector process
postgres 1670 0.3 0.0 275436 1628 ? Ss 15:17 0:00 \_ postgres: wal receiver process streaming 0/3000060
-- logfile 확인
2022-04-11 15:17:42 KST [1666]: [1-1] user=,db=,remote=LOG: database system was interrupted; last known up at 2022-04-11 15:16:57 KST
2022-04-11 15:17:43 KST [1666]: [2-1] user=,db=,remote=LOG: entering standby mode
2022-04-11 15:17:43 KST [1666]: [3-1] user=,db=,remote=LOG: redo starts at 0/2000060
2022-04-11 15:17:43 KST [1666]: [4-1] user=,db=,remote=LOG: consistent recovery state reached at 0/2000130
2022-04-11 15:17:43 KST [1664]: [3-1] user=,db=,remote=LOG: database system is ready to accept read only connections
2022-04-11 15:17:43 KST [1670]: [1-1] user=,db=,remote=LOG: started streaming WAL from primary at 0/3000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg9.6/bin/psql -p 5496 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1812 | 16384 | repl | walreceiver | 192.168.56.117 | | 59404 | 2022-04-11 15:17:43.739304+09 | 1768 | streaming | 0/30174A0 | 0/30174A0 | 0/30174A0 | 0/30174A0 | 0 | async
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1718 0.0 0.0 115660 2156 pts/0 S 14:11 0:00 -bash
postgres 1832 0.0 0.0 155448 1840 pts/0 R+ 15:22 0:00 \_ ps -u postgres uf
postgres 1799 0.0 0.3 271128 13000 pts/0 S 15:16 0:00 /engine/pg9.6/bin/postgres -D /data/pg9.6
postgres 1800 0.0 0.0 124024 696 ? Ss 15:16 0:00 \_ postgres: logger process
postgres 1802 0.0 0.0 271268 2640 ? Ss 15:16 0:00 \_ postgres: checkpointer process
postgres 1803 0.0 0.0 271128 2124 ? Ss 15:16 0:00 \_ postgres: writer process
postgres 1804 0.0 0.1 271128 5284 ? Ss 15:16 0:00 \_ postgres: wal writer process
postgres 1805 0.0 0.0 271556 1992 ? Ss 15:16 0:00 \_ postgres: autovacuum launcher process
postgres 1806 0.0 0.0 126276 1080 ? Ss 15:16 0:00 \_ postgres: stats collector process
postgres 1812 0.0 0.0 271800 2484 ? Ss 15:17 0:00 \_ postgres: wal sender process repl 192.168.56.117(59404) streaming 0/30174A0
standby
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1633 0.0 0.0 115548 2156 pts/0 S 15:13 0:00 -bash
postgres 1680 0.0 0.0 155448 1840 pts/0 R+ 15:22 0:00 \_ ps -u postgres uf
postgres 1664 0.0 0.3 271160 12996 pts/0 S 15:17 0:00 /engine/pg9.6/bin/postgres -D /data/pg9.6
postgres 1665 0.0 0.0 124024 692 ? Ss 15:17 0:00 \_ postgres: logger process
postgres 1666 0.0 0.0 271224 3328 ? Ss 15:17 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1667 0.0 0.0 271160 844 ? Ss 15:17 0:00 \_ postgres: checkpointer process
postgres 1668 0.0 0.0 271160 1880 ? Ss 15:17 0:00 \_ postgres: writer process
postgres 1669 0.0 0.0 126144 916 ? Ss 15:17 0:00 \_ postgres: stats collector process
postgres 1670 0.1 0.0 275568 1872 ? Ss 15:17 0:00 \_ postgres: wal receiver process streaming 0/30174A0
echo "select * from test;" | /engine/pg9.6/bin/psql -U app -p 5496 -d test
#######
10.20
#######
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg10.20/bin/psql -p 5410 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=replica
max_wal_senders = 3
wal_keep_segments = 100 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5410 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- recovery.conf 파일 생성 ($PGDATA)
echo "
standby_mode='on'
primary_conninfo='host=192.168.56.116 port=5410 user=repl password=repl1122'
#primary_slot_name='repl_slot_01'
trigger_file='/data/pg10.20/failover_trigger'" >> $PGDATA/recovery.conf
stanby start...
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg10.20/bin/psql -p 5410 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------
1909 | 16384 | repl | walreceiver | 192.168.56.117 | | 40628 | 2022-04-11 15:30:25.750393+09 | 557 | streaming | 0/3000060 | 0/3000060 | 0/3000060 | 0/3000060 | | | | 0 | async
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115548 2140 pts/0 S 15:26 0:00 -bash
postgres 1914 0.0 0.0 155448 1840 pts/0 R+ 15:31 0:00 \_ ps -u postgres uf
postgres 1896 0.0 0.3 271884 13132 pts/0 S 15:28 0:00 /engine/pg10.20/bin/postgres -D /data/pg10.20
postgres 1897 0.0 0.0 124704 888 ? Ss 15:28 0:00 \_ postgres: logger process
postgres 1899 0.0 0.0 271884 2652 ? Ss 15:28 0:00 \_ postgres: checkpointer process
postgres 1900 0.0 0.0 271884 2136 ? Ss 15:28 0:00 \_ postgres: writer process
postgres 1901 0.1 0.1 271884 5292 ? Ss 15:28 0:00 \_ postgres: wal writer process
postgres 1902 0.0 0.0 272324 1988 ? Ss 15:28 0:00 \_ postgres: autovacuum launcher process
postgres 1903 0.0 0.0 126956 1092 ? Ss 15:28 0:00 \_ postgres: stats collector process
postgres 1904 0.0 0.0 272176 1564 ? Ss 15:28 0:00 \_ postgres: bgworker: logical replication launcher
postgres 1909 0.0 0.0 272288 2516 ? Ss 15:30 0:00 \_ postgres: wal sender process repl 192.168.56.117(40628) streaming 0/3000060
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115548 2160 pts/0 S 15:26 0:00 -bash
postgres 1732 0.0 0.0 155448 1840 pts/0 R+ 15:31 0:00 \_ ps -u postgres uf
postgres 1722 0.0 0.3 271884 13124 pts/0 S 15:30 0:00 /engine/pg10.20/bin/postgres -D /data/pg10.20
postgres 1723 0.0 0.0 124704 888 ? Ss 15:30 0:00 \_ postgres: logger process
postgres 1724 0.0 0.0 271980 1612 ? Ss 15:30 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1725 0.0 0.0 271884 1100 ? Ss 15:30 0:00 \_ postgres: checkpointer process
postgres 1726 0.0 0.0 271884 1636 ? Ss 15:30 0:00 \_ postgres: writer process
postgres 1727 0.0 0.0 126824 928 ? Ss 15:30 0:00 \_ postgres: stats collector process
postgres 1728 0.2 0.0 276272 1968 ? Ss 15:30 0:00 \_ postgres: wal receiver process streaming 0/3000060
-- logfile 확인
2022-04-11 15:30:24 KST [1724]: [1-1] user=,db=,remote=LOG: database system was interrupted; last known up at 2022-04-11 15:29:28 KST
2022-04-11 15:30:25 KST [1724]: [2-1] user=,db=,remote=LOG: entering standby mode
2022-04-11 15:30:25 KST [1724]: [3-1] user=,db=,remote=LOG: redo starts at 0/2000060
2022-04-11 15:30:25 KST [1724]: [4-1] user=,db=,remote=LOG: consistent recovery state reached at 0/2000130
2022-04-11 15:30:25 KST [1722]: [6-1] user=,db=,remote=LOG: database system is ready to accept read only connections
2022-04-11 15:30:25 KST [1728]: [1-1] user=,db=,remote=LOG: started streaming WAL from primary at 0/3000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg10.20/bin/psql -p 5410 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------
1909 | 16384 | repl | walreceiver | 192.168.56.117 | | 40628 | 2022-04-11 15:30:25.750393+09 | 563 | streaming | 0/3018570 | 0/3018570 | 0/3018570 | 0/3018570 | | | | 0 | async
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115548 2148 pts/0 S 15:26 0:00 -bash
postgres 1928 0.0 0.0 155448 1840 pts/0 R+ 15:34 0:00 \_ ps -u postgres uf
postgres 1896 0.0 0.3 271884 13132 pts/0 S 15:28 0:00 /engine/pg10.20/bin/postgres -D /data/pg10.20
postgres 1897 0.0 0.0 124704 888 ? Ss 15:28 0:00 \_ postgres: logger process
postgres 1899 0.0 0.0 272024 2652 ? Ss 15:28 0:00 \_ postgres: checkpointer process
postgres 1900 0.0 0.0 271884 2400 ? Ss 15:28 0:00 \_ postgres: writer process
postgres 1901 0.0 0.1 271884 5292 ? Ss 15:28 0:00 \_ postgres: wal writer process
postgres 1902 0.0 0.0 272324 2028 ? Ss 15:28 0:00 \_ postgres: autovacuum launcher process
postgres 1903 0.0 0.0 126956 1120 ? Ss 15:28 0:00 \_ postgres: stats collector process
postgres 1904 0.0 0.0 272176 1564 ? Ss 15:28 0:00 \_ postgres: bgworker: logical replication launcher
postgres 1909 0.0 0.0 272568 2516 ? Ss 15:30 0:00 \_ postgres: wal sender process repl 192.168.56.117(40628) streaming 0/3018570
standby
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115548 2160 pts/0 S 15:26 0:00 -bash
postgres 1738 0.0 0.0 155448 1844 pts/0 R+ 15:34 0:00 \_ ps -u postgres uf
postgres 1722 0.0 0.3 271884 13124 pts/0 S 15:30 0:00 /engine/pg10.20/bin/postgres -D /data/pg10.20
postgres 1723 0.0 0.0 124704 888 ? Ss 15:30 0:00 \_ postgres: logger process
postgres 1724 0.0 0.0 271980 3696 ? Ss 15:30 0:00 \_ postgres: startup process recovering 000000010000000000000003
postgres 1725 0.0 0.0 271884 1100 ? Ss 15:30 0:00 \_ postgres: checkpointer process
postgres 1726 0.0 0.0 271884 2164 ? Ss 15:30 0:00 \_ postgres: writer process
postgres 1727 0.0 0.0 126824 928 ? Ss 15:30 0:00 \_ postgres: stats collector process
postgres 1728 0.1 0.0 276404 2204 ? Ss 15:30 0:00 \_ postgres: wal receiver process streaming 0/3018570
echo "select * from test;" | /engine/pg10.20/bin/psql -U app -p 5410 -d test
#######
11.15
#######
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg11.15/bin/psql -p 5411 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=replica
max_wal_senders = 3
wal_keep_segments = 100 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5411 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- recovery.conf 파일 생성 ($PGDATA)
echo "
standby_mode='on'
primary_conninfo='host=192.168.56.116 port=5411 user=repl password=repl1122'
#primary_slot_name='repl_slot_01'
trigger_file='/data/pg11.15/failover_trigger'" >> $PGDATA/recovery.conf
stanby start...
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg11.15/bin/psql -p 5411 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------
1975 | 16384 | repl | walreceiver | 192.168.56.117 | | 53564 | 2022-04-11 15:42:04.211714+09 | 570 | streaming | 0/3000060 | 0/3000060 | 0/3000060 | 0/3000060 | | | | 0 | async
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115548 2148 pts/0 S 15:26 0:00 -bash
postgres 1980 0.0 0.0 155448 1844 pts/0 R+ 15:42 0:00 \_ ps -u postgres uf
postgres 1962 0.0 0.3 272196 13148 pts/0 S 15:40 0:00 /engine/pg11.15/bin/postgres -D /data/pg11.15
postgres 1963 0.0 0.0 125008 880 ? Ss 15:40 0:00 \_ postgres: logger
postgres 1965 0.0 0.0 272308 2384 ? Ss 15:40 0:00 \_ postgres: checkpointer
postgres 1966 0.0 0.0 272196 2140 ? Ss 15:40 0:00 \_ postgres: background writer
postgres 1967 0.0 0.1 272196 5040 ? Ss 15:40 0:00 \_ postgres: walwriter
postgres 1968 0.0 0.0 272608 2048 ? Ss 15:40 0:00 \_ postgres: autovacuum launcher
postgres 1969 0.0 0.0 127272 1092 ? Ss 15:40 0:00 \_ postgres: stats collector
postgres 1970 0.0 0.0 272608 1588 ? Ss 15:40 0:00 \_ postgres: logical replication launcher
postgres 1975 0.0 0.0 272776 2548 ? Ss 15:42 0:00 \_ postgres: walsender repl 192.168.56.117(53564) streaming 0/3000060
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115668 2176 pts/0 S 15:26 0:00 -bash
postgres 1770 0.0 0.0 155448 1840 pts/0 R+ 15:42 0:00 \_ ps -u postgres uf
postgres 1761 0.0 0.3 272196 13148 pts/0 S 15:42 0:00 /engine/pg11.15/bin/postgres -D /data/pg11.15
postgres 1762 0.0 0.0 125008 880 ? Ss 15:42 0:00 \_ postgres: logger
postgres 1763 0.0 0.0 272356 1672 ? Ss 15:42 0:00 \_ postgres: startup recovering 000000010000000000000003
postgres 1764 0.0 0.0 272196 848 ? Ss 15:42 0:00 \_ postgres: checkpointer
postgres 1765 0.0 0.0 272196 1372 ? Ss 15:42 0:00 \_ postgres: background writer
postgres 1766 0.0 0.0 127128 920 ? Ss 15:42 0:00 \_ postgres: stats collector
postgres 1767 0.2 0.0 276584 1852 ? Rs 15:42 0:00 \_ postgres: walreceiver streaming 0/3000060
-- logfile 확인
2022-04-11 15:42:03 KST [1763]: [1-1] user=,db=,remote=LOG: database system was interrupted; last known up at 2022-04-11 15:41:07 KST
2022-04-11 15:42:04 KST [1763]: [2-1] user=,db=,remote=LOG: entering standby mode
2022-04-11 15:42:04 KST [1763]: [3-1] user=,db=,remote=LOG: redo starts at 0/2000028
2022-04-11 15:42:04 KST [1763]: [4-1] user=,db=,remote=LOG: consistent recovery state reached at 0/2000130
2022-04-11 15:42:04 KST [1761]: [6-1] user=,db=,remote=LOG: database system is ready to accept read only connections
2022-04-11 15:42:04 KST [1767]: [1-1] user=,db=,remote=LOG: started streaming WAL from primary at 0/3000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg11.15/bin/psql -p 5411 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------------+-----------------+-----------------+---------------+------------
1975 | 16384 | repl | walreceiver | 192.168.56.117 | | 53564 | 2022-04-11 15:42:04.211714+09 | 576 | streaming | 0/3019140 | 0/3019140 | 0/3019140 | 0/3019140 | 00:00:00.000799 | 00:00:00.001503 | 00:00:00.001746 | 0 | async
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115664 2168 pts/0 S 15:26 0:00 -bash
postgres 1990 0.0 0.0 155448 1836 pts/0 R+ 15:45 0:00 \_ ps -u postgres uf
postgres 1962 0.0 0.3 272196 13148 pts/0 S 15:40 0:00 /engine/pg11.15/bin/postgres -D /data/pg11.15
postgres 1963 0.0 0.0 125008 880 ? Ss 15:40 0:00 \_ postgres: logger
postgres 1965 0.0 0.0 272308 2640 ? Ss 15:40 0:00 \_ postgres: checkpointer
postgres 1966 0.0 0.0 272196 2140 ? Ss 15:40 0:00 \_ postgres: background writer
postgres 1967 0.0 0.1 272196 5040 ? Ss 15:40 0:00 \_ postgres: walwriter
postgres 1968 0.0 0.0 272608 2048 ? Ss 15:40 0:00 \_ postgres: autovacuum launcher
postgres 1969 0.0 0.0 127272 1092 ? Ss 15:40 0:00 \_ postgres: stats collector
postgres 1970 0.0 0.0 272608 1588 ? Ss 15:40 0:00 \_ postgres: logical replication launcher
postgres 1975 0.0 0.0 272908 2548 ? Ss 15:42 0:00 \_ postgres: walsender repl 192.168.56.117(53564) streaming 0/3019140
standby
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115668 2176 pts/0 S 15:26 0:00 -bash
postgres 1774 0.0 0.0 155448 1840 pts/0 R+ 15:45 0:00 \_ ps -u postgres uf
postgres 1761 0.0 0.3 272196 13148 pts/0 S 15:42 0:00 /engine/pg11.15/bin/postgres -D /data/pg11.15
postgres 1762 0.0 0.0 125008 880 ? Ss 15:42 0:00 \_ postgres: logger
postgres 1763 0.0 0.0 272404 3560 ? Ss 15:42 0:00 \_ postgres: startup recovering 000000010000000000000003
postgres 1764 0.0 0.0 272196 848 ? Ss 15:42 0:00 \_ postgres: checkpointer
postgres 1765 0.0 0.0 272196 1900 ? Ss 15:42 0:00 \_ postgres: background writer
postgres 1766 0.0 0.0 127128 920 ? Ss 15:42 0:00 \_ postgres: stats collector
postgres 1767 0.1 0.0 276788 2184 ? Ss 15:42 0:00 \_ postgres: walreceiver streaming 0/3019140
echo "select * from test;" | /engine/pg11.15/bin/psql -U app -p 5411 -d test
#######
12.10
#######
standby 서버에서 recovery.conf 파일이 제거 되고, standby.signal 파일을 생성해 주어야 한다.
master서버에 대한 정보는 $PGDATA/postgresql.auto.conf 파일에 적어 넣으면 된다.
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg12.10/bin/psql -p 5412 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=replica
max_wal_senders = 3
wal_keep_segments = 100 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart or echo "select pg_reload_conf(); " | /engine/pg12.10/bin/psql -p 5412 -d postgres
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5412 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- postgresql.auto.conf 수정 ($PGDATA)
echo "
primary_conninfo='host=192.168.56.116 port=5412 user=repl password=repl1122' " >> $PGDATA/postgresql.auto.conf
-- standby.signal 파일 생성
touch $PGDATA/standby.signal
stanby start...
-- master info 조회
echo "
SELECT name, setting, category, short_desc, context, pending_restart
FROM pg_catalog.pg_settings
WHERE category IN('Write-Ahead Log / Archive Recovery','Write-Ahead Log / Recovery Target')
OR name IN ('primary_conninfo','primary_slot_name','promote_trigger_file','recovery_min_apply_delay')
ORDER BY category, name; " | /engine/pg12.10/bin/psql -p 5412 -d postgres
name | setting | category | short_desc | context | pending_restart
---------------------------+-----------------------------------------------------------+------------------------------------+---------------------------------------------------------------------------------+------------+-----------------
primary_conninfo | host=192.168.56.116 port=5412 user=repl password=repl1122 | Replication / Standby Servers | Sets the connection string to be used to connect to the sending server. | postmaster | f
primary_slot_name | | Replication / Standby Servers | Sets the name of the replication slot to use on the sending server. | postmaster | f
promote_trigger_file | | Replication / Standby Servers | Specifies a file name whose presence ends recovery in the standby. | sighup | f
recovery_min_apply_delay | 0 | Replication / Standby Servers | Sets the minimum delay for applying changes during recovery. | sighup | f
archive_cleanup_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed at every restart point. | sighup | f
recovery_end_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed once at the end of recovery. | sighup | f
restore_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will retrieve an archived WAL file. | postmaster | f
recovery_target | | Write-Ahead Log / Recovery Target | Set to "immediate" to end recovery as soon as a consistent state is reached. | postmaster | f
recovery_target_action | pause | Write-Ahead Log / Recovery Target | Sets the action to perform upon reaching the recovery target. | postmaster | f
recovery_target_inclusive | on | Write-Ahead Log / Recovery Target | Sets whether to include or exclude transaction with recovery target. | postmaster | f
recovery_target_lsn | | Write-Ahead Log / Recovery Target | Sets the LSN of the write-ahead log location up to which recovery will proceed. | postmaster | f
recovery_target_name | | Write-Ahead Log / Recovery Target | Sets the named restore point up to which recovery will proceed. | postmaster | f
recovery_target_time | | Write-Ahead Log / Recovery Target | Sets the time stamp up to which recovery will proceed. | postmaster | f
recovery_target_timeline | latest | Write-Ahead Log / Recovery Target | Specifies the timeline to recover into. | postmaster | f
recovery_target_xid | | Write-Ahead Log / Recovery Target | Sets the transaction ID up to which recovery will proceed. | postmaster | f
(15 rows)
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg12.10/bin/psql -p 5412 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
2058 | 16384 | repl | walreceiver | 192.168.56.117 | | 40562 | 2022-04-11 16:04:30.913879+09 | 488 | streaming | 0/3000148 | 0/3000148 | 0/3000148 | 0/3000148 | | | | 0 | async | 2022-04-11 16:08:31.454985+09
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115664 2168 pts/0 S 15:26 0:00 -bash
postgres 2071 0.0 0.0 155448 1840 pts/0 R+ 16:08 0:00 \_ ps -u postgres uf
postgres 2018 0.0 0.3 272436 13336 ? Ss 15:50 0:00 /engine/pg12.10/bin/postgres -D /data/pg12.10
postgres 2019 0.0 0.0 125024 880 ? Ss 15:50 0:00 \_ postgres: logger
postgres 2021 0.0 0.0 272548 2384 ? Ss 15:50 0:00 \_ postgres: checkpointer
postgres 2022 0.0 0.0 272436 2116 ? Ss 15:50 0:00 \_ postgres: background writer
postgres 2023 0.0 0.1 272436 5016 ? Ss 15:50 0:00 \_ postgres: walwriter
postgres 2024 0.0 0.0 272980 2080 ? Ss 15:50 0:00 \_ postgres: autovacuum launcher
postgres 2025 0.0 0.0 127288 1084 ? Ss 15:50 0:00 \_ postgres: stats collector
postgres 2026 0.0 0.0 272984 1576 ? Ss 15:50 0:00 \_ postgres: logical replication launcher
postgres 2058 0.0 0.0 273148 2580 ? Ss 16:04 0:00 \_ postgres: walsender repl 192.168.56.117(40562) streaming 0/3000148
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115668 2176 pts/0 S 15:26 0:00 -bash
postgres 1847 0.0 0.0 155448 1840 pts/0 R+ 16:08 0:00 \_ ps -u postgres uf
postgres 1831 0.0 0.3 272436 13332 ? Ss 16:04 0:00 /engine/pg12.10/bin/postgres -D /data/pg12.10
postgres 1832 0.0 0.0 125024 880 ? Ss 16:04 0:00 \_ postgres: logger
postgres 1833 0.0 0.0 272596 1644 ? Ss 16:04 0:00 \_ postgres: startup recovering 000000010000000000000003
postgres 1834 0.0 0.0 272436 836 ? Ss 16:04 0:00 \_ postgres: checkpointer
postgres 1835 0.0 0.0 272436 1884 ? Ss 16:04 0:00 \_ postgres: background writer
postgres 1836 0.0 0.0 127144 940 ? Ss 16:04 0:00 \_ postgres: stats collector
postgres 1837 0.1 0.0 276836 1872 ? Ss 16:04 0:00 \_ postgres: walreceiver streaming 0/3000148
-- logfile 확인
2022-04-11 16:04:30 KST [1833]: [1-1] user=,db=,remote=LOG: database system was interrupted; last known up at 2022-04-11 15:51:21 KST
2022-04-11 16:04:30 KST [1833]: [2-1] user=,db=,remote=LOG: entering standby mode
2022-04-11 16:04:30 KST [1833]: [3-1] user=,db=,remote=LOG: redo starts at 0/2000060
2022-04-11 16:04:30 KST [1833]: [4-1] user=,db=,remote=LOG: consistent recovery state reached at 0/2000138
2022-04-11 16:04:30 KST [1831]: [7-1] user=,db=,remote=LOG: database system is ready to accept read only connections
2022-04-11 16:04:30 KST [1837]: [1-1] user=,db=,remote=LOG: started streaming WAL from primary at 0/3000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg12.10/bin/psql -p 5412 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
2058 | 16384 | repl | walreceiver | 192.168.56.117 | | 40562 | 2022-04-11 16:04:30.913879+09 | 494 | streaming | 0/3014FF0 | 0/3014FF0 | 0/3014FF0 | 0/3014FF0 | | | | 0 | async | 2022-04-11 16:11:58.716019+09
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115664 2176 pts/0 S 15:26 0:00 -bash
postgres 2088 0.0 0.0 155448 1844 pts/0 R+ 16:12 0:00 \_ ps -u postgres uf
postgres 2018 0.0 0.3 272436 13336 ? Ss 15:50 0:00 /engine/pg12.10/bin/postgres -D /data/pg12.10
postgres 2019 0.0 0.0 125024 880 ? Ss 15:50 0:00 \_ postgres: logger
postgres 2021 0.0 0.0 272548 2636 ? Ss 15:50 0:00 \_ postgres: checkpointer
postgres 2022 0.0 0.0 272436 2116 ? Ss 15:50 0:00 \_ postgres: background writer
postgres 2023 0.0 0.1 272436 5016 ? Ss 15:50 0:00 \_ postgres: walwriter
postgres 2024 0.0 0.0 273112 2112 ? Ss 15:50 0:00 \_ postgres: autovacuum launcher
postgres 2025 0.0 0.0 127288 1112 ? Ss 15:50 0:00 \_ postgres: stats collector
postgres 2026 0.0 0.0 272984 1576 ? Ss 15:50 0:00 \_ postgres: logical replication launcher
postgres 2058 0.0 0.0 273280 2580 ? Ss 16:04 0:00 \_ postgres: walsender repl 192.168.56.117(40562) streaming 0/3014FF0
standby
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115668 2176 pts/0 S 15:26 0:00 -bash
postgres 1852 0.0 0.0 155448 1840 pts/0 R+ 16:12 0:00 \_ ps -u postgres uf
postgres 1831 0.0 0.3 272436 13332 ? Ss 16:04 0:00 /engine/pg12.10/bin/postgres -D /data/pg12.10
postgres 1832 0.0 0.0 125024 880 ? Ss 16:04 0:00 \_ postgres: logger
postgres 1833 0.0 0.0 272644 3512 ? Ss 16:04 0:00 \_ postgres: startup recovering 000000010000000000000003
postgres 1834 0.0 0.0 272436 2136 ? Ss 16:04 0:00 \_ postgres: checkpointer
postgres 1835 0.0 0.0 272436 1884 ? Ss 16:04 0:00 \_ postgres: background writer
postgres 1836 0.0 0.0 127144 940 ? Ss 16:04 0:00 \_ postgres: stats collector
postgres 1837 0.1 0.0 277040 2188 ? Ss 16:04 0:00 \_ postgres: walreceiver streaming 0/3014FF0
echo "select * from test;" | /engine/pg12.10/bin/psql -U app -p 5412 -d test
#######
13.6
#######
master 서버에서 wal_keep_segments는 사용 안하게 됨.
standby 서버에서 recovery.conf 파일이 제거 되고, standby.signal 파일을 생성해 주어야 한다.
master서버에 대한 정보는 $PGDATA/postgresql.auto.conf 파일에 적어 넣으면 된다.
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg13.6/bin/psql -p 5413 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=replica
max_wal_senders = 3
max_replication_slots = 2 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart
or
echo "select pg_reload_conf(); " | /engine/pg13.6/bin/psql -p 5413 -d postgres
slot 생성
echo "select * from pg_create_physical_replication_slot('slot1') ; select * from pg_replication_slots ;" | /engine/pg13.6/bin/psql -p 5413 -d postgres
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5413 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- postgresql.auto.conf 수정 ($PGDATA)
echo "
primary_conninfo='host=192.168.56.116 port=5413 user=repl password=repl1122'
primary_slot_name='slot1'" >> $PGDATA/postgresql.auto.conf
-- standby.signal 파일 생성
touch $PGDATA/standby.signal
stanby start...
-- master info 조회
echo "
SELECT name, setting, category, short_desc, context, pending_restart
FROM pg_catalog.pg_settings
WHERE category IN('Write-Ahead Log / Archive Recovery','Write-Ahead Log / Recovery Target')
OR name IN ('primary_conninfo','primary_slot_name','promote_trigger_file','recovery_min_apply_delay')
ORDER BY category, name; " | /engine/pg13.6/bin/psql -p 5413 -d postgres
name | setting | category | short_desc | context | pending_restart
---------------------------+-----------------------------------------------------------+------------------------------------+---------------------------------------------------------------------------------+------------+-----------------
primary_conninfo | host=192.168.56.116 port=5413 user=repl password=repl1122 | Replication / Standby Servers | Sets the connection string to be used to connect to the sending server. | sighup | f
primary_slot_name | slot1 | Replication / Standby Servers | Sets the name of the replication slot to use on the sending server. | sighup | f
promote_trigger_file | | Replication / Standby Servers | Specifies a file name whose presence ends recovery in the standby. | sighup | f
recovery_min_apply_delay | 0 | Replication / Standby Servers | Sets the minimum delay for applying changes during recovery. | sighup | f
archive_cleanup_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed at every restart point. | sighup | f
recovery_end_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed once at the end of recovery. | sighup | f
restore_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be called to retrieve an archived WAL file. | postmaster | f
recovery_target | | Write-Ahead Log / Recovery Target | Set to "immediate" to end recovery as soon as a consistent state is reached. | postmaster | f
recovery_target_action | pause | Write-Ahead Log / Recovery Target | Sets the action to perform upon reaching the recovery target. | postmaster | f
recovery_target_inclusive | on | Write-Ahead Log / Recovery Target | Sets whether to include or exclude transaction with recovery target. | postmaster | f
recovery_target_lsn | | Write-Ahead Log / Recovery Target | Sets the LSN of the write-ahead log location up to which recovery will proceed. | postmaster | f
recovery_target_name | | Write-Ahead Log / Recovery Target | Sets the named restore point up to which recovery will proceed. | postmaster | f
recovery_target_time | | Write-Ahead Log / Recovery Target | Sets the time stamp up to which recovery will proceed. | postmaster | f
recovery_target_timeline | latest | Write-Ahead Log / Recovery Target | Specifies the timeline to recover into. | postmaster | f
recovery_target_xid | | Write-Ahead Log / Recovery Target | Sets the transaction ID up to which recovery will proceed. | postmaster | f
(15 rows)
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg13.6/bin/psql -p 5413 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------------+-----------------+-----------------+---------------+------------+-------------------------------
2176 | 16384 | repl | walreceiver | 192.168.56.117 | | 55360 | 2022-04-11 16:32:40.989917+09 | | streaming | 0/5000148 | 0/5000148 | 0/5000148 | 0/5000148 | 00:00:00.000844 | 00:00:00.001715 | 00:00:00.001802 | 0 | async | 2022-04-11 16:35:16.763246+09
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115664 2180 pts/0 S 15:26 0:00 -bash
postgres 2182 0.0 0.0 155448 1844 pts/0 R+ 16:35 0:00 \_ ps -u postgres uf
postgres 2157 0.0 0.3 272464 13336 ? Ss 16:29 0:00 /engine/pg13.6/bin/postgres -D /data/pg13.6
postgres 2158 0.0 0.0 125052 908 ? Ss 16:29 0:00 \_ postgres: logger
postgres 2160 0.0 0.0 272576 2672 ? Ss 16:29 0:00 \_ postgres: checkpointer
postgres 2161 0.0 0.0 272464 2128 ? Ss 16:29 0:00 \_ postgres: background writer
postgres 2162 0.0 0.1 272464 5024 ? Ss 16:29 0:00 \_ postgres: walwriter
postgres 2163 0.0 0.0 273000 2124 ? Ss 16:29 0:00 \_ postgres: autovacuum launcher
postgres 2164 0.0 0.0 127316 1100 ? Ss 16:29 0:00 \_ postgres: stats collector
postgres 2165 0.0 0.0 272896 1584 ? Ss 16:29 0:00 \_ postgres: logical replication launcher
postgres 2176 0.0 0.0 273152 2620 ? Ss 16:32 0:00 \_ postgres: walsender repl 192.168.56.117(55360) streaming 0/5000148
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115668 2180 pts/0 S 15:26 0:00 -bash
postgres 1912 0.0 0.0 155448 1840 pts/0 R+ 16:35 0:00 \_ ps -u postgres uf
postgres 1895 0.0 0.3 272464 13328 ? Ss 16:32 0:00 /engine/pg13.6/bin/postgres -D /data/pg13.6
postgres 1896 0.0 0.0 125052 900 ? Ss 16:32 0:00 \_ postgres: logger
postgres 1897 0.0 0.0 272624 1672 ? Ss 16:32 0:00 \_ postgres: startup recovering 000000010000000000000005
postgres 1898 0.0 0.0 272464 1092 ? Ss 16:32 0:00 \_ postgres: checkpointer
postgres 1899 0.0 0.0 272464 1888 ? Ss 16:32 0:00 \_ postgres: background writer
postgres 1900 0.0 0.0 127172 964 ? Ss 16:32 0:00 \_ postgres: stats collector
postgres 1901 0.1 0.0 276868 1888 ? Ss 16:32 0:00 \_ postgres: walreceiver streaming 0/5000148
-- logfile 확인
2022-04-11 16:32:40 KST [1895]: [3-1] user=,db=,remote=LOG: starting PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-04-11 16:32:40 KST [1895]: [4-1] user=,db=,remote=LOG: listening on IPv4 address "0.0.0.0", port 5413
2022-04-11 16:32:40 KST [1895]: [5-1] user=,db=,remote=LOG: listening on IPv6 address "::", port 5413
2022-04-11 16:32:40 KST [1895]: [6-1] user=,db=,remote=LOG: listening on Unix socket "/tmp/.s.PGSQL.5413"
2022-04-11 16:32:40 KST [1897]: [1-1] user=,db=,remote=LOG: database system was interrupted; last known up at 2022-04-11 16:30:16 KST
2022-04-11 16:32:41 KST [1897]: [2-1] user=,db=,remote=LOG: entering standby mode
2022-04-11 16:32:41 KST [1897]: [3-1] user=,db=,remote=LOG: redo starts at 0/4000028
2022-04-11 16:32:41 KST [1897]: [4-1] user=,db=,remote=LOG: consistent recovery state reached at 0/4000138
2022-04-11 16:32:41 KST [1895]: [7-1] user=,db=,remote=LOG: database system is ready to accept read only connections
2022-04-11 16:32:41 KST [1901]: [1-1] user=,db=,remote=LOG: started streaming WAL from primary at 0/5000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg13.6/bin/psql -p 5413 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------------+-----------------+-----------------+---------------+------------+-------------------------------
2176 | 16384 | repl | walreceiver | 192.168.56.117 | | 55360 | 2022-04-11 16:32:40.989917+09 | | streaming | 0/5013D08 | 0/5013D08 | 0/5013D08 | 0/5013D08 | 00:00:00.001912 | 00:00:00.004985 | 00:00:00.004992 | 0 | async | 2022-04-11 16:37:52.923078+09
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115664 2180 pts/0 S 15:26 0:00 -bash
postgres 2196 0.0 0.0 155448 1836 pts/0 R+ 16:37 0:00 \_ ps -u postgres uf
postgres 2157 0.0 0.3 272464 13336 ? Ss 16:29 0:00 /engine/pg13.6/bin/postgres -D /data/pg13.6
postgres 2158 0.0 0.0 125052 908 ? Ss 16:29 0:00 \_ postgres: logger
postgres 2160 0.0 0.0 272576 2672 ? Ss 16:29 0:00 \_ postgres: checkpointer
postgres 2161 0.0 0.0 272464 2128 ? Ss 16:29 0:00 \_ postgres: background writer
postgres 2162 0.0 0.1 272464 5024 ? Ss 16:29 0:00 \_ postgres: walwriter
postgres 2163 0.0 0.0 273132 2156 ? Ss 16:29 0:00 \_ postgres: autovacuum launcher
postgres 2164 0.0 0.0 127316 1128 ? Ss 16:29 0:00 \_ postgres: stats collector
postgres 2165 0.0 0.0 272896 1584 ? Ss 16:29 0:00 \_ postgres: logical replication launcher
postgres 2176 0.0 0.0 273468 2880 ? Ss 16:32 0:00 \_ postgres: walsender repl 192.168.56.117(55360) streaming 0/5013D08
standby
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115668 2180 pts/0 S 15:26 0:00 -bash
postgres 1915 0.0 0.0 155448 1840 pts/0 R+ 16:37 0:00 \_ ps -u postgres uf
postgres 1895 0.0 0.3 272464 13328 ? Ss 16:32 0:00 /engine/pg13.6/bin/postgres -D /data/pg13.6
postgres 1896 0.0 0.0 125052 900 ? Ss 16:32 0:00 \_ postgres: logger
postgres 1897 0.0 0.0 272672 3524 ? Ss 16:32 0:00 \_ postgres: startup recovering 000000010000000000000005
postgres 1898 0.0 0.0 272464 2644 ? Ss 16:32 0:00 \_ postgres: checkpointer
postgres 1899 0.0 0.0 272464 1888 ? Ss 16:32 0:00 \_ postgres: background writer
postgres 1900 0.0 0.0 127172 964 ? Ss 16:32 0:00 \_ postgres: stats collector
postgres 1901 0.1 0.0 277076 2204 ? Ss 16:32 0:00 \_ postgres: walreceiver streaming 0/5013D08
echo "select * from test;" | /engine/pg13.6/bin/psql -U app -p 5413 -d test
#######
14.2
#######
master 서버에서 wal_keep_segments는 사용 안하게 됨.
standby 서버에서 recovery.conf 파일이 제거 되고, standby.signal 파일을 생성해 주어야 한다.
master서버에 대한 정보는 $PGDATA/postgresql.auto.conf 파일에 적어 넣으면 된다.
master
replication user
echo "create user repl with replication password 'repl1122' ; select * from pg_user ;" | /engine/pg14.2/bin/psql -p 5414 -d postgres
postgresql.conf
echo "
# Replication setting
wal_level=replica
max_wal_senders = 3
max_replication_slots = 2 " >> $PGDATA/postgresql.conf
pg_hba.conf
echo "
# replication setting
host replication repl 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf
pg restart
or
echo "select pg_reload_conf(); " | /engine/pg14.2/bin/psql -p 5414 -d postgres
slot 생성
echo "select * from pg_create_physical_replication_slot('slot1') ; select * from pg_replication_slots ;" | /engine/pg14.2/bin/psql -p 5414 -d postgres
standby
-- datadirectory 지움
cd $PGDATA
rm -rf *
-- basebackup
pg_basebackup -h 192.168.56.116 -p 5414 -D $PGDATA -U repl -P -v -X stream
-- postgresql.conf 수정
echo "
## Replication setting
hot_standby = on
hot_standby_feedback = on" >> $PGDATA/postgresql.conf
-- postgresql.auto.conf 수정 ($PGDATA)
echo "
primary_conninfo='host=192.168.56.116 port=5414 user=repl password=repl1122'
primary_slot_name='slot1'" >> $PGDATA/postgresql.auto.conf
-- standby.signal 파일 생성
touch $PGDATA/standby.signal
stanby start...
-- master info 조회
echo "
SELECT name, setting, category, short_desc, context, pending_restart
FROM pg_catalog.pg_settings
WHERE category IN('Write-Ahead Log / Archive Recovery','Write-Ahead Log / Recovery Target')
OR name IN ('primary_conninfo','primary_slot_name','promote_trigger_file','recovery_min_apply_delay')
ORDER BY category, name; " | /engine/pg14.2/bin/psql -p 5414 -d postgres
name | setting | category | short_desc | context | pending_restart
---------------------------+-----------------------------------------------------------+------------------------------------+---------------------------------------------------------------------------------+------------+-----------------
primary_conninfo | host=192.168.56.116 port=5414 user=repl password=repl1122 | Replication / Standby Servers | Sets the connection string to be used to connect to the sending server. | sighup | f
primary_slot_name | slot1 | Replication / Standby Servers | Sets the name of the replication slot to use on the sending server. | sighup | f
promote_trigger_file | | Replication / Standby Servers | Specifies a file name whose presence ends recovery in the standby. | sighup | f
recovery_min_apply_delay | 0 | Replication / Standby Servers | Sets the minimum delay for applying changes during recovery. | sighup | f
archive_cleanup_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed at every restart point. | sighup | f
recovery_end_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be executed once at the end of recovery. | sighup | f
restore_command | | Write-Ahead Log / Archive Recovery | Sets the shell command that will be called to retrieve an archived WAL file. | sighup | f
recovery_target | | Write-Ahead Log / Recovery Target | Set to "immediate" to end recovery as soon as a consistent state is reached. | postmaster | f
recovery_target_action | pause | Write-Ahead Log / Recovery Target | Sets the action to perform upon reaching the recovery target. | postmaster | f
recovery_target_inclusive | on | Write-Ahead Log / Recovery Target | Sets whether to include or exclude transaction with recovery target. | postmaster | f
recovery_target_lsn | | Write-Ahead Log / Recovery Target | Sets the LSN of the write-ahead log location up to which recovery will proceed. | postmaster | f
recovery_target_name | | Write-Ahead Log / Recovery Target | Sets the named restore point up to which recovery will proceed. | postmaster | f
recovery_target_time | | Write-Ahead Log / Recovery Target | Sets the time stamp up to which recovery will proceed. | postmaster | f
recovery_target_timeline | latest | Write-Ahead Log / Recovery Target | Specifies the timeline to recover into. | postmaster | f
recovery_target_xid | | Write-Ahead Log / Recovery Target | Sets the transaction ID up to which recovery will proceed. | postmaster | f
(15 rows)
master
-- 구성상태 확인 (location 값은 계속 변경되므로 쿼리나 ps를 수행하는 시점에 따라 동일하지 않을 수 있다.)
echo "select * from pg_stat_replication ;" | /engine/pg14.2/bin/psql -p 5414 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
2227 | 16384 | repl | walreceiver | 192.168.56.117 | | 43686 | 2022-04-11 16:44:34.124914+09 | | streaming | 0/3000060 | 0/3000060 | 0/3000060 | 0/3000060 | | | | 0 | async | 2022-04-11 16:47:15.071001+09
(1 row)
-- process 확인 (wal sender)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115664 2180 pts/0 S 15:26 0:00 -bash
postgres 2237 0.0 0.0 155448 1844 pts/0 R+ 16:47 0:00 \_ ps -u postgres uf
postgres 2208 0.0 0.3 272612 13108 ? Ss 16:42 0:00 /engine/pg14.2/bin/postgres -D /data/pg14.2
postgres 2209 0.0 0.0 125436 924 ? Ss 16:42 0:00 \_ postgres: logger
postgres 2211 0.0 0.0 272728 2844 ? Ss 16:42 0:00 \_ postgres: checkpointer
postgres 2212 0.0 0.0 272612 2132 ? Ss 16:42 0:00 \_ postgres: background writer
postgres 2213 0.0 0.1 272612 5288 ? Ss 16:42 0:00 \_ postgres: walwriter
postgres 2214 0.0 0.0 273160 2172 ? Ss 16:42 0:00 \_ postgres: autovacuum launcher
postgres 2215 0.0 0.0 127688 1136 ? Ss 16:42 0:00 \_ postgres: stats collector
postgres 2216 0.0 0.0 273048 1580 ? Ss 16:42 0:00 \_ postgres: logical replication launcher
postgres 2227 0.0 0.0 273300 2628 ? Ss 16:44 0:00 \_ postgres: walsender repl 192.168.56.117(43686) streaming 0/3000060
standby
-- process 확인 (wal receiver)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115668 2184 pts/0 S 15:26 0:00 -bash
postgres 1946 0.0 0.0 155448 1844 pts/0 R+ 16:47 0:00 \_ ps -u postgres uf
postgres 1933 0.0 0.3 272612 13108 ? Ss 16:44 0:00 /engine/pg14.2/bin/postgres -D /data/pg14.2
postgres 1934 0.0 0.0 125436 924 ? Ss 16:44 0:00 \_ postgres: logger
postgres 1935 0.0 0.0 272776 1716 ? Ss 16:44 0:00 \_ postgres: startup recovering 000000010000000000000003
postgres 1936 0.0 0.0 272612 1092 ? Ss 16:44 0:00 \_ postgres: checkpointer
postgres 1937 0.0 0.0 272612 1896 ? Ss 16:44 0:00 \_ postgres: background writer
postgres 1938 0.0 0.0 127696 1000 ? Ss 16:44 0:00 \_ postgres: stats collector
postgres 1939 0.1 0.0 277024 1932 ? Ss 16:44 0:00 \_ postgres: walreceiver streaming 0/3000060
-- logfile 확인
2022-04-11 16:44:33 KST [1933]: [3-1] user=,db=,remote=LOG: starting PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-04-11 16:44:33 KST [1933]: [4-1] user=,db=,remote=LOG: listening on IPv4 address "0.0.0.0", port 5414
2022-04-11 16:44:33 KST [1933]: [5-1] user=,db=,remote=LOG: listening on IPv6 address "::", port 5414
2022-04-11 16:44:33 KST [1933]: [6-1] user=,db=,remote=LOG: listening on Unix socket "/tmp/.s.PGSQL.5414"
2022-04-11 16:44:33 KST [1935]: [1-1] user=,db=,remote=LOG: database system was interrupted; last known up at 2022-04-11 16:43:51 KST
2022-04-11 16:44:34 KST [1935]: [2-1] user=,db=,remote=LOG: entering standby mode
2022-04-11 16:44:34 KST [1935]: [3-1] user=,db=,remote=LOG: redo starts at 0/2000028
2022-04-11 16:44:34 KST [1935]: [4-1] user=,db=,remote=LOG: consistent recovery state reached at 0/2000138
2022-04-11 16:44:34 KST [1933]: [7-1] user=,db=,remote=LOG: database system is ready to accept read-only connections
2022-04-11 16:44:34 KST [1939]: [1-1] user=,db=,remote=LOG: started streaming WAL from primary at 0/3000000 on timeline 1
replication test
master
create database test with encoding='UTF8' template=template0 lc_collate='C' lc_ctype='en_US.UTF-8' ;
create user app password 'app1122' ;
grant connect on database test to app ;
grant all privileges on database test to app ;
\c test app
create table test ( id int ) ;
insert into test values (1) ;
select * from test ;
\q
echo "select * from pg_stat_replication ;" | /engine/pg14.2/bin/psql -p 5414 -d postgres
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+---------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
2227 | 16384 | repl | walreceiver | 192.168.56.117 | | 43686 | 2022-04-11 16:44:34.124914+09 | | streaming | 0/301AB38 | 0/301AB38 | 0/301AB38 | 0/301AB38 | | | | 0 | async | 2022-04-11 16:49:28.655154+09
(1 row)
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1852 0.0 0.0 115664 2184 pts/0 S 15:26 0:00 -bash
postgres 2255 0.0 0.0 155448 1840 pts/0 R+ 16:49 0:00 \_ ps -u postgres uf
postgres 2208 0.0 0.3 272612 13108 ? Ss 16:42 0:00 /engine/pg14.2/bin/postgres -D /data/pg14.2
postgres 2209 0.0 0.0 125436 924 ? Ss 16:42 0:00 \_ postgres: logger
postgres 2211 0.0 0.0 272728 2844 ? Ss 16:42 0:00 \_ postgres: checkpointer
postgres 2212 0.0 0.0 272612 2132 ? Ss 16:42 0:00 \_ postgres: background writer
postgres 2213 0.0 0.1 272612 5288 ? Ss 16:42 0:00 \_ postgres: walwriter
postgres 2214 0.0 0.0 273160 2172 ? Ss 16:42 0:00 \_ postgres: autovacuum launcher
postgres 2215 0.0 0.0 127688 1136 ? Ss 16:42 0:00 \_ postgres: stats collector
postgres 2216 0.0 0.0 273048 1580 ? Ss 16:42 0:00 \_ postgres: logical replication launcher
postgres 2227 0.0 0.0 273644 2888 ? Ss 16:44 0:00 \_ postgres: walsender repl 192.168.56.117(43686) streaming 0/301AB38
standby
ps -u postgres uf
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
postgres 1691 0.0 0.0 115668 2184 pts/0 S 15:26 0:00 -bash
postgres 1950 0.0 0.0 155448 1840 pts/0 R+ 16:49 0:00 \_ ps -u postgres uf
postgres 1933 0.0 0.3 272612 13108 ? Ss 16:44 0:00 /engine/pg14.2/bin/postgres -D /data/pg14.2
postgres 1934 0.0 0.0 125436 924 ? Ss 16:44 0:00 \_ postgres: logger
postgres 1935 0.0 0.0 272824 3572 ? Ss 16:44 0:00 \_ postgres: startup recovering 000000010000000000000003
postgres 1936 0.0 0.0 272612 2404 ? Ss 16:44 0:00 \_ postgres: checkpointer
postgres 1937 0.0 0.0 272612 1896 ? Ss 16:44 0:00 \_ postgres: background writer
postgres 1938 0.0 0.0 127696 1000 ? Ss 16:44 0:00 \_ postgres: stats collector
postgres 1939 0.1 0.0 277232 2184 ? Ss 16:44 0:00 \_ postgres: walreceiver streaming 0/301AB38
echo "select * from test;" | /engine/pg14.2/bin/psql -U app -p 5414 -d test
분류 전체보기
- PostgreSQL Replication rntjd 2022.04.19
PostgreSQL Replication rntjd
2022. 4. 19. 13:25
728x90
반응형
반응형