PostgreSQL 12新增了pg_stat_progress_cluster,用以统计database的活动进度,加强了数据库的易用性.

PG11

[xdb@localhost testdb]$ psql -p 5433 -d testdbpsql (11.2)Type "help" for help.testdb=# select * FROM pg_stat_progress_cluster;ERROR: relation "pg_stat_progress_cluster" does not existLINE 1: select * FROM pg_stat_progress_cluster; ^testdb=#

PG 11没有该relation.

PG12

[pg12@localhost ~]$ psql -d testdbpsql (12beta1)Type "help" for help.testdb=# vacuum full;VACUUMtestdb=# testdb=# drop table t_status;;vacuum full;DROP TABLEtestdb=# create table t_status(id int,c1 varchar(200),c2 varchar(200),c3 varchar(200));CREATE TABLEtestdb=# testdb=# insert into t_status select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;INSERT 0 1000000testdb=# testdb=# update t_status set c1 = lpad(c1,200,'*'),c2=lpad(c2,200,'*');UPDATE 1000000testdb=# testdb=# vacuum full;

查询pg_stat_progress_cluster监控

testdb=# select pid,datname,relid::regclass,command,phase,heap_blks_scanned,heap_tuples_scanned FROM pg_stat_progress_cluster; pid | datname | relid | command | phase | heap_blks_scanned | heap_tuples_scanned -------+---------+-------+-------------+-------------------+-------------------+--------------------- 10805 | testdb | t1 | VACUUM FULL | seq scanning heap | 2989 | 552780(1 row)

参考资料
Postgres 12 highlight - More progress reporting