本篇内容主要讲解“PostgreSQL中pgmetrics有什么作用”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“PostgreSQL中pgmetrics有什么作用”吧!

pgmetrics通过连接到数据库，获取数据库当前状态（大多数指标通过STAT系统动态视图获取）。

如果连接的是本地数据库，则同时会采集服务器的状态信息。

获取指标后，可以存储为JSON或TEXT格式。

pgmetrics--helppgmetricscollectsPostgreSQLinformationandmetrics.Usage:pgmetrics[OPTION]...[DBNAME]Generaloptions:-t,--timeout=SECSindividualquerytimeoutinseconds(default:5)-i,--input=FILEdon'tconnecttodb,insteadreadanddisplaythispreviouslysavedJSONfile-V,--versionoutputversioninformation,thenexit-?,--help[=options]showthishelp,thenexit--help=variableslistenvironmentvariables,thenexitCollectionoptions:-S,--no-sizesdon'tcollecttablespaceandrelationsizes-c,--schema=REGEXPcollectonlyfromschema(s)matchingPOSIXregexp-C,--exclude-schema=REGEXPdoNOTcollectfromschema(s)matchingPOSIXregexp-a,--table=REGEXPcollectonlyfromtable(s)matchingPOSIXregexp-A,--exclude-table=REGEXPdoNOTcollectfromtable(s)matchingPOSIXregexp--omit=WHATdoNOTcollecttheitemsspecifiedasacomma-separatedlistof:"tables","indexes","sequences","functions","extensions","triggers","statements"--sql-length=LIMITcollectonlyfirstLIMITcharactersofallSQLqueries(default:500)--statements-limit=LIMITcollectonlyutmostLIMITnumberofrowfrompg_stat_statements(default:100)Outputoptions:-f,--format=FORMAToutputformat;"human",or"json"(default:"human")-l,--toolong=SECSforhumanoutput,transactionsrunninglongerthanthisareconsideredtoolong(default:60)-o,--output=FILEwriteoutputtothespecifiedfile--no-pagerdonotinvokethepagerforttyoutputConnectionoptions:-h,--host=HOSTNAMEdatabaseserverhostorsocketdirectory(default:"/data01/pg11/pg_root1921")-p,--port=PORTdatabaseserverport(default:1921)-U,--username=USERNAMEdatabaseusername(default:"postgres")--no-passwordneverpromptforpasswordFormoreinformation,visit<https://pgmetrics.io>.pgmetrics 采集维度

pgmetrics通过数据库用户连接到数据库进行采集。

1、集群

2、数据库

3、操作系统

采集代码：

https://github.com/rapidloop/pgmetrics/blob/master/collector/collect.go

当采集数据的角色不为超级用户时，可能有些指标无法被正确采集（例如权限问题），但是此时并不会报错，只是输出可以正常被采集的指标并记录下来。

可以使用crontab，定期调用。

建议

1、设置语句超时时间，为业务可接受时间。（因为每个指标都要调用对应的SQL来进行采集，万一因为锁堵塞导致采集不及时，可以避免长时间等待）。

例子

1、超时时间为3秒，不输入密码，不分页，输出JSON格式，输出到文件，采集实例信息、系统信息、数据库postgres与newdb信息。

pgmetrics-t3--no-password--no-pager-fjson-o./log_`date+%s`postgresnewdb

lllog*-rw-r--r--1postgrespostgres89KOct123:14log_1538406857

2、同上，只是输出的为TEXT格式。

pgmetrics-t3--no-password--no-pager-fhumanpostgresnewdb

3、使用已保留的JSON文件，生成text报告.

pgmetrics-i./log_1538406857--no-pager|lessTEXT报告内容示例

pgmetrics-t3--no-password--no-pager-fhumanpostgresnewdb

