Редактор SQL запросов для СУБД PostgreSQL

Не нашли нужных функций? Пишите в комментариях на форуме и мы добавим нужный функционал

Для работы с СУБД, установка дополнительного ПО не требуется. Все необходимые библиотеки идут в комплекте

По умолчанию используется библиотека версии 9.6

Для обновления версии клиентской библиотеки замените  (скопируйте с другим именем) файл libpq.dll в директории .\DLL\PG и укажите путь к данному файлу в поле "Vendor library"

• Создание нового листа

• Структура листа

  • Менеджер соединения с БД

  • Дерево объектов

    • DATABASE

    • PG_CATALOG

    • INFORMATION_SCHEMA

    • OBJECT

      • TABLE

      • INDEX

      • VIEW

      • MATERIALIZED_VIEW

      • FUNCTION

      • PROCEDURE

      • TRIGGER

      • SEQUENCE

      • DOMAIN

    • EVENT TRIGGER

    • EXTENSION

    • FOREIGN SERVER

    • STANDBY STATUS

    • TABLESPACE

    • GROUPS

    • USERS

    • MONITOR

      • DASHBOARD​

      • SESSION LIST

      • LOCKS

      • SERVER STATISTICS

      • PARAMETER EDITOR

      • INDEX NOT USING

    • PARAMETER

  • Кнопки управления​

  • Рабочая область листа

  • Подсказчик кода

  • Меню рабочей области листа

Создание нового листа

​

1. Нажатием кнопки "New list "PostgreSQL""

2. File - New - Postgresql

3. Комбинация клавиш:  Shift + F3

Менеджер соединения с БД​

Вызов менеджера соединения с БД

• нажать кнопку "Show connection manager" в блоке "Connection control"

• комбинация клавиш Alt+F2  

Ранее сохраненные соединения отображаются в списке "Connection list"

Создание нового соединения

• "Vendor library (libpg.dll)" - полный путь к клиентской библиотеке для работы с postgreSQL. По умолчанию используется библиотека ".\DLL\PG\libpg.dll"

• "Alias*" - уникальный идентификатор, отображается в списке сохраненных соединений

• "Group name" - имя группы в списке. 

• "Server" - DNS имя сервера или его IP адрес

• "Port" - порт сервера на котором работает листенер PostgreSQL. По умолчанию порт 5432.

• "Database" - имя БД к которой производить подключение. По умолчанию: postgres

• "Login*" - имя пользователя. 

• "Password*" - пароль.

• "Charset" - кодировка, по умолчанию WIN1251. Поддерживаемые кодировки в 12 версии 

Кнопки управления

• "Test connect" - проверить соединение с БД PostgreSQL используя введенные параметры

• "Connect" - подключится к БД используя введенные параметры

• "Save" - сохранить соединение

• "Delete connect" - удалить ранее сохраненное соединение (удаляется соединение выделенное в списке "Connection list")

После подключения к СУБД, смена текущей базы выполняется в том числе, через выпадающий список "Current database".

​

​

Для отключения от БД всех соединений текущего листа нажать на кнопку "Disconnect current list" 

Кнопки блока "Management"

"Session manager" - список сессий в кластере PostgresSQL (открывается "Database manager", вкладка "Session list")

"Database statistics"  -  статистика работы баз на кластере PostgreSQL (открывается "Database manager", вкладка "Database statistics")

"Lock object list" - блокировки в базах данных

"Config" - просмотр pg_config

"File settings" - просмотр таблицы pg_file_settings

"Language" - установленные языки программирования

"Percentage before freezing" - сколько процентов осталось  до фриза таблицы в процентах

Дерево объектов

Дерево объектов - интерфейс для визуализации структуры кластера, управления объектами. 

Корневая запись дерева структурно состоит из имени БД к которой подключен лист, версии кластера и PID сессии в БД для основного процесса.

​

-----

Каждый лист создает несколько соединений с БД:

• соединение для отображения информации в дереве объектов

• соединение для выполнения SQL запросов, команд (основной процесс)

• соединение для выполнения SQL запросов, команд (дополнительный процесс)

DATABASE

Список баз созданных в кластере.

Текущая база помечается отдельным значком

->Запрос для извлечения данных<-

SELECT d.oid,

              d.datname AS "Name",
              r.rolname AS "Owner",
              pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding",
              t.spcname AS "Tablespace",
              pg_catalog.shobj_description(d.oid, 'pg_database') AS "Description",
              v.datname as "vacuum",
              case when current_database() =  d.datname then 1 else 0 end "current"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
  JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid
  LEFT JOIN pg_catalog.pg_stat_progress_vacuum v on v.datname=d.datname
 order by 1

-----

​

Для каждой БД на кластере выводится следующая информация:

• Владелец БД

• Кодировка базы

