본문 바로가기
엑셈 경쟁력/DB 인사이드

DB 인사이드 | PostgreSQL Replication - Function

by EXEM 2023. 6. 29.

 

 

이번 문서에서는 PostgreSQL Replication과 관련된 Function에 대해 알아보겠습니다.

 

Function List

WAL 관련 Function
pg_current_wal_lsn pg_current_wal_flush_lsn pg_current_wal_insert_lsn
pg_walfile_name pg_walfile_name_offset pg_switch_wal
pg_wal_lsn_diff    
Standby Server 관련 Function
pg_is_in_recovery pg_last_wal_receive_lsn pg_last_wal_replay_lsn
pg_last_xact_replay_timestamp pg_get_wal_replay_pause_state pg_is_wal_replay_paused
pg_wal_replay_pause pg_wal_replay_resume  
Replication 관련 Function
pg_create_physical_replication_slot pg_create_logical_replication_slot pg_drop_replication_slot
pg_copy_physical_replication_slot pg_copy_logical_replication_slot pg_logical_slot_get_changes
pg_logical_slot_peek_changes pg_logical_slot_get_binary_changes pg_logical_slot_peek_binary_changes
pg_stat_reset_replication_slot    

WAL 관련 Function

Function Name Description
pg_current_wal_lsn 현재 WAL 파일의 Write LSN를 반환
pg_current_wal_flush_lsn 현재 WAL Record flush LSN를 반환(디스크에 기록할 WAL LSN)
pg_current_wal_insert_lsn 현재 WAL Record에 입력할 LSN를 반환
pg_walfile_name LSN에 해당하는 WAL 파일의 이름을 반환
pg_walfile_name_offset LSN에 해당하는 WAL 파일의 이름과 Byte offset을 반환
pg_switch_wal WAL 파일 전환(Switch)
pg_wal_lsn_diff 두 WAL LSN의 차이(단위:Byte)

 

pg_current_wal_lsn, pg_currnt_wal_flush_lsn, pg_current_wal_insert_lsn

각 Function는 WAL 파일에 대한 LSN(Log Sequence Number)를 반환합니다.

변경사항

PostgreSQL 10 버전을 기준으로 Function 명이 변경되었습니다.

PostgreSQL 9.6 이하 PostgreSQL 10 이상
pg_current_xlog_location pg_current_wal_lsn
pg_current_xlog_flush_lsn pg_current_wal_flush_lsn
pg_current_xlog_insert_lsn pg_current_wal_insert_lsn
-- Main Server에서 수행
SELECT pg_current_wal_lsn() , 
       pg_current_wal_flush_lsn() ,
       pg_current_wal_insert_lsn() ;
 pg_current_wal_lsn | pg_current_wal_flush_lsn | pg_current_wal_insert_lsn
--------------------+--------------------------+---------------------------
 0/7AFD8000         | 0/7A3FC000               | 0/7B3D8038

Main Server에서 수행하면, 현재 WAL의 LSN을 반환합니다.

📢 트랜잭션이 발생하지 않을 경우 각 Function의 결과값은 동일할 수 있습니다.
-- Standby Server에서 수행
SELECT pg_current_wal_lsn() , 
       pg_current_wal_flush_lsn() ,
       pg_current_wal_insert_lsn() ;
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

Standby Server로 구성된 서버에서 수행하면, Function을 수행할 수 없다는 에러가 출력됩니다.

 

pg_walfile_name, pg_walfile_name_offset

WAL LSN에 해당하는 WAL 파일을 확인할 수 있습니다.

변경사항

PostgreSQL 10 버전을 기준으로 Function 명이 변경되었습니다.

PostgreSQL 9.6 이하 PostgreSQL 10 이상
pg_xlogfile_name pg_walfile_name
pg_xlogfile_name_offset pg_walfile_name_offset
-- Main Server에서 수행
SELECT pg_walfile_name('0/7B3D8038') , 
       pg_walfile_name_offset('0/7B3D8038') ;
     pg_walfile_name      |       pg_walfile_name_offset
--------------------------+------------------------------------
 00000001000000000000007B | (00000001000000000000007B,4030520)

위 예시의 0/7B3D8038 LSN의 WAL 파일 이름00000001000000000000007B이고, Byte offset4030520입니다.

