监控系统部署

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

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

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

    3. 针对该集群执行剧本：

    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可用），那么您可以使用 剧本中的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）中的实时状态信息仍然可用。

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

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

    监控已有实例：精简模式

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

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

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

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

    修改配置清单

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

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

    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

    若要启用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

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

    示例：在实例层面指定连接信息 ```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) ```

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

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

    您可以使用 中的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会从 指定的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_hba.conf 文件，添加以下规则以允许监控用户从本地，以及管理机使用密码访问数据库。

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