PostgreSQL 논리 복제는 pgEdge 복제 클러스터 뒤의 전원과 구성을 제공하여 테이블을 선택적으로 복제하고 해당 테이블의 변경 사항을 더 세분화된 수준으로 복제할 수 있게 합니다. 실시간 분석, 낮은 대기 시간 또는 고 가용성을 위해 pgEdge 분산 PostgreSQL 복제를 사용하든지, 복제 구성 및 쿼리 사용을 최적화하면 성능, 일관성 및 신뢰성을 최적화할 수 있습니다.
Postgres 복제는 데이터베이스 간 데이터를 복제하는 강력한 도구입니다. 물리적 복제와 달리 논리 복제는 복제할 데이터 및 사용 방식에 대해 더 많은 제어 및 유연성을 제공합니다.
본 블로그에서는 PostgreSQL 데이터베이스의 논리 복제를 관리하기 쉽게 하는 쿼리를 탐구합니다.
Postgres 논리 복제 상태 모니터링
논리 복제 설정의 상태를 모니터링하는 것은 복제가 원활하게 실행되는지 확인하기 위한 중요한 작업입니다. pg_stat_subscription 뷰를 쿼리하여 데이터베이스의 모든 구독의 상태를 모니터링할 수 있습니다.
SELECT
subname AS subscription_name,
pid AS process_id,
usename AS user_name,
application_name,
client_addr AS client_address,
state,
sync_state,
sent_lsn,
write_lsn,
flush_lsn,
replay_lsn,
clock_timestamp() - write_lsn_timestamp AS replication_delay
FROM
pg_stat_subscription
ORDER BY
subscription_name;
subscription_name | process_id | user_name | application_name | client_address | state | sync_state | sent_lsn | write_lsn | flush_lsn | replay_lsn | replication_delay
-------------------+------------+-----------+------------------+----------------+-------------+------------+--------------+--------------+--------------+--------------+-------------------
sub1 | 23456 | postgres | logical_rep_sub | 192.168.1.10 | streaming | synced | 0/3000128 | 0/3000128 | 0/3000128 | 0/3000128 | 00:00:00.12345
sub2 | 23478 | postgres | logical_rep_sub | 192.168.1.11 | catchup | async | 0/4000238 | 0/4000200 | 0/40001F8 | 0/40001E0 | 00:00:02.67890
subname– 구독의 이름입니다.state– 구독 프로세스의 상태입니다 (예: 스트리밍, catchup, 초기화).sync_state– 구독의 동기화 상태입니다.sent_lsn,write_lsn,flush_lsn,replay_lsn– 이러한 열은 복제 진행 상황을 나타내는 다양한 로그 시퀀스 번호(LSN)를 나타냅니다.replication_delay– LSN이 작성된 후 구독자에서 적용되기까지의 지연 시간은 복제 지연을 식별하는 데 중요합니다.
이 쿼리는 논리적 복제 상태에 대한 포괄적인 개요를 제공하여 복제 지연이나 연결이 끊긴 구독자와 같은 문제를 신속하게 식별할 수 있습니다.
Postgres 복제 지연 분석
복제 지연을 분석하는 것은 복제된 데이터베이스 간의 일관성과 신선도를 유지하는 데 중요합니다. pg_replication_slots 시스템 뷰를 사용하여 게시자와 구독자 간의 복제 지연을 계산하는 데 도움이 될 수 있습니다:
SELECT
s.slot_name,
s.active,
s.restart_lsn,
pg_wal_lsn_diff(pg_current_wal_lsn(), s.restart_lsn) AS replication_lag_bytes,
clock_timestamp() - pg_last_xact_replay_timestamp() AS replication_lag_time
FROM
pg_replication_slots s
WHERE
s.active = true
AND
s.plugin = 'pgoutput';
slot_name | active | restart_lsn | replication_lag_bytes | replication_lag_time
-----------+--------+-------------+-----------------------+-----------------------
slot1 | t | 0/3000128 | 65536 | 00:00:00.12345
slot2 | t | 0/4000238 | 131072 | 00:00:02.67890
slot_name– 사용 중인 복제 슬롯의 이름입니다.replication_lag_bytes– 게시자의 현재 WAL 위치와 구독자가 인식하는 마지막 WAL 위치 간의 바이트 차이입니다.replication_lag_time– 구독자에서 재생된 마지막 트랜잭션과 현재 시간 간의 시간 차이입니다.
이 쿼리는 논리 복제의 크기 및 시간 기반 지연을 평가하여, 지연이 허용 가능한 임계값을 초과할 경우 예방 조치를 취할 수 있도록 도와줍니다.
복제 슬롯 사용 모니터링
복제 슬롯은 논리 복제에서 중요하며, 모든 구독자가 이를 처리할 때까지 WAL 세그먼트가 유지되도록 보장합니다. 복제 슬롯의 사용 상황을 모니터링하기 위해 pg_replication_slots 뷰를 쿼리할 수 있습니다:
SELECT
slot_name,
plugin,
slot_type,
active,
confirmed_flush_lsn,
pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS slot_lag_bytes
FROM
pg_replication_slots
WHERE
slot_type = 'logical';
slot_name | plugin | slot_type | active | confirmed_flush_lsn | slot_lag_bytes
-----------+---------+-----------+--------+---------------------+----------------
slot1 | pgoutput| logical | t | 0/3000128 | 65536
slot2 | pgoutput| logical | t | 0/4000238 | 131072
slot_name– 복제 슬롯의 이름입니다.slot_lag_bytes– 현재 WAL 위치와 마지막 위치 간의 바이트 단위 지연입니다. 이는 슬롯에 의해 플러시된 것으로 확인된 위치까지의 거리를 의미합니다.
복제 슬롯 사용 상황을 모니터링하는 것은 WAL 세그먼트 보존과 관련된 문제를 예방하는 데 중요합니다. 이러한 문제는 발행자의 디스크 공간 고갈로 이어질 수 있습니다.
사용되지 않는 복제 슬롯 삭제
시간이 지남에 따라, 특히 구독자를 제거하거나 복제 구성을 변경한 후에는 사용되지 않는 복제 슬롯이 누적될 수 있습니다. 이러한 사용되지 않는 슬롯은 WAL 파일의 불필요한 보존을 유발하여 디스크 공간의 낭비로 이어질 수 있습니다. 다음 쿼리는 사용되지 않는 복제 슬롯을 식별하고 삭제하는 데 도움이 됩니다:
DO $$
DECLARE
slot_record RECORD;
BEGIN
FOR slot_record IN
SELECT slot_name FROM pg_replication_slots WHERE active = false
LOOP
EXECUTE format('SELECT pg_drop_replication_slot(%L)', slot_record.slot_name);
END LOOP;
END $$;
이 쿼리는 비활성화된 복제 슬롯을 반복하며 pg_drop_replication_slot 관리 함수를 사용하여 삭제합니다. 사용되지 않는 복제 슬롯을 정기적으로 정리하면 데이터베이스가 효율적으로 유지되고 WAL 파일 보존과 관련된 잠재적인 문제를 방지할 수 있습니다.
복제 슬롯 생성
새 논리적 복제 슬롯을 만들어야 하는 경우 다음 쿼리가 유용합니다:
SELECT * FROM pg_create_logical_replication_slot('my_slot', 'pgoutput');
slot_name | xlog_position
-----------+---------------
my_slot | 0/3000128
이 쿼리는 pg_create_logical_replication_slot 함수를 사용하여 지정된 이름과 출력 플러그인(poutput을 사용하는 예시)으로 새 논리적 복제 슬롯을 생성합니다. 이 쿼리는 새로운 논리적 복제 구성을 설정할 때 유용하며, 구독자가 WAL 레코드의 올바른 지점부터 변경 사항을 수신할 수 있는지 확인하는 데 사용할 수 있습니다.
pglogical을 사용한 논리적 복제 최적화
더 고급 논리적 복제 기능을 위해 pglogical 확장 기능을 사용하는 경우, 다음 쿼리는 모든 pglogical 구독의 상태를 확인하는 데 도움이 될 수 있습니다:
SELECT
subscription_name,
status,
received_lsn,
replay_lag,
last_received_change,
pending_changes
FROM
pglogical.show_subscription_status();
subscription_name | status | received_lsn | replay_lag | last_received_change | pending_changes
-------------------+----------+--------------+------------+---------------------+-----------------
sub_pglogical1 | replicating | 0/3000128 | 00:00:01.234 | 2024-08-22 10:30:00 | 5
sub_pglogical2 | idle | 0/4000238 | 00:00:00.000 | 2024-08-22 10:29:30 | 0
subscription_name– pglogical 구독의 이름.replay_lag– 마지막으로 받은 변경 사항과 현재 시간 사이의 지연 시간.pending_changes– 가입자에게 적용 대기 중인 변경 사항의 수입니다.
이 쿼리는 pglogical 구독의 상세 개요를 제공하여 복제 설정을 세밀하게 조정하고 문제를 해결하는 데 도움이 됩니다.
결론
pgEdge Distributed PostgreSQL은 클러스터 전체에서 논리적 복제를 사용하여 복제할 데이터와 해당 데이터를 저장하는 방법을 정확하게 제어할 수 있습니다. pgEdge는 데이터 복제 프로세스에 대한 세밀한 제어를 제공하는 다재다능한 도구를 개발하고 있습니다. 이 블로그에서 소개된 쿼리는 논리적 복제 클러스터를 효과적으로 모니터링, 관리 및 최적화하는 데 도움이 될 수 있습니다. 이러한 쿼리를 사용하여 데이터 일관성을 보장하고 복제 지연을 최소화하며 충돌을 방지하여 견고하고 신뢰할 수 있는 데이터베이스 환경을 유지하는 데 중요합니다.
논리적 복제 작업을 계속하면 이러한 쿼리를 정기적인 모니터링 및 유지 관리 루틴에 통합하여 PostgreSQL 데이터베이스와 pgEdge 클러스터가 최적으로 작동하도록 보장하는 것이 좋습니다.
Source:
https://dzone.com/articles/optimizing-debugging-postgresql-replication-queries