標準 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