[root@node1 pgsoft]# ls
pg14 pgmetrics_1.15.2_linux_amd64.tar.gz pg_recovery-master pg_recovery-master.zip postgresql-14.6
[root@node1 pgsoft]# tar -xvf pgmetrics_1.15.2_linux_amd64.tar.gz
pgmetrics_1.15.2_linux_amd64/LICENSE
pgmetrics_1.15.2_linux_amd64/README.md
pgmetrics_1.15.2_linux_amd64/pgmetrics
tar: pgmetrics_1.15.2_linux_amd64/pgmetrics: time stamp 2023-08-27 16:32:00 is 862525.069943984 s in the future
[root@node1 pgsoft]# ls
pg14 pgmetrics_1.15.2_linux_amd64 pgmetrics_1.15.2_linux_amd64.tar.gz pg_recovery-master pg_recovery-master.zip postgresql-14.6
[root@node1 pgsoft]# cd pgmetrics_1.15.2_linux_amd64/
[root@node1 pgmetrics_1.15.2_linux_amd64]# ls
LICENSE pgmetrics README.md
3.使用
[root@node1 pgmetrics_1.15.2_linux_amd64]# ./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: "root")
-w, --no-password never prompt for password
--role=ROLE do SET ROLE before collection
For more information, visit <https://pgmetrics.io>.
二、使用方法
[pg14@node1 pgmetrics_1.15.2_linux_amd64]$ ./pgmetrics -Utest2 -p5666 -hnode1
Password: pgmetrics: warning: failed to guess log file location/access denied, specify explicitly with --log-file or --log-dir
pgmetrics run at: 17 Aug 2023 5:10:46 PM (now)
PostgreSQL Cluster:
Name:
Server Version: 14.6
Server Started: 17 Aug 2023 5:08:19 PM (2 minutes ago)
System Identifier: 7267837774730583287
Timeline: 1
Last Checkpoint: 17 Aug 2023 5:08:05 PM (2 minutes ago)
REDO LSN: 0/19FF600
Checkpoint LSN: 0/19FF600 (0 B since REDO)
Transaction IDs: oldest = 727, next = 764, range = 37
Notification Queue: 0.0% used
Active Backends: 1 (max 100)
Recovery Mode? no
System Information:
Hostname: node1
CPU Cores: 1 x Intel(R) Core(TM) i5-1035G1 CPU @ 1.00GHz
Load Average: 0.01
Memory: used=490 MiB, free=266 MiB, buff=2.3 MiB, cache=2.4 GiB
Swap: used=264 KiB, free=3.0 GiB
+---------------------------------+-----------------+
| Setting | Value |
+---------------------------------+-----------------+
| shared_buffers | 16384 (128 MiB) |
| work_mem | 4096 (4.0 MiB) |
| maintenance_work_mem | 65536 (64 MiB) |
| temp_buffers | 1024 (8.0 MiB) |
| autovacuum_work_mem | -1 |
| temp_file_limit | -1 |
| max_worker_processes | 8 |
| autovacuum_max_workers | 3 |
| max_parallel_workers_per_gather | 2 |
| effective_io_concurrency | 1 |
+---------------------------------+-----------------+
WAL Files:
WAL Archiving? no
WAL Files: 1
+--------------------+----------------+
| Setting | Value |
+--------------------+----------------+
| wal_level | replica |
| archive_timeout | 0 |
| 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: 4.5 KiB per checkpoint
Total Checkpoints: 288 sched (97.0%) + 9 req (3.0%) = 297
Total Write: 1.4 MiB, @ 16 B per sec
Buffers Allocated: 1090 (8.5 MiB)
Buffers Written: 167 chkpt (94.9%) + 0 bgw (0.0%) + 9 be (5.1%)
Clean Scan Stops: 0
BE fsyncs: 0
Counts Since: 16 Aug 2023 4:42:48 PM (1 day 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: 1 (1.0% of max 100)
Problematic: 0 waiting on locks, 0 waiting on other, 0 xact too long, 0 idle in xact
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 |
+---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
| pg14 | 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_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 | | |
| test2 | yes | | yes | | | | yes | | |
+---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+
Tablespaces:
+------------+-------+---------------------+---------+--------------------------+---------------------------+
| Name | Owner | Location | Size | Disk Used | Inode Used |
+------------+-------+---------------------+---------+--------------------------+---------------------------+
| pg_default | pg14 | $PGDATA = /pgdata02 | 33 MiB | 18 GiB (68.9%) of 26 GiB | 267255 (2.0%) of 13629440 |
| pg_global | pg14 | $PGDATA = /pgdata02 | 560 KiB | 18 GiB (68.9%) of 26 GiB | 267255 (2.0%) of 13629440 |
+------------+-------+---------------------+---------+--------------------------+---------------------------+
Database #1:
Name: postgres
Owner: pg14
Tablespace: pg_default
Connections: 0 (no max limit)
Frozen Xid Age: 37
Transactions: 2910 (100.0%) commits, 0 (0.0%) rollbacks
Cache Hits: 99.8%
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: 16 Aug 2023 4:43:11 PM (1 day ago)
Size: 8.3 MiB
Database #2:
Name: test2
Owner: pg14
Tablespace: pg_default
Connections: 1 (no max limit)
Frozen Xid Age: 37
Transactions: 2978 (99.8%) commits, 6 (0.2%) rollbacks
Cache Hits: 99.5%
Rows Changed: ins 77.5%, upd 6.1%, del 16.5%
Total Temp: 0 B in 0 files
Problems: 0 deadlocks, 0 conflicts
Totals Since: 16 Aug 2023 4:43:46 PM (1 day ago)
Size: 8.4 MiB
Installed Extensions:
+-------------+---------+---------------------------------------------------------------------+
| Name | Version | Comment |
+-------------+---------+---------------------------------------------------------------------+
| pageinspect | 1.9 | inspect the contents of database pages at a low level |
| pg_recovery | 1.0 | recovery table data of update/delete/rollback rows and drop columns |
| plpgsql | 1.0 | PL/pgSQL procedural language |
+-------------+---------+---------------------------------------------------------------------+
Table #1 in "test2":
Name: test2.public.t_insert
Columns: 4
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 118.2% est. rows modified
Row Estimate: 81.8% live of total 11
Rows Changed: ins 84.6%, upd 0.0%, del 15.4%
HOT Updates: 0.0% of all updates
Seq Scans: 3, 8.3 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 88.9% (idx=0.0%)
Size: 8.0 KiB
Table #2 in "test2":
Name: test2.public.t1
Columns: 2
Manual Vacuums: never
Manual Analyze: never
Auto Vacuums: never
Auto Analyze: never
Post-Analyze: 125.0% est. rows modified
Row Estimate: 25.0% live of total 8
Rows Changed: ins 54.5%, upd 18.2%, del 27.3%
HOT Updates: 100.0% of all updates
Seq Scans: 10, 5.6 rows/scan
Idx Scans: 0, 0.0 rows/scan
Cache Hits: 97.0% (idx=0.0%)
Size: 8.0 KiB