pg12新特性-监控数据库活动

CREATE INDEX

每当创建索引或 REINDEX 运行时，pg_stat_progress_create_index视图将包含当前正在创建索引的每个后端进程的一行。

postgres=# d pg_stat_progress_create_index 
        View "pg_catalog.pg_stat_progress_create_index"
       Column       |  Type   | Collation | Nullable | Default 
--------------------+---------+-----------+----------+---------
 pid                | integer |           |          | 
 datid              | oid     |           |          | 
 datname            | name    |           |          | 
 relid              | oid     |           |          | 
 index_relid        | oid     |           |          | 
 command            | text    |           |          | 
 phase              | text    |           |          | 
 lockers_total      | bigint  |           |          | 
 lockers_done       | bigint  |           |          | 
 current_locker_pid | bigint  |           |          | 
 blocks_total       | bigint  |           |          | 
 blocks_done        | bigint  |           |          | 
 tuples_total       | bigint  |           |          | 
 tuples_done        | bigint  |           |          | 
 partitions_total   | bigint  |           |          | 
 partitions_done    | bigint  |           |          | 

phase: Current processing phase of index creation 关于阶段描述参考官方文档

VACUUM

每当 VACUUM 运行时，pg_stat_progress_vacuum视图将包含当前正在清除的每个后端进程（包括自动vacuum工作进程）的一行。

postgres=# d pg_stat_progress_vacuum
           View "pg_catalog.pg_stat_progress_vacuum"
       Column       |  Type   | Collation | Nullable | Default 
--------------------+---------+-----------+----------+---------
 pid                | integer |           |          | 
 datid              | oid     |           |          | 
 datname            | name    |           |          | 
 relid              | oid     |           |          | 
 phase              | text    |           |          | 
 heap_blks_total    | bigint  |           |          | 
 heap_blks_scanned  | bigint  |           |          | 
 heap_blks_vacuumed | bigint  |           |          | 
 index_vacuum_count | bigint  |           |          | 
 max_dead_tuples    | bigint  |           |          | 
 num_dead_tuples    | bigint  |           |          | 

phase:Current processing phase of vacuum.  关于阶段描述参考官方文档

CLUSTER

CLUSTER的作用是依据索引对列数据排序。CLUSTER和VACUUM FULL都会物理移动数据。 每当"CLUSTER"或"VACUUM FULL"运行时，pg_stat_progress_cluster视图将包含当前运行任一命令的每个后端进程的一行。

postgres=# d pg_stat_progress_cluster
           View "pg_catalog.pg_stat_progress_cluster"
       Column        |  Type   | Collation | Nullable | Default 
---------------------+---------+-----------+----------+---------
 pid                 | integer |           |          | 
 datid               | oid     |           |          | 
 datname             | name    |           |          | 
 relid               | oid     |           |          | 
 command             | text    |           |          | 
 phase               | text    |           |          | 
 cluster_index_relid | oid     |           |          | 
 heap_tuples_scanned | bigint  |           |          | 
 heap_tuples_written | bigint  |           |          | 
 heap_blks_total     | bigint  |           |          | 
 heap_blks_scanned   | bigint  |           |          | 
 index_rebuild_count | bigint  |           |          | 

phase:Current processing phase. 关于阶段描述参考官方文档

测试

以上的视图对于日常运行维护过程有很大的帮助，可以观察操作进度，特别对于长时间运行的操作。pg的易管理性不断增加。

eg1：
create index test_parallel_idx on test_parallel(name);
postgres=# select pid,datname,relid,command,phase,current_locker_pid,tuples_total,tuples_done  from pg_stat_progress_create_index;
  pid  | datname | relid |   command    |             phase              | current_locker_pid | tuples_total | tuples_done 
-------+---------+-------+--------------+--------------------------------+--------------------+--------------+-------------
 12612 | test    | 16387 | CREATE INDEX | building index: scanning table |                  0 |            0 |           0
(1 row)
postgres=# select pid,datname,relid,command,phase,current_locker_pid,tuples_total,tuples_done  from pg_stat_progress_create_index;
  pid  | datname | relid |   command    |                 phase                  | current_locker_pid | tuples_total | tuples_done 
-------+---------+-------+--------------+----------------------------------------+--------------------+--------------+-------------
 12612 | test    | 16387 | CREATE INDEX | building index: loading tuples in tree |                  0 |     11003000 |     1814012
(1 row)

reference

官方文档