监控系统部署

如何使用Pigsty监控已有的PostgreSQL实例？如RDS for PG

对于由Pigsty所创建的实例，所有监控组件均已自动配置妥当。但对于非Pigsty所创建的现存Pigsty实例，若希望使用Pigsty监控系统的部分对其监控，则需一些额外的配置。

太长；不看

  1. 在目标实例创建监控对象：监控对象配置

  2. 在配置清单中声明该集群：

    1. pg-test:
    2.   hosts:                                # 为每个实例分配唯一本地端口
    3.     10.10.10.11: { pg_seq: 1, pg_role: primary , pg_exporter_port: 20001}
    4.     10.10.10.12: { pg_seq: 2, pg_role: replica , pg_exporter_port: 20002}
    5.     10.10.10.13: { pg_seq: 3, pg_role: offline , pg_exporter_port: 20003}
    6.   vars:
    7.     pg_cluster: pg-test                 # 填入集群名称
    8.     pg_version: 14                      # 填入数据库大版本
    9.     pg_databases: [{ name: test }]      # 填入数据库列表（每个数据库对象作为一个数组元素）
    11. # 在全局/集群/实例配置中提供监控用户密码 pg_monitor_username/pg_monitor_password

  3. 针对该集群执行剧本：./pgsql-monly.yml -l pg-test

  4. 该剧本会在Grafana中注册目标PostgreSQL数据源，因此PGCAT功能完整可用。该剧本会在元节点本地部署PG Exporter监控远程PG实例，故PGSQL中纯数据库相关指标可用。但主机节点、连接池、负载均衡、高可用Patroni相关指标则不可用。

监控部署概述

如果用户只希望使用Pigsty的监控系统部分，比如希望使用Pigsty监控系统监控已有的PostgreSQL实例，那么可以使用 仅监控部署（monitor only） 模式。仅监控模式下，您可以使用Pigsty管理监控其他PostgreSQL实例（目前默认支持10+以上的版本，更老的版本可以通过手工修改 pg_exporter 配置文件支持）

首先，您需要在1台元节点上完成标准的Pigsty的标准安装流程，然后便可以将更多的数据库实例接入监控。按照目标数据库节点的访问权限，又可以分为两种情况：

如果目标节点可被管理

如果目标DB节点可以被Pigsty所管理（ssh可达，sudo可用），那么您可以使用 pgsql.yml 剧本中的pg-exporter任务，使用相同的的方式，在目标节点上部署监控组件：PG Exporter， 您也可以使用该剧本的其他任务，在已有实例节点上部署额外的组件及其监控：连接池Pgbouncer与负载均衡器HAProxy。此外，您也可以使用 nodes.yml 中的 node-exporter与 promtail 任务，部署主机节点监控与日志收集组件。从而获得与原生Pigsty数据库实例完全一致的使用体验。

因为目标数据库集群已存在，您需要参考本节的内容手工在目标数据库集群上创建监控用户、模式与扩展。其余流程与完整部署并无区别。

  1. # 修改pigsty配置参数，在节点上添加yum repo，然后通过yum安装软件包
  2. exporter_install: yum # none|yum|binary, none by default
  3. exporter_repo_url: http://<your primary ip address>/pigsty.repo
  5. ./nodes.yml -l <yourcluster> -t node-exporter  # 部署节点指标监控
  6. ./nodes.yml -l <yourcluster> -t promtail       # 部署节点日志收集
  7. ./pgsql.yml -l <yourcluster> -t pg-exporter    # 部署PG指标监控收集

如果只有数据库连接串

如果您只能通过PGURL（数据库连接串）的方式访问目标数据库，则可以考虑使用仅监控模式/精简模式（Monitor Only：Monly）监控目标数据库。在此模式下，所有监控组件均部署在安装Pigsty的元节点上。监控系统不会有 节点，连接池，负载均衡器，高可用组件的相关指标，但数据库本身，以及数据目录（Catalog）中的实时状态信息仍然可用。

为了执行精简监控部署，您同样需要参考本节的内容手工在目标数据库集群上创建监控用户、模式与扩展，并确保可以从元节点上使用监控用户访问目标数据库。此后，针对目标集群执行 pgsql-monly.yml剧本即可完成部署。

