Мониторинг использования, статистика

Взято с pgtune

select * from pg_stat_user_tables q order by q.seq_scan desc

select * from pg_statio_user_tables t order by t.heap_blks_read desc


Расширение CREATE EXTENSION pg_buffercache;

использование буферов объектами (таблицами, индексами, прочим):

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

relname             |buffers |
--------------------|--------|
sl_user_access_role |35119   |
sl_user_access      |18547   |
t_users             |17817   |
msg_content         |14132   |
sl_report_data      |13379   |


объекты (таблицы и индексы) в кэше:

 SELECT c.relname, count(*) AS buffers,usagecount
 FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY c.relname,usagecount;

relname                          |buffers |usagecount |
---------------------------------|--------|-----------|
sl_user_access_role              |35119   |5          |
sl_user_access                   |18546   |5          |
t_users                          |17817   |5          |
msg_content                      |14147   |5          |
sl_report_data                   |13396   |5          |
pk_sl_f_cons_rep                 |10942   |5          |
pk_sl_f_form_rep                 |10520   |5          |


Это запрос показывает какой процент общего буфера используют обьекты (таблицы и индексы) и на сколько процентов объекты находятся в самом кэше (буфере):

SELECT
 c.relname,
 pg_size_pretty(count(*) * 8192) as buffered,
 round(100.0 * count(*) /
 (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)
 AS buffers_percent,
 round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1)
 AS percent_of_relation
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 20;

relname                    |buffered |buffers_percent |percent_of_relation |
---------------------------|---------|----------------|--------------------|
sl_user_access_role        |274 MB   |6.7             |100.0               |
sl_user_access             |145 MB   |3.5             |100.0               |
t_users                    |139 MB   |3.4             |100.0               |
msg_content                |111 MB   |2.7             |100.0               |
sl_report_data             |105 MB   |2.6             |99.9                |
pk_sl_f_cons_rep           |85 MB    |2.1             |98.3                |
pk_sl_f_form_rep           |82 MB    |2.0             |98.5                |
mv_budgets_last_sum        |82 MB    |2.0             |100.0               |
sl_f_form_rep              |40 MB    |1.0             |99.9                |
sl_report_requisites       |36 MB    |0.9             |20.2                |

sl_f_cons_rep              |39 MB    |0.9             |99.9                |

Далее берем топ таблицу и смотрим в каких процедурах она используется и как, все ли индексы на месте.

select proname,prosrc from pg_proc where proname= your_function_name; 

Интервал выполнения и sleep

do $$
DECLARE
t NUMERIC;
BEGIN
select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

perform pg_sleep(2);

select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;


select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

end $$

----------------- 

00000: t=1512065827.6849

00000: t=1512065829.6977

00000: t=1512065829.69793

BAT file for backup postgres schema

@echo off
set PGPASSWORD=prm_salary
set PGUSER=prm_salary
set PGDATABASE=db_ris_mkrpk
set PGBIN="C:\Program Files\PostgreSQL\9.6\bin\"
set t=%date%_%time%
set d=%t:~10,4%%t:~7,2%%t:~4,2%_%t:~15,2%%t:~18,2%%t:~21,2%
set BACKUP_FILE="C:\dumps\prm_salary_%d%.dmp"
set FILELOG="C:\dumps\prm_salary_%d%.log"
(
 echo Backup start %date%  %time% file %BACKUP_FILE%
 %PGBIN%"pg_dump" --schema=prm_salary --format=c --file=%BACKUP_FILE%
 echo End of backup %date%  %time%
)>> %FILELOG% 2>&1
forfiles /p "C:\dumps" /s /m *.log /D -5 /C "cmd /c del @path"
forfiles /p "C:\dumps" /s /m *.dmp /D -5 /C "cmd /c del @path"

Блокировки

-- запросы онлайн
SELECT * FROM pg_stat_activity order by client_addr;

-- блокировки по пидам
SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'sandbox' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid()

