标准 Unix 工具
在大多数 Unix 平台上，PostgreSQL会修改ps报告的命令标题，以便可以轻松识别各个服务器进程。示例显示如下
$ ps auxww | grep ^postgres postgres 15551 0.0 0.1 57536 7132 pts/0 S 18:02 0:00 postgres -i postgres 15554 0.0 0.0 57536 1184 ? Ss 18:02 0:00 postgres: writer process postgres 15555 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: checkpointer process postgres 15556 0.0 0.0 57536 916 ? Ss 18:02 0:00 postgres: wal writer process postgres 15557 0.0 0.0 58504 2244 ? Ss 18:02 0:00 postgres: autovacuum launcher process postgres 15558 0.0 0.0 17512 1068 ? Ss 18:02 0:00 postgres: stats collector process postgres 15582 0.0 0.0 58772 3080 ? Ss 18:04 0:00 postgres: joe runbug 127.0.0.1 idle postgres 15606 0.0 0.0 58772 3052 ? Ss 18:07 0:00 postgres: tgl regression [local] SELECT waiting postgres 15610 0.0 0.0 58772 3056 ? Ss 18:07 0:00 postgres: tgl regression [local] idle in transaction
（ ps的适当调用因平台不同而不同，显示内容的细节也不同。此示例来自最近的 Linux 系统。）此处列出的第一个进程是主服务器进程。显示的命令参数与启动时使用的参数相同。接下来的五个进程是主进程自动启动的后台工作进程。（如果您已将系统设置为不启动统计信息收集器，则“统计信息收集器”进程将不存在；同样，可以禁用“自动清理启动器”进程。）其余每个进程都是处理一个客户端连接的服务器进程。每个这样的进程都将其命令行显示设置为以下形式
postgres: user database host activity
用户、数据库和（客户端）主机项在客户端连接的整个生命周期内保持不变，但活动指示器会发生变化。活动可以是空闲（即等待客户端命令）、事务空闲（在BEGIN块内等待客户端）或命令类型名称（例如SELECT）。此外，如果服务器进程当前正在等待另一个会话持有的锁，则会附加等待。在上面的例子中，我们可以推断出进程 15606 正在等待进程 15610 完成其事务并从而释放一些锁。（进程 15610 一定是阻止者，因为没有其他活动会话。在更复杂的情况下，有必要查看pg_locks系统视图以确定谁在阻止谁。）
如果已配置cluster_name，则集群名称也将显示在ps输出中：
$ psql -c 'SHOW cluster_name' cluster_name -------------- server1 (1 row) $ ps aux|grep server1 postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: server1: writer process ...
如果您关闭了update_process_title，则活动指示器不会更新；进程标题仅在启动新进程时设置一次。在某些平台上，这可以节省大量每个命令的开销；在其他平台上，这微不足道。
统计信息收集器
由于统计信息的收集会给查询执行增加一些开销，因此可以配置系统收集或不收集信息。这由通常在postgresql.conf中设置的配置参数控制。（有关设置配置参数的详细信息，请参阅第 19 章。）
参数track_activities可以监视任何服务器进程正在执行的当前命令。
参数track_counts控制是否收集有关表和索引访问的统计数据。
参数track_functions可以跟踪用户定义函数的使用情况。
参数track_io_timing可以监控块的读写时间。
通常这些参数在postgresql.conf中设置，以便它们适用于所有服务器进程，但是可以使用SET命令在各个会话中打开或关闭它们。
另一个要点是，当要求服务器进程显示任何这些统计信息时，它首先获取收集器进程发出的最新报告，然后继续将此快照用于所有统计视图和函数，直到其当前事务结束。因此，只要您继续当前事务，统计信息就会显示静态信息。同样，当在事务内首次请求任何此类信息时，将收集有关所有会话的当前查询的信息，并且相同的信息将在整个事务中显示。这是一个功能，而不是错误，因为它允许您对统计信息执行多个查询并关联结果，而不必担心数字在您下面发生变化。但是，如果您想看到每个查询的新结果，请确保在任何事务块之外执行查询。或者，您可以调用pg_stat_clear_snapshot（），这将丢弃当前事务的统计信息快照（如果有）。统计信息的下一次使用将导致获取新的快照。
事务还可以在视图pg_stat_xact_all_tables、pg_stat_xact_sys_tables、pg_stat_xact_user_tables和pg_stat_xact_user_functions中查看自己的统计信息（尚未传输到收集器） 。这些数字的作用与上述不同；相反，它们在整个事务过程中不断更新。
动态统计视图
| View Name | Description |
|---|---|
| pg_stat_activity | One row per server process, showing information related to the current activity of that process, such as state and current query. See pg_stat_activity for details. |
| pg_stat_replication | One row per WAL sender process, showing statistics about replication to that sender's connected standby server. See pg_stat_replication for details. |
| pg_stat_wal_receiver | Only one row, showing statistics about the WAL receiver from that receiver's connected server. See pg_stat_wal_receiver for details. |
| pg_stat_ssl | One row per connection (regular and replication), showing information about SSL used on this connection. See pg_stat_ssl for details. |
| pg_stat_progress_vacuum | One row for each backend (including autovacuum worker processes) running VACUUM, showing current progress. |
收集的统计信息视图
| View Name | Description |
|---|---|
| pg_stat_archiver | One row only, showing statistics about the WAL archiver process's activity. See pg_stat_archiver for details. |
| pg_stat_bgwriter | One row only, showing statistics about the background writer process's activity. See pg_stat_bgwriter for details. |
| pg_stat_database | One row per database, showing database-wide statistics. See pg_stat_database for details. |
| pg_stat_database_conflicts | One row per database, showing database-wide statistics about query cancels due to conflict with recovery on standby servers. See pg_stat_database_conflicts for details. |
| pg_stat_all_tables | One row for each table in the current database, showing statistics about accesses to that specific table. See pg_stat_all_tables for details. |
| pg_stat_sys_tables | Same as pg_stat_all_tables, except that only system tables are shown. |
| pg_stat_user_tables | Same as pg_stat_all_tables, except that only user tables are shown. |
| pg_stat_xact_all_tables | Similar to pg_stat_all_tables, but counts actions taken so far within the current transaction (which are not yet included in pg_stat_all_tables and related views). The columns for numbers of live and dead rows and vacuum and analyze actions are not present in this view. |
| pg_stat_xact_sys_tables | Same as pg_stat_xact_all_tables, except that only system tables are shown. |
| pg_stat_xact_user_tables | Same as pg_stat_xact_all_tables, except that only user tables are shown. |
| pg_stat_all_indexes | One row for each index in the current database, showing statistics about accesses to that specific index. See pg_stat_all_indexes for details. |
| pg_stat_sys_indexes | Same as pg_stat_all_indexes, except that only indexes on system tables are shown. |
| pg_stat_user_indexes | Same as pg_stat_all_indexes, except that only indexes on user tables are shown. |
| pg_statio_all_tables | One row for each table in the current database, showing statistics about I/O on that specific table. See pg_statio_all_tables for details. |
| pg_statio_sys_tables | Same as pg_statio_all_tables, except that only system tables are shown. |
| pg_statio_user_tables | Same as pg_statio_all_tables, except that only user tables are shown. |
| pg_statio_all_indexes | One row for each index in the current database, showing statistics about I/O on that specific index. See pg_statio_all_indexes for details. |
| pg_statio_sys_indexes | Same as pg_statio_all_indexes, except that only indexes on system tables are shown. |
| pg_statio_user_indexes | Same as pg_statio_all_indexes, except that only indexes on user tables are shown. |
| pg_statio_all_sequences | One row for each sequence in the current database, showing statistics about I/O on that specific sequence. See pg_statio_all_sequences for details. |
| pg_statio_sys_sequences | Same as pg_statio_all_sequences, except that only system sequences are shown. (Presently, no system sequences are defined, so this view is always empty.) |
| pg_statio_user_sequences | Same as pg_statio_all_sequences, except that only user sequences are shown. |
| pg_stat_user_functions | One row for each tracked function, showing statistics about executions of that function. See pg_stat_user_functions for details. |
| pg_stat_xact_user_functions | Similar to pg_stat_user_functions, but counts only calls during the current transaction (which are not yet included in pg_stat_user_functions). |
每个索引的统计数据对于确定正在使用哪些索引以及它们的有效性特别有用。
pg_statio_视图主要用于确定缓冲区缓存的有效性。当实际磁盘读取次数远小于缓冲区命中次数时，缓存可以满足大多数读取请求而无需调用内核调用。但是，这些统计数据并不能说明全部情况：由于PostgreSQL处理磁盘 I/O 的方式，不在PostgreSQL缓冲区缓存中的数据可能仍驻留在内核的 I/O 缓存中，因此仍可能被获取而无需物理读取。建议有兴趣获取有关PostgreSQL I/O 行为的更多详细信息的用户将PostgreSQL统计信息收集器与操作系统实用程序结合使用，以便深入了解内核对 I/O 的处理。
查看锁
另一个用于监控数据库活动的有用工具是pg_locks系统表。它允许数据库管理员查看锁管理器中未完成锁的信息。例如，此功能可用于：
-
查看当前未完成的所有锁、特定数据库中关系上的所有锁、特定关系上的所有锁或特定PostgreSQL会话持有的所有锁。
-
确定当前数据库中具有最多未授予锁的关系（这可能是数据库客户端之间的争用根源）。
-
确定锁争用对整体数据库性能的影响，以及争用随整体数据库流量变化的程度。
pg_locks视图的详细信息见第 50.65 节。有关使用PostgreSQL锁定和管理并发性的更多信息，请参阅第 13 章。
进度报告
PostgreSQL有能力在命令执行期间报告某些命令的进度。目前，唯一支持进度报告的命令是VACUUM。未来可能会扩展此功能。
每当VACUUM运行时，pg_stat_progress_vacuum视图将为当前正在清理的每个后端（包括自动清理工作进程）包含一行。下表描述了将报告的信息并提供了有关如何解释这些信息的信息。VACUUM FULL目前不支持进度报告，并且运行VACUUM FULL的后端不会在此视图中列出。
Table 28-20. pg_stat_progress_vacuum View
| Column | Type | Description |
|---|---|---|
| pid | integer | Process ID of backend. |
| datid | oid | OID of the database to which this backend is connected. |
| datname | name | Name of the database to which this backend is connected. |
| relid | oid | OID of the table being vacuumed. |
| phase | text | Current processing phase of vacuum. See Table 28-21. |
| heap_blks_total | bigint | Total number of heap blocks in the table. This number is reported as of the beginning of the scan; blocks added later will not be (and need not be) visited by this VACUUM. |
| heap_blks_scanned | bigint | Number of heap blocks scanned. Because the visibility map is used to optimize scans, some blocks will be skipped without inspection; skipped blocks are included in this total, so that this number will eventually become equal to heap_blks_total when the vacuum is complete. This counter only advances when the phase is scanning heap. |
| heap_blks_vacuumed | bigint | Number of heap blocks vacuumed. Unless the table has no indexes, this counter only advances when the phase is vacuuming heap. Blocks that contain no dead tuples are skipped, so the counter may sometimes skip forward in large increments. |
| index_vacuum_count | bigint | Number of completed index vacuum cycles. |
| max_dead_tuples | bigint | Number of dead tuples that we can store before needing to perform an index vacuum cycle, based on maintenance_work_mem. |
| num_dead_tuples | bigint | Number of dead tuples collected since the last index vacuum cycle. |
Table 28-21. VACUUM phases
| Phase | Description |
|---|---|
| initializing | VACUUM is preparing to begin scanning the heap. This phase is expected to be very brief. |
| scanning heap | VACUUM is currently scanning the heap. It will prune and defragment each page if required, and possibly perform freezing activity. The heap_blks_scanned column can be used to monitor the progress of the scan. |
| vacuuming indexes | VACUUM is currently vacuuming the indexes. If a table has any indexes, this will happen at least once per vacuum, after the heap has been completely scanned. It may happen multiple times per vacuum if maintenance_work_mem (or, in the case of autovacuum, autovacuum_work_mem if set) is insufficient to store the number of dead tuples found. |
| vacuuming heap | VACUUM is currently vacuuming the heap. Vacuuming the heap is distinct from scanning the heap, and occurs after each instance of vacuuming indexes. If heap_blks_scanned is less than heap_blks_total, the system will return to scanning the heap after this phase is completed; otherwise, it will begin cleaning up indexes after this phase is completed. |
| cleaning up indexes | VACUUM is currently cleaning up indexes. This occurs after the heap has been completely scanned and all vacuuming of the indexes and the heap has been completed. |
| truncating heap | VACUUM is currently truncating the heap so as to return empty pages at the end of the relation to the operating system. This occurs after cleaning up indexes. |
| performing final cleanup | VACUUM is performing final cleanup. During this phase, VACUUM will vacuum the free space map, update statistics in pg_class, and report statistics to the statistics collector. When this phase is completed, VACUUM will end. |
动态跟踪
PostgreSQL提供支持数据库服务器动态跟踪的功能。这允许在代码中的特定点调用外部实用程序，从而跟踪执行情况。
源代码中已插入许多探测器或跟踪点。这些探测器旨在供数据库开发人员和管理员使用。默认情况下，探测器不会编译到PostgreSQL中；用户需要明确告知配置脚本以使探测器可用。
默认情况下，探测器不可用，因此您需要明确告诉 configure 脚本使探测器在PostgreSQL中可用。要包含 DTrace 支持，请在 configure 中指定--enable-dtrace 。
https://www.postgresql.org/docs/9.6/dynamic-trace.html
标签：tables,stat,heap,数据库,indexes,pg,监控,活动,postgres From： https://www.cnblogs.com/wonchaofan/p/18221340