• Табличное пространство по умолчанию

• Комментарий к базе

• STATISTICS - статистика по использованию БД

->Запрос для извлечения данных<-

select d.datname,
       d.numbackends,
       d.xact_commit,
       d.xact_rollback,
       d.blks_read,
       d.blks_hit,
       d.tup_returned,
       d.tup_fetched,
       d.tup_inserted,
       d.tup_updated,
       d.tup_deleted,
       d.conflicts,
       d.temp_files,
       d.temp_bytes,
       d.deadlocks,
       d.blk_read_time,
       d.blk_write_time,
       d.stats_reset
  from pg_catalog.pg_stat_database d
 where d.datname =  $$DBNAME

-----

• VACUUM PROGRESS - состояние процессов выполняющих vacuum

->Запрос для извлечения данных<-

select (select relname from pg_catalog.pg_class where oid = v.relid) as "Table",
       v.phase,
       v.heap_blks_total,
       v.heap_blks_scanned,
       v.heap_blks_vacuumed,
       v.index_vacuum_count,
       v.max_dead_tuples,
       v.num_dead_tuples
  from pg_catalog.pg_stat_progress_vacuum v
 where v.datname = $$DBNAME

-----

​

PG_CATALOG

Список системных объектов схемы pg_catalog​

->Запрос для извлечения данных<-

SELECT oid,
       relname as "Name"
  FROM pg_catalog.pg_class
  where relnamespace = (select oid from                pg_catalog.pg_namespace  

where nspname = 'pg_catalog')
       and relkind in ('r','v','m')
  order by 2

-----

Меню

• Copy name - скопировать имя объекта в буфер обмена

• Create code [select] - сформировать текст запроса к объекту (во фразе select выводятся имена всех полей)

• Data - отобразить данные из таблицы (вкладка Data листа) 

INFORMATION_SCHEMA

Список системных представлений

->Запрос для извлечения данных<-

SELECT oid,
       relname as "Name"
  FROM pg_catalog.pg_class
  where relnamespace = (select oid from                pg_catalog.pg_namespace  

where nspname = 'information_schema')
       and relkind in ('r','v','m')
  order by 2

-----

Меню

• Copy name - скопировать имя объекта в буфер обмена

• Create code [select] - сформировать текст запроса к объекту (во фразе select выводятся имена всех полей)

• Data - отобразить данные из таблицы (вкладка Data листа) 

Узел OBJECT

Корневой узел, содержит группы основных объектов в БД. В скобках выводится имя БД. 

​

Список схем для всех дочерних узлов формируется запросом:

->Запрос для извлечения данных<-

SELECT n.oid, n.nspname AS "schema_name"
  FROM pg_catalog.pg_namespace n
 WHERE n.nspname  not like 'pg_%' and n.nspname <> 'information_schema'
ORDER BY 1

-----

​

TABLES

Список таблиц базы сгруппированный по схемам. 

Партицированные таблицы помечаются отдельным значком.

->Запрос для извлечения данных<-

SELECT c.oid, n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table'  WHEN 't' THEN 'TOAST' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partition_table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','t','f','p')
   AND c.relispartition = false
   and n.nspname='public'
ORDER BY c.relname

-----

​

Меню:

• Copy name - скопировать имя объекта в буфер обмена

• Create code [select] - сформировать текст запроса к объекту (во фразе select выводятся имена всех полей)

• Get code - сформировать код создания таблицы

• Get statistics - отобразить статистику использования таблицы. Данные выводятся на вкладке Statistics основного окна

• Reindex - перестроить все индексы на таблице (используется команда reindex)

• Data - отобразить данные таблицы (limit 50)

• COLUMN - список колон таблицы, выводится имя колонки и ее тип

->Запрос для извлечения данных<-

select column_name||'  ['||data_type||']' "col"
  from information_schema.columns
 where Upper(table_schema||'.'||table_name)=Upper($$TABLE_NAME) order by column_name

-----

• INDEX - список индексов на таблице. Для каждого индекса дополнительно выводится список колонок по которым он построен

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

->Запрос для извлечения данных<-

SELECT c.relname,
       i.indkey,
       a.attname
  FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_attribute a on  a.attrelid=c.oid
WHERE c.relkind IN ('i')
  AND n.nspname =$$SCHEMA_NAME
  and c2.relname =$$TABLE_NAME
ORDER BY 1

-----

• TRIGGER - список триггеров на таблице. 

->Запрос для извлечения данных<-

select t.trigger_name,
       t.event_manipulation,
       t.event_object_schema||'.'|| t.event_object_table "table",
       t.action_order,
       t.action_condition,
       t.action_statement,
       t.action_orientation,
       t.action_timing,
       t.action_reference_old_table,
       t.action_reference_new_table,
       t.action_reference_old_row,
       t.action_reference_new_row,
       t.created
  from information_schema.triggers t 
 where t.event_object_schema =  $$SCHEMA_NAME
   and t.event_object_table = $$TABLE_NAME