pgmetricsrunat:1Oct201811:14:17PM(3minutesago)PostgreSQLCluster:Name:ServerVersion:11beta3ServerStarted:22Sep201811:31:32PM(1weekago)SystemIdentifier:6593269818598452546Timeline:1LastCheckpoint:1Oct201810:55:00PM(22minutesago)REDOLSN:29EE/89C3EA08CheckpointLSN:29F4/B02A04F0(25GiBsinceREDO)TransactionIDs:4030798045to633147358(diff=-3397650687)NotificationQueue:0.0%usedActiveBackends:35(max2000)RecoveryMode?noSystemInformation:Hostname:pg11-testCPUCores:64xIntel(R)Xeon(R)Platinum8163CPU@2.50GHzLoadAverage:35.17Memory:used=317GiB,free=5.4GiB,buff=377MiB,cache=181GiBSwap:used=0B,free=0B+---------------------------------+--------------------+|Setting|Value|+---------------------------------+--------------------+|shared_buffers|39321600(300GiB)||work_mem|8192(8.0MiB)||maintenance_work_mem|2097152(2.0GiB)||temp_buffers|1024(8.0MiB)||autovacuum_work_mem|-1||temp_file_limit|-1||max_worker_processes|128||autovacuum_max_workers|16||max_parallel_workers_per_gather|0||effective_io_concurrency|0|+---------------------------------+--------------------+WALFiles:WALArchiving?noWALFiles:9600+--------------------+------------------+|Setting|Value|+--------------------+------------------+|wal_level|minimal||archive_timeout|0||wal_compression|off||max_wal_size|614400(9.4TiB)||min_wal_size|153600(2.3TiB)||checkpoint_timeout|2100||full_page_writes|off||wal_keep_segments|0|+--------------------+------------------+BGWriter:CheckpointRate:0.03perminAverageWrite:118GiBpercheckpointTotalCheckpoints:355sched(88.8%)+45req(11.2%)=400TotalWrite:126TiB,@153MiBpersecBuffersAllocated:10825060769(81TiB)BuffersWritten:6157847013chkpt(36.3%)+6749938129bgw(39.8%)+4047065773be(23.9%)CleanScanStops:6107928BEfsyncs:0CountsSince:21Sep201810:42:07PM(1weekago)+------------------------------+--------------+|Setting|Value|+------------------------------+--------------+|bgwriter_delay|10msec||bgwriter_flush_after|64(512KiB)||bgwriter_lru_maxpages|1000||bgwriter_lru_multiplier|10||block_size|8192||checkpoint_timeout|2100sec||checkpoint_completion_target|1e-05|+------------------------------+--------------+Backends:TotalBackends:35(1.8%ofmax2000)Problematic:0waitingonlocks,8waitingonother,1xacttoolong,12idleinxactOtherWaitingBackends:+-------+----------+---------+-------------+----------+-----------------------+------------------------+|PID|User|App|ClientAddr|Database|Wait|QueryStart|+-------+----------+---------+-------------+----------+-----------------------+------------------------+|16514|postgres|pgbench||postgres|Client/ClientRead|1Oct201811:14:17PM||16515|postgres|pgbench||postgres|IPC/ClogGroupUpdate|1Oct201811:14:17PM||16517|postgres|pgbench||postgres|Client/ClientRead|1Oct201811:14:17PM||16523|postgres|pgbench||postgres|IPC/ClogGroupUpdate|1Oct201811:14:17PM||16527|postgres|pgbench||postgres|Client/ClientRead|1Oct201811:14:17PM||16533|postgres|pgbench||postgres|Client/ClientRead|1Oct201811:14:17PM||16535|postgres|pgbench||postgres|Client/ClientRead|1Oct201811:14:17PM||40144|postgres|psql||postgres|Client/ClientRead|1Oct201810:55:00PM|+-------+----------+---------+-------------+----------+-----------------------+------------------------+LongRunning(>60sec)Transactions:+-------+----------+------+-------------+----------+-----------------------------------------+|PID|User|App|ClientAddr|Database|TransactionStart|+-------+----------+------+-------------+----------+-----------------------------------------+|40259|postgres|psql||postgres|1Oct201810:54:56PM(22minutesago)|+-------+----------+------+-------------+----------+-----------------------------------------+IdlinginTransaction:+-------+----------+---------+-------------+----------+----------+------------------------+|PID|User|App|ClientAddr|Database|Aborted?|StateChange|+-------+----------+---------+-------------+----------+----------+------------------------+|16507|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16508|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16512|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16513|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16518|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16523|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16524|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16528|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16530|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16534|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16535|postgres|pgbench||postgres|no|1Oct201811:14:17PM||16537|postgres|pgbench||postgres|no|1Oct201811:14:17PM|+-------+----------+---------+-------------+----------+----------+------------------------+VacuumProgress:VacuumProcess#1:Phase:scanningheapDatabase:postgresTable:ScanProgress:201556359of205237504(98.2%complete)HeapBlksVac'ed:0of205237504IdxVacCycles:0DeadTuples:0DeadTuplesMax:291+------------------------------+-------------------+|Setting|Value|+------------------------------+-------------------+|maintenance_work_mem|2097152(2.0GiB)||autovacuum|on||autovacuum_analyze_threshold|50||autovacuum_vacuum_threshold|50||autovacuum_freeze_max_age|1200000000||autovacuum_max_workers|16||autovacuum_naptime|60sec||vacuum_freeze_min_age|50000000||vacuum_freeze_table_age|1150000000|+------------------------------+-------------------+Roles:+---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+|Name|Login|Repl|Super|CreatRol|CreatDB|BypassRLS|Inherit|Expires|MemberOf|+---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+|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_read_server_files|||||||yes||||pg_write_server_files|||||||yes||||pg_execute_server_program|||||||yes|||+---------------------------+-------+------+-------+-----------+----------+------------+---------+---------+--------------------------------------------------------------+Tablespaces:+------------+----------+------------------------------------+---------+----------------------------+-------------------------+|Name|Owner|Location|Size|DiskUsed|InodeUsed|+------------+----------+------------------------------------+---------+----------------------------+-------------------------+|pg_default|postgres|$PGDATA=/data01/pg11/pg_root1921|337GiB|494GiB(12.1%)of4.0TiB|59771(1.4%)of4194304||pg_global|postgres|$PGDATA=/data01/pg11/pg_root1921|598KiB|494GiB(12.1%)of4.0TiB|59771(1.4%)of4194304||tbs1|postgres|/data02/pg11/tbs1|1.9TiB|2.0TiB(49.9%)of4.0TiB|53907(1.3%)of4194304||tbs2|postgres|/data03/pg11/tbs2|2.2GiB|550GiB(13.4%)of4.0TiB|62361(1.5%)of4194304|+------------+----------+------------------------------------+---------+----------------------------+-------------------------+Database#1:Name:postgresOwner:postgresTablespace:pg_defaultConnections:35(nomaxlimit)FrozenXidAge:847687930Transactions:32236616423(100.0%)commits,148(0.0%)rollbacksCacheHits:100.0%RowsChanged:ins25.1%,upd74.9%,del0.0%TotalTemp:1.9GiBin4filesProblems:0deadlocks,0conflictsTotalsSince:21Sep201811:32:32PM(1weekago)Size:2.2TiBSequences:+------------------------+------------+|Sequence|CacheHits|+------------------------+------------+|part_p_partkey_seq|||region_r_regionkey_seq|||nation_n_nationkey_seq|||supplier_s_suppkey_seq|||customer_c_custkey_seq|||orders_o_orderkey_seq|||hints_id_seq|||user_order_id_seq|100.0%|+------------------------+------------+InstalledExtensions:+--------------------+---------+-------------------------------------------------------------------+|Name|Version|Comment|+--------------------+---------+-------------------------------------------------------------------+|btree_gin|1.3|supportforindexingcommondatatypesinGIN||cube|1.4|datatypeformultidimensionalcubes||dblink|1.2|connecttootherPostgreSQLdatabasesfromwithinadatabase||imgsmlr|1.0|imagesimilaritymodule||pg_hint_plan|1.3.0|||pg_oltp_bench|1.0|supportingfunctionforoltpbenchmark||pg_prewarm|1.2|prewarmrelationdata||pg_stat_statements|1.5|trackexecutionstatisticsofallSQLstatementsexecuted||pg_trgm|1.4|textsimilaritymeasurementandindexsearchingbasedontrigrams||pgsocket|1.0|TCPIPSocketclient||plpgsql|1.0|PL/pgSQLprocedurallanguage||rum|1.2|RUMindexaccessmethod|+--------------------+---------+-------------------------------------------------------------------+Database#2:Name:newdbOwner:postgresTablespace:pg_defaultConnections:0(nomaxlimit)FrozenXidAge:801198930Transactions:410(97.2%)commits,12(2.8%)rollbacksCacheHits:97.1%RowsChanged:ins100.0%,upd0.0%,del0.0%TotalTemp:0Bin0filesProblems:0deadlocks,0conflictsTotalsSince:1Oct201810:47:44PM(30minutesago)Size:23MiBInstalledExtensions:+---------+---------+------------------------------+|Name|Version|Comment|+---------+---------+------------------------------+|plpgsql|1.0|PL/pgSQLprocedurallanguage|+---------+---------+------------------------------+Table#1in"postgres":Name:postgres.public.partColumns:9ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal39999206RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:78.2%(idx=75.0%)Size:6.6GiBBloat:442MiB(6.6%)+--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+|idx_part__6|btree|857MiB|442MiB(51.5%)|75.0%|0|0.0|0.0||idx_part__5|btree|1.5GiB|442MiB(28.1%)|75.0%|0|0.0|0.0||idx_part_p_1|btree|9.3MiB|442MiB(4734.9%)|75.0%|0|0.0|0.0||idx_part_1|btree|1.5GiB|442MiB(28.5%)|75.0%|0|0.0|0.0||part_pkey|btree|857MiB|442MiB(51.5%)|75.0%|0|0.0|0.0|+--------------+-------+---------+-------------------+------------+-------+----------------+-------------------+Table#2in"postgres":Name:postgres.public.regionColumns:3ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal5RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:83.0%(idx=75.0%)Size:40KiB+-------------+-------+--------+-------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-------------+-------+--------+-------+------------+-------+----------------+-------------------+|region_pkey|btree|16KiB||75.0%|0|0.0|0.0||idx5|btree|16KiB||75.0%|0|0.0|0.0|+-------------+-------+--------+-------+------------+-------+----------------+-------------------+Table#3in"postgres":Name:postgres.public.nationColumns:4ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal25RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:83.0%(idx=75.0%)Size:40KiB+----------------------+-------+--------+-------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+----------------------+-------+--------+-------+------------+-------+----------------+-------------------+|idx6|btree|16KiB||75.0%|0|0.0|0.0||nation_pkey|btree|16KiB||75.0%|0|0.0|0.0||idx_nation_regionkey|btree|16KiB||75.0%|0|0.0|0.0|+----------------------+-------+--------+-------+------------+-------+----------------+-------------------+Table#4in"postgres":Name:postgres.public.supplierColumns:7ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal1999109RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:79.4%(idx=75.0%)Size:369MiBBloat:9.5MiB(2.6%)+-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+|supplier_pkey|btree|43MiB|9.5MiB(22.1%)|75.0%|0|0.0|0.0||idx4|btree|60MiB|9.5MiB(15.7%)|75.0%|0|0.0|0.0||idx_supplier_nation_key|btree|43MiB|9.5MiB(22.1%)|75.0%|0|0.0|0.0|+-------------------------+-------+--------+-----------------+------------+-------+----------------+-------------------+Table#5in"postgres":Name:postgres.public.customerColumns:8ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal29998813RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:78.2%(idx=75.0%)Size:5.8GiBBloat:92MiB(1.5%)+---------------+-------+---------+----------------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+---------------+-------+---------+----------------+------------+-------+----------------+-------------------+|customer_pkey|btree|643MiB|92MiB(14.3%)|75.0%|0|0.0|0.0|+---------------+-------+---------+----------------+------------+-------+----------------+-------------------+Table#6in"postgres":Name:postgres.public.partsuppColumns:5ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal159999995RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:78.1%(idx=75.0%)Size:29GiBBloat:587MiB(2.0%)+----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+|idx_partsupp_suppkey|btree|3.3GiB|587MiB(17.1%)|75.0%|0|0.0|0.0||idx_partsupp_partkey|btree|3.3GiB|587MiB(17.1%)|75.0%|0|0.0|0.0||idx|btree|6.1GiB|587MiB(9.5%)|75.0%|0|0.0|0.0||partsupp_pkey|btree|4.7GiB|587MiB(12.2%)|75.0%|0|0.0|0.0||idx_partsupp__4|btree|3.3GiB|587MiB(17.1%)|75.0%|0|0.0|0.0|+----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+Table#7in"postgres":Name:postgres.public.ordersColumns:9ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal299997901RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:78.1%(idx=75.0%)Size:43GiBBloat:1.8GiB(4.2%)+----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+|idx_orders_orderdate|btree|6.3GiB|1.8GiB(28.7%)|75.0%|0|0.0|0.0||idx_orders__6|btree|8.8GiB|1.8GiB(20.5%)|75.0%|0|0.0|0.0||idx_orders_custkey|btree|6.3GiB|1.8GiB(28.7%)|75.0%|0|0.0|0.0||orders_pkey|btree|6.3GiB|1.8GiB(28.7%)|75.0%|0|0.0|0.0|+----------------------+-------+---------+-----------------+------------+-------+----------------+-------------------+Table#8in"postgres":Name:postgres.public.lineitemColumns:16ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal1200018409RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:78.1%(idx=77.1%)Size:199GiBBloat:6.6GiB(3.3%)+-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+|idx_lineitem_orderkey|btree|25GiB|6.6GiB(26.4%)|77.4%|0|0.0|0.0||idx1|btree|16GiB|6.6GiB(41.8%)|77.4%|0|0.0|0.0||idx_lineitem_shipdate|btree|45GiB|6.6GiB(14.6%)|77.4%|0|0.0|0.0||idx_lineitem__2|btree|25GiB|6.6GiB(26.4%)|77.4%|0|0.0|0.0||idx_lineitem__3|btree|25GiB|6.6GiB(26.4%)|77.4%|0|0.0|0.0||idx_lineitem__11|btree|25GiB|6.6GiB(26.4%)|77.4%|0|0.0|0.0||idx_part_l_2|btree|5.3GiB|6.6GiB(124.0%)|77.4%|0|0.0|0.0||lineitem_pkey|btree|35GiB|6.6GiB(18.8%)|75.0%|0|0.0|0.0|+-----------------------+-------+---------+------------------+------------+-------+----------------+-------------------+Table#9in"postgres":Name:postgres.hint_plan.hintsColumns:4ManualVacuums:1,last6hoursagoManualAnalyze:neverAutoVacuums:31,last2hoursagoAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:0.0%liveoftotal0RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:75.0%(idx=75.0%)Size:8.0KiB+--------------------+-------+---------+-------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+--------------------+-------+---------+-------+------------+-------+----------------+-------------------+|hints_norm_and_app|btree|8.0KiB||75.0%|0|0.0|0.0||hints_pkey|btree|8.0KiB||75.0%|0|0.0|0.0|+--------------------+-------+---------+-------+------------+-------+----------------+-------------------+Table#10in"postgres":Name:postgres.public.pgbench_historyTablespace:tbs1Columns:6ManualVacuums:5,last6hoursagoManualAnalyze:1,last4daysagoAutoVacuums:26,last2hoursagoAutoAnalyze:70,last7hoursagoPost-Analyze:7.4%est.rowsmodifiedRowEstimate:100.0%liveoftotal32165335009RowsChanged:ins100.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:99.4%(idx=0.0%)Size:1.5TiBTable#11in"postgres":Name:postgres.public.pgbench_tellersTablespace:tbs1Columns:4ManualVacuums:4,last6minutesagoManualAnalyze:1,last4daysagoAutoVacuums:7581,last4minutesagoAutoAnalyze:7581,last4minutesagoPost-Analyze:6219.9%est.rowsmodifiedRowEstimate:17.8%liveoftotal56200RowsChanged:ins0.0%,upd99.9%,del0.0%HOTUpdates:99.9%ofallupdatesSeqScans:1,10000.0rows/scanIdxScans:32236503570,1.0rows/scanCacheHits:100.0%(idx=100.0%)Size:174MiBBloat:253MiB(145.3%)+----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+|pgbench_tellers_pkey|btree|80MiB|253MiB(316.8%)|100.0%|32236503570|1.0|1.0|+----------------------+-------+--------+------------------+------------+-------------+----------------+-------------------+Table#12in"postgres":Name:postgres.public.pgbench_accountsTablespace:tbs1Columns:4ManualVacuums:5,last6minutesagoManualAnalyze:2,last4daysagoAutoVacuums:27,last2hoursagoAutoAnalyze:2828,last6minutesagoPost-Analyze:13.1%est.rowsmodifiedRowEstimate:98.3%liveoftotal101746633RowsChanged:ins0.3%,upd99.7%,del0.0%HOTUpdates:100.0%ofallupdatesSeqScans:1,100000000.0rows/scanIdxScans:64473007140,1.0rows/scanCacheHits:100.0%(idx=100.0%)Size:13GiBBloat:977MiB(7.2%)+-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+|pgbench_accounts_pkey|btree|2.1GiB|977MiB(45.6%)|100.0%|64473007140|1.0|1.0|+-----------------------+-------+---------+-----------------+------------+-------------+----------------+-------------------+Table#13in"postgres":Name:postgres.public.pgbench_branchesTablespace:tbs1Columns:3ManualVacuums:4,last6minutesagoManualAnalyze:1,last4daysagoAutoVacuums:7578,last4minutesagoAutoAnalyze:7578,last4minutesagoPost-Analyze:22546.2%est.rowsmodifiedRowEstimate:6.5%liveoftotal15504RowsChanged:ins0.0%,upd99.9%,del0.0%HOTUpdates:99.9%ofallupdatesSeqScans:2,1000.0rows/scanIdxScans:32236503570,1.0rows/scanCacheHits:100.0%(idx=100.0%)Size:65MiBBloat:96MiB(147.5%)+-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+|pgbench_branches_pkey|btree|31MiB|96MiB(308.9%)|100.0%|32236503570|1.1|1.0|+-----------------------+-------+--------+-----------------+------------+-------------+----------------+-------------------+Table#14in"postgres":Name:postgres.public.user_orderColumns:11ManualVacuums:2,last22minutesagoManualAnalyze:neverAutoVacuums:1,last3hoursagoAutoAnalyze:1,last7hoursagoPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal999928RowsChanged:ins100.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:1,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:98.4%(idx=99.8%)Size:135MiBBloat:24MiB(17.7%)+-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+|user_order_pkey|btree|21MiB|24MiB(111.0%)|99.8%|0|0.0|0.0|+-----------------+-------+--------+-----------------+------------+-------+----------------+-------------------+Table#1in"newdb":Name:newdb.public.pgbench_historyColumns:6ManualVacuums:1,last30minutesagoManualAnalyze:1,last30minutesagoAutoVacuums:neverAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:0.0%liveoftotal0RowsChanged:ins0.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:0,0.0rows/scanIdxScans:0,0.0rows/scanCacheHits:0.0%(idx=0.0%)Size:0BTable#2in"newdb":Name:newdb.public.pgbench_tellersColumns:4ManualVacuums:1,last30minutesagoManualAnalyze:1,last30minutesagoAutoVacuums:neverAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal10RowsChanged:ins100.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:1,10.0rows/scanIdxScans:0,0.0rows/scanCacheHits:76.2%(idx=0.0%)Size:40KiB+----------------------+-------+--------+-------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+----------------------+-------+--------+-------+------------+-------+----------------+-------------------+|pgbench_tellers_pkey|btree|16KiB|||0|0.0|0.0|+----------------------+-------+--------+-------+------------+-------+----------------+-------------------+Table#3in"newdb":Name:newdb.public.pgbench_accountsColumns:4ManualVacuums:1,last30minutesagoManualAnalyze:1,last30minutesagoAutoVacuums:neverAutoAnalyze:neverPost-Analyze:0.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal100000RowsChanged:ins100.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:1,100000.0rows/scanIdxScans:0,0.0rows/scanCacheHits:80.0%(idx=0.0%)Size:13MiBBloat:192KiB(1.5%)+-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+|pgbench_accounts_pkey|btree|2.2MiB|192KiB(8.7%)||0|0.0|0.0|+-----------------------+-------+---------+----------------+------------+-------+----------------+-------------------+Table#4in"newdb":Name:newdb.public.pgbench_branchesColumns:3ManualVacuums:1,last30minutesagoManualAnalyze:1,last30minutesagoAutoVacuums:neverAutoAnalyze:neverPost-Analyze:100.0%est.rowsmodifiedRowEstimate:100.0%liveoftotal1RowsChanged:ins100.0%,upd0.0%,del0.0%HOTUpdates:0.0%ofallupdatesSeqScans:1,1.0rows/scanIdxScans:0,0.0rows/scanCacheHits:58.3%(idx=0.0%)Size:40KiB+-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+|Index|Type|Size|Bloat|CacheHits|Scans|RowsRead/Scan|RowsFetched/Scan|+-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+|pgbench_branches_pkey|btree|16KiB|||0|0.0|0.0|+-----------------------+-------+--------+-------+------------+-------+----------------+-------------------+小结

