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

DB 인사이드 | PostgreSQL Replication - Catalog

by EXEM 2023. 6. 29.

 

 

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

 

Catalog Name Description
pg_stat_replication Replication에 대한 통계 확인
pg_stat_wal_receiver WAL Receiver에 대한 통계 확인
pg_replication_slots Replication Slot에 대한 정보 확인
pg_stat_replication_slots [Since. v14] Replication Slot에 대한 통계 확인
pg_publication Logical Replication의 Publication에 대한 정보 확인
pg_publication_rel Relation과 Publication간의 매핑정보 확인
pg_publication_tables Publication에 포함된 Table 확인
pg_subscription Subscription에 대한 정보 확인
pg_subscription_rel Subscription에 포함된 Table 확인
pg_stat_subscription_stats [Since. v15] Logical Replication 구독 오류에 대한 통계 확인

 

pg_stat_replication

WAL Sender 프로세스당 하나의 Row로 표시되며, WAL S프로세스에 연결된 Standby Server에 대한 정보 및 통계를 보여줍니다. WAL Sender에 연결된 Standby Server 개수만큼 표시되며, Down Stream Standby Server는 표시되지 않습니다. Streaming과 Logical로 구성된 Replication에 대해서만 표시됩니다.

변경사항

  • PostgreSQL 10 버전부터 *_location*_lsn으로 변경되었습니다.
  • PostgreSQL 10 버전부터 write_lag, flush_lag, replay_lag 컬럼이 추가되었습니다.
  • PostgreSQL 12 버전부터 replay_time 컬럼이 추가되었습니다.
SELECT * FROM pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 42435
usesysid         | 16384
usename          | replicauser
application_name | walreceiver
client_addr      | 10.10.45.237
client_hostname  |
client_port      | 57364
backend_start    | 2023-05-17 15:58:22.570344+09
backend_xmin     |
state            | streaming
sent_lsn         | 1/B8610A28
write_lsn        | 1/B8610A28
flush_lsn        | 1/B8610A28
replay_lsn       | 1/B8610A28
write_lag        | 00:00:00.047901
flush_lag        | 00:00:00.048666
replay_lag       | 00:00:00.092449
sync_priority    | 0
sync_state       | async
reply_time       | 2023-05-24 15:41:31.047546+09
Column Name Description
pid WAL Sender 프로세스 PID
usesysid WAL Sender 프로세스에 연결한 User OID
username WAL Sender 프로세스에 연결 User명
application_name WAL Sender에 연결된 Application 이름
client_addr WAL Sender에 연결된 Client IP.
NULL일 경우 Unix 소켓을 통해 연결됨을 의미
client_hostname WAL Sender에 연결된 Client Hostname(DNS lookup에 의해 조회될 경우 표시).
postgresql.conf의 log_hostname=off일 경우 NULL 표시
client_port WAL Sender에 연결된 Client의 TCP Port. (프로세스 조회에서도 확인가능 ps -ef)
Unix 소켓을 통한 연결은 -1
backend_start Client가 WAL Sender에 연결된 시간
backend_xmin Standby Server에서 hot_standby_feedback에 의해 보고된 xmin 
state WAL Sender의 상태
- startup : WAL Sender 시작
- catchup : Stadnby Server가 Main Server를 따라잡고 있는 중
- streadming : Standby Server가 Main Server의 변경사항을 스트리밍 중
- backup : WAL Sender가 Backup을 보내는 중.
- stopping : WAL Sender 중지 중
sent_lsn [Rename. v10] 전송된 마지막 WAL LSN
write_lsn [Rename. v10] Standby Server가 Disk 쓴 마지막 WAL LSN
flush_lsn [Rename. v10] Standby Server에 의해 Disk로 Flush 된 마지막 WAL LSN
replay_lsn [Rename. v10] Standby Server의 Database로 재생되는 마지막 WAL LSN
write_lag [Since. v10] Main Server에서 최근 WAL을 Flush하고 Standby Server가 기록했다는 알림을 받는 사이에 경과된 시간.
flush_lag [Since. v10] Main Server에서 최근 WAL을 Flush하고 Standby Server가 기록+Flush했다는 알림을 받는 사이에 경과된 시간.
replay_lag [Since. v10] Main Server에서 최근 WAL을 Flush하고 Standby Server가 기록+Flush+적용했다는 알림을 받는 사이에 경과된 시간.
sync_priority 우선 순위 기반 동기식 복제에서 동기식 대기로 선택하기 위한 Standby Server의 우선순위.
sync_state 동기화 상태. (async, potential, sync, quorum)
- async : Standby server는 비동기상태.
- potential : Standby Server는 비동기상태 이지만, 현재 동기 서버 중 하나가 실패할 경우 잠재적으로 동기화할 수 있음.
- sync : Standby Server는 동기식상태
- quorum : Standby Server는 quorum Standby 후보로 간주
reply_time [Since. v12] Standby Server에서 받은 마지막 응답 메시지의 전송시간
📢 WAL 파일의 변경사항이 없으면, write_lag, flush_lag, replay_lag는 NULL로 표시될 수 있습니다.