-----

• SIZE OBJECT - размер таблицы и всех ее индексов

->Запрос для извлечения данных<-

SELECT pg_size_pretty(pg_total_relation_size($$SCHEMA_NAME.$$TABLE_NAME)) "Size"

-----

• STATISTICS - статистика использования таблицы

->Запрос для извлечения данных<-

select t.seq_scan,
       t.seq_tup_read,
       t.idx_scan,
       t.idx_tup_fetch,
       t.n_tup_ins,
       t.n_tup_del,
       t.n_tup_hot_upd,
       t.n_live_tup,
       t.n_dead_tup,
       t.last_vacuum,
       t.last_autovacuum,
       t.last_analyze,
       t.last_autoanalyze
  from pg_catalog.pg_stat_user_tables t
 where t.schemaname = $$SCHEMA_NAME
   and t.relname = $$TABLE_NAME

-----

​

Для партицированных таблиц дополнительно выводятся

• PARKEY - ключ партицирования

->Запрос для извлечения данных<-

SELECT pg_catalog.pg_get_partkeydef($$TABLE_OID::pg_catalog.oid) "parkey"

-----

• PARTITION - список партиций таблицы

->Запрос для извлечения данных<-

SELECT c.oid::pg_catalog.regclass "oid",    c.relname,
       pg_catalog.pg_get_expr(c.relpartbound, c.oid) "rel",
       c.relkind
  FROM pg_catalog.pg_class c,
       pg_catalog.pg_inherits i
 WHERE c.oid=i.inhrelid AND i.inhparent = $$TABLE_OID
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',
         c.oid::pg_catalog.regclass::pg_catalog.text

-----

Для каждой партиции выводится информация как для обычных таблиц (COLUMN, INDEX, TRIGGER, SIZE OBJECT, STATISTICS) , а так же

• BOUND - фильтр на основе которого принимается решение по помещению записи в данную партицию 

​

Для сторонних таблиц дополнительно выводится:

• srvname - имя удаленного сервера

• ftoptions - параметры удаленной таблицы (имя схемы на удаленном сервере и имя таблицы)

• srvoptions - параметры удаленного сервера (имя сервера или его IP, port и имя базы данных)

->Запрос для извлечения данных<-

SELECT ft.ftrelid,
              ft.ftserver,
              ft.ftoptions::text,
              fs.srvname,
              fs.srvoptions::text
  FROM pg_foreign_table ft,
             pg_foreign_server fs
 where ft.ftserver = fs.oid
     and ft.ftrelid = $$OID_TABLE

-----

​

​

INDEX

Список индексов в БД сгруппированный по схемам

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

​

Список индексов

->Запрос для извлечения данных<-

SELECT c.relname as "Name", 
       c2.relname as "TabName",
       ns.nspname "Schema"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
     JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_namespace ns ON c2.relnamespace = ns.oid 
WHERE c.relkind IN ('i')
      AND n.nspname = $$SCHEMA_NAME
ORDER BY 1

-----

Размер индекса

->Запрос для извлечения данных<-

select pg_size_pretty(pg_relation_size($$SCHEMA_NAME.$$INDEX_NAME));

-----

Список колонок по которым построен индекс

->Запрос для извлечения данных<-

select
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
    and i.relname = $$INDEX_NAME
    and i.relnamespace = (select oid from pg_namespace where nspname = $$SCHEMA_NAME)
group by
    t.relname,
    i.relname

-----

​

​

VIEW

Список представлений в БД сгруппированный по схемам

Меню

• Copy name - скопировать имя view в буфер обмена

• Source - сформировать код создания представления

• Data - отобразить данные представления (limit 50)

​

​

Список представлений

->Запрос для извлечения данных<-

SELECT oid,
       relname as "Name"
  FROM pg_catalog.pg_class
 where relnamespace = (select oid from pg_catalog.pg_namespace  where nspname = $$SCHEMA_NAME)
   and relkind in ('v')

  order by 2

-----

​

Source

->Запрос для извлечения данных<-

select view_definition from information_schema.views where table_schema = $$SCHEMA_NAME and table_name = $$VIEW_NAME

-----

MATERIALIZED VIEW

Список материализованных представлений в БД сгруппированных по схемам

Меню:

• Copy name - скопировать имя материализованного представления в буфер обмена

• Refresh matview - сформировать и вывести в редактор команду на обновление данных материализованного представления

• Source - сформировать код создания материализованного представления

• Data - отобразить данные из материализованного представления (limit 50)

​

Список материализованных представлений

->Запрос для извлечения данных<-