本文着重介绍此种监控部署模式。

监控系统 - 图1

图：仅监控模式架构示意图，部署于管理机本地的多个PG Exporter用于监控多个远程数据库实例。

精简部署与标准部署的区别

Pigsty监控系统由三个核心模块组成：

事项\等级L1L2L3
名称基础部署托管部署完整部署
英文basicmanagedfull
场景只有连接串DB已存在，节点可管理实例由Pigsty创建
PGCAT功能✅ 完整可用✅ 完整可用✅ 完整可用
PGSQL功能✅ 限PG指标✅ 限PG与节点指标✅ 完整功能
连接池指标❌ 不可用⚠️ 选装✅ 预装项
负载均衡器指标❌ 不可用⚠️ 选装✅ 预装项
PGLOG功能❌ 不可用⚠️ 选装✅ 预装项
PG Exporter⚠️ 部署于元节点✅ 部署于DB节点✅ 部署于DB节点
Node Exporter❌ 不部署✅ 部署于DB节点✅ 部署于DB节点
侵入DB节点✅ 无侵入⚠️ 安装Exporter⚠️ 完全由Pigsty管理
监控现有实例✅ 可支持✅ 可支持❌ 仅用于Pigsty托管实例
监控用户与视图人工创建人工创建Pigsty自动创建
部署使用剧本pgsql-monly.ymlpgsql.yml -t pg-exporter,promtail
nodes.yml -t node-exporter		pgsql.yml -t pg-exporter
nodes.yml -t node-exporter
所需权限元节点可达的PGURLDB节点ssh与sudo权限DB节点ssh与sudo权限
功能概述基础功能：PGCAT+PGSQL大部分功能完整功能

监控已有实例：精简模式

为数据库实例部署监控系统分为三步：准备监控对象，修改配置清单，执行部署剧本。

准备监控对象

为了将外部现存PostgreSQL实例纳入监控，您需要有一个可用于访问该实例/集群的连接串。任何可达连接串（业务用户，超级用户）均可使用，但我们建议使用一个专用监控用户以避免权限泄漏。

  • 监控用户：默认使用的用户名为 dbuser_monitor， 该用户需要属于 pg_monitor 角色组，或确保具有相关视图访问权限。
  • 监控认证：默认使用密码访问，您需要确保HBA策略允许监控用户从管理机或DB节点本地访问数据库。
  • 监控模式：固定使用名称 monitor，用于安装额外的监控视图与扩展插件，非必选，但强烈建议创建。
  • 监控扩展：强烈建议启用PG自带的监控扩展 pg_stat_statements

关于监控对象的准备细节，请参考文后：监控对象配置 一节。

修改配置清单

如同部署一个全新的Pigsty实例一样，您需要在配置清单（配置文件或CMDB）中声明该目标集群。例如，为集群与实例指定身份标识。不同之处在于，您还需要在实例层次为每一个实例手工分配一个唯一的本地端口号（ pg_exporter_port）。

下面是一个数据库集群声明样例：

  1. pg-test:
  2.   hosts:                                # 为每个实例分配唯一本地端口
  3.     10.10.10.11: { pg_seq: 1, pg_role: primary , pg_exporter_port: 20001}
  4.     10.10.10.12: { pg_seq: 2, pg_role: replica , pg_exporter_port: 20002}
  5.     10.10.10.13: { pg_seq: 3, pg_role: offline , pg_exporter_port: 20003}
  6.   vars:
  7.     pg_cluster: pg-test                 # 填入集群名称
  8.     pg_version: 14                      # 填入数据库大版本
  9.     pg_databases: [{ name: test }]      # 填入数据库列表（每个数据库对象作为一个数组元素）
  11. # 在全局/集群/实例配置中提供监控用户密码 pg_monitor_username/pg_monitor_password

注，即使您通过域名访问数据库，依然需要通过填入实际IP地址的方式来声明数据库集群。

