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个进度视图：

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

 

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  |           |          |