SELECT oid,
       relname as "Name"
  FROM pg_catalog.pg_class
  where relnamespace = (select oid from pg_catalog.pg_namespace  where nspname = $$SCHEMA_NAME
    and relkind in ('m')
  order by 2 

-----

​

Source

->Запрос для извлечения данных<-

select definition from pg_catalog.pg_matviews m where schemaname = $$SCHEMA_NAME and matviewname = $$MATVIEWNAME

-----

FUNCTION

Список функций в БД сгруппированный по схемам

Меню

• Get code - извлечь код создания выбранной функции и добавить его в окно редактора запросов

​

Список функций

->Запрос для извлечения данных<-

SELECT p.oid, n.nspname AS schema_name 
      ,p.proname AS function_name
      ,pg_get_function_arguments(p.oid) AS args 
FROM pg_proc p 
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE     n.nspname = $$SCHEMA_NAME
  and prokind in ('f','a','w')
order by n.nspname
 

-----

​

Source

->Запрос для извлечения данных<-

select pg_get_functiondef($$OID_FUNCTION)

-----

PROCEDURE

Список процедур в БД сгруппированный по схемам.

Узел выводится для PostgreSQL 11 версии и старше. 

Меню

• Get code - извлечь код создания выбранной функции и добавить его в окно редактора запросов

​

Список функций

->Запрос для извлечения данных<-

SELECT p.oid, n.nspname AS schema_name 
      ,p.proname AS function_name
      ,pg_get_function_arguments(p.oid) AS args 
FROM pg_proc p 
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE     n.nspname = $$SCHEMA_NAME
  and prokind in ('p')
order by n.nspname

-----

​

Source

->Запрос для извлечения данных<-

select pg_get_functiondef($$OID_PROCEDURE)

-----

TRIGGER

Список триггеров в БД сгруппированный по схемам

Список триггеров

->Запрос для извлечения данных<-

select t.trigger_name,
       t.event_manipulation,
       t.event_object_schema||'.'|| t.event_object_table "table",
       t.action_order,
       t.action_condition,
       t.action_statement,
       t.action_orientation,
       t.action_timing,
       t.action_reference_old_table,
       t.action_reference_new_table,
       t.action_reference_old_row,
       t.action_reference_new_row,
       t.created
  from information_schema.triggers t 
 where t.trigger_schema =$$SCHEMA_NAME

-----

SEQUENCE

Список генераторов последовательности в БД сгруппированный по схемам

Список последовательностей

->Запрос для извлечения данных<-

select s.sequence_name,
       s.data_type,
       s.numeric_precision,
       s.numeric_precision_radix,
       s.numeric_scale,
       s.start_value,
       s.minimum_value,
       s.maximum_value,
       s.increment,
       s.cycle_option
  from information_schema.sequences s
 where  s.sequence_schema=$$SCHEMA_NAME

-----

DOMAIN

Список доменов в БД сгруппированный по схемам

Список доменов

->Запрос для извлечения данных<-

select d.domain_name,
       d.data_type,
       d.character_maximum_length,
       d.character_octet_length,
       d.character_set_catalog,
       d.character_set_schema,
       d.character_set_name,
       d.collation_catalog,
       d.collation_schema,
       d.collation_name,
       d.numeric_precision,
       d.numeric_precision_radix,
       d.numeric_scale,
       d.datetime_precision,
       d.interval_type,
       d.interval_precision,
       d.domain_default,
       d.udt_catalog,
       d.udt_schema,
       d.udt_name,
       d.scope_catalog,
       d.scope_schema,
       d.scope_name,
       d.maximum_cardinality,
       d.dtd_identifier
  from information_schema.domains d
 where d.domain_schema =$$SCHEMA_NAME

-----

EVENT TRIGGER

Список событийных триггеров в БД

Список событийных триггеров

->Запрос для извлечения данных<-

select t.oid,
       t.evtevent,
       p.proname,
       t.evtname,
       p.oid "proc_oid",
       case evtenabled when 'O' then 'Origin'
                       when 'D' then 'Disabled'
                       when 'R' then 'Replica'
                       when 'A' then 'Always'
       end "Modes"
  from pg_catalog.pg_event_trigger t,
       pg_catalog.pg_proc p
where t.evtfoid = p.oid

-----

EXTENSION

Список установленных дополнений

Список событийных триггеров

->Запрос для извлечения данных<-

select t.oid,
       t.evtevent,
       p.proname,
       t.evtname,
       p.oid "proc_oid",
       case evtenabled when 'O' then 'Origin'
                       when 'D' then 'Disabled'
                       when 'R' then 'Replica'
                       when 'A' then 'Always'
       end "Modes"
  from pg_catalog.pg_event_trigger t,
       pg_catalog.pg_proc p
where t.evtfoid = p.oid

-----

Меню

• View default manager - открыть менеджер управления установкой дополнений по умолчанию  

Менеджер позволяет устанавливать/удалять дополнения по умолчанию.

​

FOREIGN SERVER

Сторонний сервер. Используется в СУБД PostgreSQL для подключения к внешним серверам.

Список сторонних серверов

->Запрос для извлечения данных<-

select oid,
       srvname,
       srvtype,
       srvversion,
       srvacl::text,
       srvoptions::text
  from pg_catalog.pg_foreign_server 

-----

​

wrapper

->Запрос для извлечения данных<-

select w.oid,
       w.fdwname,
       (select u.usename from pg_catalog.pg_user u where u.usesysid =  w.fdwowner) "owner",
       (select p.proname from pg_catalog.pg_proc p where p.oid =  w.fdwhandler) "proc",
       (select p.proname from pg_catalog.pg_proc p where p.oid =  w.fdwvalidator) "validator",
       w.fdwacl::text,
       w.fdwoptions::text
  from pg_catalog.pg_foreign_data_wrapper w,
       pg_catalog.pg_foreign_server s
 where s.oid = $$OIDSERVER
   and s.srvfdw = w.oid

-----

Таблицы использующие данный "внешний сервер"

->Запрос для извлечения данных<-

SELECT (select (select n.nspname from pg_catalog.pg_namespace n where n.oid = c.relnamespace )||'.'||relname
         from pg_catalog.pg_class c
        where c.oid = f.ftrelid ) "table_name",
       ftrelid,
       ftserver,
       ftoptions::text
  FROM pg_foreign_table  f
 where ftserver =$$OID_SERVER

-----

​

MAPPING

->Запрос для извлечения данных<-

SELECT (select (select n.nspname from pg_catalog.pg_namespace n where n.oid = c.relnamespace )||'.'||relname
         from pg_catalog.pg_class c
        where c.oid = f.ftrelid ) "table_name",
       ftrelid,
       ftserver,
       ftoptions::text
  FROM pg_foreign_table  f
 where ftserver =$$OID_SERVER

-----

​

STANDBY STATUS

Состояние процессов трансляции операций на резервный сервер

Список сессий репликации операций

->Запрос для извлечения данных<-

SELECT pid,
       usesysid,
       usename,
       application_name,
       client_addr::text,
       client_hostname::text,
       client_port,
       backend_start,
       backend_xmin,
       state,                                                       
       sent_lsn::text,
       write_lsn::text,
       flush_lsn::text,
       replay_lsn::text,
       write_lag::text,
       flush_lag::text,
       replay_lag::text,
       sync_priority,
       sync_state,
       reply_time,
       pg_current_wal_lsn()::text "current_wal_lsn"
  FROM pg_stat_replication 

-----

TABLESPACE

Список созданных табличных пространств 

Список табличных пространств

->Запрос для извлечения данных<-

SELECT spcname AS "Name",
       pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
       pg_catalog.pg_tablespace_location(oid) AS "Location",
       pg_catalog.array_to_string(spcacl, E'\n') AS "Access privileges",
       spcoptions::text AS "Options",
       pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS "Size",
       pg_catalog.shobj_description(oid, 'pg_tablespace') AS "Description"
  FROM pg_catalog.pg_tablespace
ORDER BY 1

-----

​

По умолчанию размер пространства не выводится. Для включения вывода установить переключатель "Show the size of the db" в "List setings"

GROUPS

Список групп (или ролей) в кластере PostgreSQL. Группа от пользователя отличается только возможностью подключатся к кластеру.

Список групп (ролей)

->Запрос для извлечения данных<-

SELECT r.rolname,
       r.rolsuper::int,
       r.rolinherit::int,
       r.rolcreaterole::int,
       r.rolcreatedb::int,
       r.rolcanlogin::int,
       r.rolconnlimit,
       r.rolvaliduntil,
       ARRAY(SELECT b.rolname
           FROM pg_catalog.pg_auth_members m
           JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) 
              WHERE m.member = r.oid)::text as memberof,
       pg_catalog.shobj_description(r.oid, 'pg_authid') AS description, 
       r.rolreplication::int, 
       r.rolbypassrls::int
