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;