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

DB 인사이드 | PostgreSQL Replication - 설정 확인

by EXEM 2023. 6. 29.

 

 

※ 목차 ※
1. File-based Log Shipping Replication 설정확인
2. Streaming Replication 설정확인
3. Logical Replication 설정확인

 

1. File-based Log Shipping Replication

File-based Log Shipping Replication 설정 확인

PostgreSQL 프로세스조회를 통해서 file-based Log Shipping 설정이 되었는지 확인할 수 있습니다. Main Server에서는 archiver 프로세스가 작동하며, Standby Server에서는 Main Server에서 전송하는 WAL 파일을 대기하는 프로세스가 작동합니다.

Main Server 프로세스

[postgres@main ~] ps -ef | grep postgres
postgres 10065     1  0 16:21 ?        00:00:00 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
postgres 10068 10065  0 16:21 ?        00:00:00 postgres: logger
postgres 10070 10065  0 16:21 ?        00:00:00 postgres: checkpointer
postgres 10071 10065  0 16:21 ?        00:00:00 postgres: background writer
postgres 10072 10065  0 16:21 ?        00:00:00 postgres: walwriter
postgres 10073 10065  0 16:21 ?        00:00:00 postgres: autovacuum launcher
postgres 10074 10065  0 16:21 ?        00:00:00 postgres: archiver last was 000000010000000000000014
postgres 10075 10065  0 16:21 ?        00:00:00 postgres: stats collector
postgres 10076 10065  0 16:21 ?        00:00:00 postgres: logical replication launcher

Main Server가 Standby Server에 000000010000000000000014 WAL 파일을 전송했다는 것을 의미합니다.

 

Standby Server 프로세스

[postgres@standby ~] ps -ef | grep postgres
postgres 12389     1  1 17:17 ?        00:00:00 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
postgres 12392 12389  0 17:17 ?        00:00:00 postgres: logger
postgres 12393 12389  1 17:17 ?        00:00:00 postgres: startup waiting for archiver last was 000000010000000000000015
postgres 12396 12389  0 17:17 ?        00:00:00 postgres: checkpointer
postgres 12397 12389  0 17:17 ?        00:00:00 postgres: background writer
postgres 12398 12389  0 17:17 ?        00:00:00 postgres: stats collector

Standby Server는 Main Server에 000000010000000000000015 WAL 파일을 전송받기를 대기하는 상태를 의미합니다. 다른 표시로 프로세스가 "postgres: startup recovering 000000010000000000000015"와 같이 조회되는 경우 recovering이라고 표시되지만, 000000010000000000000015 WAL 파일을 대기하고 있는 상태를 말합니다. 즉, Standby Server에서 waiting과 recovering의 의미는 크게 중요하지 않으며, 뒤에 나열된 WAL 파일을 수신하기 위해 대기 중임을 의미합니다.

 

File-based Log Shipping Replication 설정 확인

File-based Log Shipping은 WAL 파일이 OS Level에서 scp 또는 cp 명령등으로 전달되기 때문에 Catalog를 통해 동기화 여부를 확인할 수 없습니다. 따라서, 프로세스 작동여부와 Main Server와 Standby Server의 Log를 확인하여 동기화 여부를 판단할 수 있습니다.

Main Server에서 WAL Switch가 발생하면 postgresql.conf에 작성된 archive_command 명령을 통해 Standby Server로 WAL 파일이 전송됩니다.

## Main Server (log_min_messages=debug3 으로 설정해야 아래내용 확인가능)
2023-03-20 16:27:08.284 KST [37103] DEBUG:  executing archive command "scp pg_wal/000000010000000000000018 postgres@10.10.45.231:/var/lib/pgsql/Archive/$f"
2023-03-20 16:27:08.351 KST [37100] DEBUG:  snapshot of 0+0 running transaction ids (lsn 0/19000060 oldest xid 734 latest complete 733 next xid 734)
2023-03-20 16:27:08.832 KST [37103] DEBUG:  archived write-ahead log file "000000010000000000000018"