-- Standby Server에서 수행
SELECT pg_walfile_name('0/7B3D8038') , 
       pg_walfile_name_offset('0/7B3D8038') ;
ERROR:  recovery is in progress
HINT:  pg_walfile_name() cannot be executed during recovery.

Standby Server로 구성된 서버에서 수행하면, Function을 수행할 수 없다는 에러가 출력됩니다.

 

pg_wal_lsn_diff

인자로 입력되는 두 WAL LSN의 차이를 계산합니다. 위치의 차이는 숫자로 출력되며, 두 WAL LSN 간의 크기 차이를 나타내며 단위는 Byte입니다.

변경사항

PostgreSQL 10 버전을 기준으로 Function 명이 변경되었습니다.

PostgreSQL 9.6 이하 PostgreSQL 10 이상
pg_xlog_location_diff pg_wal_lsn_diff
SELECT pg_wal_lsn_diff( '1/301351D0', '1/301329C8' ) ;
 pg_wal_lsn_diff
-----------------
           10248

위 예시에서 1/301351D0 WAL LSN과 1/301329C8 WAL LSN의 차이는 10248Byte(10MB)입니다. pg_wal_lsn_diff Function을 사용하지 않을 경우 아래와 같이 계산할 수 있습니다.

SELECT '1/301351D0'::pg_lsn - '1/301329C8'::pg_lsn ;
 ?column?
----------
    10248

 

pg_switch_wal

WAL 파일 전환(Switch)을 수행합니다. 아래 예시에서 2F WAL 파일 다음 순번인 30으로 Switching 합니다.

-- Main Server에서 수행
SELECT pg_switch_wal() ;
 pg_switch_wal
---------------
 0/2F090958
-- Standby Server에서 수행
SELECT pg_switch_wal() ;
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

Standby Server로 구성된 서버에서 수행하면, Function을 수행할 수 없다는 에러가 출력됩니다.


Standby Server 관련 Function

Function Name Description
pg_is_in_recovery PostgreSQL Server가 복구상태인지 확인
pg_last_wal_receive_lsn Standby Server가 마지막으로 수신한 WAL 파일의 LSN를 반환
pg_last_wal_replay_lsn Standby Server가 마지막으로 재생한 WAL 파일의 LSN를 반환
pg_last_xact_replay_timestamp Standby Server가 마지막으로 재생한 트랜잭션의 Timestamp를 반환
pg_get_wal_replay_pause_state 복구 일시 중지 상태 확인 (not paused / pause requested / paused)
pg_is_wal_replay_paused 복구 일시 중지 상태 확인
pg_wal_replay_pause 복구 일시중지 요청
pg_wal_replay_resume 복구 진행 요청

 

pg_is_in_recovery, pg_last_wal_receive_lsn, pg_last_wal_replay_lsn

pg_is_in_recovery Function은 PostgreSQL Server가 복구상태인지 아닌지를 출력합니다. Standby Server에서는 t로 조회되고, Main Server에서는 f로 조회됩니다. pg_last_wal_receive_lsn Function은 Standby Server가 마지막으로 수신한(Receive) WAL 파일의 위치를 반환합니다. pg_last_wal_replay_lsn Function은 Standby Server가 마지막으로 재생한(Replay) WAL LSN을 반환합니다.

변경사항

PostgreSQL 10 버전을 기준으로 Function 명이 변경되었습니다.

PostgreSQL 9.6 이하 PostgreSQL 10 이상
pg_last_xlog_receive_location pg_last_wal_receive_lsn
pg_last_xlog_replay_location pg_last_wal_replay_lsn
-- Standby Server에서 수행
SELECT pg_is_in_recovery() , 
       pg_last_wal_receive_lsn() ,
       pg_last_wal_replay_lsn() ;
 pg_is_in_recovery | pg_last_wal_receive_lsn | pg_last_wal_replay_lsn
-------------------+-------------------------+------------------------
 t                 | 1/B7FDCDA8              | 1/B7FDCDA8

위의 SQL을 수행한 Server는 pg_is_in_recoveryt로 확인되므로 Standby Server로 운영 중이며, 1/B7FDCDA8 WAL LSN 데이터까지 수신하고 재생(Replay) 하였음을 확인할 수 있습니다.

 

