PostgreSQL DBA(62) - PG 12 More progress reporting

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