postgres logical replication verify monitoring pglogical - ghdrako/doc_snipets GitHub Wiki

Monitoring zabbix - safe_show_subscription_status zastepuje show_subscription_status bo zawiera connection string z haslem

Za pomocą replicatin user:

CREATE or replace FUNCTION zabbix.safe_show_subscription_status()
RETURNS TABLE(subscription_name text, status text, provider_node text) 
AS $$
BEGIN
    RETURN QUERY
    SELECT s.subscription_name, s.status, s.provider_node
    FROM pglogical.show_subscription_status() s; -- alias dla rozróżnienia
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION zabbix.safe_show_subscription_status() TO zabbix;

--User Zabbix:
Select * from zabbix.safe_show_subscription_status(); dziala
Select * from pglogical.show_subscription_status(); blad

SELECT sync_nspname||'.'||sync_relname AS table_name,
    sync_status,
    sync_statuslsn
FROM pglogical.local_sync_status
WHERE sync_relname IS NOT NULL
ORDER BY table_name;

Monitor slots

select status from pglogical.show_subscription_status();
replicating
down
initializing
disabled 
down

active column. If a slot does not have a connection to a consumer, the column will be false.

select * from pg_subscription system;
\dRs+

select slot_name,pg_size_pretty(pg_current_wal_lsn() - confirmed_flush_lsn) FROM pg_replication_slots WHERE slot_type = 'logical';

-- Monitor replication lag    
SELECT    
redo_lsn,   
slot_name,   
restart_lsn,   
ROUND((redo_lsn-restart_lsn) / 1024 / 1024 / 1024, 2)  AS  GB_behind   
FROM  PG_CONTROL_CHECKPOINT(), PG_REPLICATION_SLOTS;

show wal_level
show cloudsql.enable_pglogical; # verify logical replication enable
select * from pglogical.node;
select * from pglogical.local_node;
select * FROM pglogical.node_interface; # show node 
SELECT * FROM pglogical.node_interface where if_name = 'provider';
select * FROM pg_replication_slots; # show slots
select * from pglogical.replication_set;
select * from pglogical.replication_set_table;
SELECT * FROM pglogical.replication_set_table
WHERE set_id = (select set_id from pglogical.replication_set where set_name='default');


SELECT data FROM pg_logical_slot_get_changes('repl_slot1', NULL, NULL, 'pretty-print', '1'); # show data in slot


select pg_drop_replication_slot('repl_slot2') from pg_replication_slots where slot_name = 'repl_slot2';
select pg_drop_replication_slot('<slot_name>') from pg_replication_slots where slot_name = '<slot_name>';

SELECT * FROM pg_create_logical_replication_slot('repl_slot2', 'wal2json');


SELECT count(*) FROM pg_logical_slot_get_changes('repl_slot1', NULL, NULL, 'pretty-print', '1') 
union all
SELECT count(*) FROM pg_logical_slot_get_changes('repl_slot2', NULL, NULL, 'pretty-print', '1')
union all
SELECT count(*) FROM pg_logical_slot_get_changes('repl_slot3', NULL, NULL, 'pretty-print', '1')
union all
SELECT count(*) FROM pg_logical_slot_get_changes('repl_slot4', NULL, NULL, 'pretty-print', '1')
⚠️ **GitHub.com Fallback** ⚠️