pg_stat_wal_receiver

현재 WAL Receiver에 대한 통계 정보를 나타냅니다. Receiver에 대한 Catalog로 Main Server에서는 조회되는 내용이 없으며, Standby Server에서 조회 가능합니다. Streaming Replication 구성에서만 조회 가능합니다.

변경사항

  • PostgreSQL 11 버전부터 sender_host, sender_port 컬럼이 추가되었습니다.
  • PostgreSQL 13 버전부터 received_lsnwritten_lsn, flushed_lsn으로 분리되었습니다.
SELECT * FROM pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 24021
status                | streaming
receive_start_lsn     | 0/2D000000
receive_start_tli     | 1
written_lsn           | 1/B861BBE8
flushed_lsn           | 1/B861BBE8
received_tli          | 1
last_msg_send_time    | 2023-05-24 15:43:09.883988+09
last_msg_receipt_time | 2023-05-24 15:43:09.883011+09
latest_end_lsn        | 1/B861BBE8
latest_end_time       | 2023-05-24 15:42:09.775377+09
slot_name             | physical_slot
sender_host           | 10.10.45.236
sender_port           | 5432
conninfo              | user=replicauser passfile=/var/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=10.10.45.236 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
Column Name Description
pid WAL Receiver 프로세스 PID
status WAL Receiver 프로세스 상태
receive_start_lsn WAL Receiver가 시작될때 사용되는 처음 WAL LSN
receive_start_tli WAL Receiver가 시작될때 사용되는 처음 Timeline 번호
written_lsn [Since. v13] 이미 수신되어 디스크에 Write된 마지막 WAL LSN(Flush는 제외)
flushed_lsn [Since. v13] 이미 수신되어 디스크에 Flush된 마지막 WAL LSN
received_lsn [Deprecated. v13] 이미 수신되어 디스크에 Flush된 마지막 WAL LSN
received_tli 디스크에 수신되고 Flush된 마지막 WAL LSN의 Timeline 번호
last_msg_sned_time 원본 WAL Sender로 부터 받은 마지막 메시지의 전송 시간
last_msg_receipt_time 원본 WAL Sender로 부터 받은 마지막 메시지의 수신 시간
latest_end_lsn 원본 WAL Sender에게 보고된 마지막 WAL LSN
latest_end_time 원본 WAL Sender에게 보고된 마지막 WAL LSN 시간
slot_name WAL Receiver에서 사용하는 Replication Slot 이름
sender_host [Since. v11] WAL Receiver가 연결한 WAL Sender의 Host. (hostname, IP, Directory Path 일 수 있음)
sender_port [Since. v11] WAL Receiver가 연결한 WAL Sender의 Port
conninfo WAL Sender에 연결하기 위한 연결정보

pg_replication_slots

현재 PostgreSQL에 존재하는 Replication Slot의 목록을 표시합니다.

변경사항

  • PostgreSQL 10 버전부터 temporary 컬럼이 추가되었습니다.
  • PostgreSQL 13 버전부터 wal_status, safe_wal_size 컬럼이 추가되었습니다.
  • PostgreSQL 14 버전부터 two_phase 컬럼이 추가되었습니다.