pg_last_xact_replay_timestamp

Standby Server가 마지막으로 재생한 트랜잭션의 Timestamp를 반환합니다.

-- Standby Server에서 수행
SELECT pg_last_xact_replay_timestamp() ;
 pg_last_xact_replay_timestamp
-------------------------------
 2023-05-22 11:56:47.375693+09

위 예시에서 Standby Server에서 재생한 마지막 트랜잭션의 Timestamp는 2023-05-22 11:56:47.375693+09이라는 것을 확인할 수 있습니다. 현재시간을 나타내는 now() Function와 차이를 통해 Replication 지연시간을 확인할 수 있습니다.

SELECT EXTRACT ( EPOCH FROM now() - pg_last_xact_replay_timestamp() ) AS delay ;
    delay
-------------
 1667.233977
📢 트랜잭션이 많이 발생하는 Database에서는 이 결과의 수치로 Replication 지연을 판단하기에는 좋을 수 있으나, 트랜잭션이 많이 발생하지 않는 Database에서는 트랜잭션의 Timestamp가 증가하지 않기 때문에 위의 수치가 계속 증가하기 때문에 Replication 지연을 판단하기에는 부적합합니다.

 

WAL 복구 상태조작 Function

  • pg_is_wal_replay_paused : WAL 복구 상태를 확인합니다.
  • pg_wal_replay_pause : WAL 복구를 일시중지합니다.
  • pg_wal_replay_resume : WAL 복구를 재개합니다.
  • pg_get_wal_replay_pause_state : WAL 복구 상태를 확인합니다.

변경사항

PostgreSQL 10 버전을 기준으로 Function 명이 변경되었습니다.

PostgreSQL 9.6 이하 PostgreSQL 10 이상
pg_is_xlog_replay_paused pg_is_wal_replay_paused
pg_xlog_replay_pause pg_wal_replay_pause
pg_xlog_replay_resume pg_wal_replay_resume

PostgreSQL 14 버전부터 pg_get_wal_replay_pause_state Function이 새로 생겼습니다.

WAL 복구 일시중지 상태 확인

현재 WAL 복구 일시중지 상태가 FALSE(not paused)입니다.

SELECT pg_is_wal_replay_paused() , 
       pg_get_wal_replay_pause_state() ;
 pg_is_wal_replay_paused | pg_get_wal_replay_pause_state
-------------------------+-------------------------------
 f                       | not paused

 

WAL 복구 일시중지

WAL 복구 일시중지를 하고 확인 시 상태가 TRUE(paused)인 것을 확인할 수 있습니다.

SELECT pg_wal_replay_pause() ;
 pg_wal_replay_pause
---------------------
-- WAL 복구상태 확인
SELECT pg_is_wal_replay_paused() , 
       pg_get_wal_replay_pause_state() ;
 pg_is_wal_replay_paused | pg_get_wal_replay_pause_state
-------------------------+-------------------------------
 t                       | paused

 

WAL 복구 재개

WAL 복구 재개를 하면 상태가 다시 FALSE(not paused)가 된 것을 확인할 수 있습니다.

SELECT pg_wal_replay_resume() ;
 pg_wal_replay_resume
----------------------
-- WAL 복구상태 확인
SELECT pg_is_wal_replay_paused() , 
       pg_get_wal_replay_pause_state() ;
 pg_is_wal_replay_paused | pg_get_wal_replay_pause_state
-------------------------+-------------------------------
 f                       | not paused

Replication Slot 관련 Function

Function Name Description
pg_create_physical_replication_slot Physical Replication Slot 생성 (Streaming Replication 사용)
pg_create_logical_replication_slot Logical Replication Slot 생성 (Logical Replication 사용)
pg_drop_replication_slot Replication Slot 삭제
pg_copy_physical_replication_slot [Since. v12] Physical Replication Slot을 복제
pg_copy_logical_replication_slot [Since. v12] Logical Replication Slot을 복제
pg_logical_slot_get_changes Logical Replication Slot에서 변경사항 출력(재조회불가)
pg_logical_slot_peek_changes Logical Replication Slot에서 변경사항 출력(재조회가능)
pg_logical_slot_get_binary_changes Logical Replication Slot에서 변경사항을 Binary로 출력(재조회불가)
pg_logical_slot_peek_binary_changes Logical Replication Slot에서 변경사항을 Binary로 출력(재조회가능)
pg_stat_reset_replication_slot [Since. v14] Replication Slot의 통계 초기화

 