若要启用PGCAT功能，您需要显式在 pg_databases 中列出目标集群的数据库名称列表，在此列表中的数据库将被注册为Grafana的数据源，您可以直接通过Grafana访问该实例的Catalog数据。若您不希望使用PGCAT相关功能，不设置该变量，或置为空数组即可。

连接信息

说明：Pigsty将默认使用以下规则生成监控连接串。但参数 pg_exporter_url 存在时，将直接覆盖拼接连接串。

  1. postgres://{{ pg_monitor_username }}:{{ pg_monitor_password }}@{{ inventory_hostname }}:{{ pg_port }}/postgres?sslmode=disable

您可以在全局使用统一的监控用户/密码设置，或者在集群层面或实例层次根据实际情况按需配置以下连接参数

  1. pg_monitor_username: dbuser_monitor  # 监控用户名，若使用全局统一配置则无需在此配置
  2. pg_monitor_password: DBUser.Monitor  # 监控用户密码，若使用全局统一配置则无需在此配置
  3. pg_port: 5432                        # 若使用非标准的数据库端口，在此修改

示例：在实例层面指定连接信息 ```yaml pg-test: hosts: # Specify the access URL for the instance 10.10.10.11: pg_seq: 1 pg_role: primary pg_exporter_port: 20001 pg_monitor_username: monitor_user1 pg_monitor_password: monitor_pass1 10.10.10.12: pg_seq: 2 pg_role: replica pg_exporter_port: 20002 # Specify pg_exporter_url directly pg_exporter_url: ‘postgres://someuser:pass@rds.pg.hongkong.xxx:5432/postgres?sslmode=disable’’ 10.10.10.13: pg_seq: 3 pg_role: offline pg_exporter_port: 20003 pg_monitor_username: monitor_user3 pg_monitor_password: monitor_pass3 vars: pg_cluster: pg-test # Fill in cluster name pg_version: 14 # Fill in the major version of the database pg_databases: [{ name: test }] # Fill in the database list (each database object as an array element) ```

执行部署剧本

集群声明完成后，将其纳入监控非常简单，在元节点上针对目标集群使用剧本 pgsql-monly.yml 即可：

  1. ./pgsql-monly.yml -l <cluster>     # 在指定集群上完成监控部署

监控已有实例：托管部署

在托管部署模式下，目标DB节点可以被Pigsty所管理（ssh可达，sudo可用），用户将在已有的节点上加装以下监控组件：promtail, node_exporter, pg_exporter。

您可以使用 nodes.yml中的node-exporter任务，以及 pgsql.yml 剧本中的pg-exporter任务，在目标节点上部署监控组件：node_exporter 与 pg_exporter：

因为目标数据库集群已存在，您需要在目标数据库集群上创建监控用户、模式与扩展。

  1. # 修改pigsty配置参数，在节点上添加yum repo，然后通过yum安装软件包
  2. exporter_install: yum # none|yum|binary, none by default
  3. exporter_repo_url: http://<your primary ip address>/pigsty.repo
  5. ./nodes.yml -l <yourcluster> -t promtail       # 部署节点日志收集（可选，注意日志位置）
  6. ./nodes.yml -l <yourcluster> -t node-exporter  # 部署节点指标监控
  7. ./pgsql.yml -l <yourcluster> -t pg-exporter    # 部署PG指标监控收集

当exporter_install的值为yum时，Pigsty会从 exporter_repo_url 指定的URL下载Repo文件至节点本地的/etc/yum.repos.d中。通常您应当填入管理节点上的Pigsty本地源地址，例如：http://10.10.10.10/pigsty.repo。

监控对象配置

如何在已有实例上配置监控所需的用户，模式，扩展、视图与函数。

监控用户

以Pigsty默认使用的监控用户dbuser_monitor为例，在目标数据库集群创建以下用户。

  1. CREATE USER dbuser_monitor;
  2. GRANT pg_monitor TO dbuser_monitor;
  3. COMMENT ON ROLE dbuser_monitor IS 'system monitor user';
  4. ALTER USER dbuser_monitor SET log_min_duration_statement = 1000;
  5. ALTER USER dbuser_monitor PASSWORD 'DBUser.Monitor'; -- 按需修改监控用户密码（建议修改！！）