Standby Server는 Main Server로부터 전달받은 WAL 파일을 Restore 합니다.

## Standby Server Log
## Main Server에서 전달된 000000010000000000000018 WAL 파일을 Restore
## Restore 완료 후 000000010000000000000019 WAL 파일을 Restore 시도하지만, 파일이 없어서 실패
2023-03-20 16:27:12.982 KST [64103] LOG:  restored log file "000000010000000000000018" from archive
2023-03-20 16:27:13.025 KST [64103] LOG:  redo starts at 0/180000A0
cp: cannot stat '/var/lib/pgsql/Archive/000000010000000000000019': No such file or directory
cp: cannot stat '/var/lib/pgsql/Archive/00000002.history': No such file or directory
📢 Standby Server는 restore_command 명령으로 필요한 WAL 파일 Restore를 지속적으로 시도합니다. Main Server로부터 필요한 WAL 파일이 Archive Directory에 전달되면 Restore가 수행된 후 다음 WAL 파일 Restore를 시도합니다. 이와 같은 이유로 Log에 cp: cannot stat... 과 같은 내용이 계속 기록됩니다.

 

File-based Log Shipping Replication 실패

Standby Server OFF 등 여러 이유로 인해 Standby Server에서 필요로 하는 WAL 파일이 없을 경우 Replication은 중단되었다고 볼 수 있습니다. 아래 예시에서 Standby Server는 00000001000000000000001A WAL 파일 Restore를 시도하지만, WAL 파일이 없으므로 실패합니다.

## Standby Server Log
cp: cannot stat '/var/lib/pgsql/Archive/00000001000000000000001A': No such file or directory
cp: cannot stat '/var/lib/pgsql/Archive/00000002.history': No such file or directory

Main Server에서 WAL Switch가 발생하여 archive_command 명령을 통해 Standby Server로 WAL 파일(00000001000000000000001B)이 전송됩니다.

## Main Server Log
2023-03-20 16:58:20.597 KST [39274] DEBUG:  executing archive command "scp pg_wal/00000001000000000000001B postgres@10.10.45.231:/var/lib/pgsql/Archive/$f"
2023-03-20 16:58:20.756 KST [39271] DEBUG:  snapshot of 0+0 running transaction ids (lsn 0/1C000060 oldest xid 734 latest complete 733 next xid 734)

Standby Server의 Archive Directory를 보면 00000001000000000000001A WAL 파일이 존재하지 않습니다.

[postgres@standby Archive] ls -l /var/lib/pgsql/Archive
drwxr-xr-x  2 postgres postgres       70 Mar 20 16:31 .
drwx------. 9 postgres postgres      176 Feb 20 16:13 ..
-rw-------  1 postgres postgres 16777216 Mar 20 16:27 000000010000000000000018
-rw-------  1 postgres postgres 16777216 Mar 20 16:31 000000010000000000000019
-rw-------  1 postgres postgres 16777216 Mar 20 16:58 00000001000000000000001B

위 예시와 같이 00000001000000000000001A WAL 파일은 존재하지 않고 그다음 00000001000000000000001B WAL 파일이 존재할 경우 Standby Server는 00000001000000000000001A WAL 파일의 부재를 알지 못하며 아래와 같이 Standby Server는 00000001000000000000001A WAL 파일 Restore를 시도합니다. 00000001000000000000001A 이후의 WAL 파일이 Main Server에서 전송되지만, Standby Server에서는 00000001000000000000001A WAL 파일 Restore를 하지 못했기 때문에 이후 WAL 파일에 대해 Restore 시도를 하지 않습니다.

cp: cannot stat '/var/lib/pgsql/Archive/00000001000000000000001A': No such file or directory
cp: cannot stat '/var/lib/pgsql/Archive/00000002.history': No such file or directory

