PostgreSQL DBA(62) - PG 12 More progress reporting

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

PG11

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

PG 11没有该relation.

PG12

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