请注意，这里创建的监控用户与密码需要与 pg_monitor_username与pg_monitor_password 保持一致。

配置数据库 pg_hba.conf 文件，添加以下规则以允许监控用户从本地，以及管理机使用密码访问数据库。

  1. # allow local role monitor with password
  2. local   all  dbuser_monitor                    md5
  3. host    all  dbuser_monitor  127.0.0.1/32      md5
  4. host    all  dbuser_monitor  <管理机器IP地址>/32 md5

监控模式

监控模式与扩展是可选项，即使没有，Pigsty监控系统的主体也可以正常工作，但我们强烈建议创建监控模式，并至少启用PG官方自带的 pg_stat_statements，该扩展提供了关于查询性能的重要数据。注意：该扩展必须列入数据库参数shared_preload_libraries 中方可生效，修改该参数需要重启数据库。

创建扩展模式：

  1. CREATE SCHEMA IF NOT EXISTS monitor;               -- 创建监控专用模式
  2. GRANT USAGE ON SCHEMA monitor TO dbuser_monitor;   -- 允许监控用户使用

监控扩展

创建扩展插件：

  1. -- 强烈建议启用 pg_stat_statements 扩展
  2. CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "monitor";
  4. -- 可选的其他扩展
  5. CREATE EXTENSION IF NOT EXISTS "pgstattuple" WITH SCHEMA "monitor";
  6. CREATE EXTENSION IF NOT EXISTS "pg_qualstats" WITH SCHEMA "monitor";
  7. CREATE EXTENSION IF NOT EXISTS "pg_buffercache" WITH SCHEMA "monitor";
  8. CREATE EXTENSION IF NOT EXISTS "pageinspect" WITH SCHEMA "monitor";
  9. CREATE EXTENSION IF NOT EXISTS "pg_prewarm" WITH SCHEMA "monitor";
  10. CREATE EXTENSION IF NOT EXISTS "pg_visibility" WITH SCHEMA "monitor";
  11. CREATE EXTENSION IF NOT EXISTS "pg_freespacemap" WITH SCHEMA "monitor";

监控视图

监控视图提供了若干常用的预处理结果，并对某些需要高权限的监控指标进行权限封装（例如共享内存分配），便于查询与使用。强烈建议在所有需要监控的数据库中创建