Standby Server에서 필요로 하는 WAL 파일이 Main Server의 Archive Directory 등에 보관되어 있다면, 해당 WAL 파일에 대한 Restore가 가능하여 Replication은 유지될 수 있으나, 보관하고 있지 않다면 Replication은 중단(실패)된 것으로 볼 수 있습니다. 이러한 경우 현재까지의 Main Server 상태로 Standby Server를 재구성해야 Replication을 재개할 수 있습니다.

2. Sreaming Replication

Streaming Replication 설정 확인

Streaming Replication 설정 확인은 PostgreSQL 프로세스 조회로 확인할 수 있으며, Catalog를 통해서도 확인할 수 있습니다. 프로세스 조회 시 walsender, walreceiver, startup 세 가지 프로세스가 동작하고 있는지 확인하면 됩니다. walsender 프로세스는 Main Server에서 실행되며, walreceiver, startup 프로세스는 Standby Server에서 실행됩니다. Standby Server의 walreceiver 프로세스가 Main Server의 walsender 프로세스가 전달하는 WAL Recode를 수신하는 역할을 담당하므로, walreceiver 프로세스가 동작하고 있지 않다면 Replication 설정은 실패한 것으로 볼 수 있습니다.

Main Server 프로세스

[postgres@main ~] ps -ef | grep postgres
postgres 42421     1  0 15:58 ?        00:00:00 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
postgres 42423 42421  0 15:58 ?        00:00:00 postgres: logger
postgres 42425 42421  0 15:58 ?        00:00:00 postgres: checkpointer
postgres 42426 42421  0 15:58 ?        00:00:00 postgres: background writer
postgres 42427 42421  0 15:58 ?        00:00:00 postgres: walwriter
postgres 42428 42421  0 15:58 ?        00:00:00 postgres: autovacuum launcher
postgres 42429 42421  0 15:58 ?        00:00:00 postgres: stats collector
postgres 42430 42421  0 15:58 ?        00:00:00 postgres: logical replication launcher
postgres 42435 42421  0 15:58 ?        00:00:00 postgres: walsender replicauser 10.10.45.231(57364) streaming 0/7000060

 

Standby Server 프로세스

Main Server에서 walsender 프로세스가 7번 WAL 파일의 000060 WAL Record를 Standby Server로 전송하는 중이라는 것을 확인할 수 있습니다.

[postgres@streaming ~] ps -ef | grep postgres
postgres 22901     1  0 15:00 ?        00:00:00 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
postgres 22903 22901  0 15:00 ?        00:00:00 postgres: logger
postgres 22904 22901  0 15:00 ?        00:00:00 postgres: startup recovering 00000001000000000000002F
postgres 22905 22901  0 15:00 ?        00:00:00 postgres: checkpointer
postgres 22906 22901  0 15:00 ?        00:00:00 postgres: background writer
postgres 22907 22901  0 15:00 ?        00:00:00 postgres: stats collector
postgres 24021 22901  0 15:58 ?        00:00:06 postgres: walreceiver streaming 0/7000060

Standby Server의 walreceiver는 Main Server의 walsender로부터 7번 WAL 파일의 000060 WAL Record를 전송받고 있음을 확인할 수 있습니다.

 

Streaming Replication 모니터링

Streaming Replication 설정이 완료되면, 각 Server에서는 아래와 같은 Catalog에서 Replication에 대한 내용을 확인할 수 있습니다.

Main Server에서 확인 가능한 Catalog

Catalog Name Description
pg_stat_replication Replication에 대한 통계 확인
pg_replication_slots Replication Slot에 대한 정보 확인

 

Standby Server에서 확인 가능한 Catalog

Catalog Name Description
pg_stat_wal_receiver WAL Receiver에 대한 통계 확인
📢 Catalog에 대한 설명은 PostgreSQL Replication - Catalog에서 확인할 수 있습니다.

 

