SQL-запросы получения метрик для обзорной панели#

SELECT setting, name FROM pg_settings WHERE name IN ('wal_keep_size', 'synchronous_commit', 'synchronous_standby_names');

SELECT setting, name FROM pg_settings WHERE name IN ('wal_keep_segments', 'synchronous_commit', 'synchronous_standby_names');

SELECT
  client_addr,
  state,
  pg_size_pretty(pg_current_wal_lsn() - replay_lsn) AS total_lag_bytes,
  slot_type,
  active
FROM
  pg_stat_replication stat
  LEFT JOIN pg_replication_slots slot ON stat.pid = slot.active_pid;

SHOW installer.cluster_type

SELECT
  current_setting('max_connections') AS max_connections,
  (current_setting('max_connections')::integer - count(*)::integer) AS available_connections,
  (current_setting('max_connections')::integer) - (current_setting('max_connections')::integer - count(*)::integer) AS used_connections,
  current_setting('superuser_reserved_connections') AS superuser_reserved_connections
FROM
  pg_stat_activity;

SELECT CASE WHEN (state IS NULL) THEN 'backend' ELSE state END, count(*) FROM pg_stat_activity GROUP BY state;

SELECT
  datname,
  client_addr,
  usename,
  CASE
    WHEN (state IS NULL) THEN 'system_process'
    ELSE state
  END,
  count(*) AS COUNT
FROM
  pg_stat_activity
GROUP BY
  datname,
  client_addr,
  usename,
  state
ORDER BY
  datname,
  client_addr;

SELECT
  (SELECT wal_bytes AS wal_amount FROM pg_stat_wal) AS wal_count,
  (SELECT sum(xact_commit + xact_rollback) FROM pg_stat_database) AS transaction_count,
  (SELECT sum(calls) s FROM pg_stat_statements) AS query_count;

SELECT
  (SELECT PG_CURRENT_WAL_LSN() - '0/0' AS wal_amount) AS wal_count,
  (SELECT sum(xact_commit + xact_rollback) FROM pg_stat_database) AS transaction_count,
  (SELECT sum(calls) s FROM pg_stat_statements) AS query_count;

SELECT (sum(total_exec_time) / sum(calls))::integer AS avg_time FROM pg_stat_statements;

SELECT (sum(total_time) / sum(calls))::integer AS avg_time FROM pg_stat_statements;

SELECT
  datname,
  pg_catalog.pg_get_userbyid (datdba) AS owner,
  pg_size_pretty(pg_catalog.pg_database_size (datname)) AS SIZE
FROM
  pg_catalog.pg_database
ORDER BY
  datname;

SELECT
  datname,
  pg_catalog.pg_get_userbyid (datdba) AS owner,
  pg_catalog.pg_encoding_to_char (encoding) AS encoding,
  datcollate,
  datctype,
  datallowconn,
  datconnlimit,
  pg_size_pretty(pg_catalog.pg_database_size (datname)) AS size,
  t.spcname AS tablespace,
  CASE
    WHEN pg_catalog.pg_tablespace_location (t.oid) = '' THEN 'default'
    ELSE pg_catalog.pg_tablespace_location (t.oid)
  END AS location,
  pg_catalog.shobj_description (d.oid, 'pg_database') AS description
FROM
  pg_catalog.pg_database d
  JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid
ORDER BY
  datname;

SELECT
  round(
    (
      100 * sum(blks_hit) / (sum(blks_hit) + sum(blks_read))
    )::numeric,
    1
  ) AS cache_hit_ratio,
  round(
    (
      100 * sum(xact_commit) / (sum(xact_commit) + sum(xact_rollback))
    )::numeric,
    1
  ) AS commit_ratio,
  sum(xact_commit)::bigint AS commit_sum,
  sum(xact_rollback)::bigint AS rollback_sum
FROM
  pg_stat_database;

SELECT
  EXTRACT(
    epoch
    FROM
      (
        CASE
          WHEN STATE = 'active' THEN age (NOW(), query_start)
        END
      )
  )::integer AS active
FROM
  pg_stat_activity
WHERE
  backend_type = 'client backend'
  AND STATE = 'active'
  AND (NOW() - xact_start) > TIME '00:01:00'
ORDER BY
  active DESC
LIMIT
  1;

SELECT
  EXTRACT(
    epoch
    FROM
      (
        CASE
          WHEN STATE = 'idle in transaction' THEN age (NOW(), query_start)
        END
      )
  )::integer AS idle