1、pgmetrics用法非常简单，连接到数据库，获取内置的监测指标的内容。

pgmetrics--helppgmetricscollectsPostgreSQLinformationandmetrics.Usage:pgmetrics[OPTION]...[DBNAME]Generaloptions:-t,--timeout=SECSindividualquerytimeoutinseconds(default:5)-i,--input=FILEdon'tconnecttodb,insteadreadanddisplaythispreviouslysavedJSONfile-V,--versionoutputversioninformation,thenexit-?,--help[=options]showthishelp,thenexit--help=variableslistenvironmentvariables,thenexitCollectionoptions:-S,--no-sizesdon'tcollecttablespaceandrelationsizes-c,--schema=REGEXPcollectonlyfromschema(s)matchingPOSIXregexp-C,--exclude-schema=REGEXPdoNOTcollectfromschema(s)matchingPOSIXregexp-a,--table=REGEXPcollectonlyfromtable(s)matchingPOSIXregexp-A,--exclude-table=REGEXPdoNOTcollectfromtable(s)matchingPOSIXregexp--omit=WHATdoNOTcollecttheitemsspecifiedasacomma-separatedlistof:"tables","indexes","sequences","functions","extensions","triggers","statements"--sql-length=LIMITcollectonlyfirstLIMITcharactersofallSQLqueries(default:500)--statements-limit=LIMITcollectonlyutmostLIMITnumberofrowfrompg_stat_statements(default:100)Outputoptions:-f,--format=FORMAToutputformat;"human",or"json"(default:"human")-l,--toolong=SECSforhumanoutput,transactionsrunninglongerthanthisareconsideredtoolong(default:60)-o,--output=FILEwriteoutputtothespecifiedfile--no-pagerdonotinvokethepagerforttyoutputConnectionoptions:-h,--host=HOSTNAMEdatabaseserverhostorsocketdirectory(default:"/data01/pg11/pg_root1921")-p,--port=PORTdatabaseserverport(default:1921)-U,--username=USERNAMEdatabaseusername(default:"postgres")--no-passwordneverpromptforpasswordFormoreinformation,visit<https://pgmetrics.io>.

2、用户可以使用pgmetrics定义收集数据库状态信息。

3、结合pgdash，可以将pgmetrics收集的内容可视化，并提供告警能力。

4、我在之前还写过一些数据库指标监控的文档，请参考末尾。

5、如发现需要新增的指标，或者有些指标不正确，可以发ISSUE给作者。

目前pgmetrics的指标已经比较丰富，可以用于日常的状态和性能监控。

到此，相信大家对“PostgreSQL中pgmetrics有什么作用”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！