Catalog 내용을 확인하기에 앞서 Replication Monitoring시 접하는 write, flush, replay 용어에 대해 먼저 알아보겠습니다.

구분 Description
Write 트랜잭션이 Commit 되면 해당 트랜잭션에 의해 변경된 내용을 순차적으로 WAL Record에 기록합니다.
Flush WAL Record에 기록된 변경 내용이 디스크에 안전하게 저장합니다. 이를 통해 내구성을 확보할 수 있으며 서버 장애 발생 시 데이터 손실을 방지할 수 있습니다. 또한, Standby Server로 전송하는 내용을 신뢰할 수 있습니다.
Replay Main Server로 부터 WAL Record를 수신하고 생성된 순서대로 Database에 적용하여 Main Server와 동기화시킵니다.


Main Server Catalog 확인

SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 42435
usesysid         | 16384
usename          | replicauser
application_name | walreceiver
client_addr      | 10.10.45.231
client_hostname  |
client_port      | 57364
backend_start    | 2023-05-17 15:58:22.570344+09
backend_xmin     |
state            | streaming
sent_lsn         | 0/2F08ED40
write_lsn        | 0/2F08ED40
flush_lsn        | 0/2F08ED40
replay_lsn       | 0/2F08ED40
write_lag        | 00:00:00.001215
flush_lag        | 00:00:00.001818
replay_lag       | 00:00:00.002327
sync_priority    | 0
sync_state       | async
reply_time       | 2023-05-17 16:58:47.317277+09
  • WAL Sender 프로세스 PID는 42435이고, replicauser로 접속하였습니다.(replicauser의 oid는 16384입니다.)
  • WAL Receiver가 접속한 서버 IP는 10.10.45.231이고, 57364 Port를 통하여 2023-05-17 15:58:22에 접속하였습니다. client_hostname과 backend_xmin은 관련 파라미터가 설정되지 않아 NULL로 표시되었습니다.
  • Replication은 현재 Streaming 중이며, 0/2F08ED40까지 WAL Record를 보냈고 Standby Server에서 0/2F08ED40 WAL Record를 적용하고 있습니다.
  • Write, Flush, Replay 단계에서 지연은 1초 미만입니다.
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                | 756
catalog_xmin        |
restart_lsn         | 0/2F08ED78
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |
two_phase           | f
  • physical_slot 이름으로 생성된 Replication Slot은 physical로 생성되었으며, PID가 42435인 프로세스가 사용하고 있습니다.
  • 이 Slot이 유지되기 위해서는 트랜잭션 ID의 xmin이 756부터 필요합니다.
  • Standby Server가 필요로 할 수 있는 가장 오래된 WAL Record는 0/2F08ED78 입니다.

 

Stadnby Server Catalog 확인

SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 24021
status                | streaming
receive_start_lsn     | 0/2D000000
receive_start_tli     | 1
written_lsn           | 0/2F08ED78
flushed_lsn           | 0/2F08ED78
received_tli          | 1
last_msg_send_time    | 2023-05-17 16:59:28.27257+09
last_msg_receipt_time | 2023-05-17 16:59:28.271647+09
latest_end_lsn        | 0/2F08ED78
latest_end_time       | 2023-05-17 16:58:58.267503+09
slot_name             | physical_slot
sender_host           | 10.10.45.230
sender_port           | 5432
conninfo              | user=replicauser passfile=/var/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=10.10.45.230 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
  • WAL Receiver 프로세스의 PID는 24021이고, 현재 Streaming 중입니다.
  • 0/2D000000에서 0/2F08ED78까지 WAL 데이터(LSN)는 전송받아 적용하였습니다.
  • Replication이 수행할 때 Timeline ID는 1이었고, 현재도 1로 유지되고 있습니다.
  • WAL Sender 프로세스의(Main Server) IP는 10.10.45.230이고 5432 Port를 사용하며 physical_slot이름의 Replication Slot을 통하여 WAL 데이터가 전송됩니다.
  • Replication이 연결된 후 현재까지 전송받은 WAL 데이터의 크기는 약 33MB입니다.(0/2F08ED78 - 0/2D000000)
