[Postgres] Monitor cloud DB (AZURE / IBM)

Quand on n’a pas d’outils pour faire le monitoring des bases PostgreSQL hébergées dans le cloud (IBM ou AZURE…)

voici quelques requêtes , vue, fonction SQl utiles pour interroger la base : 

- Monitoring the locks . 
-- view lock_monitor

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

SELECT * from lock_monitor;

--track the locks 

set : log_lock_waits to on 

-- détect LWLock  

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;   


- Réplication

-- (as pré-requisite) tables without PK and UC :     
 
select tab.table_schema,tab.table_name
from information_schema.tables tab left join information_schema.table_constraints tco        
on tab.table_schema = tco.table_schema and tab.table_name = tco.table_name          
and tco.constraint_type in ('PRIMARY KEY','UNIQUE')
where tab.table_type ='BASE TABLE'
and tab.table_schema
not in ('pg_catalog','information_schema') and tco.constraint_name
is null 
order by table_schema,table_name;

-- réplication check : 

select   pid, client_addr, state, sync_state,pg_wal_lsn_diff(sent_lsn, write_lsn) as write_lag,  
         pg_wal_lsn_diff(sent_lsn, flush_lsn) as flush_lag,  
         pg_wal_lsn_diff(sent_lsn, replay_lsn) as replay_lag
from pg_stat_replication;


- Historiser pg_stat_statements (utile en cas de restart de l'instance)

--create extension pg_stat_statements ;

--pour Azure
set role db_admin; 
CREATE SCHEMA IF NOT EXISTS admin AUTHORIZATION db_admin;

--pour IBM 
set role admin;
CREATE SCHEMA IF NOT EXISTS admin AUTHORIZATION admin;

SELECT 'set search_path = ' || n.nspname || ';'
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
WHERE e.extname = 'pg_stat_statements';

gexec

select count(*) as AVANT from pg_stat_statements;

select 'create table admin.pg_stat_statements_' || to_char(current_timestamp,'YYYY_MM_DD_HH24hMI') || ' as select * from pg_stat_statements;';

gexec

select pg_stat_statements_reset();

select count(*) as APRES from pg_stat_statements;

dt admin.pg_stat_statements*

reset role;