Monitor Deploy

如何使用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 }] # 填入数据库列表（每个数据库对象作为一个数组元素）

  1. 在全局/集群/实例配置中提供监控用户密码 pg_monitor_username/pg_monitor_password

    ```
  2. 针对该集群执行剧本：./pgsql-monly.yml -l pg-test
  3. 该剧本会在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剧本即可完成部署。

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

Monitor Deploy - 图1

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

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

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

事项\等级 L1 L2 L3
名称 基础部署 托管部署 完整部署
英文 basic managed full
场景 只有连接串 DB已存在，节点可管理 实例由Pigsty创建
PGCAT功能 ✅ 完整可用 ✅ 完整可用 ✅ 完整可用
PGSQL功能 ✅ 限PG指标 ✅ 限PG与节点指标 ✅ 完整功能
连接池指标 ❌ 不可用 ⚠️ 选装 ✅ 预装项
负载均衡器指标 ❌ 不可用 ⚠️ 选装 ✅ 预装项
PGLOG功能 ❌ 不可用 ⚠️ 选装 ✅ 预装项
PG Exporter ⚠️ 部署于元节点 ✅ 部署于DB节点 ✅ 部署于DB节点
Node Exporter ❌ 不部署 ✅ 部署于DB节点 ✅ 部署于DB节点
侵入DB节点 ✅ 无侵入 ⚠️ 安装Exporter ⚠️ 完全由Pigsty管理
监控现有实例 ✅ 可支持 ✅ 可支持 ❌ 仅用于Pigsty托管实例
监控用户与视图 人工创建 人工创建 Pigsty自动创建
部署使用剧本 pgsql-monly.yml pgsql.yml -t pg-exporter,promtail nodes.yml -t node-exporter pgsql.yml -t pg-exporter nodes.yml -t node-exporter
所需权限 元节点可达的PGURL DB节点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’; ```

Last modified 2022-06-04: fill en docs (5a858d3)