监控模式与监控视图定义

  1. --==================================================================--
  2. --                            Monitor Schema                        --
  3. --==================================================================--
  5. ----------------------------------------------------------------------
  6. -- cleanse
  7. ----------------------------------------------------------------------
  8. CREATE SCHEMA IF NOT EXISTS monitor;
  9. GRANT USAGE ON SCHEMA monitor TO dbuser_monitor;
  10. GRANT USAGE ON SCHEMA monitor TO "{{ pg_admin_username }}";
  11. GRANT USAGE ON SCHEMA monitor TO "{{ pg_replication_username }}";
  13. --==================================================================--
  14. --                            Monitor Views                         --
  15. --==================================================================--
  17. ----------------------------------------------------------------------
  18. -- Table bloat estimate : monitor.pg_table_bloat
  19. ----------------------------------------------------------------------
  20. DROP VIEW IF EXISTS monitor.pg_table_bloat CASCADE;
  21. CREATE OR REPLACE VIEW monitor.pg_table_bloat AS
  22. SELECT CURRENT_CATALOG AS datname, nspname, relname , tblid , bs * tblpages AS size,
  23.        CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff)/tblpages::FLOAT ELSE 0 END AS ratio
  24. FROM (
  25.          SELECT ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
  26.                 tblpages, fillfactor, bs, tblid, nspname, relname, is_na
  27.          FROM (
  28.                   SELECT
  29.                       ( 4 + tpl_hdr_size + tpl_data_size + (2 * ma)
  30.                           - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
  31.                           - CASE WHEN ceil(tpl_data_size)::INT % ma = 0 THEN ma ELSE ceil(tpl_data_size)::INT % ma END
  32.                           ) AS tpl_size, (heappages + toastpages) AS tblpages, heappages,
  33.                       toastpages, reltuples, toasttuples, bs, page_hdr, tblid, nspname, relname, fillfactor, is_na
  34.                   FROM (
  35.                            SELECT
  36.                                tbl.oid AS tblid, ns.nspname , tbl.relname, tbl.reltuples,
  37.                                tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
  38.                                coalesce(toast.reltuples, 0) AS toasttuples,
  39.                                coalesce(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
  40.                                current_setting('block_size')::numeric AS bs,
  41.                                CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
  42.                                24 AS page_hdr,
  43.                                23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
  44.                                    + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
  45.                                sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
  46.                                bool_or(att.atttypid = 'pg_catalog.name'::regtype)
  47.                                    OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
  48.                            FROM pg_attribute AS att
  49.                                     JOIN pg_class AS tbl ON att.attrelid = tbl.oid
  50.                                     JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
  51.                                     LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
  52.                                     LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
  53.                            WHERE NOT att.attisdropped AND tbl.relkind = 'r' AND nspname NOT IN ('pg_catalog','information_schema')
  54.                            GROUP BY 1,2,3,4,5,6,7,8,9,10
  55.                        ) AS s
  56.               ) AS s2
  57.      ) AS s3
  58. WHERE NOT is_na;
  59. COMMENT ON VIEW monitor.pg_table_bloat IS 'postgres table bloat estimate';
  61. ----------------------------------------------------------------------
  62. -- Index bloat estimate : monitor.pg_index_bloat
  63. ----------------------------------------------------------------------
  64. DROP VIEW IF EXISTS monitor.pg_index_bloat CASCADE;
  65. CREATE OR REPLACE VIEW monitor.pg_index_bloat AS
  66. SELECT CURRENT_CATALOG AS datname, nspname, idxname AS relname, tblid, idxid, relpages::BIGINT * bs AS size,
  67.        COALESCE((relpages - ( reltuples * (6 + ma - (CASE WHEN index_tuple_hdr % ma = 0 THEN ma ELSE index_tuple_hdr % ma END)
  68.                                                + nulldatawidth + ma - (CASE WHEN nulldatawidth % ma = 0 THEN ma ELSE nulldatawidth % ma END))
  69.                                   / (bs - pagehdr)::FLOAT  + 1 )), 0) / relpages::FLOAT AS ratio
  70. FROM (
  71.          SELECT nspname,idxname,indrelid AS tblid,indexrelid AS idxid,
  72.                 reltuples,relpages,
  73.                 current_setting('block_size')::INTEGER                                                               AS bs,
  74.                 (CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END)  AS ma,
  75.                 24                                                                                                   AS pagehdr,
  76.                 (CASE WHEN max(COALESCE(pg_stats.null_frac, 0)) = 0 THEN 2 ELSE 6 END)                               AS index_tuple_hdr,
  77.                 sum((1.0 - COALESCE(pg_stats.null_frac, 0.0)) *
  78.                     COALESCE(pg_stats.avg_width, 1024))::INTEGER                                                     AS nulldatawidth
  79.          FROM pg_attribute
  80.                   JOIN (
  81.              SELECT pg_namespace.nspname,
  82.                     ic.relname                                                   AS idxname,
  83.                     ic.reltuples,
  84.                     ic.relpages,
  85.                     pg_index.indrelid,
  86.                     pg_index.indexrelid,
  87.                     tc.relname                                                   AS tablename,
  88.                     regexp_split_to_table(pg_index.indkey::TEXT, ' ') :: INTEGER AS attnum,
  89.                     pg_index.indexrelid                                          AS index_oid
  90.              FROM pg_index
  91.                       JOIN pg_class ic ON pg_index.indexrelid = ic.oid
  92.                       JOIN pg_class tc ON pg_index.indrelid = tc.oid
  93.                       JOIN pg_namespace ON pg_namespace.oid = ic.relnamespace
  94.                       JOIN pg_am ON ic.relam = pg_am.oid
  95.              WHERE pg_am.amname = 'btree' AND ic.relpages > 0 AND nspname NOT IN ('pg_catalog', 'information_schema')
  96.          ) ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
  97.                   JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
  98.              AND ((pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
  99.                  OR (pg_stats.tablename = ind_atts.idxname AND pg_stats.attname = pg_attribute.attname))
  100.          WHERE pg_attribute.attnum > 0
  101.          GROUP BY 1, 2, 3, 4, 5, 6
  102.      ) est;
  103. COMMENT ON VIEW monitor.pg_index_bloat IS 'postgres index bloat estimate (btree-only)';
  106. ----------------------------------------------------------------------
  107. -- Relation Bloat : monitor.pg_bloat
  108. ----------------------------------------------------------------------
  109. DROP VIEW IF EXISTS monitor.pg_bloat CASCADE;
  110. CREATE OR REPLACE VIEW monitor.pg_bloat AS
  111. SELECT coalesce(ib.datname, tb.datname)                                                   AS datname,
  112.        coalesce(ib.nspname, tb.nspname)                                                   AS nspname,
  113.        coalesce(ib.tblid, tb.tblid)                                                       AS tblid,
  114.        coalesce(tb.nspname || '.' || tb.relname, ib.nspname || '.' || ib.tblid::RegClass) AS tblname,
  115.        tb.size                                                                            AS tbl_size,
  116.        CASE WHEN tb.ratio < 0 THEN 0 ELSE round(tb.ratio::NUMERIC, 6) END                 AS tbl_ratio,
  117.        (tb.size * (CASE WHEN tb.ratio < 0 THEN 0 ELSE tb.ratio::NUMERIC END)) ::BIGINT    AS tbl_wasted,
  118.        ib.idxid,
  119.        ib.nspname || '.' || ib.relname                                                    AS idxname,
  120.        ib.size                                                                            AS idx_size,
  121.        CASE WHEN ib.ratio < 0 THEN 0 ELSE round(ib.ratio::NUMERIC, 5) END                 AS idx_ratio,
  122.        (ib.size * (CASE WHEN ib.ratio < 0 THEN 0 ELSE ib.ratio::NUMERIC END)) ::BIGINT    AS idx_wasted
  123. FROM monitor.pg_index_bloat ib
  124.          FULL OUTER JOIN monitor.pg_table_bloat tb ON ib.tblid = tb.tblid;
  126. COMMENT ON VIEW monitor.pg_bloat IS 'postgres relation bloat detail';
  129. ----------------------------------------------------------------------
  130. -- monitor.pg_index_bloat_human
  131. ----------------------------------------------------------------------
  132. DROP VIEW IF EXISTS monitor.pg_index_bloat_human CASCADE;
  133. CREATE OR REPLACE VIEW monitor.pg_index_bloat_human AS
  134. SELECT idxname                            AS name,
  135.        tblname,
  136.        idx_wasted                         AS wasted,
  137.        pg_size_pretty(idx_size)           AS idx_size,
  138.        round(100 * idx_ratio::NUMERIC, 2) AS idx_ratio,
  139.        pg_size_pretty(idx_wasted)         AS idx_wasted,
  140.        pg_size_pretty(tbl_size)           AS tbl_size,
  141.        round(100 * tbl_ratio::NUMERIC, 2) AS tbl_ratio,
  142.        pg_size_pretty(tbl_wasted)         AS tbl_wasted
  143. FROM monitor.pg_bloat
  144. WHERE idxname IS NOT NULL;
  145. COMMENT ON VIEW monitor.pg_index_bloat_human IS 'postgres index bloat info in human-readable format';
  147. ----------------------------------------------------------------------
  148. -- monitor.pg_table_bloat_human
  149. ----------------------------------------------------------------------
  150. DROP VIEW IF EXISTS monitor.pg_table_bloat_human CASCADE;
  151. CREATE OR REPLACE VIEW monitor.pg_table_bloat_human AS
  152. SELECT tblname                                          AS name,
  153.        idx_wasted + tbl_wasted                          AS wasted,
  154.        pg_size_pretty(idx_wasted + tbl_wasted)          AS all_wasted,
  155.        pg_size_pretty(tbl_wasted)                       AS tbl_wasted,
  156.        pg_size_pretty(tbl_size)                         AS tbl_size,
  157.        tbl_ratio,
  158.        pg_size_pretty(idx_wasted)                       AS idx_wasted,
  159.        pg_size_pretty(idx_size)                         AS idx_size,
  160.        round(idx_wasted::NUMERIC * 100.0 / idx_size, 2) AS idx_ratio
  161. FROM (SELECT datname,
  162.              nspname,
  163.              tblname,
  164.              coalesce(max(tbl_wasted), 0)                         AS tbl_wasted,
  165.              coalesce(max(tbl_size), 1)                           AS tbl_size,
  166.              round(100 * coalesce(max(tbl_ratio), 0)::NUMERIC, 2) AS tbl_ratio,
  167.              coalesce(sum(idx_wasted), 0)                         AS idx_wasted,
  168.              coalesce(sum(idx_size), 1)                           AS idx_size
  169.       FROM monitor.pg_bloat
  170.       WHERE tblname IS NOT NULL
  171.       GROUP BY 1, 2, 3
  172.      ) d;
  173. COMMENT ON VIEW monitor.pg_table_bloat_human IS 'postgres table bloat info in human-readable format';
  177. ----------------------------------------------------------------------
  178. -- Activity Overview: monitor.pg_session
  179. ----------------------------------------------------------------------
  180. DROP VIEW IF EXISTS monitor.pg_session CASCADE;
  181. CREATE OR REPLACE VIEW monitor.pg_session AS
  182. SELECT coalesce(datname, 'all') AS datname, numbackends, active, idle, ixact, max_duration, max_tx_duration, max_conn_duration
  183. FROM (
  184.          SELECT datname,
  185.                 count(*)                                         AS numbackends,
  186.                 count(*) FILTER ( WHERE state = 'active' )       AS active,
  187.                 count(*) FILTER ( WHERE state = 'idle' )         AS idle,
  188.                 count(*) FILTER ( WHERE state = 'idle in transaction'
  189.                     OR state = 'idle in transaction (aborted)' ) AS ixact,
  190.                 max(extract(epoch from now() - state_change))
  191.                 FILTER ( WHERE state = 'active' )                AS max_duration,
  192.                 max(extract(epoch from now() - xact_start))      AS max_tx_duration,
  193.                 max(extract(epoch from now() - backend_start))   AS max_conn_duration
  194.          FROM pg_stat_activity
  195.          WHERE backend_type = 'client backend'
  196.            AND pid <> pg_backend_pid()
  197.          GROUP BY ROLLUP (1)
  198.          ORDER BY 1 NULLS FIRST
  199.      ) t;
  200. COMMENT ON VIEW monitor.pg_session IS 'postgres activity group by session';
  203. ----------------------------------------------------------------------
  204. -- Sequential Scan: monitor.pg_seq_scan
  205. ----------------------------------------------------------------------
  206. DROP VIEW IF EXISTS monitor.pg_seq_scan CASCADE;
  207. CREATE OR REPLACE VIEW monitor.pg_seq_scan AS
  208.     SELECT schemaname                                                        AS nspname,
  209.            relname,
  210.            seq_scan,
  211.            seq_tup_read,
  212.            seq_tup_read / seq_scan                                           AS seq_tup_avg,
  213.            idx_scan,
  214.            n_live_tup + n_dead_tup                                           AS tuples,
  215.            round(n_live_tup * 100.0::NUMERIC / (n_live_tup + n_dead_tup), 2) AS live_ratio
  216.     FROM pg_stat_user_tables
  217.     WHERE seq_scan > 0
  218.       and (n_live_tup + n_dead_tup) > 0
  219.     ORDER BY seq_scan DESC;
  220. COMMENT ON VIEW monitor.pg_seq_scan IS 'table that have seq scan';
  221. ``` 查看共享内存分配的函数（PG13以上可用）

DROP FUNCTION IF EXISTS monitor.pg_shmem() CASCADE; CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF pg_shmem_allocations AS SELECT * FROM pg_shmem_allocations; LANGUAGE SQL SECURITY DEFINER; COMMENT ON FUNCTION monitor.pg_shmem() IS ‘security wrapper for pg_shmem’; ```

最后修改 2022-05-27: init commit (1e3e284)