Physical Replication Slot 생성/복제 Function

  • pg_create_physical_replication_slot : Physical Replication Slot을 생성합니다.
  • pg_copy_physical_replication_slot : Physical Replication Slot을 복제합니다.

Physical Replication Slot 생성 (pg_create_physical_replication_slot)

SELECT pg_create_physical_replication_slot( 'physical_slot' ) ;
 pg_create_physical_replication_slot
-------------------------------------
 (physical_slot,)

 

Physical Replication Slot 확인 (pg_replication_slots Catalog)

slot_type Column을 통하여 Slot이 Physical 인지 Logical 인지 확인할 수 있으며, 현재 Replication에 연결된 Standby Server가 없으므로 active Column이 FALSE인 것을 확인할 수 있습니다.

SELECT slot_name, plugin, slot_type, temporary, active, wal_status
FROM   pg_replication_slots ;
   slot_name   | plugin | slot_type | temporary | active | wal_status
---------------+--------+-----------+-----------+--------+------------
 physical_slot |        | physical  | f         | t      | reserved

 

Physical Replication Slot 복제 (pg_copy_physical_replication_slot)

PostgreSQL 12 버전에서 새로 추가된 pg_copy_physical_replication_slot은 Physical Type으로 생성된 Replication Slot을 복제합니다. 복제 대상 Slot이 예약된 상태인 경우에만 복제가 가능합니다.(pg_catalog.pg_replication_slots의 wal_status = reserved)

SELECT pg_copy_physical_replication_slot( 'physical_slot', 'pslot_copy' );
 pg_copy_physical_replication_slot
-----------------------------------
 (pslot_copy,)
SELECT slot_name, plugin, slot_type, temporary, active, wal_status 
FROM   pg_replication_slots ;
   slot_name   | plugin | slot_type | temporary | active | wal_status
---------------+--------+-----------+-----------+--------+------------
 physical_slot |        | physical  | f         | t      | reserved
 pslot_copy    |        | physical  | f         | f      | reserved

복제 대상 Slot이 예약된 상태가 아니거나 존재하지 않을 경우 Slot 복제는 실패합니다.

SELECT slot_name, plugin, slot_type, temporary, active, wal_status
FROM   pg_replication_slots ;
   slot_name    | plugin | slot_type | temporary | active | wal_status
----------------+--------+-----------+-----------+--------+------------
 physical_slot  |        | physical  | f         | f      |
-- 복제대상 Replication Slot이 없을 경우
SELECT pg_copy_physical_replication_slot( 'physical_slot', 'pslot_copy' );
ERROR:  replication slot "physical_slot" does not exist
-- 복제대상 Replication Slot의 wal_status가 reserved가 아닐 경우
SELECT pg_copy_physical_replication_slot( 'physical_slots', 'pslot_copy' );
ERROR:  cannot copy a replication slot that doesn't reserve WAL

 

Logical Replication Slot 생성/복제 Function

  • pg_create_logical_replication_slot : Logical Replication Slot을 생성합니다.
  • pg_copy_logical_replication_slot : Logical Replication Slot을 복제합니다.

Logical Replication Slot 생성 (pg_create_logical_replication_slot)

SELECT pg_create_logical_replication_slot( 'logical_slot', 'pgoutput' ) ;
 pg_create_logical_replication_slot
------------------------------------
 (logical_slot,4/55B61248)

 

Logical Replication Slot 확인 (pg_replication_slots Catalog)

slot_type Column을 통하여 Slot이 Physical 인지 Logical 인지 확인할 수 있으며, 현재 Replication에 연결된 Standby Server가 없으므로 active Column이 FALSE인 것을 확인할 수 있습니다.

SELECT slot_name, plugin, slot_type, temporary, active, wal_status 
FROM   pg_replication_slots ;
   slot_name   |  plugin  | slot_type | temporary | active | wal_status
---------------+----------+-----------+-----------+--------+------------
 logical_slot  | pgoutput | logical   | f         | f      | reserved

 

Logical Replication Slot 복제 (pg_copy_logical_replication_slot)