📢 전송된 WAL 데이터크기 조회는 pg_wal_lsn_diff Funcation으로 확인할 수 있습니다. SELECT pg_wal_lsn_diff( '0/2F08ED78', '0/2D000000' ) / 1024 / 1024 ; Replication 관련 Funcation에 대한 설명은 PostgreSQL Replication - Function을 참조하세요.

 

Streaming Replication TEST

Main Server에서 입력한 데이터가 Standby Server로 반영되는지 확인해 보겠습니다. Main Server에서 Table을 생성하고, 현재시간을 입력합니다.

-- Main Server에서 데이터생성
CREATE TABLE test001 ( c1 timestamp without time zone ) ;
INSERT INTO test001 VALUES ( now() );

postgres=# SELECT * FROM test001;
             c1
----------------------------
 2023-02-06 15:53:21.310735
(1 row)

Main Server에서 입력한 데이터가 Standby Server에서 조회가 됨을 확인할 수 있습니다.

-- Standby Server에서 Replication 데이터 확인
SELECT * FROM test001 ;
             c1
----------------------------
 2023-02-06 15:53:21.310735
(1 row)

Standby Server는 Read-Only 성격으로 인해 오직 SELECT만 가능합니다.

-- Standby Server에서 Insert 수행(DML)
INSERT INTO test001 VALUES ( now() );
ERROR:  cannot execute INSERT in a read-only transaction
-- Standby Server에서 Delete 수행(DML)
DELETE FROM test001 ;
ERROR:  cannot execute DELETE in a read-only transaction
-- Standby Server에서 Create Table 수행(DDL)
CREATE TABLE TEST002 ( c1 INTEGER ) ;
ERROR:  cannot execute CREATE TABLE in a read-only transaction
-- Standby Server에서 Drop Table 수행(DDL)
DROP TABLE test001 ;
ERROR:  cannot execute DROP TABLE in a read-only transaction

3. Logical Replication

Logical Replication 설정 확인

Logical Replication 설정 확인은 PostgreSQL 프로세스 조회로 확인할 수 있으며, Catalog를 통해서도 확인할 수 있습니다. 프로세스 조회 시 walsender, logical replication worker 두 가지 프로세스가 동작하고 있는지 확인하면 됩니다. walsender 프로세스는 Main Server에서 실행되며, logical replication worker 프로세스는 Standby Server에서 실행됩니다.

Main Server 프로세스 (게시자-Publisher)

walsender 프로세스가 작동하면 Logical Replication이 설정되었음을 확인할 수 있습니다.

[postgres@main ~] ps -ef | grep postgres
postgres 25878     1  0 11:14 ?        00:00:00 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
postgres 25879 25878  0 11:14 ?        00:00:00 postgres: logger
postgres 25881 25878  0 11:14 ?        00:00:00 postgres: checkpointer
postgres 25882 25878  0 11:14 ?        00:00:00 postgres: background writer
postgres 25883 25878  0 11:14 ?        00:00:00 postgres: walwriter
postgres 25884 25878  0 11:14 ?        00:00:00 postgres: autovacuum launcher
postgres 25885 25878  0 11:14 ?        00:00:00 postgres: stats collector
postgres 25886 25878  0 11:14 ?        00:00:00 postgres: logical replication launcher
postgres 26476 25878  0 11:36 ?        00:00:00 postgres: walsender repluser 10.10.45.231(56472) START_REPLICATION

 

Main Server 프로세스 (구독자-Subscriber)

logical replication worker 프로세스가 작동하면 Logical Replication이 설정되었음을 확인할 수 있습니다.

