DBA가 알면 좋은 쿼리 ( SLOT )

SELECT slot_name, slot_type, active,

pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_from_current,

restart_lsn, confirmed_flush_lsn

FROM pg_replication_slots

ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

이 쿼리를 사용할 때는 pg_wal이 굉장히 많이 잡혀있어서 확인하게 된게 계기이다.

이걸 통해 고아 slot들이 있음을 확인했다.

 

 

SELECT * FROM pg_subscription;

slot들 중에서 구독된 게 있는지 확인했고, 고아슬롯인지 확인햇다.

 

 

SELECT slot_name, slot_type, active, plugin, database, temporary

FROM pg_replication_slots

WHERE slot_type='logical'

ORDER BY active DESC, slot_name;

 

SELECT pid, application_name, state, sync_state, client_addr

FROM pg_stat_replication;

 

checkpoint;

고아 슬롯을 제거했다면 checkpoint로 반영시키면 된다. 

 

SELECT pg_drop_replication_slot('pg_18005_sync_17433_7540556035005825047');

SELECT pg_drop_replication_slot('pg_18005_sync_17474_7540556035005825047');

SELECT pg_drop_replication_slot('raw_sub_v_shop_stock');

슬롯을 제거하는 쿼리

 

SELECT slot_name, slot_type, active

FROM pg_replication_slots

WHERE slot_type='physical';

 

SELECT slot_name, slot_type, active FROM pg_replication_slots WHERE slot_type='logical';