PostgreSQL 执行进度监控：VACUUM 、ANALYZE、CREATE INDEX 、CLUSTER、Base Backup

 

1、背景

在PG中，我们经常因为执行不知道多久结束而苦恼，主要集中在：

1）vacuum、vacuum full（阻塞读写）、analyze

2）pg_dump、pg_restore，缺少进度条，尤其对于大表的导出，只能通过数据量大致估算，但数据在DB中和磁盘上的量是有偏差的。

3）create index（不会阻塞读取，会阻塞写入，在创建时，你不知道花多少时间，无法评估业务的阻塞）、reindex（会阻塞读写）

 

pg_basebackup 这个可以通过查看复制了多少数据量，来大概估算。

 

2、现状

PG9.6支持了pg_stat_progress_vacuum，PG13目前有如下5个进度视图：

?

1 2 3 4 5

27.4.1. ANALYZE Progress Reporting 27.4.2. CREATE INDEX Progress Reporting 27.4.3. VACUUM Progress Reporting 27.4.4. CLUSTER Progress Reporting 27.4.5. Base Backup Progress Reporting

 

官方文档有详细说明： 

https://www.postgresql.org/docs/13/progress-reporting.html

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

pithe=# \d pg_stat_progress*               View "pg_catalog.pg_stat_progress_analyze"           Column           |  Type   | Collation | Nullable | Default ---------------------------+---------+-----------+----------+---------  pid                       | integer |           |          |  datid                     | oid     |           |          |  datname                   | name    |           |          |  relid                     | oid     |           |          |  phase                     | text    |           |          |  sample_blks_total         | bigint  |           |          |  sample_blks_scanned       | bigint  |           |          |  ext_stats_total           | bigint  |           |          |  ext_stats_computed        | bigint  |           |          |  child_tables_total        | bigint  |           |          |  child_tables_done         | bigint  |           |          |  current_child_table_relid | oid     |           |          |             View "pg_catalog.pg_stat_progress_basebackup"         Column        |  Type   | Collation | Nullable | Default ----------------------+---------+-----------+----------+---------  pid                  | integer |           |          |  phase                | text    |           |          |  backup_total         | bigint  |           |          |  backup_streamed      | bigint  |           |          |  tablespaces_total    | bigint  |           |          |  tablespaces_streamed | bigint  |           |          |              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  |           |          |           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  |           |          |              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  |           |          |