一、pgmetrics功能概述
pgmetrics是一个开源工具，用于从正在运行的 PostgreSQL 服务器收集和报告各种统计数据和信息，以帮助进行故障排除、监控和自动化。
目前已收集 350 多个指标，请参阅 JSON 输出或源代码以获取完整列表。以下是收集的指标的概述：
- 服务器：版本、系统标识符、时间线、事务 ID 环绕、检查点滞后
- 复制：主端、备用端、物理和逻辑复制槽
- WAL 归档：归档率、WAL 和就绪文件数、上次成功和失败时间
- BG Writer：检查点速率、总检查点（sched+req）、缓冲区
- 真空相关：正在进行的自动/手动真空进度、上次分析/真空、设置
- 表空间：文件系统的位置、大小、磁盘和 inode 使用情况
- 数据库：大小、膨胀、禁用触发器、安装的扩展、临时文件、事务 ID 环绕、死锁、冲突
- 角色：用户、群组、会员
- 活跃后端：事务运行时间过长、事务空闲、等待锁
- 表格：真空、分析、行估计、idx 和 seq 扫描、缓存命中率、热更新率、大小、膨胀
- 索引：缓存命中率、扫描、读取/扫描的行数、获取/扫描的行数
- 序列：缓存命中率
- 系统指标：核心、平均负载、内存和磁盘使用情况
- 设置：当前值和默认值不同
- 慢查询：来自 pg_stat_statements（如果可用）
- 锁：已授予和等待的锁，来自 pg_locks
- 作业进度：分析、备份、集群、复制、创建索引和清理作业的进度
- 被阻止的查询：被阻止的查询以及它们正在等待的查询
- 除了从通用 PostgreSQL 服务器收集指标之外，它还可以： PgBouncer： 连接到 PgBouncer 的管理数据库时收集并报告有关PgBouncer实例的信息。 从 v1.11.0 开始也支持Odyssey 。
- 日志文件：处理并从 PostgreSQL 日志文件中提取信息。（在 v1.8.0 中添加）
- AWS RDS、Aurora：从 AWS RDS 数据库收集 RDS 指标和增强监控指标，包括 AWS RDS Aurora（在 v1.9.0 中添加）
- Citus：收集有关Citus 扩展的信息（在 v1.10.0 中添加）
- Azure：从 Azure 数据库为 PostgreSQL（单个服务器、灵活服务器和托管 Citus）收集指标。（在 v1.13.0 中添加）
- Pgpool：从 Pgpool v4.x 实例收集指标和信息（在 v1.15.0 中添加）
pgmetrics是一个独立的、无依赖的工具：不需要安装 PostgreSQL 扩展，并且工具本身也没有包或安装依赖项。
输出格式
- pgmetrics可以以人性化文本格式报告收集到的统计数据 ，也可以将其导出为JSON或CSV格式。JSON 和 CSV 格式的输出旨在用于监控和自动化脚本。 pgmetrics还可以以文本格式重新显示之前保存的 JSON 文件的内容。
PostgreSQL 版本
- pgmetrics可以与 PostgreSQL 版本9.3至16一起使用。它还可以与以下提供的托管 PostgreSQL 实例一起使用：
- 亚马逊 AWS Aurora
- 亚马逊 AWS RDS
- Microsoft Azure Database For PostgreSQL 简单服务器
- Microsoft Azure Database For PostgreSQL 灵活服务器
- 适用于 PostgreSQL 超大规模/Citus 的 Microsoft Azure 数据库
- Google GCP SQL
可用性
pgmetrics用 Go 编写，可作为零依赖静态链接命令行工具下载，适用于许多平台。它是开源的，并根据 Apache License 2.0 获得许可。
二、pgmetrics环境部署
2.1、官网下载地址
wget https://github.com/rapidloop/pgmetrics/releases/download/v1.16.0/pgmetrics_1.16.0_linux_amd64.tar.gz
2.2、解压
tar xvf pgmetrics_1.16.0_linux_amd64.tar.gz
2.3、切换目录
cd pgmetrics_1.16.0_linux_amd64
2.4、查看帮助
./pgmetrics --help pgmetrics collects PostgreSQL information and metrics. Usage: pgmetrics [OPTION]... [DBNAME] General options: -t, --timeout=SECS individual query timeout in seconds (default: 5) --lock-timeout=MILLIS lock timeout in milliseconds (default: 50) -i, --input=FILE don't connect to db, instead read and display this previously saved JSON file -V, --version output version information, then exit -?, --help[=options] show this help, then exit --help=variables list environment variables, then exit Collection options: -S, --no-sizes don't collect tablespace and relation sizes -c, --schema=REGEXP collect only from schema(s) matching POSIX regexp -C, --exclude-schema=REGEXP do NOT collect from schema(s) matching POSIX regexp -a, --table=REGEXP collect only from table(s) matching POSIX regexp -A, --exclude-table=REGEXP do NOT collect from table(s) matching POSIX regexp --omit=WHAT do NOT collect the items specified as a comma-separated list of: "tables", "indexes", "sequences", "functions", "extensions", "triggers", "statements", "log", "citus", "indexdefs", "bloat" --sql-length=LIMIT collect only first LIMIT characters of all SQL queries (default: 500) --statements-limit=LIMIT collect only utmost LIMIT number of row from pg_stat_statements (default: 100) --only-listed collect info only from the databases listed as command-line args (use with Heroku) --all-dbs collect info from all user databases --log-file location of PostgreSQL log file --log-dir read all the PostgreSQL log files in this directory --log-span=MINS examine the last MINS minutes of logs (default: 5) --aws-rds-dbid AWS RDS/Aurora database instance identifier --az-resource Azure resource ID --pgpool collect only Pgpool metrics Output options: -f, --format=FORMAT output format; "human", "json" or "csv" (default: "human") -l, --toolong=SECS for human output, transactions running longer than this are considered too long (default: 60) -o, --output=FILE write output to the specified file --no-pager do not invoke the pager for tty output Connection options: -h, --host=HOSTNAME database server host or socket directory (default: "/var/run/postgresql") -p, --port=PORT database server port (default: 5432) -U, --username=USERNAME database user name (default: "postgres") -w, --no-password never prompt for password --role=ROLE do SET ROLE before collection For more information, visit <https://pgmetrics.io>.
三、Pgmetrics使用技巧
3.1、仅收集名称中包含“enmotech”的表：
代码如下：
./pgmetrics --no-password -a 'enmotech' -h 192.168.1.72 postgres
显示结果：
pgmetrics run at: 13 Jul 2024 10:32:13 PM (now) PostgreSQL Cluster: Name: Server Version: 16.3 Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago) System Identifier: 7390392581339028604 Timeline: 1 Last Checkpoint: 13 Jul 2024 10:27:58 PM (4 minutes ago) REDO LSN: 0/15000060 Checkpoint LSN: 0/15000098 (56 B since REDO) Transaction IDs: oldest = 723, next = 763, range = 40 Notification Queue: 0.0% used Active Backends: 5 (max 100) Recovery Mode? no WAL Files: WAL Archiving? yes WAL Files: 11 Ready Files: 0 Archive Rate: 0.28 per min Last Archived: 13 Jul 2024 10:28:15 PM (3 minutes ago) Last Failure: Totals: 21 succeeded, 0 failed Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +--------------------+----------------+ | Setting | Value | +--------------------+----------------+ | wal_level | replica | | archive_timeout | 10 | | wal_compression | off | | max_wal_size | 1024 (1.0 GiB) | | min_wal_size | 80 (80 MiB) | | checkpoint_timeout | 300 | | full_page_writes | on | | wal_keep_size | 0 | +--------------------+----------------+ BG Writer: Checkpoint Rate: 0.20 per min Average Write: 398 KiB per checkpoint Total Checkpoints: 14 sched (93.3%) + 1 req (6.7%) = 15 Total Write: 9.2 MiB, @ 2.1 KiB per sec Buffers Allocated: 892 (7.0 MiB) Buffers Written: 746 chkpt (63.5%) + 0 bgw (0.0%) + 428 be (36.5%) Clean Scan Stops: 0 BE fsyncs: 0 Counts Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +------------------------------+--------------+ | Setting | Value | +------------------------------+--------------+ | bgwriter_delay | 200 msec | | bgwriter_flush_after | 64 (512 KiB) | | bgwriter_lru_maxpages | 100 | | bgwriter_lru_multiplier | 2 | | block_size | 8192 | | checkpoint_timeout | 300 sec | | checkpoint_completion_target | 0.9 | +------------------------------+--------------+ Backends: Total Backends: 5 (5.0% of max 100) Problematic: 0 waiting on locks, 4 waiting on other, 0 xact too long, 0 idle in xact Other Waiting Backends: +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ | PID | User | App | Client Addr | Database | Wait | Query Start | +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ | 2118 | postgres | psql | | postgres | Client / ClientRead | 13 Jul 2024 10:13:31 PM | | 2137 | postgres | citus_internal gpid=999999990000001858 | 192.168.1.73/32 | postgres | Client / ClientRead | 13 Jul 2024 10:32:12 PM | | 2139 | postgres | citus_internal gpid=999999990000001894 | 192.168.1.72/32 | postgres | Client / ClientRead | 13 Jul 2024 10:31:55 PM | | 2150 | postgres | citus_internal gpid=999999990000002168 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 10:32:12 PM | +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ Locks: +------------+-------------+-------+ | Lock Type | Not Granted | Total | +------------+-------------+-------+ | relation | 0 | 4 | | virtualxid | 0 | 1 | +------------+-------------+-------+ | | 0 | 5 | +------------+-------------+-------+ Vacuum Progress: No manual or auto vacuum jobs in progress. +------------------------------+----------------+ | Setting | Value | +------------------------------+----------------+ | maintenance_work_mem | 65536 (64 MiB) | | autovacuum | on | | autovacuum_analyze_threshold | 50 | | autovacuum_vacuum_threshold | 50 | | autovacuum_freeze_max_age | 200000000 | | autovacuum_max_workers | 3 | | autovacuum_naptime | 60 sec | | vacuum_freeze_min_age | 50000000 | | vacuum_freeze_table_age | 150000000 | +------------------------------+----------------+ Roles: +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires | Member Of | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | postgres | yes | yes | yes | yes | yes | yes | yes | | | | pg_monitor | | | | | | | yes | | pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables | | pg_read_all_settings | | | | | | | yes | | | | pg_read_all_stats | | | | | | | yes | | | | pg_stat_scan_tables | | | | | | | yes | | | | pg_signal_backend | | | | | | | yes | | | | pg_checkpoint | | | | | | | yes | | | | pg_use_reserved_connections | | | | | | | yes | | | | pg_read_server_files | | | | | | | yes | | | | pg_write_server_files | | | | | | | yes | | | | pg_execute_server_program | | | | | | | yes | | | | pg_database_owner | | | | | | | yes | | | | pg_read_all_data | | | | | | | yes | | | | pg_write_all_data | | | | | | | yes | | | | pg_create_subscription | | | | | | | yes | | | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ Tablespaces: +------------+----------+----------+---------+ | Name | Owner | Location | Size | +------------+----------+----------+---------+ | pg_default | postgres | | 24 MiB | | pg_global | postgres | | 564 KiB | +------------+----------+----------+---------+ Database #1: Name: postgres Owner: postgres Tablespace: pg_default Connections: 6 (no max limit) Frozen Xid Age: 40 Transactions: 4219 (99.7%) commits, 13 (0.3%) rollbacks Cache Hits: 99.5% Rows Changed: ins 69.0%, upd 13.7%, del 17.3% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: Size: 9.6 MiB Sequences: +----------------------+------------+ | Sequence | Cache Hits | +----------------------+------------+ | storageid_seq | | | enmotech_dist_id_seq | 100.0% | | enmotech_col_id_seq | 100.0% | | enmotech_ref_id_seq | 100.0% | +----------------------+------------+ Installed Extensions: +----------------+---------+------------------------------+ | Name | Version | Comment | +----------------+---------+------------------------------+ | citus | 12.2-1 | Citus distributed database | | citus_columnar | 12.2-1 | Citus Columnar extension | | plpgsql | 1.0 | PL/pgSQL procedural language | +----------------+---------+------------------------------+ Table #1 in "postgres": Name: postgres.public.enmotech_dist Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B Table #2 in "postgres": Name: postgres.public.enmotech_col Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B Table #3 in "postgres": Name: postgres.public.enmotech_ref Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B
3.2、仅收集名称中包含“报告”或“审计”的表：
代码如下：
./pgmetrics --no-password -a '(report|audit)' -h 192.168.1.72 postgres
显示结果：
pgmetrics run at: 14 Jul 2024 4:17:26 PM (now) PostgreSQL Cluster: Name: Server Version: 16.3 Server Started: 14 Jul 2024 4:14:10 PM (3 minutes ago) System Identifier: 7390392581339028604 Timeline: 1 Last Checkpoint: 14 Jul 2024 4:14:09 PM (3 minutes ago) REDO LSN: 0/1F000060 Checkpoint LSN: 0/1F000060 (0 B since REDO) Transaction IDs: oldest = 723, next = 768, range = 45 Notification Queue: 0.0% used Active Backends: 3 (max 100) Recovery Mode? no WAL Files: WAL Archiving? yes WAL Files: 8 Ready Files: 0 Archive Rate: 0.00 per min Last Archived: Last Failure: Totals: 0 succeeded, 0 failed Totals Since: 14 Jul 2024 4:14:10 PM (3 minutes ago) +--------------------+----------------+ | Setting | Value | +--------------------+----------------+ | wal_level | replica | | archive_timeout | 10 | | wal_compression | off | | max_wal_size | 1024 (1.0 GiB) | | min_wal_size | 80 (80 MiB) | | checkpoint_timeout | 300 | | full_page_writes | on | | wal_keep_size | 0 | +--------------------+----------------+ BG Writer: Checkpoint Rate: 0.30 per min Average Write: 0 B per checkpoint Total Checkpoints: 0 sched (0.0%) + 1 req (100.0%) = 1 Total Write: 0 B, @ 0 B per sec Buffers Allocated: 560 (4.4 MiB) Buffers Written: 0 chkpt (0.0%) + 0 bgw (0.0%) + 0 be (0.0%) Clean Scan Stops: 0 BE fsyncs: 0 Counts Since: 14 Jul 2024 4:14:09 PM (3 minutes ago) +------------------------------+--------------+ | Setting | Value | +------------------------------+--------------+ | bgwriter_delay | 200 msec | | bgwriter_flush_after | 64 (512 KiB) | | bgwriter_lru_maxpages | 100 | | bgwriter_lru_multiplier | 2 | | block_size | 8192 | | checkpoint_timeout | 300 sec | | checkpoint_completion_target | 0.9 | +------------------------------+--------------+ Backends: Total Backends: 3 (3.0% of max 100) Problematic: 0 waiting on locks, 2 waiting on other, 0 xact too long, 0 idle in xact Other Waiting Backends: +------+----------+---------------------------------+-----------------+----------+---------------------+------------------------+ | PID | User | App | Client Addr | Database | Wait | Query Start | +------+----------+---------------------------------+-----------------+----------+---------------------+------------------------+ | 1658 | postgres | citus_internal gpid=60000001657 | 192.168.1.72/32 | postgres | Client / ClientRead | 14 Jul 2024 4:17:16 PM | | 1659 | postgres | citus_internal gpid=70000001651 | 192.168.1.73/32 | postgres | Client / ClientRead | 14 Jul 2024 4:17:26 PM | +------+----------+---------------------------------+-----------------+----------+---------------------+------------------------+ Locks: +------------+-------------+-------+ | Lock Type | Not Granted | Total | +------------+-------------+-------+ | relation | 0 | 4 | | virtualxid | 0 | 1 | +------------+-------------+-------+ | | 0 | 5 | +------------+-------------+-------+ Vacuum Progress: No manual or auto vacuum jobs in progress. +------------------------------+----------------+ | Setting | Value | +------------------------------+----------------+ | maintenance_work_mem | 65536 (64 MiB) | | autovacuum | on | | autovacuum_analyze_threshold | 50 | | autovacuum_vacuum_threshold | 50 | | autovacuum_freeze_max_age | 200000000 | | autovacuum_max_workers | 3 | | autovacuum_naptime | 60 sec | | vacuum_freeze_min_age | 50000000 | | vacuum_freeze_table_age | 150000000 | +------------------------------+----------------+ Roles: +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires | Member Of | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | postgres | yes | yes | yes | yes | yes | yes | yes | | | | pg_monitor | | | | | | | yes | | pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables | | pg_read_all_settings | | | | | | | yes | | | | pg_read_all_stats | | | | | | | yes | | | | pg_stat_scan_tables | | | | | | | yes | | | | pg_signal_backend | | | | | | | yes | | | | pg_checkpoint | | | | | | | yes | | | | pg_use_reserved_connections | | | | | | | yes | | | | pg_read_server_files | | | | | | | yes | | | | pg_write_server_files | | | | | | | yes | | | | pg_execute_server_program | | | | | | | yes | | | | pg_database_owner | | | | | | | yes | | | | pg_read_all_data | | | | | | | yes | | | | pg_write_all_data | | | | | | | yes | | | | pg_create_subscription | | | | | | | yes | | | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ Tablespaces: +------------+----------+----------+---------+ | Name | Owner | Location | Size | +------------+----------+----------+---------+ | pg_default | postgres | | 24 MiB | | pg_global | postgres | | 564 KiB | +------------+----------+----------+---------+ Database #1: Name: postgres Owner: postgres Tablespace: pg_default Connections: 4 (no max limit) Frozen Xid Age: 45 Transactions: 108 (97.3%) commits, 3 (2.7%) rollbacks Cache Hits: 98.0% Rows Changed: ins 0.0%, upd 0.0%, del 0.0% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: Size: 9.6 MiB Sequences: +----------------------+------------+ | Sequence | Cache Hits | +----------------------+------------+ | storageid_seq | | | enmotech_dist_id_seq | | | enmotech_col_id_seq | | | enmotech_ref_id_seq | | +----------------------+------------+ Installed Extensions: +--------------------+---------+------------------------------------------------------------------------+ | Name | Version | Comment | +--------------------+---------+------------------------------------------------------------------------+ | citus | 12.2-1 | Citus distributed database | | citus_columnar | 12.2-1 | Citus Columnar extension | | pg_stat_statements | 1.10 | track planning and execution statistics of all SQL statements executed | | plpgsql | 1.0 | PL/pgSQL procedural language | +--------------------+---------+------------------------------------------------------------------------+ Slow Queries: +-------+----------+------------+-----------+----------------------------------------------------+ | Calls | Avg Time | Total Time | Rows/Call | Query | +-------+----------+------------+-----------+----------------------------------------------------+ | 1 | 67ms | 67ms | 10 | SELECT current_database() AS db, schemaname, tab | | 1 | 59ms | 59ms | 3 | SELECT logicalrelid::oid, citus_table_size(logical | | 2 | 8ms | 16ms | 7 | SELECT S.relid, S.schemaname, S.relname, current_d | | 2 | 7ms | 14ms | 0 | SELECT funcid, schemaname, funcname, current_datab | | 2 | 4ms | 9ms | 438 | SELECT name, setting, COALESCE(boot_val,$1), sourc | | 2 | 1ms | 3ms | 5 | SELECT indexrelid, pg_get_indexdef(indexrelid) FRO | | 4 | 0s | 3ms | 1 | SELECT pg_tablespace_size($1) | | 1 | 3ms | 3ms | 0 | SELECT queryid, userid, dbid, query, executor, COA | | 2 | 1ms | 3ms | 15 | SELECT R.oid, R.rolname, R.rolsuper, R.rolinherit, | | 2 | 1ms | 2ms | 5 | SELECT S.relid, S.indexrelid, S.schemaname, S.reln | | 2 | 0s | 1ms | 1 | SELECT (SELECT $1) + (SELECT SUM(pg_table_size(rel | | 2 | 0s | 1ms | 1 | SELECT pg_database_size($1) | | 36 | 0s | 1ms | 0 | SELECT waiting_pid, waiting_node_id, waiting_trans | | 2 | 0s | 1ms | 4 | SELECT relid, schemaname, relname, current_databas | | 2 | 0s | 1ms | 1 | SELECT EXTRACT($1 FROM pg_postmaster_start_time()) | | 1 | 1ms | 1ms | 2 | SELECT nodeid, groupid, nodename, nodeport, COALES | | 2 | 0s | 0s | 7 | SELECT COALESCE(D.datname, $1), L.locktype, L.mode | | 1 | 0s | 0s | 44 | SELECT userid, dbid, queryid, LEFT(COALESCE(query, | | 2 | 0s | 0s | 2 | SELECT COALESCE(datname, $2), COALESCE(usename, $3 | | 2 | 0s | 0s | 0 | SELECT COALESCE(usename, $1), application_name, | | 2 | 0s | 0s | 4 | SELECT name, current_database(), COALESCE(default_ | | 2 | 0s | 0s | 0 | WITH P AS (SELECT DISTINCT pid FROM pg_locks WHERE | | 2 | 0s | 0s | 8 | SELECT name FROM pg_ls_waldir() WHERE name ~ $1 | | 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, COALESCE(command, | | 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, index_relid::int, | | 8 | 0s | 0s | 0 | SELECT gid FROM pg_prepared_xacts WHERE gid LIKE $ | | 2 | 0s | 0s | 1 | SELECT checkpoint_lsn, redo_lsn, timeline_id, n | | 2 | 0s | 0s | 8 | SELECT backend_type, count(*) FROM pg_stat_activit | | 1 | 0s | 0s | 1 | SELECT (SELECT $1) + (SELECT SUM(pg_table_size(rel | | 2 | 0s | 0s | 1 | SELECT current_user | | 2 | 0s | 0s | 1 | SELECT D.oid, D.datname, D.datdba, D.dattablespace | | 2 | 0s | 0s | 0 | SELECT pid, datname, COALESCE(relid, $1), COALESCE | | 2 | 0s | 0s | 1 | SELECT system_identifier FROM pg_control_system() | | 2 | 0s | 0s | 0 | SELECT pid, datname, relid::int, COALESCE(command, | | 2 | 0s | 0s | 0 | SELECT slot_name, COALESCE(plugin, $1), slot_type, | | 2 | 0s | 0s | 1 | SELECT COUNT(*) FROM pg_ls_archive_statusdir() WHE | | 2 | 0s | 0s | 0 | SELECT pid, datname, COALESCE(relid::int, $1::int) | | 2 | 0s | 0s | 1 | SELECT pg_current_wal_flush_lsn(), pg_current_ | | 2 | 0s | 0s | 1 | SELECT archived_count, COALESCE(last_archived_ | | 2 | 0s | 0s | 1 | SELECT COALESCE(inet_client_addr() = inet_server_a | | 2 | 0s | 0s | 2 | SELECT oid, spcname, pg_get_userbyid(spcowner), | | 2 | 0s | 0s | 1 | SELECT wal_records, wal_fpi, wal_bytes, wal_buffer | | 2 | 0s | 0s | 0 | SELECT T.oid, T.tgrelid, T.tgname, P.proname F | | 2 | 0s | 0s | 0 | SELECT pid, COALESCE(phase, $1), COALESCE(bac | | 2 | 0s | 0s | 1 | SELECT pg_is_in_recovery(), COALESCE(pg_last_wa | | 2 | 0s | 0s | 1 | SELECT checkpoints_timed, checkpoints_req, checkpo | | 2 | 0s | 0s | 0 | SELECT c.oid, inhparent::regclass, COALESCE(pg_get | | 1 | 0s | 0s | 0 | WITH sc AS (SELECT srsubid, COUNT(*) AS c FROM | | 2 | 0s | 0s | 1 | SELECT current_database() | | 1 | 0s | 0s | 0 | WITH pc AS (SELECT pubname, COUNT(*) AS c FROM pg_ | | 2 | 0s | 0s | 1 | SELECT pg_notification_queue_usage() | | 2 | 0s | 0s | 0 | SELECT status, receive_start_lsn, receive_start_tl | | 2 | 0s | 0s | 0 | SELECT c.oid, i.inhparent::regclass FROM pg_cla | | 1 | 0s | 0s | 1 | SELECT citus_version() | +-------+----------+------------+-----------+----------------------------------------------------+
3.3、收集除名称中包含“enmotech”的表之外的所有表：
代码如下：
./pgmetrics --no-password -A enmotech -h 192.168.1.72 postgres
显示结果：
pgmetrics run at: 13 Jul 2024 10:51:09 PM (now) PostgreSQL Cluster: Name: Server Version: 16.3 Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago) System Identifier: 7390392581339028604 Timeline: 1 Last Checkpoint: 13 Jul 2024 10:27:58 PM (23 minutes ago) REDO LSN: 0/15000060 Checkpoint LSN: 0/15000098 (56 B since REDO) Transaction IDs: oldest = 723, next = 763, range = 40 Notification Queue: 0.0% used Active Backends: 5 (max 100) Recovery Mode? no WAL Files: WAL Archiving? yes WAL Files: 11 Ready Files: 0 Archive Rate: 0.23 per min Last Archived: 13 Jul 2024 10:28:15 PM (22 minutes ago) Last Failure: Totals: 21 succeeded, 0 failed Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +--------------------+----------------+ | Setting | Value | +--------------------+----------------+ | wal_level | replica | | archive_timeout | 10 | | wal_compression | off | | max_wal_size | 1024 (1.0 GiB) | | min_wal_size | 80 (80 MiB) | | checkpoint_timeout | 300 | | full_page_writes | on | | wal_keep_size | 0 | +--------------------+----------------+ BG Writer: Checkpoint Rate: 0.20 per min Average Write: 314 KiB per checkpoint Total Checkpoints: 18 sched (94.7%) + 1 req (5.3%) = 19 Total Write: 9.2 MiB, @ 1.7 KiB per sec Buffers Allocated: 895 (7.0 MiB) Buffers Written: 746 chkpt (63.5%) + 0 bgw (0.0%) + 428 be (36.5%) Clean Scan Stops: 0 BE fsyncs: 0 Counts Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +------------------------------+--------------+ | Setting | Value | +------------------------------+--------------+ | bgwriter_delay | 200 msec | | bgwriter_flush_after | 64 (512 KiB) | | bgwriter_lru_maxpages | 100 | | bgwriter_lru_multiplier | 2 | | block_size | 8192 | | checkpoint_timeout | 300 sec | | checkpoint_completion_target | 0.9 | +------------------------------+--------------+ Backends: Total Backends: 5 (5.0% of max 100) Problematic: 0 waiting on locks, 4 waiting on other, 0 xact too long, 0 idle in xact Other Waiting Backends: +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ | PID | User | App | Client Addr | Database | Wait | Query Start | +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ | 2118 | postgres | psql | | postgres | Client / ClientRead | 13 Jul 2024 10:13:31 PM | | 2173 | postgres | citus_internal gpid=999999990000001858 | 192.168.1.73/32 | postgres | Client / ClientRead | 13 Jul 2024 10:51:08 PM | | 2176 | postgres | citus_internal gpid=999999990000001894 | 192.168.1.72/32 | postgres | Client / ClientRead | 13 Jul 2024 10:51:03 PM | | 2184 | postgres | citus_internal gpid=999999990000002168 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 10:51:08 PM | +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ Locks: +------------+-------------+-------+ | Lock Type | Not Granted | Total | +------------+-------------+-------+ | relation | 0 | 4 | | virtualxid | 0 | 1 | +------------+-------------+-------+ | | 0 | 5 | +------------+-------------+-------+ Vacuum Progress: No manual or auto vacuum jobs in progress. +------------------------------+----------------+ | Setting | Value | +------------------------------+----------------+ | maintenance_work_mem | 65536 (64 MiB) | | autovacuum | on | | autovacuum_analyze_threshold | 50 | | autovacuum_vacuum_threshold | 50 | | autovacuum_freeze_max_age | 200000000 | | autovacuum_max_workers | 3 | | autovacuum_naptime | 60 sec | | vacuum_freeze_min_age | 50000000 | | vacuum_freeze_table_age | 150000000 | +------------------------------+----------------+ Roles: +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires | Member Of | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | postgres | yes | yes | yes | yes | yes | yes | yes | | | | pg_monitor | | | | | | | yes | | pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables | | pg_read_all_settings | | | | | | | yes | | | | pg_read_all_stats | | | | | | | yes | | | | pg_stat_scan_tables | | | | | | | yes | | | | pg_signal_backend | | | | | | | yes | | | | pg_checkpoint | | | | | | | yes | | | | pg_use_reserved_connections | | | | | | | yes | | | | pg_read_server_files | | | | | | | yes | | | | pg_write_server_files | | | | | | | yes | | | | pg_execute_server_program | | | | | | | yes | | | | pg_database_owner | | | | | | | yes | | | | pg_read_all_data | | | | | | | yes | | | | pg_write_all_data | | | | | | | yes | | | | pg_create_subscription | | | | | | | yes | | | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ Tablespaces: +------------+----------+----------+---------+ | Name | Owner | Location | Size | +------------+----------+----------+---------+ | pg_default | postgres | | 24 MiB | | pg_global | postgres | | 564 KiB | +------------+----------+----------+---------+ Database #1: Name: postgres Owner: postgres Tablespace: pg_default Connections: 6 (no max limit) Frozen Xid Age: 40 Transactions: 5545 (99.7%) commits, 17 (0.3%) rollbacks Cache Hits: 99.6% Rows Changed: ins 69.0%, upd 13.7%, del 17.3% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: Size: 9.6 MiB Sequences: +----------------------+------------+ | Sequence | Cache Hits | +----------------------+------------+ | storageid_seq | | | enmotech_dist_id_seq | 100.0% | | enmotech_col_id_seq | 100.0% | | enmotech_ref_id_seq | 100.0% | +----------------------+------------+ Installed Extensions: +----------------+---------+------------------------------+ | Name | Version | Comment | +----------------+---------+------------------------------+ | citus | 12.2-1 | Citus distributed database | | citus_columnar | 12.2-1 | Citus Columnar extension | | plpgsql | 1.0 | PL/pgSQL procedural language | +----------------+---------+------------------------------+ Table #1 in "postgres": Name: postgres.columnar_internal.options Columns: 5 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 1, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B ACL: +----------+---------------------------------------------------------------+------------+ | Role | Privileges | Granted By | +----------+---------------------------------------------------------------+------------+ | postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres | +----------+---------------------------------------------------------------+------------+ +--------------+-------+---------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +--------------+-------+---------+-------+------------+-------+----------------+-------------------+ | options_pkey | btree | 8.0 KiB | | | 0 | 0.0 | 0.0 | +--------------+-------+---------+-------+------------+-------+----------------+-------------------+ Table #2 in "postgres": Name: postgres.columnar_internal.stripe Columns: 9 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 4, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=75.0%) Size: 0 B ACL: +----------+---------------------------------------------------------------+------------+ | Role | Privileges | Granted By | +----------+---------------------------------------------------------------+------------+ | postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres | +----------+---------------------------------------------------------------+------------+ +-----------------------------+-------+---------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +-----------------------------+-------+---------+-------+------------+-------+----------------+-------------------+ | stripe_pkey | btree | 8.0 KiB | | 75.0% | 0 | 0.0 | 0.0 | | stripe_first_row_number_idx | btree | 8.0 KiB | | 75.0% | 0 | 0.0 | 0.0 | +-----------------------------+-------+---------+-------+------------+-------+----------------+-------------------+ Table #3 in "postgres": Name: postgres.columnar_internal.chunk_group Columns: 4 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 1, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B ACL: +----------+---------------------------------------------------------------+------------+ | Role | Privileges | Granted By | +----------+---------------------------------------------------------------+------------+ | postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres | +----------+---------------------------------------------------------------+------------+ +------------------+-------+---------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +------------------+-------+---------+-------+------------+-------+----------------+-------------------+ | chunk_group_pkey | btree | 8.0 KiB | | | 0 | 0.0 | 0.0 | +------------------+-------+---------+-------+------------+-------+----------------+-------------------+ Table #4 in "postgres": Name: postgres.columnar_internal.chunk Columns: 14 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 1, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 8.0 KiB ACL: +----------+---------------------------------------------------------------+------------+ | Role | Privileges | Granted By | +----------+---------------------------------------------------------------+------------+ | postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER | postgres | +----------+---------------------------------------------------------------+------------+ +------------+-------+---------+-------+------------+-------+----------------+-------------------+ | Index | Type | Size | Bloat | Cache Hits | Scans | Rows Read/Scan | Rows Fetched/Scan | +------------+-------+---------+-------+------------+-------+----------------+-------------------+ | chunk_pkey | btree | 8.0 KiB | | | 0 | 0.0 | 0.0 | +------------+-------+---------+-------+------------+-------+----------------+-------------------+
3.4、仅收集模式“public”和“schema_server”中的所有内容：
代码如下：
./pgmetrics --no-password -c 'public|schema_server' -h 192.168.1.72 postgres
显示结果：
pgmetrics run at: 13 Jul 2024 11:07:07 PM (now) PostgreSQL Cluster: Name: Server Version: 16.3 Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago) System Identifier: 7390392581339028604 Timeline: 1 Last Checkpoint: 13 Jul 2024 10:27:58 PM (39 minutes ago) REDO LSN: 0/15000060 Checkpoint LSN: 0/15000098 (56 B since REDO) Transaction IDs: oldest = 723, next = 763, range = 40 Notification Queue: 0.0% used Active Backends: 5 (max 100) Recovery Mode? no WAL Files: WAL Archiving? yes WAL Files: 11 Ready Files: 0 Archive Rate: 0.21 per min Last Archived: 13 Jul 2024 11:05:19 PM (1 minute ago) Last Failure: Totals: 23 succeeded, 0 failed Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +--------------------+----------------+ | Setting | Value | +--------------------+----------------+ | wal_level | replica | | archive_timeout | 10 | | wal_compression | off | | max_wal_size | 1024 (1.0 GiB) | | min_wal_size | 80 (80 MiB) | | checkpoint_timeout | 300 | | full_page_writes | on | | wal_keep_size | 0 | +--------------------+----------------+ BG Writer: Checkpoint Rate: 0.20 per min Average Write: 271 KiB per checkpoint Total Checkpoints: 21 sched (95.5%) + 1 req (4.5%) = 22 Total Write: 9.2 MiB, @ 1.4 KiB per sec Buffers Allocated: 902 (7.0 MiB) Buffers Written: 746 chkpt (63.5%) + 0 bgw (0.0%) + 428 be (36.5%) Clean Scan Stops: 0 BE fsyncs: 0 Counts Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +------------------------------+--------------+ | Setting | Value | +------------------------------+--------------+ | bgwriter_delay | 200 msec | | bgwriter_flush_after | 64 (512 KiB) | | bgwriter_lru_maxpages | 100 | | bgwriter_lru_multiplier | 2 | | block_size | 8192 | | checkpoint_timeout | 300 sec | | checkpoint_completion_target | 0.9 | +------------------------------+--------------+ Backends: Total Backends: 5 (5.0% of max 100) Problematic: 0 waiting on locks, 4 waiting on other, 0 xact too long, 0 idle in xact Other Waiting Backends: +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ | PID | User | App | Client Addr | Database | Wait | Query Start | +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ | 2201 | postgres | citus_internal gpid=999999990000002168 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 11:07:07 PM | | 2216 | postgres | citus_internal gpid=999999990000001858 | 192.168.1.73/32 | postgres | Client / ClientRead | 13 Jul 2024 11:07:06 PM | | 2220 | postgres | citus_internal gpid=999999990000001894 | 192.168.1.72/32 | postgres | Client / ClientRead | 13 Jul 2024 11:06:09 PM | | 2228 | postgres | citus_internal gpid=999999990000002355 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 11:05:11 PM | +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ Locks: +------------+-------------+-------+ | Lock Type | Not Granted | Total | +------------+-------------+-------+ | relation | 0 | 4 | | virtualxid | 0 | 1 | +------------+-------------+-------+ | | 0 | 5 | +------------+-------------+-------+ Vacuum Progress: No manual or auto vacuum jobs in progress. +------------------------------+----------------+ | Setting | Value | +------------------------------+----------------+ | maintenance_work_mem | 65536 (64 MiB) | | autovacuum | on | | autovacuum_analyze_threshold | 50 | | autovacuum_vacuum_threshold | 50 | | autovacuum_freeze_max_age | 200000000 | | autovacuum_max_workers | 3 | | autovacuum_naptime | 60 sec | | vacuum_freeze_min_age | 50000000 | | vacuum_freeze_table_age | 150000000 | +------------------------------+----------------+ Roles: +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires | Member Of | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | postgres | yes | yes | yes | yes | yes | yes | yes | | | | pg_monitor | | | | | | | yes | | pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables | | pg_read_all_settings | | | | | | | yes | | | | pg_read_all_stats | | | | | | | yes | | | | pg_stat_scan_tables | | | | | | | yes | | | | pg_signal_backend | | | | | | | yes | | | | pg_checkpoint | | | | | | | yes | | | | pg_use_reserved_connections | | | | | | | yes | | | | pg_read_server_files | | | | | | | yes | | | | pg_write_server_files | | | | | | | yes | | | | pg_execute_server_program | | | | | | | yes | | | | pg_database_owner | | | | | | | yes | | | | pg_read_all_data | | | | | | | yes | | | | pg_write_all_data | | | | | | | yes | | | | pg_create_subscription | | | | | | | yes | | | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ Tablespaces: +------------+----------+----------+---------+ | Name | Owner | Location | Size | +------------+----------+----------+---------+ | pg_default | postgres | | 24 MiB | | pg_global | postgres | | 564 KiB | +------------+----------+----------+---------+ Database #1: Name: postgres Owner: postgres Tablespace: pg_default Connections: 6 (no max limit) Frozen Xid Age: 42 Transactions: 6672 (99.7%) commits, 22 (0.3%) rollbacks Cache Hits: 99.6% Rows Changed: ins 69.0%, upd 13.7%, del 17.3% Total Temp: 0 B in 0 files Problems: 0 deadlocks, 0 conflicts Totals Since: Size: 9.6 MiB Sequences: +----------------------+------------+ | Sequence | Cache Hits | +----------------------+------------+ | enmotech_dist_id_seq | 100.0% | | enmotech_col_id_seq | 100.0% | | enmotech_ref_id_seq | 100.0% | +----------------------+------------+ Installed Extensions: +----------------+---------+------------------------------+ | Name | Version | Comment | +----------------+---------+------------------------------+ | citus | 12.2-1 | Citus distributed database | | citus_columnar | 12.2-1 | Citus Columnar extension | | plpgsql | 1.0 | PL/pgSQL procedural language | +----------------+---------+------------------------------+ Table #1 in "postgres": Name: postgres.public.enmotech_dist Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B Table #2 in "postgres": Name: postgres.public.enmotech_col Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B Table #3 in "postgres": Name: postgres.public.enmotech_ref Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B
3.5、收集模式“public”中名为“enmotech”的表：
代码如下：
./pgmetrics --no-password -c public -a 'enmotech' -h 192.168.1.72 postgres
显示结果：
pgmetrics run at: 13 Jul 2024 11:11:58 PM (now) PostgreSQL Cluster: Name: Server Version: 16.3 Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago) System Identifier: 7390392581339028604 Timeline: 1 Last Checkpoint: 13 Jul 2024 11:07:58 PM (4 minutes ago) REDO LSN: 0/18000028 Checkpoint LSN: 0/18000060 (56 B since REDO) Transaction IDs: oldest = 723, next = 765, range = 42 Notification Queue: 0.0% used Active Backends: 5 (max 100) Recovery Mode? no WAL Files: WAL Archiving? yes WAL Files: 11 Ready Files: 0 Archive Rate: 0.21 per min Last Archived: 13 Jul 2024 11:08:00 PM (3 minutes ago) Last Failure: Totals: 24 succeeded, 0 failed Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +--------------------+----------------+ | Setting | Value | +--------------------+----------------+ | wal_level | replica | | archive_timeout | 10 | | wal_compression | off | | max_wal_size | 1024 (1.0 GiB) | | min_wal_size | 80 (80 MiB) | | checkpoint_timeout | 300 | | full_page_writes | on | | wal_keep_size | 0 | +--------------------+----------------+ BG Writer: Checkpoint Rate: 0.20 per min Average Write: 261 KiB per checkpoint Total Checkpoints: 22 sched (95.7%) + 1 req (4.3%) = 23 Total Write: 9.2 MiB, @ 1.4 KiB per sec Buffers Allocated: 902 (7.0 MiB) Buffers Written: 751 chkpt (63.7%) + 0 bgw (0.0%) + 428 be (36.3%) Clean Scan Stops: 0 BE fsyncs: 0 Counts Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +------------------------------+--------------+ | Setting | Value | +------------------------------+--------------+ | bgwriter_delay | 200 msec | | bgwriter_flush_after | 64 (512 KiB) | | bgwriter_lru_maxpages | 100 | | bgwriter_lru_multiplier | 2 | | block_size | 8192 | | checkpoint_timeout | 300 sec | | checkpoint_completion_target | 0.9 | +------------------------------+--------------+ Backends: Total Backends: 5 (5.0% of max 100) Problematic: 0 waiting on locks, 4 waiting on other, 0 xact too long, 0 idle in xact Other Waiting Backends: +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ | PID | User | App | Client Addr | Database | Wait | Query Start | +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ | 2216 | postgres | citus_internal gpid=999999990000001858 | 192.168.1.73/32 | postgres | Client / ClientRead | 13 Jul 2024 11:11:56 PM | | 2220 | postgres | citus_internal gpid=999999990000001894 | 192.168.1.72/32 | postgres | Client / ClientRead | 13 Jul 2024 11:11:11 PM | | 2228 | postgres | citus_internal gpid=999999990000002355 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 11:05:11 PM | | 2237 | postgres | citus_internal gpid=999999990000002168 | 192.168.1.71/32 | postgres | Client / ClientRead | 13 Jul 2024 11:11:57 PM | +------+----------+----------------------------------------+-----------------+----------+---------------------+-------------------------+ Locks: +------------+-------------+-------+ | Lock Type | Not Granted | Total | +------------+-------------+-------+ | relation | 0 | 4 | | virtualxid | 0 | 1 | +------------+-------------+-------+ | | 0 | 5 | +------------+-------------+-------+ Vacuum Progress: No manual or auto vacuum jobs in progress. +------------------------------+----------------+ | Setting | Value | +------------------------------+----------------+ | maintenance_work_mem | 65536 (64 MiB) | | autovacuum | on | | autovacuum_analyze_threshold | 50 | | autovacuum_vacuum_threshold | 50 | | autovacuum_freeze_max_age | 200000000 | | autovacuum_max_workers | 3 | | autovacuum_naptime | 60 sec | | vacuum_freeze_min_age | 50000000 | | vacuum_freeze_table_age | 150000000 | +------------------------------+----------------+ Roles: +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | Name | Login | Repl | Super | Creat Rol | Creat DB | Bypass RLS | Inherit | Expires | Member Of | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ | postgres | yes | yes | yes | yes | yes | yes | yes | | | | pg_monitor | | | | | | | yes | | pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables | | pg_read_all_settings | | | | | | | yes | | | | pg_read_all_stats | | | | | | | yes | | | | pg_stat_scan_tables | | | | | | | yes | | | | pg_signal_backend | | | | | | | yes | | | | pg_checkpoint | | | | | | | yes | | | | pg_use_reserved_connections | | | | | | | yes | | | | pg_read_server_files | | | | | | | yes | | | | pg_write_server_files | | | | | | | yes | | | | pg_execute_server_program | | | | | | | yes | | | | pg_database_owner | | | | | | | yes | | | | pg_read_all_data | | | | | | | yes | | | | pg_write_all_data | | | | | | | yes | | | | pg_create_subscription | | | | | | | yes | | | +-----------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+ Tablespaces: +------------+----------+----------+---------+ | Name | Owner | Location | Size | +------------+----------+----------+---------+ | pg_default | postgres | | 24 MiB | | pg_global | postgres | | 564 KiB | +------------+----------+----------+---------+ Installed Extensions: +----------------+---------+------------------------------+ | Name | Version | Comment | +----------------+---------+------------------------------+ | citus | 12.2-1 | Citus distributed database | | citus_columnar | 12.2-1 | Citus Columnar extension | | plpgsql | 1.0 | PL/pgSQL procedural language | +----------------+---------+------------------------------+ Table #1 in "postgres": Name: postgres.public.enmotech_dist Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B Table #2 in "postgres": Name: postgres.public.enmotech_col Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B Table #3 in "postgres": Name: postgres.public.enmotech_ref Columns: 3 Manual Vacuums: never Manual Analyze: never Auto Vacuums: never Auto Analyze: never Post-Analyze: 0.0% est. rows modified Row Estimate: 0.0% live of total 0 Rows Changed: ins 0.0%, upd 0.0%, del 0.0% HOT Updates: 0.0% of all updates Seq Scans: 0, 0.0 rows/scan Idx Scans: 0, 0.0 rows/scan Cache Hits: 0.0% (idx=0.0%) Size: 0 B
3.6、要跳过索引和序列的收集
代码如下：
[postgres@Node1 pgmetrics_1.16.0_linux_amd64]$ ./pgmetrics --no-password --omit=indexes,sequences -h 192.168.1.72 postgres
显示结果：
pgmetrics run at: 13 Jul 2024 10:25:57 PM (now) PostgreSQL Cluster: Name: Server Version: 16.3 Server Started: 13 Jul 2024 9:17:58 PM (1 hour ago) System Identifier: 7390392581339028604 Timeline: 1 Last Checkpoint: 13 Jul 2024 10:17:58 PM (7 minutes ago) REDO LSN: 0/13000060 Checkpoint LSN: 0/13000098 (56 B since REDO) Transaction IDs: oldest = 723, next = 763, range = 40 Notification Queue: 0.0% used Active Backends: 5 (max 100) Recovery Mode? no WAL Files: WAL Archiving? yes WAL Files: 11 Ready Files: 0 Archive Rate: 0.28 per min Last Archived: 13 Jul 2024 10:18:05 PM (7 minutes ago) Last Failure: Totals: 19 succeeded, 0 failed Totals Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +--------------------+----------------+ | Setting | Value | +--------------------+----------------+ | wal_level | replica | | archive_timeout | 10 | | wal_compression | off | | max_wal_size | 1024 (1.0 GiB) | | min_wal_size | 80 (80 MiB) | | checkpoint_timeout | 300 | | full_page_writes | on | | wal_keep_size | 0 | +--------------------+----------------+ BG Writer: Checkpoint Rate: 0.21 per min Average Write: 393 KiB per checkpoint Total Checkpoints: 13 sched (92.9%) + 1 req (7.1%) = 14 Total Write: 8.7 MiB, @ 2.2 KiB per sec Buffers Allocated: 819 (6.4 MiB) Buffers Written: 687 chkpt (61.6%) + 0 bgw (0.0%) + 428 be (38.4%) Clean Scan Stops: 0 BE fsyncs: 0 Counts Since: 13 Jul 2024 9:17:58 PM (1 hour ago) +------------------------------+--------------+ | Setting | Value | +------------------------------+--------------+
3.7、 更多信息
生成JSON格式文件；生成csv格式文件。更多格式。。。
生成JSON格式文件；生成csv格式文件。更多格式。。。
四、总结
综上所述， pgmetrics 是一个强大而灵活的 PostgreSQL 数据库监控工具，它不仅能够提供实时的性能监控，还能协助进行故障排除和自动化操作。它的安装简便，功能丰富，并且易于与其他监控系统集成，是 PostgreSQL 数据库管理的得力助手。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