[postgres@standby ~] ps -ef | grep postgres
postgres 42937     1  0 11:21 ?        00:00:00 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
postgres 42939 42937  0 11:21 ?        00:00:00 postgres: logger
postgres 42940 42937  0 11:21 ?        00:00:00 postgres: checkpointer
postgres 42941 42937  0 11:21 ?        00:00:00 postgres: background writer
postgres 42943 42937  0 11:21 ?        00:00:00 postgres: walwriter
postgres 42944 42937  0 11:21 ?        00:00:00 postgres: autovacuum launcher
postgres 42945 42937  0 11:21 ?        00:00:00 postgres: logical replication launcher
postgres 43354 42937  0 11:36 ?        00:00:00 postgres: logical replication worker for subscription 16404

 

Logical Replication 설정 확인

Logical Replication 설정이 완료되면, 각 Server에서는 아래와 같은 Catalog에서 Replication에 대한 내용을 확인할 수 있습니다.

Main Server에서 확인 가능한 Catalog

Catalog Name Description
pg_publication Logical Replication의 Publication에 대한 정보 확인
pg_publication_tables Publication에 포함된 Table 확인
pg_replication_slots Logical Replication이 구성된 Slot에 대한 정보 확인
pg_stat_replication_slots [Since. v14] Replication Slot에 대한 통계 확인
pg_stat_replication Replication에 대한 통계 확인

 

Standby Server에서 확인 가능한 Catalog

Catalog Name Description
pg_subscription Subscription에 대한 정보 확인
📢 Catalog에 대한 설명은 PostgreSQL Replication - Catalog에서 확인할 수 있습니다.

 

Main Server Catalog 확인

pg_publication Catalog를 통하여 Publication의 정보를 확인할 수 있습니다.

-- Publication 정보 확인
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
  • Publication명은 my_publication이고(pubname), Owner는 postgres입니다.(pubowner)
  • 일부 Table으로 구성되어 있으며(puballtables=f), INSERT, UPDATE, DELETE, TRUNCATE에 대해 Replication을 수행합니다.(pubinsert, pubupdate, pubdelete, pubtruncate)

pg_publication Catalog에서 확인 한 Publication에 구성된 Table 목록을 pg_publication_tables Catalog를 통하여 확인할 수 있습니다.

-- Publication 구성 Table 확인
SELECT * FROM pg_publication_tables ;
    pubname     | schemaname |      tablename
----------------+------------+----------------------
 my_publication | public     | replication_table_01
 my_publication | public     | replication_table_02
(2 rows)
  • my publication은 public Schema의 replication_table_01, replication_table_02 Table로 구성되어 있습니다.

Logical Replication은 Replication Slot을 사용합니다. Logical Replication에서 사용되는 Slot에 대한 정보를 pg_replication_slots Catalog를 통하여 확인할 수 있습니다.

-- 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          | 3290
xmin                |
catalog_xmin        | 758
restart_lsn         | 0/3072518
confirmed_flush_lsn | 0/3072550
wal_status          | reserved
safe_wal_size       |
two_phase           | f
  • Replication Slot명은 my_subscription입니다.(slot_name)
📢 예시에서는 Standby Server에서 Logical Slot을 지정하지 않고 Subcription을 생성하였기 때문에, Subscription명으로 Replication Slot명이 지정되었습니다.(기본값)
  • pgoutput 모듈(plugin)을 사용하여 Logical Replication을 수행하며(slot_type), 임시적으로 생성된 Slot은 아닙니다(temporary).
  • Replication 대상 Database는 postgres입니다(datoid, database).
  • 현재 Replication Slot은 Active 상태이며(active), Replication을 담당하는 프로세스 ID는 3290입니다(active_pid)
## 프로세스 조회 시 Replication을 담당하는 프로세스를 확인할 수 있습니다.
ps -ef | grep 3290
postgres 3290 3177 0 Apr21 ? 00:00:07 postgres: walsender repluser 10.10.45.231(54756) START_REPLICATION
  • Replication Slot을 유지하기 위한 Catalog 트랜잭션 xmin은 758입니다(catlog_xmin).