-- Physical Replication Slot
SELECT * FROM pg_replication_slots ;
-[ RECORD 1 ]-------+--------------
slot_name           | physical_slot
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 42435
xmin                | 799
catalog_xmin        |
restart_lsn         | 1/B861BCD0
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |
two_phase           | f
-- Logical Replication Slot
SELECT * FROM pg_replication_slots ;
-[ RECORD 1 ]-------+----------------
slot_name           | my_subscription
plugin              | pgoutput
slot_type           | logical
datoid              | 14486
database            | postgres
temporary           | f
active              | t
active_pid          | 13161
xmin                |
catalog_xmin        | 796
restart_lsn         | 4/5620E6A8
confirmed_flush_lsn | 4/5620E6E0
wal_status          | reserved
safe_wal_size       |
two_phase           | f
Column Name Description
slot_name Replication Slot의 고유 식별자(이름)
plugin Logical Replication일 경우, 출력 plugin을 포함하는 공유 개체의 이름.
Physical Replication일 경우에는 NULL
slot_type Slot Type이 Physical 인지 Logical 인지 구분
datoid Logical Replication일 경우 Slot에 연결된 Database OID.(pg_database.oid)
Physical Replicaiton 일 경우 NULL
database Logical Replication일 경우 Slot에 연결된 Database 이름.(pg_database.datname)
Physical Replicaiton 일 경우 NULL
temporary [Since. v10] Slot이 임시인지 아닌지 여부 (TRUE / FALSE)
active Slot 상태 (Active : TRUE / Inactive : FALSE)
active_pid Slot 상태가 Acive인 경우(active = TRUE) Slot을 사용하는 세션의 PID
xmin Slot이 유지되기 위해 Database가 필요한 가장 오래된 트랜잭션
catalog_xmin Slot이 유지되기 위해 Database를 필요로 하는 System Catalog에 영향을 미치는 가장 오래된 트랜잭션
restart_lsn Slot이 소비자에게 여전히 필요할 수 있는 가장 오래된 WAL의 주소(LSN)
confirmed_flush_lsn Logical Replication의 경우 Slot의 소비자가 데이터 수신을 확인한 주소(LSN)
Physical Replication의 경우 NULL
wal_status [Since. v13] Slot에서 요청한 WAL 파일의 사용가능여부
- reserved : 요청한 WAL 파일이 max_wal_size 내에 존재
- extended : max_wal_size는 초과하였지만, Replication Slot 또는 wal_keep_size에 의해 유지
- unreserved : WAL 파일을 보유하지 않고 다음 Checkpoint에서 제거해야 함을 의
- lost : WAL 파일이 제거되었으며, Slot을 더 이상 사용할 수 없음을 의미
- unreserved와 lost 상태는 max_slot_wal_keep_size가 음수가 이닌 경우에만 표시되며, restart_lsn = NULL 이면 NULL로 표시됩니다.
safe_wal_size [Since. v13] Slot이 “lost” 상태가 될 위험이 없도록 WAL에 기록할 수 있는 바이트 수.
lost 상태이거나 max_slot_wal_keep_size = -1 인 경우 NULL
two_phase [Since. v14] Logical Replication의 경우 준비된 트랜잭션을 디코딩하기 위한 Slot 활성화여부.
Physical Replication의 경우 항상 FALSE

pg_stat_replication_slots

Logical Replication Slot 사용에 대한 통계정보를 표시합니다.

변경사항

  • PostgreSQL 14 버전에 생긴 Catalog입니다.
SELECT * FROM pg_stat_replication_slots ;
-[ RECORD 1 ]+----------------
slot_name    | my_subscription
spill_txns   | 4
spill_count  | 259
spill_bytes  | 17303997849
stream_txns  | 0
stream_count | 0
stream_bytes | 0
total_txns   | 47
total_bytes  | 1644946476
stats_reset  |
Column Name Description
slot_name Replication Slot의 고유 식별자(이름)
spill_txns WAL의 변경사항을 디코딩하는데 논리적 디코딩에서 사용한 메모리가 logical_decoding_work_mem을 초과하여 디스크로 넘어간 트랜잭션의 수
spill_count WAL의 변경사항을 디코딩하는 동안 트랜잭션이 디스크에 넘어간 횟수
spill_bytes WAL의 변경사항을 디코딩하는 동안 트랜잭션이 디스크에 넘어간 디코딩된 트랜잭션 데이터의 양
stream_txns WAL의 변경사항을 디코딩하는데 논리적 디코딩에서 사용한 메모리가 logical_decoding_work_mem을 초과한 후 디코딩 출력 플러그인으로 스트리밍되는 진행 중인 트랜잭션 수
stream_count WAL의 변경사항을 디코딩하는 동안 트랜잭션이 디코딩 출력 플러그인으로 스트리밍된 횟수
stream_bytes WAL의 변경사항을 디코딩하는 동안 트랜잭션을 디코딩 출력 플러그인으로 스트리밍하기 위해 디코딩된 트랜잭션 데이터의 양
total_txns 디코딩 출력 플러그인으로 보낸 디코딩된 트랜잭션의 수
total_bytes WAL의 변경사항을 디코딩하는 동안 디코딩 출력 플러그인으로 트랜잭션을 전송하기 위해 디코딩된 트랜잭션 데이터의 양
stats_reset 통계정보가 마지막으로 재설정된 시간

