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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了PostgreSQL 执行进度监控：VACUUM 、ANALYZE、CREATE INDEX 、CLUSTER、Base Backup，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4737字，纯文字阅读大概需要7分钟。

内容图文

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

内容总结

以上是互联网集市为您收集整理的PostgreSQL 执行进度监控：VACUUM 、ANALYZE、CREATE INDEX 、CLUSTER、Base Backup全部内容，希望文章能够帮你解决PostgreSQL 执行进度监控：VACUUM 、ANALYZE、CREATE INDEX 、CLUSTER、Base Backup所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端