FROM
  pg_stat_activity
WHERE
  backend_type = 'client backend'
  AND STATE = 'idle in transaction'
  AND (NOW() - xact_start) > TIME '00:00:01'
ORDER BY
  idle DESC
LIMIT
  1;

SELECT
  EXTRACT(
    epoch
    FROM
      (
        CASE
          WHEN STATE = 'active' THEN age (NOW(), query_start)
        END
      )
  )::integer AS active,
  datname,
  usename,
  client_addr,
  application_name,
  pid,
  client_port,
  query,
  STATE
FROM
  pg_stat_activity
WHERE
  backend_type = 'client backend'
  AND STATE = 'active'
  AND (NOW() - xact_start) > TIME '00:01:00'
ORDER BY
  active DESC
LIMIT
  10;

SELECT
  EXTRACT(
    epoch
    FROM
      (
        CASE
          WHEN STATE = 'idle in transaction' THEN age (NOW(), query_start)
        END
      )
  )::integer AS idle,
  datname,
  usename,
  client_addr,
  application_name,
  pid,
  client_port,
  query,
  STATE
FROM
  pg_stat_activity
WHERE
  backend_type = 'client backend'
  AND STATE = 'idle in transaction'
  AND (NOW() - xact_start) > TIME '00:00:01'
ORDER BY
  idle DESC
LIMIT
  10;

SELECT CAST(pg_current_wal_insert_lsn() AS VARCHAR) AS current_lsn;

SELECT max(age(datfrozenxid)) frozen_age FROM pg_database;

SELECT
  name,
  setting
FROM
  pg_settings
WHERE
  name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'autovacuum_max_workers',
    'wal_level'
  )
ORDER BY
  name;

SELECT
  name,
  unit,
  setting,
  context,
  vartype,
  source,
  boot_val,
  reset_val
FROM
  pg_settings
ORDER BY
  name;

SELECT pg_size_pretty(sum(temp_bytes)) AS temp_file_size FROM pg_stat_database;

SELECT max(last_autovacuum) last_autovacuum FROM pg_stat_all_tables;

SELECT
  round(
    100.0 * buffers_checkpoint / NULLIF(
      (
        buffers_checkpoint + buffers_clean + buffers_backend
      ),
      0
    ),
    1
  ) AS clean_by_chkp
FROM
  pg_stat_bgwriter;

WITH
  chkp AS (
    SELECT
      buffers_written::numeric AS buffers_checkpoint
    FROM
      pg_stat_checkpointer
  ),
  bgw AS (
    SELECT
      buffers_clean::numeric
    FROM
      pg_stat_bgwriter
  ),
  io AS (
    SELECT
      SUM(writes * op_bytes)::numeric AS buffers_backend
    FROM
      pg_stat_io
    WHERE
      backend_type = 'client backend'
      AND context IN ('normal', 'vacuum')
  )
SELECT
  ROUND(
    100.0 * chkp.buffers_checkpoint / NULLIF(
      (
        chkp.buffers_checkpoint + bgw.buffers_clean + io.buffers_backend
      ),
      0
    ),
    1
  ) AS clean_by_chkp
FROM
  chkp,
  bgw,
  io;

WITH
  chkp AS (
    SELECT
      buffers_written::numeric AS buffers_checkpoint
    FROM
      pg_stat_checkpointer
  ),
  bgw AS (
    SELECT
      buffers_clean::numeric
    FROM
      pg_stat_bgwriter
  ),
  io AS (
    SELECT
      SUM(write_bytes)::numeric AS buffers_backend
    FROM
      pg_stat_io
    WHERE
      backend_type = 'client backend'
      AND context IN ('normal', 'vacuum')
  )
SELECT
  ROUND(
    100.0 * chkp.buffers_checkpoint / NULLIF(
      (
        chkp.buffers_checkpoint + bgw.buffers_clean + io.buffers_backend
      ),
      0
    ),
    1
  ) AS clean_by_chkp
FROM
  chkp,
  bgw,
  io;

SELECT version() AS version, 
(SELECT 'Platform V Pangolin' AS edition WHERE
EXISTS (SELECT * FROM pg_catalog.pg_proc
WHERE proname='sber_version'));

SELECT
  now() - pg_postmaster_start_time() AS uptime,
  pg_postmaster_start_time() AS boot_time,
  now() - pg_conf_load_time() AS conf_reload_time,
  pg_is_in_recovery() AS is_in_recovery;