pg_publication

PostgreSQL 10 버전부터 Logical Replication 기능이 도입되어 모니터링을 위한 pg_publication Catalog가 생성되었습니다. 게시자(Publisher)에 대한 정보를 포함합니다.

변경사항

  • PostgreSQL 11 버전부터 pubtruncate 컬럼이 추가되었습니다.
  • PostgreSQL 13 버전부터 pubviaroot 컬럼이 추가되었습니다.;
SELECT * FROM pg_publication ;
-[ RECORD 1 ]+---------------
oid          | 16411
pubname      | my_publication
pubowner     | 10
puballtables | f
pubinsert    | t
pubupdate    | t
pubdelete    | t
pubtruncate  | t
pubviaroot   | f
Column Name Description
oid Row Identifier
pubname Publication 이름
pubowner Publication 소유자 (pg_authid.oid)
puballtables Database의 모든 테이블을
- TRUE : 자동으로 Database의 모든 테이블을 포함. 이후에 작성될 테이블도 포함
- FALSE : Publication에 포함된 테이블을 지정
pubinsert Publication 테이블에 대해 INSERT 작업 복제 여부 (TRUE / FALSE)
pubupdate Publication 테이블에 대해 UPDATE 작업 복제 여부 (TRUE / FALSE)
pubdelete Publication 테이블에 대해 DELETE 작업 복제 여부 (TRUE / FALSE)
pubtruncate Publication 테이블에 대해 TRUNCATE 작업 복제 여부 (TRUE / FALSE)
pubviaroot If true, operations on a leaf partition are replicated using the identity and schema of its topmost partitioned ancestor mentioned in the publication instead of its own.

pg_publiacion_rel

Database와 Publication 간의 관계 정보가 포함되어 있습니다. 게시(Publication)에 포함된 테이블의 정보를 조회할 수 있지만, oid를 표시하므로 가독성은 떨어지므로 pg_publication_tables Catalog로 대체하여 조회할 수 있습니다. FOR ALL TABLES로 생성한 Publication은 pg_publication_rel Catalog에서 조회가 되지 않으며 pg_publication_tables Catalog를 통해 조회할 수 있습니다.

변경사항

  • PostgreSQL 12 버전부터 oid 컬럼이 추가되었습니다.
  • PostgreSQL 15 버전부터 prqual, prattrs 컬럼이 추가되었습니다.
SELECT * FROM pg_publication_rel ;
  oid  | prpubid | prrelid
-------+---------+---------
 16412 |   16411 |   16394
 16413 |   16411 |   16401
Column Name Description
oid Row Identifier
prpubid Publication 참조(pg_publication.oid)
prrelid Relation 참조(pg_class.oid)
prqual Expression tree (in nodeToString() representation) for the relation's publication qualifying condition. Null if there is no publication qualifying condition.
prattrs This is an array of values that indicates which table columns are part of the publication. For example, a value of 1 3 would mean that the first and the third table columns are published. A null value indicates that all columns are published.

pg_publication_tables

Database와 Publication 간의 관계 정보가 포함되어 있습니다. 게시(Publication)에 포함된 테이블의 정보를 조회할 수 있습니다. FOR ALL TABLES로 생성한 Publication은 pg_publication_rel Catalog에서 조회가 되지 않으며 pg_publication_tables Catalog를 통해 조회할 수 있습니다.

변경사항

  • PostgreSQL 15 버전부터 attnames, rowfilter 컬럼이 추가되었습니다.
SELECT * FROM pg_publication_tables ;
    pubname     | schemaname |      tablename