PostgreSQL 12 버전에서 새로 추가된 pg_copy_logical_replication_slot은 Logical Type으로 생성된 Replication Slot을 복제합니다.

SELECT pg_copy_logical_replication_slot( 'logical_slot', 'lslot_copy' );
 pg_copy_logical_replication_slot
----------------------------------
 (lslot_copy,4/55B612B8)
SELECT slot_name, plugin, slot_type, temporary, active, wal_status 
FROM   pg_replication_slots ;
   slot_name   |  plugin  | slot_type | temporary | active | wal_status
---------------+----------+-----------+-----------+--------+------------
 logical_slot  | pgoutput | logical   | f         | f      | reserved
 lslot_copy    | pgoutput | logical   | f         | f      | reserved

 

Logical Replication Slot 삭제 Function

  • pg_drop_replication_slot : Repliaction Slot을 삭제합니다.

Replication Slot 삭제

SELECT pg_drop_replication_slot( 'physical_slot' ) ;
 pg_drop_replication_slot
--------------------------

 

Replication Slot 삭제

SELECT pg_drop_replication_slot( 'logical_slot' ) ;
 pg_drop_replication_slot
--------------------------

 

Logical Decoding Data 확인 Function

  • pg_logical_slot_get_changes : Logical Replication에서 변경사항을 출력합니다.
  • pg_logical_slot_peek_changes : Logical Replication에서 변경사항을 출력합니다.(엿보기)
  • pg_logical_slot_get_binary_changes : Logical Replication에서 변경사항을 Binary 형태로 출력합니다.
  • pg_logical_slot_peek_binary_changes : Logical Replication에서 변경사항을 Binary 형태로 출력합니다.(엿보기)

pg_logical_slot_get_changes

Logical Replication Slot에서 변경사항을 출력하는 Function입니다.

-- Logical Replication 생성
SELECT pg_create_logical_replication_slot( 'logical_slots', 'test_decoding' ) ;
-- DDL 및 DML 수행
CREATE TABLE test1 ( c1 INTEGER ) ;
INSERT INTO test1 VALUES ( 1 ) ;
SELECT * FROM pg_logical_slot_get_changes( 'logical_slots', NULL, NULL ) ;
    lsn     | xid |                   data
------------+-----+-------------------------------------------
 4/55B8EC10 | 775 | BEGIN 775
 4/55B908C8 | 775 | COMMIT 775
 4/55B908C8 | 776 | BEGIN 776
 4/55B908C8 | 776 | table public.test1: INSERT: c1[integer]:1
 4/55B90938 | 776 | COMMIT 776

Logical Replication에서 DDL 작업에 관한 내용은 전달되지 않기 때문에 pg_logical_slot_get_changes 결과에서 작업내용은 출력되지 않고 트랜잭션 시작(BEGIN)과 끝(COMMIT)만 출력됩니다.(xid=775) DML 작업에 대해서는 변경사항을 확인할 수 있습니다.(xid=776) 위 예시에서 변경사항에 대한 내용을 사용(조회)하였기 때문에 트랜잭션이 발생하지 않는다는 상황에서 다시 Function을 수행하면 조회결과는 출력되지 않습니다.

SELECT * FROM pg_logical_slot_get_changes( 'logical_slots', NULL, NULL ) ;
 lsn | xid | data
-----+-----+------

 

pg_logical_slot_peek_changes

Logical Replication Slot에서 변경사항을 출력하는 Function으로 pg_logical_slot_get_changes Function과 동일한 기능이지만, pg_logical_slot_get_changes Function은 재조회가 불가능하고 pg_logical_slot_peek_changes 재조회가 가능하다는 차이점이 있습니다.

-- Logical Replication 생성
SELECT pg_create_logical_replication_slot( 'logical_slots', 'test_decoding' ) ;
-- DDL 및 DML 수행
CREATE TABLE test1 ( c1 INTEGER ) ;
INSERT INTO test1 VALUES ( 1 ) ;
SELECT * FROM pg_logical_slot_peek_changes( 'logical_slots', NULL, NULL ) ;
    lsn     | xid |                   data
------------+-----+-------------------------------------------
 4/55B91480 | 778 | BEGIN 778
 4/55B94260 | 778 | COMMIT 778
 4/55B94298 | 779 | BEGIN 779
 4/55B94298 | 779 | table public.test1: INSERT: c1[integer]:1
 4/55B94308 | 779 | COMMIT 779