--3
SELECT locktype, relation::regclass,mode, transactionid AS tid,
virtualtransaction AS vtid,pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid=l.database WHERE (db.datname='cabinet' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();



SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     kl.pid     AS blocking_pid,
     ka.usename AS blocking_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
 JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
 JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted

select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0

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 t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;

SELECT blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query
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
  AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='cabinet';

Размер таблиц в postgres

см. прочие поля в таблице.

SELECT oid,
       table_schema,
       table_name,
       total_bytes,
       pg_size_pretty(total_bytes) AS humna_size
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a
-- where round(total_bytes/1024/1024) > 100 -- только если размер больше 100Мб
order by total_bytes desc

Логирование удаления объектов в postgres

select inet_client_addr()

create table oz.important_table(id numeric);
insert into oz.important_table values(1);

select * from oz.important_table

create view oz.v_test as select * from oz.important_table;

drop view oz.v_test;

select * from oz.v_test

CREATE OR REPLACE FUNCTION public.trg_log_drop()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
  obj record;
BEGIN
  FOR obj in SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF obj.object_type in ('table','view') THEN
      RAISE NOTICE 'TABLE % DROPPED by transaction % (pre-commit)',
                   obj.object_identity, txid_current();
      INSERT INTO public.droplog (tablename, dropxid,user_ip)
             VALUES (obj.object_identity, txid_current(),inet_client_addr());
    END IF;
  END LOOP;
END;
$function$

CREATE EVENT TRIGGER table_drop_logger
  ON sql_drop
  WHEN TAG IN ('DROP TABLE','DROP VIEW')
  EXECUTE PROCEDURE public.trg_log_drop()

CREATE TABLE public.droplog (
  ts        timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  tablename text NOT NULL,
  dropxid   bigint,
  user_ip   text
);

select * from public.droplog

drop table oz.important_table;

Извлечение данных из 2-х курсоров, и возврат курсора

CREATE TABLE pkg_get_cabinet_element.tmp_get_button_tbl (
id numeric NULL,
"name" varchar(500) NULL,
is_edit int4 NULL,
param varchar NULL,
"action" varchar NULL,
dependences_list json NULL,
ico varchar(20) NULL,
action2 varchar(2000) NULL,
button_param varchar(4000) NULL,
identifier varchar(100) NULL
);

CREATE OR REPLACE FUNCTION pkg_get_cabinet_element.get_buttons_for_grades(in_user numeric, in_comment numeric, in_org numeric DEFAULT NULL::numeric)
 RETURNS SETOF pkg_get_cabinet_element.tmp_get_button_tbl
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
declare
  _cur_name1  varchar(64):= md5(clock_timestamp()::varchar);
  _cur_name2  varchar(64):= md5(clock_timestamp()::varchar);
  _rec       pkg_get_cabinet_element.tmp_get_button_tbl%rowtype;
  _rcur      refcursor;
begin
   SELECT _cur_name1
     INTO _rcur
     FROM pkg_get_cabinet_element.get_button(refcur     => _cur_name1::REFCURSOR, in_user => in_user, in_tab => 963, in_sys => 450, in_org => in_org, in_comment => in_comment);
  LOOP
    FETCH _rcur INTO _rec;
    EXIT WHEN NOT FOUND;
    RETURN NEXT _rec;
    raise notice '%','-1-';
  END LOOP;
   SELECT _cur_name2
     INTO _rcur
     FROM pkg_get_cabinet_element.get_button(refcur     => _cur_name2::REFCURSOR, in_user => in_user, in_tab => 1563, in_sys => 490, in_org => in_org, in_comment => in_comment);
  LOOP
    FETCH _rcur INTO _rec;
    EXIT WHEN NOT FOUND;
    RETURN NEXT _rec;
    raise notice '%','-2-';
  END LOOP;
end;
$function$

-----------

select * from pkg_get_cabinet_element.get_buttons_for_grades(in_user=> 71389407,in_org=> 1001,in_comment=> 3869)

id   |name                 |is_edit |param |action |dependences_list                                                                                     |ico          |action2 |button_param |identifier |
-----|---------------------|--------|------|-------|-----------------------------------------------------------------------------------------------------|-------------|--------|-------------|-----------|
2306 |Изменить специалиста |1       |      |save   |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"10", "val":""}} } |icon_ui_redo |edit    |             |5.02.2     |
2305 |Принять в работу     |1       |      |save   |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"3", "val":""}} }  |             |        |             |5.02.1     |

DO $$
declare
 cur refcursor := 'qwe';
begin
 open cur for
  select * from pkg_get_cabinet_element.get_buttons_for_grades(in_user=> 71389407,in_org=> 1001,in_comment=> 3869);
END
$$
   
fetch all in qwe;

id   |name                 |is_edit |param |action |dependences_list                                                                                     |ico          |action2 |button_param |identifier |
-----|---------------------|--------|------|-------|-----------------------------------------------------------------------------------------------------|-------------|--------|-------------|-----------|
2306 |Изменить специалиста |1       |      |save   |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"10", "val":""}} } |icon_ui_redo |edit    |             |5.02.2     |
2305 |Принять в работу     |1       |      |save   |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"3", "val":""}} }  |             |        |             |5.02.1     |