----------------+------------+----------------------
 my_publication | public     | replication_table_01
 my_publication | public     | replication_table_02
Column Name Description
pubname Publication 이름(pg_publication.pubname)
schemaname 테이블이 포함된 Schema 이름(pg_namespace.nspname)
tablename 테이블 이름(pg_class.relname)
attnames 게시(Publication)에 포함된 테이블 Column 이름(pg_attribute.attname). Publication 설정 시 Column을 지정하지 않은경우 테이블의 모든 Column이 포함
rowfilter 테이블의 게시 자격 조건에 대한 표현식(조건)

pg_subscription

pg_subscription Catalog는 Logical Replication의 구독(Subscription)에 대한 내용을 표시합니다.

  • PostgreSQL 12 버전부터 oid 컬럼이 추가되었습니다.
  • PostgreSQL 13 버전부터 subslotname 컬럼에 NULL이 가능합니다.
  • PostgreSQL 14 버전부터 subbinary, substream 컬럼이 추가되었습니다.
  • PostgreSQL 15 버전부터 subskiplsn, subtwophasestate, subdisableonerr 컬럼이 추가되었습니다.
  • PostgreSQL 16 버전부터 suborigin 컬럼이 추가되었습니다.
SELECT * FROM pg_subscription ;
-[ RECORD 1 ]---+----------------------------------------------------------------------------
oid             | 16425
subdbid         | 14486
subname         | my_subscription
subowner        | 10
subenabled      | t
subbinary       | f
substream       | f
subconninfo     | dbname=postgres host=10.10.45.230 port=5432 user=repluser password=repluser
subslotname     | my_subscription
subsynccommit   | off
subpublications | {my_publication}
Column Name Description
oid Row Identifier
subdbid 구독(Subscription)이 있는 Database OID (pg_database.oid)
subskiplsn [Since. v15] 변경사항을 건너뛸 트랜잭션의 완료 LSN. 그렇지 않으면 0/0
subname Subscription 이름
subowner Subscription 소유자 (pg_authid.oid)
subenabled Subscription 활성화 여부 (TRUE / FALSE)
subbinary [Since. v14] Binary Format으로 데이터를 보내도록 요청 할지 여부(TRUE / FALSE)
substream [Since. v14] 구독에서 진행중인 트랜잭션의 Streaming 허용 여부(TRUE / FALSE)
subtwophasestate [Since. v15] two-phase mode 상태(d: disabled, p: pending enablement, e: enabled)
subdisableonerr [Since. v15] 오류를 감지하면 구독을 비활성 할지 여부(TRUE / FALSE)
subconninfo Publication에 연결하기 위한 연결정보
subslotname Publication에 연결하기 위한 Slot 이름. NULL = NONE
subsynccommit 구독자에 대한 synchronous_commit 설정 값을 포함 (ON / OFF)
subpublications 구독한 게시(Publication) 이름의 배열
suborigin [Since. v16] none과 any 사용가능
- none : 게시자(Publisher)는 출처에 관계없이 변경사항을 전송 (Default)
- any : 원본이 없는 변경사항만 전송하도록 게시자(Publisher)에게 요청

pg_subscription_rel

Publication(구독)과의 관계에 대한 상태를 표시합니다.

변경사항

  • PostgreSQL 13 버전부터 srsublsn 컬럼에 NULL이 가능합니다.
SELECT * FROM pg_subscription_rel ;
 srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+-----------
   16425 |   16411 | r          | 0/3072468
   16425 |   16418 | r          | 0/3072468
Column Name Description
srsubid Subscription의 참조번호(pg_subscription.oid)
srrelid Subscription에 포함된 Object ID
srsubstate Object 상태(i: initialize, d: data is being copied, s: synchronized, r: ready)
srsublsn sr상태의 마지막 LSN

pg_stat_subscription_stats

Logical Replication 구독 오류를 보여줍니다.

변경사항

  • PostgreSQL 15 버전에 생긴 Catalog입니다.
Column Name Description
subid Subscription oid
subname Subscription 이름
apply_error_count 변경내용을 적용하는 동안 발생한 오류 횟수
sync_error_count 초기 테이블 동기화 중 발생한 오류 횟수
stats_reset 현재 통계가 마지막으로 재설정된 시간

 

 

 

 

 

 

기획 및 글 | DB기술기획팀

 

 

 

 

 

 

 

댓글