PostgreSQL 14 버전부터 pg_stat_replication_slots Catalog가 생성되어 Replication Slot에 대한 통계정보를 확인할 수 있습니다.

-- Replication Slot 통계 확인
SELECT * FROM pg_stat_replication_slots ;
-[ RECORD 1 ]+----------------
slot_name    | my_subscription
spill_txns   | 2
spill_count  | 237
spill_bytes  | 15851997849
stream_txns  | 0
stream_count | 0
stream_bytes | 0
total_txns   | 37
total_bytes  | 178269828
stats_reset  |
  • Logical 디코딩하면서 logical_decoding_work_mem을 초과한 트랜잭션 수는 2개이고(spill_txns) 디스크로 237회 넘어갔으며(spill_count) 디스크로 넘어간 데이터 양은 15,851,997,849 Bytes(spill_bytes)입니다.
  • my_subscription Replication Slot을 통해 37개의 디코딩된 트랜잭션을(total_txns) Plugin으로 전송했습니다. 그 크기는 178,269,828 Bytes(total_bytes)입니다.
📢 디스크로 Spill 되는 횟수는 logical_decoding_work_mem 파라미터 설정을 따릅니다. 기본값은 64MB입니다.

 

pg_stat_replication Catalog에서 Logical Replication에 대한 통계를 확인할 수 있습니다.

-- Replication 통계 확인
SELECT * FROM pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 13161
usesysid         | 16384
usename          | repluser
application_name | my_subscription
client_addr      | 10.10.45.231
client_hostname  |
client_port      | 55036
backend_start    | 2023-05-03 17:25:14.178759+09
backend_xmin     |
state            | streaming
sent_lsn         | 4/5620E6E0
write_lsn        | 4/5620E6E0
flush_lsn        | 4/5620E6E0
replay_lsn       | 4/5620E6E0
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-06-08 10:07:13.03648+09
  • WAL Sender 프로세스 PID는 13161이고, repluser로 접속하였습니다.(repluser의 oid는 16384입니다.)
  • WAL Receiver가 접속한 서버 IP는 10.10.45.231이고, 55036 Port를 통하여 2023-05-03 17:25:14에 접속하였습니다. client_hostname과 backend_xmin은 관련 파라미터가 설정되지 않아 NULL로 표시되었습니다.
  • Replication은 현재 Streaming 중이며, 4/5620E6E0 까지 WAL Record를 보냈고 Standby Server에서 4/5620E6E0 WAL Record를 적용하였습니다.
  • 현재 Replication의 Write, Flush, Replay 단계에서 지연은 없고, Standby Server에서 받은 마지막 응답시간은 2023-06-08 10:07:13입니다.

 

Standby Server Catalog 확인

Standby Server에서는 Main Server의 walsender로부터 전달받은 내용을 단순히 복원(Replay) 하기 때문에 Replication에 대한 통계를 확인할 수 없습니다. 따라서 Standby Server에서는 Subscription 즉, Main Server의 접속정보정도만 확인이 가능합니다.

-- Subscription 확인
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}

 

 

 

 

기획 및 글 | DB기술기획팀

 

 

 

 

 

 

저작자표시 비영리 변경금지

'엑셈 경쟁력 > DB 인사이드' 카테고리의 다른 글

DB 인사이드 | PostgreSQL Replication - Slot  (2) 2023.07.26
DB 인사이드 | PostgreSQL Replication - Parameter  (6) 2023.07.26
DB 인사이드 | PostgreSQL Replication - Catalog  (0) 2023.06.29
DB 인사이드 | PostgreSQL Replication - Function  (0) 2023.06.29
DB 인사이드 | PostgreSQL Replication - Trouble Shooting  (0) 2023.05.25

관련글

댓글

티스토리툴바