FROM pg_catalog.pg_roles r
  where rolcanlogin = false
ORDER BY 1 

-----

USERS

Список пользователей БД (роли которым разрешено подключатся к кластеру PostgreSQL)

Список пользователей 

->Запрос для извлечения данных<-

SELECT r.rolname,
       r.rolsuper::int,
       r.rolinherit::int,
       r.rolcreaterole::int,
       r.rolcreatedb::int,
       r.rolcanlogin::int,
       r.rolconnlimit,
       r.rolvaliduntil,
       ARRAY(SELECT b.rolname
           FROM pg_catalog.pg_auth_members m
           JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) 
              WHERE m.member = r.oid)::text as memberof,
       pg_catalog.shobj_description(r.oid, 'pg_authid') AS description, 
       r.rolreplication::int, 
       r.rolbypassrls::int
FROM pg_catalog.pg_roles r
  where rolcanlogin = true
ORDER BY 1 

-----

MONITOR

Основное средство управления и мониторинга работы кластера PostgreSQL

Для запуска дважды кликнуть по "MONITOR" или "PARAMETER"

​

DASHBOARD

Основная информация по нагрузке на БД. Данные обновляются 1 раз в сек. 

• Server session - информация по сессиям (всего, активных, простаивающих)

• Transaction per second - информация по транзакциям (активных, фиксирующих данные, выполняющих откат)

