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
官方文档
以上是 pg12新特性监控数据库活动 的全部内容， 来源链接： utcz.com/z/533360.html
