DB 인사이드 | PostgreSQL Replication - Catalog

이번 문서에서는 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_lsn이 written_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	s와 r상태의 마지막 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기술기획팀