• Tuples In - данные по операциям изменяющим данные (вставка, обновление, удаление)

• Tuples Out - данные по операциям извлечения данных из базы (Количество строк, возвращённое запросами в этой базе данных; Количество строк, возвращённое запросами в этой базе данных)

• Block I/O - количество блоков прочитанных с диска или найденных в памяти

​

Для выбора базы по которой выводить статистику использовать выпадающий список "Base statistics". Доступно: имя базы или ALL - по всем суммарно

Управление сбором данных осуществляется через переключатель "Running"

->Запрос для извлечения данных<-

SELECT 'session_stats' AS chart_name, t.*
    FROM (SELECT  * from
       (SELECT 'Total'  "Name1", count(*) "COUNT1" FROM pg_stat_activity
          where datname = $$DB_NAME
       ) AS "1",
       (SELECT 'Active' "Name2", count(*) "COUNT2" FROM pg_stat_activity WHERE state = 'active'
          and datname = $$DB_NAME
       ) "2",
       (SELECT 'Idle'   "Name3", count(*) "COUNT3" FROM pg_stat_activity WHERE state = 'idle'
         and datname = $$DB_NAME
       ) "3"
    ) t
    UNION ALL
    SELECT 'tps_stats' AS chart_name, t.*
    FROM (SELECT * from
       (SELECT 'Transactions' "Name1", sum(xact_commit) + sum(xact_rollback) "COUNT1" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "1",
       (SELECT 'Commits' "Name2", sum(xact_commit) "COUNT2"  FROM pg_stat_database
         where datname = $$DB_NAME
       ) "2",
       (SELECT 'Rollbacks' "Name3", sum(xact_rollback) "COUNT3" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "3"
    ) t
    UNION ALL
    SELECT 'ti_stats' AS chart_name, t.*
    FROM (SELECT * from
       (SELECT 'Inserts' "Name1", sum(tup_inserted) "COUNT1" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "1",
       (SELECT 'Updates' "Name2", sum(tup_updated) "COUNT2" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "2",
       (SELECT 'Deletes' "Name3", sum(tup_deleted) "COUNT3" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "3"
    ) t
    UNION ALL
    SELECT 'to_stats' AS chart_name, t.*
    FROM (SELECT * from
       (SELECT 'Fetched' "Name1", sum(tup_fetched) "COUNT1" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "1",
       (SELECT 'Returned' "Name2", sum(tup_returned) "COUNT2" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "2",
       (SELECT '' "Name3", 0 "COUNT3") "3"
    ) t
    UNION ALL
    SELECT 'bio_stats' AS chart_name, t.*
    FROM (SELECT * from
       (SELECT 'Reads' "Name1", sum(blks_read) "COUNT1" FROM pg_stat_database
         where datname = $$DB_NAME
       )"1",
       (SELECT 'Hits' "Name2", sum(blks_hit) "COUNT2" FROM pg_stat_database
         where datname = $$DB_NAME
       ) "2",
       (SELECT '' "Name3", 0 "COUNT3") "3"
    ) t 

-----

​

SESSION LIST

Менеджер сессий в кластере.

Обновление данных выполняется в ручном режиме. 

Элементы управления

Выпадающий список "Query type" - вид SQL запроса используемого для извлечения данных

• Version >= 10

• Version <10

​

Version >= 10  - для баз версии 10 и старше

->Запрос для извлечения данных<-

SELECT
        datname,
        pid,
        usename,
        application_name,
        cast(client_addr as text),
        to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS backend_start,
        to_char(query_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS query_start,
        state,
        wait_event_type || ': ' || wait_event AS wait_event,
        cast(pg_blocking_pids(pid) as text) AS blocking_pids,
        query,
        backend_type
    FROM
        pg_stat_activity
        ORDER BY pid

-----

​

Version < 10  - баз версий меньше 10 

->Запрос для извлечения данных<-

SELECT
        datname,
        pid,
        usename,
        application_name,
        cast(client_addr as text),
        to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS backend_start, 
        to_char(query_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS query_start,
        state,
        '' wait_event,
        '---' blocking_pids,
        query,
        '9.0' backend_type
    FROM
        pg_stat_activity
        ORDER BY pid

-----

Обновить данные

Завершить выполнение текущего запроса без разрыва соединения (select pg_cancel_backend($$PID))

Завершить текущую сессию (select pg_terminate_backend($$PID))

При выделении сессии в списке "Session list" в поле "SQL Text" выводится текущий SQL запрос в сессии.

При переключении на вкладку "Query plan" выводится план выполнения запроса

​

​

LOCK

Блокировки в БД

->Запрос для извлечения данных<-

select l.locktype,
       d.datname,
       c.relname,
       l.page,
       l.tuple,
       l.transactionid,
       cl.relname,
       l.objid,
       l.pid,
       l.mode
  from pg_locks l left join pg_database d on l.database=d.oid
                  left join pg_class c on l.relation=c.oid
                  left join pg_class cl on l.classid=cl.oid
order by 2

-----

​

При выделении строки в списке "Lock list" в блоке "Process" выводится подробная информация по процессу.

Двойной клик по строке в поле "Process" отображает текст запроса в сессии

​

​

Server statistics

Различные статистики работы, полезная информация

Страница Database stats

->Запрос для извлечения данных<-

select l.locktype,
       d.datname,
       c.relname,
       l.page,
       l.tuple,
       l.transactionid,
       cl.relname,
       l.objid,
       l.pid,
       l.mode
  from pg_locks l left join pg_database d on l.database=d.oid
                  left join pg_class c on l.relation=c.oid
                  left join pg_class cl on l.classid=cl.oid
order by 2

-----

​

Страница "PG_CONFIG DATA"

Данные из системной таблицы pg_config

->Запрос для извлечения данных<-

select * from pg_config

-----

​

Страница "pg_file_settings"

Данные из системной таблицы pg_file_settings

->Запрос для извлечения данных<-

select f.sourcefile, f.name, f.setting, f.applied, f.error from pg_file_settings f

-----

​

Страница "Language support"

Список поддерживаемых языков программирования

->Запрос для извлечения данных<-

select l.lanname,
       a.rolname,
       l.lanispl,
       l.lanpltrusted,
       l.lanplcallfoid,
       l.laninline,
       l.lanvalidator
  from pg_language l left join pg_authid a on l.lanowner=a.oid

-----

​

Страница "Percentage before freezing"

На странице отображается информация о качестве работы процессов autovacuum. Процент в поле "Percentage before freezing" близкий к нулю говорит о том, что авто вакуум не успевает очищать таблицы и велика вероятность ситуации при которой потребуется переводить базу в однопользовательский режим и выполнять vacuum full.

При большом количестве таблиц с низким процентом необходимо проанализировать значения параметров авто вакуума на предмет их изменения на более агрессивные значения.

->Запрос для извлечения данных<-

with rel_frozen as (select c.oid::regclass as table_name,
                           (select e.nspname from pg_catalog.pg_namespace e where oid = c.relnamespace) as schema, 
                        greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age,
                        greatest(age(c.relfrozenxid), age(t.relfrozenxid))*100./(pow(2,31)-1) as perc_wrpa
from pg_class c left join pg_class t on c.reltoastrelid = t.oid
where c.relkind in ('r','m'))
select table_name::text "table", schema, 100 - round(perc_wrpa::numeric,2) "froz" from rel_frozen order by 3  des

-----

​

​

PARAMETER EDITOR

Редактор параметров кластера PostgreSQL

Поле "Filter" служит для фильтрации списка параметров по введённому тексту. Текст ищется по всем колонкам и в любой части строки

Поле "Category" служит для блочной фильтрации списка

​

Изменение значения параметра

• выделить параметр на вкладке "Server parameter"

• ввести новое значение в поле "Value"

• нажать кнопку "Apply"

​

->Запрос для извлечения данных<-

SELECT name,
       category,
       setting,
       unit,
       short_desc
  FROM pg_settings
order by category 

-----

Изменение значения

->Запрос для извлечения данных<-

alter system set $$PARAMETER_NAME = $$NEW_VALUE

SELECT pg_reload_conf()

-----

​

NOT USING INDEX

Список индексов к которым не было обращений или их количество меньше заданного в поле "show indices with hit less"

->Запрос для извлечения данных<-

select * from pg_catalog.pg_stat_all_indexes
  where idx_scan < $$NUMBER
    and schemaname not in ('pg_toast','pg_catalog')
order by idx_scan 

-----

Кнопки управления

• Execute current SQL - Выполнить текущий выделенный запрос или все запросы на листе 

Execute - аналогично нажатию на кнопку

To run the script from a file - выполнить скрипт с диска. Скрипт не загружается в редактор.

Check the script file for validate - проверить на корректность скрипт на диске без его выполнения

Show a list of command for execute - произвести разбор команд в редакторе на блоки выполнения. Выводится внутренний код и текст единичной команды

• Commit - зафиксировать изменения

• Rollback - откатить изменения внесенные в данные последними запросами

• Stop execute - остановить выполнение текущего запроса

• Close open cursor - закрыть текущий набор данных

• Show plan - показать план выполнения выделенного запроса

->Запрос для извлечения данных<-

EXPLAIN VERBOSE <query> 

-----

​

• Export data in file - выгрузка данных грида (видимой части данных) в файл на диске в:

Export data in XLS - выгрузить данные в файл в формате XLS

Export data in TXT - выгрузить данные в файл в формате TXT

Export data in CSV - выгрузить данные в файл в формате CSV

Export data in HTML - выгрузить данные в файл в формате HTML

Export data in RTF - выгрузить данные в файл в формате RTF

• Save current list in file - сохранить содержимое текущего листа в файл на диске

• Open file - открыть менеджер файлов

• List saving code - открыть менеджер создания блоков кода. Блоки кода - это конструкции кода сохраненные в программе, доступ к которым осуществляется из выпадающего меню кнопки

Создание блока кода

1. Нажать кнопку "New"

2. Из выпадающего списка "Parent name" выбрать имя корневого узла. Корневой узел так же является родителем в меню. Для добавления новой записи в список нажать кнопку "Add new parent" и внести новые значения

3. Из выпадающего списка "Page type" выбрать тип СУБД для которых должен отображаться блок кода в списке

4. В поле "Code" ввести текст блока кода, данный текст добавляется в редактор

5. Нажать кнопку "Save" 

• Project manager - открыть менеджер проектов

​

​

Рабочая область

Это поле на листе для ввода запросов/команд.

​

Разделение кода на составные части

Движок редактора запросов перед выполнением команд производит разбор кода, определяя составные части.

Для разделения кода используются специальные символы (; - точка с запятой, / - слеш, go - ключевое слово на отдельной строке).

​

Программа поддерживает два синтаксиса кода:

• Классический синтаксис Oracle: для отделения команд друг от друга используется символ ; (точка с запятой) за исключением команд create, begin, declate - данные команды отделяет от других символ / (слеш) на отдельной строке

• Синтаксис MSSQL Server: для отделения команд друг от друга используется ключевое слово go на отдельной строке

Разрешено использовать любой из двух синтаксисов, в том числе оба одновременно

Логирование выполняемых операций

Текст любой команды/запроса языка SQL после выполнения сохраняется в локальном хранилище (база Core - таблица cer_history_query). В базе фиксируется: текст запроса/команды фактически выполненной ядром программы, статус выполнения, время выполнения, количество измененных строк, сообщения БД (output)

Дополнительно к сохранению данных в БД прогресс выполнения каждой команды фиксируется на вкладке "Messages" листа: отображается статус выполнения команд, время выполнения, номера строк в редакторе на которых располагается выполненная команда. Данные вкладки очищаются перед запуском выполнения команд

Иконки в левой части окна отображают прогресс выполнения(выполненные команды, текущая команда, ожидают выполнения, ошибка выполнения запроса, строка с ошибкой в запросе)

​

Подсказчик кода

Подсказчик кода помогает ускорить написание запрос, подсказывая имена объектов, колонок.

Для схем - отображаются все объекты схемы

Для алиасов таблиц, представлений - список колонок

По части имени объекта - отображаются объекты в БД имя которых начинается с введенного текста. Подсказка отображается по комбинации клавиш Crtl+Space

Алиасы запросов

В программе для запросов можно назначить алиас и в последствии выполнять запрос по нему, подробно смотрите в разделе "Диспетчер алиасов запросов"

​

Меню рабочей области

• Copy - скопировать выделенный текст в буфер обмена

• Past - вставить содержимое буфера обмена в окно редактора

• Cut - вырезать выделенный текст

• SelectAll - выделить весь текст в рабочей области

• View table - просмотр подробной информации по выделенной таблице

• Get DDL - показать код создания выделенной таблицы

• Show command help - показать справку по выбранной команде

• Show object data - показать данные выделенной таблицы, представления, синонима

• Project manager -> Add object - создать страницу для выделенного кода в текущем проекте по умолчанию

• Project manager -> Search object card - найти карточку выделенного объекта в текущем проекте по умолчанию

• Comment -> Add current line - добавить символы комментария в начало текущей строки 

• Comment ->Remove current line - удалить символы комментария в начале текущей строки

• Comment -> Add selected text - добавить символы комментария в начало каждой из выделенных строк

• Comment -> Remove selected text - удалить символы комментария в начале каждой из выделенных строк