-- 재조회
SELECT * FROM pg_logical_slot_peek_changes( 'logical_slots', NULL, NULL ) ;
    lsn     | xid |                   data
------------+-----+-------------------------------------------
 4/55B91480 | 778 | BEGIN 778
 4/55B94260 | 778 | COMMIT 778
 4/55B94298 | 779 | BEGIN 779
 4/55B94298 | 779 | table public.test1: INSERT: c1[integer]:1
 4/55B94308 | 779 | COMMIT 779
📢간단히 영어 단어로 확인할 수 있습니다. get은 가져오는 것으로 조회를 하면 소멸됩니다. peek는 엿보는 것으로 조회를 해도 소멸되지 않습니다.

 

pg_logical_slot_get_binary_changes, pg_logical_slot_peek_binary_changes

pg_logical_slot_get_changes, pg_logical_slot_peek_changes Function과 동일한 기능을 수행하지만, 출력결과를 Binary 형태로 출력합니다.

-- Logical Replication 생성
SELECT pg_create_logical_replication_slot( 'logical_slots', 'test_decoding' ) ;
-- DDL 및 DML 수행
CREATE TABLE test1 ( c1 INTEGER ) ;
INSERT INTO test1 VALUES ( 1 ) ;
SELECT * FROM pg_logical_slot_peek_binary_changes( 'logical_slots', NULL, NULL );
    lsn     | xid |                                          data
------------+-----+----------------------------------------------------------------------------------------
 4/55BC63C8 | 786 | \x424547494e20373836
 4/55BC9828 | 786 | \x434f4d4d495420373836
 4/55BC9860 | 787 | \x424547494e20373837
 4/55BC9860 | 787 | \x7461626c65207075626c69632e74657374313a20494e534552543a2063315b696e74656765725d3a3130
 4/55BC98D0 | 787 | \x434f4d4d495420373837
SELECT * FROM pg_logical_slot_get_binary_changes( 'logical_slots', NULL, NULL );
    lsn     | xid |                                          data
------------+-----+----------------------------------------------------------------------------------------
 4/55BC63C8 | 786 | \x424547494e20373836
 4/55BC9828 | 786 | \x434f4d4d495420373836
 4/55BC9860 | 787 | \x424547494e20373837
 4/55BC9860 | 787 | \x7461626c65207075626c69632e74657374313a20494e534552543a2063315b696e74656765725d3a3130
 4/55BC98D0 | 787 | \x434f4d4d495420373837

 

pg_stat_reset_replication_slot

Replication Slot 통계를 초기화할 수 있습니다. 이 Function은 PostgreSQL 14 버전에서 생겼습니다.

-- 현재 Replication Slot 통계확인
SELECT * FROM pg_stat_replication_slots ;
-[ RECORD 1 ]+------------------------------
slot_name    | logical_slots
spill_txns   | 0
spill_count  | 0
spill_bytes  | 0
stream_txns  | 0
stream_count | 0
stream_bytes | 0
total_txns   | 9
total_bytes  | 26410340
stats_reset  |

Replication Slot 명을 지정하여 Function을 수행하면 해당 Slot에 대해서만 초기화가 됩니다. 지정하지 않을 경우 모든 Replication Slot에 대해 초기화가 됩니다.

-- Replication Slot 통계 초기화
SELECT pg_stat_reset_replication_slot( 'logical_slots' ) ;
 pg_stat_reset_replication_slot
--------------------------------

Replication Slot의 통계정보는 모두 0으로 초기화되며 stats_reset Column에 초기화를 수행한 시간이 기록됩니다.

-- 초기화 후 Replication Slot 통계확인
SELECT * FROM pg_stat_replication_slots ;
-[ RECORD 1 ]+------------------------------
slot_name    | logical_slots
spill_txns   | 0
spill_count  | 0
spill_bytes  | 0
stream_txns  | 0
stream_count | 0
stream_bytes | 0
total_txns   | 0
total_bytes  | 0
stats_reset  | 2023-05-24 11:15:23.054164+09

 

 

 

 

기획 및 글 | DB기술기획팀

 

 

 

 

 

 

댓글