9 操作进度

本章将介绍一组专用的系统视图，它们用于指示各种命令的执行进度，而这些命令的运行时间可能较长。执行此类命令可能需要大量资源、造成负载（例如输入/输出子系统的负载）、与相邻进程竞争，甚至阻塞相邻进程。考虑到这些风险，管理员应提前规划此类操作，并准备能够跟踪其进度、估计何时完成的工具。本章讨论的所有工具都是视图，与 pg_stat_activity 和 pg_locks 类似，它们显示访问时刻的状态。这些统计信息主要用于运行过程中出现的特殊场景，也可以显示在 PostgreSQL 管理工具的用户界面中。它们不太适合在监控系统中积累和显示。

尽管可能运行很长时间的命令很多，但数据库管理系统只为其中一小部分提供了跟踪工具：

• pg_stat_progress_vacuum —— 清理和自动清理操作的执行；

• pg_stat_progress_analyze —— 收集规划器统计信息的执行；

• pg_stat_progress_basebackup —— 备份操作的执行；

• pg_stat_progress_cluster —— 执行 CLUSTER 和 VACUUM FULL 命令；

• pg_stat_progress_create_index —— 执行 CREATE INDEX 和 REINDEX 命令；

• pg_stat_progress_copy —— 执行 COPY 命令。

pg_stat_progress_vacuum 视图已在专门介绍清理的 第 8 章中详细讨论，因此这里不再重复介绍。

所有这些视图都显示正在执行的命令；这些命令通常是在需要时手动启动的（自动清理除外）。数据库管理系统在后续版本中很可能会扩大对命令的支持，并增加新的类似视图。

9.1 pg_stat_progress_analyze 视图

执行查询时，数据库管理系统会采用规划器从众多可能的计划中选出的、从资源使用角度来看最优的执行计划。评估计划需要使用描述表中数据的统计信息，而所选计划的质量取决于这些统计信息的准确性。收集到的统计信息会反映在系统视图 pg_stats¹ 中，该视图基于表 pg_statistic²。在数据库管理系统运行过程中，数据的数量和质量特征可能会发生变化，而且规划器统计信息在 pg_statistics 中也不可避免地会过时。在数据不断变化的情况下，定期更新统计信息非常重要：否则，基于过时信息选择的计划会效率低下，导致资源使用过多并延长执行时间。

统计信息通常由自动清理工作进程收集，但管理员也可以强制启动收集过程（使用 ANALYZE 命令³ 或 VACUUM），然后等待它结束，或者估计它需要多长时间。

在以下各种情况下可能需要这样做：

• 从逻辑备份（使用 pg_dump 制作）恢复后，或者使用 COPY 命令加载数据后，需要收集初始统计信息，因为此前根本不存在统计信息；

• 修改了 default_statistics_target 参数⁴ 后，该参数限制要分析和收集的数据量；

• 在使用 EXPLAIN⁵ 开始深入研究某个查询的性能问题之前，可以先确认统计信息仍然是最新的；

• 使用 pg_upgrade 工具⁶ 升级数据库管理系统版本后，因为这种操作不会迁移规划器统计信息。

还可能有其他场景，但无论如何，统计信息收集过程可能持续很长时间，管理员应了解它还会运行多久。可以使用 pg_stat_progress_analyze 视图跟踪统计信息收集操作。视图中的每一行对应一个正在收集统计信息的进程，并包含以下字段：

• pid —— 操作系统中的进程标识符；

• datid、datname —— 已建立连接的数据库的标识符和名称；

• relid —— 正在其中收集统计信息的表的标识符；

• phase —— 统计信息收集阶段：

— initializing —— 准备扫描表；

— acquiring sample rows —— 获取用于分析的随机行样本；

— acquiring inherited sample rows —— 从子表获取行样本；

— computing statistics —— 根据选出的行集计算统计信息；

— computing extended statistics —— 计算扩展统计信息；

— finalizing analyze —— 完成工作，更新 pg_class；

• sample_blks_total —— 计算统计信息所需的随机样本中的块总数；

• sample_blks_scanned —— 已经扫描的块总数；

• ext_stats_total —— 扩展统计信息对象的数量；

• ext_stats_computed —— 已计算的扩展统计信息对象数量，仅在 computing extended statistics 阶段递增；

• child_tables_total —— 子表总数，用于分区表收集统计信息的情况；

• child_tables_done —— 已扫描的子表数量，仅在 acquiring inherited sample rows 阶段递增；

• current_child_table_relid —— 当前正在扫描的子表标识符；仅在 acquiring inherited sample rows 阶段包含当前值。

视图中的原始数据可能不够直观，因此为了提高信息量，可以做一些处理：将标识符转换成名称，将块转换成字节，并从 pg_stat_activity 获取额外信息，通过 pid 字段连接两个视图。

在测试环境中很难正好捕捉到自动收集统计信息的过程，因此可以在一个会话中使用 \watch 1 执行查询，再在另一个会话中启动 ANALYZE 命令。第一个会话中会出现类似下面的输出：

# SELECT
    a.pid, now() - a.xact_start AS xact_age,
    p.datname, p.relid::regclass AS relation,
    a.state, a.wait_event_type ||'.'|| a.wait_event AS wait_event,
    p.phase,
    pg_size_pretty(p.sample_blks_total * (
        SELECT current_setting('block_size')::int )) AS sample_size,
    round(100 * p.sample_blks_scanned /
        greatest(p.sample_blks_total,1), 2) AS "scanned,%",
    p.ext_stats_total ||'/'|| p.ext_stats_computed AS "ext_total/done",
    p.child_tables_total ||'/'|| p.child_tables_done AS "child_total/done",
    current_child_table_relid::regclass AS child_in_progress,
    a.query
  FROM pg_stat_progress_analyze p
  INNER JOIN pg_stat_activity a ON p.pid = a.pid
  ORDER BY now() - a.xact_start DESC;
-[ RECORD 1 ]-----+----------------------
pid               | 536666
xact_age          | 00:00:07.660563
datname           | pgbench
relation          | pgbench_accounts
state             | active
wait_event        | IO.DataFileRead
phase             | acquiring sample rows
sample_size       | 522 MB
scanned,%         | 85.00
ext_total/done    | 0/0
child_total/done  | 0/0
child_in_progress | -
query             | ANALYZE;

ANALYZE 命令已经执行了七秒；为了计算统计信息，需要获取 522 MB 的样本，目前已经扫描了 85%。在这个例子中，再过几秒扫描就会结束，然后进入统计信息计算阶段。对于包含分区表或扩展统计信息的复杂场景，还有其他字段提供更详细的信息。

9.2 pg_stat_progress_basebackup 视图

备份是生产数据库管理系统运行中的常规任务之一，根据数据库大小和硬件特征的不同，备份可能需要很长时间。使用 pg_basebackup 工具⁷ 时，会完整复制数据文件和所需的 WAL 日志（传输它们时可能会启动额外的 walsender 进程）。这会给磁盘子系统造成很大负载，并可能减慢并发查询。如果资源不足并且性能受到负面影响，管理员需要了解备份已经运行了多久、还要多久才能结束。pg_basebackup 工具本身提供了跟踪功能（参数 -P、--progress），但也可以从数据库管理系统一侧使用 pg_stat_progress_basebackup 视图跟踪进度。

视图中的每一行对应一个备份进程，并包含以下信息：

• pid —— 操作系统中的进程标识符；

• phase —— 备份阶段：

— initializing —— 准备备份；

— waiting for checkpoint to finish —— walsender 等待检查点完成；

— estimating backup size —— walsender 计算要传输的数据库文件大小；

— streaming database files —— walsender 传输数据；

— waiting for wal archiving to finish —— walsender 等待 WAL 日志段完成归档；

— transferring wal files —— walsender 传输备份过程中直接创建的 WAL 日志段；

• backup_total —— 将要传输的数据总量。该值在备份前计算，是一个近似估计，因为复制过程中数据目录的内容可能发生变化。如果将要传输的数据开始超过计算出的值，该字段会设置为 backup_streamed。如果 pg_basebackup 禁用了估算（参数 --no-estimate-size），该值将不存在（NULL）；

• backup_streamed —— 已传输的数据量，仅在 streaming database files 和 transferring wal files 阶段递增；

• tablespaces_total —— 将要传输的表空间总数；

• tablespaces_streamed —— 已传输的表空间数量，仅在 streaming database files 阶段递增。

要看到任何结果，可以在相邻终端启动备份，并使用 \watch 1 执行下面的查询。由于得到的备份不会被使用，可以将其输出重定向到 /dev/null：

# pg_basebackup -P -R -X none -c fast -Ft -h 127.0.0.1 -U postgres -D - > /dev/null

备份执行期间，查询 pg_stat_progress_basebackup 的会话中会显示其执行进度：

# SELECT
    a.pid,
    host(a.client_addr) AS started_from,
    to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS') AS started_at,
    now() - backend_start AS duration,
    a.state, a.wait_event_type ||'.'|| a.wait_event AS waiting,
    p.phase,
    pg_size_pretty(p.backup_total) AS size_total,
    pg_size_pretty(p.backup_streamed) AS sent,
    round(100 * p.backup_streamed / greatest(p.backup_total,1), 2) AS "sent,%",
    p.tablespaces_total ||'/'|| p.tablespaces_streamed AS "ts_total/streamed"
  FROM pg_stat_progress_basebackup p
  INNER JOIN pg_stat_activity a ON p.pid = a.pid
  ORDER BY now() - backend_start DESC;
-[ RECORD 1 ]--------------+-------------------------
pid                        | 545355
started_from               | 127.0.0.1
started_at                 | 2023-03-06 04:43:41
duration                   | 00:00:36.700719
state                      | active
waiting                    | IO.BaseBackupRead
phase                      | streaming database files
size_total                 | 713 MB
sent                       | 623 MB
sent,%                     | 87.00
ts_total/streamed          | 1/0

从这段输出可以看出，备份已经运行了三十多秒，数据库内容的大部分已经发送给客户端，备份已经完成 87%，很快就会结束。从 pg_stat_activity 视图中取出了 state 字段和等待标记；由于进程正在复制数据文件且不使用锁，这个标记大多数情况下会显示输入/输出等待。

9.3 pg_stat_progress_cluster 视图

CLUSTER⁸ 和 VACUUM FULL⁹ 命令用于重建表。它们会消耗大量资源，并设置排他锁，禁止访问正在处理的表。如果使用不当，这些命令很容易在自身完成前持续阻塞应用程序，因此管理员需要能够跟踪其执行进度的工具。数据库管理系统提供了 pg_stat_progress_cluster 视图，其中每一行包含一个操作的信息，字段如下：

• pid —— 操作系统中的进程标识符；

• datid、datname —— 执行操作的数据库的标识符和名称；

• relid —— 正在重建的表的标识符；

• command —— 正在执行的命令，可以是 CLUSTER 或 VACUUM FULL；

• phase —— 操作阶段：

— initializing —— 准备扫描表；

— seq scanning heap —— 顺序扫描表；

— index scanning heap —— CLUSTER 命令对表执行索引扫描；

— sorting tuples —— CLUSTER 命令对行进行排序；

— writing new heap —— 将数据写入新的表文件；

— swapping relation files —— 用新文件替换旧文件；

— rebuilding index —— 重建索引；

— performing final cleanup —— 清理并完成工作；

• cluster_index_relid —— CLUSTER 命令使用的索引标识符；

• heap_tuples_scanned —— 表中已扫描行的总数，仅在顺序扫描、索引扫描和写入新表阶段变化；

• heap_tuples_written —— 表中已写入行的总数，仅在顺序扫描、索引扫描和写入新表阶段变化；

• heap_blks_total —— 开始顺序扫描表时表中的块总数；

• heap_blks_scanned —— 已扫描块的总数，仅在顺序扫描表阶段变化；

• index_rebuild_count —— 已重建索引的总数，仅在重建索引阶段变化。

根据这些字段，可以很容易地了解命令执行所处的阶段，并估计执行进度。为了获得更多信息，也可以将该视图与 pg_stat_activity 连接起来并做一些处理。

为了让查询得到有意义的结果，需要在相邻终端启动 CLUSTER 或 VACUUM FULL 命令：

# VACUUM FULL pgbench_accounts;

此时，在查询 pg_stat_progress_cluster 的会话中可以看到下面的输出：

# SELECT
    a.pid,
    now() - a.xact_start AS xact_age,
    p.datname,
    p.relid::regclass AS relation,
    p.cluster_index_relid::regclass AS index,
    a.state,
    (SELECT count(distinct l.pid) FROM pg_locks l
      WHERE l.relation = p.relid AND NOT l.granted) AS blocked_total,
    a.wait_event_type ||'.'|| a.wait_event AS wait_event,
    p.phase,
    pg_size_pretty(p.heap_blks_total * (
        SELECT current_setting('block_size')::int)) AS size_total,
    round(100 * p.heap_blks_scanned /
        greatest(p.heap_blks_total,1), 2) AS "scanned,%",
    coalesce(p.heap_tuples_scanned, 0) AS tuples_scanned,
    coalesce(p.heap_tuples_written, 0) AS tuples_written,
    a.query
  FROM pg_stat_progress_cluster p
  INNER JOIN pg_stat_activity a ON p.pid = a.pid
  ORDER BY now() - a.xact_start DESC;
-[ RECORD 1 ]--+-------------------------------
pid            | 550142
xact_age       | 00:00:06.867676
datname        | pgbench
relation       | pgbench_accounts
index          | -
state          | active
blocked_total  | 33
wait_event     | IO.DataFileExtend
phase          | seq scanning heap
size_total     | 257 MB
scanned,%      | 70.00
tuples_scanned | 1404122
tuples_written | 1404122
query          | VACUUM FULL pgbench_accounts;

在这个例子中，VACUUM FULL 命令已经运行了六秒，完成了 70%，但还有 33 个会话在等待它结束；这些会话正在查询被重建的表。因此，执行这些查询的应用程序也被阻塞，被迫等待该命令完成。

9.4 pg_stat_progress_create_index 视图

在应用程序积极开发、应用中出现新类型查询时，创建索引可能是很常见的操作。创建索引，尤其是为大表创建索引，可能需要很长时间、消耗大量资源，并影响并发查询的性能。

可以使用 pg_stat_progress_create_index 视图跟踪这一过程，视图中的每一行描述一个正在构建索引的进程：

• pid —— 操作系统中的进程标识符；

• datid、datname —— 执行操作的数据库的标识符和名称；

• relid —— 索引所属表的标识符；

• index_relid —— 正在对其执行操作的索引标识符；

• command —— 正在执行的命令：CREATE INDEX、CREATE INDEX CONCURRENTLY、REINDEX 或 REINDEX CONCURRENTLY；

• phase —— 操作阶段：

— initializing —— 准备工作；

— waiting for writers before build —— CREATE INDEX CONCURRENTLY 或 REINDEX CONCURRENTLY 命令等待持有写锁且可能正在读取该表的事务结束。在阻塞模式下执行操作时会跳过此阶段。可以通过 lockers_total、lockers_done 和 current_locker_pid 跟踪此阶段的详细执行情况；

— building index —— 支持跟踪的访问方法会报告自身的状态。可以通过 blocks_total 和 blocks_done 字段跟踪索引构建的详细情况，但 tuples_total 和 tuples_done 的值也可能发生变化；

— waiting for writers before validation —— CREATE INDEX CONCURRENTLY 或 REINDEX CONCURRENTLY 命令等待持有写锁、并且可能正在向表写入的事务结束。在阻塞模式下执行操作时会跳过此阶段。可以通过 lockers_total、lockers_done 和 current_locker_pid 跟踪此阶段的详细执行情况；

— index validation: scanning index —— CREATE INDEX CONCURRENTLY 命令扫描索引，查找需要检查的行。在阻塞模式下执行操作时会跳过此阶段。阶段的详细进度反映在 blocks_total 和 blocks_done 字段中；

— index validation: sorting tuples —— CREATE INDEX CONCURRENTLY 命令对扫描阶段找到的行进行排序；

— index validation: scanning table —— CREATE INDEX CONCURRENTLY 命令扫描表，以检查前两个阶段收集的索引行。在阻塞模式下执行操作时会跳过此阶段。阶段的详细进度反映在 blocks_total 和 blocks_done 中； — waiting for old snapshots —— CREATE INDEX CONCURRENTLY 或 REINDEX CONCURRENTLY 命令等待可能看到表内容的事务释放快照。在阻塞模式下执行操作时会跳过此阶段。详细进度反映在 lockers_total、lockers_done 和 current_locker_pid 中；

— waiting for readers before marking dead —— 在将旧索引标记为不可用之前，REINDEX CONCURRENTLY 命令等待持有读锁的事务结束。在阻塞模式下执行操作时会跳过此阶段。详细进度反映在 lockers_total、lockers_done 和 current_locker_pid 中；

— waiting for readers before dropping —— 在删除旧索引之前，REINDEX CONCURRENTLY 命令等待持有读锁的事务结束。在阻塞模式下执行操作时会跳过此阶段。详细进度反映在 lockers_total、lockers_done 和 current_locker_pid 中；

• lockers_total —— 必须等待的进程数量；

• lockers_done —— 已经等待结束的进程数量；

• current_locker_pid —— 当前持有锁的进程标识符；

• blocks_total (tuples_total) —— 此阶段需要处理的块（行）总数；

• blocks_done (tuples_done) —— 此阶段已经处理的块（行）总数；

• partitions_total —— 必须创建索引的分区总数（处理分区表时）；

• partitions_done —— 已经完成索引创建的分区总数（处理分区表时）。

该视图非常详细地显示了索引构建进度。特别要注意，表示块和行的值对应于单独的阶段，而不是整个过程。

为了得到查询结果，需要在相邻会话中启动索引重建：

# REINDEX CONCURRENTLY pgbench_accounts_pkey;

在查询 pg_stat_progress_create_index 的会话中可以看到类似下面的输出。由此可知，索引重建正处于扫描表阶段，该阶段已经完成 75%。随后将开始加载行的阶段，其进度可以通过 tuples_done,% 字段跟踪。

# SELECT
    a.pid,
    now() - a.xact_start AS xact_age,
    p.datname,
    p.relid::regclass AS relation,
    p.index_relid::regclass AS index,
    a.state, a.wait_event_type ||'.'|| a.wait_event AS wait_event,
    p.phase,
    p.current_locker_pid AS locker_pid,
    p.lockers_total ||'/'|| p.lockers_done AS lockers,
    pg_size_pretty(p.blocks_total * (
        SELECT current_setting('block_size')::int)) AS size_total,
    round(100 * p.blocks_done /
        greatest(p.blocks_total, 1), 2) AS "size_done,%",
    p.tuples_total,
    round(100 * p.tuples_done /
        greatest(p.tuples_total, 1), 2) AS "tuples_done,%",
    p.partitions_total ||'/'|| round(100 * p.partitions_done /
        greatest(p.partitions_total, 1), 2) AS "parts_total/done,%",
    a.query
  FROM pg_stat_progress_create_index p
  INNER JOIN pg_stat_activity a ON p.pid = a.pid
  ORDER BY now() - a.xact_start DESC;
-[ RECORD 1 ]------+--------------------------------------------------
pid                | 550142
xact_age           | 00:00:02.523236
datname            | pgbench
relation           | pgbench_accounts
index              | pgbench_accounts_pkey
state              | active
wait_event         | LWLock.WALWrite
phase              | building index: scanning table
locker_pid         | 0
lockers            | 0/0
size_total         | 261 MB
size_done,%        | 75.00
tuples_total       | 0
tuples_done,%      | 0.00
parts_total/done,% | 0/0.00
query              | REINDEX INDEX CONCURRENTLY pgbench_accounts_pkey;

9.5 pg_stat_progress_copy 视图

pg_stat_progress_copy 视图用于跟踪 COPY 命令¹⁰，这些命令用于加载和导出数据，也包括使用 pg_dump 和 pg_restore 工具的情况。

在数据量较大而资源不足时，该命令可能持续很长时间，并影响并发查询的性能。

视图中的每一行描述一个命令的执行情况，并包含以下信息：

• pid —— 操作系统中的进程标识符；

• datid、datname —— 已建立连接的数据库的标识符和名称；

• relid —— 正在操作的表的标识符；

• command —— 正在执行的命令，可以是 COPY TO 或 COPY FROM；

• type —— 处理数据时使用的输入/输出方法：FILE、PROGRAM、PIPE（用于 COPY FROM STDIN 和 COPY TO STDOUT），或者逻辑复制初始同步表时的 CALLBACK；

• bytes_processed —— 命令处理的字节总数；

• bytes_total —— 执行 COPY FROM 时源文件的字节大小。如果无法确定大小，该值可能为零；

• tuples_processed —— COPY 命令处理的行总数；

• tuples_excluded —— 被指定的 WHERE 条件丢弃的行总数。

为了获得更多信息，应将该视图与 pg_stat_activity 连接起来，通过调用 pg_relation_size 添加表大小，并采用只适用于 COPY 命令的进度估算方法。

# SELECT
    a.pid,
    a.state, a.wait_event_type ||'.'|| a.wait_event AS wait_event,
    now() - a.xact_start AS xact_age,
    p.datname, p.relid::regclass AS relation,
    pg_size_pretty(pg_relation_size(p.relid)) AS table_size_total,
    c.reltuples::bigint AS table_tuples_total,
    p.tuples_processed,
    p.tuples_excluded,
    pg_size_pretty(p.bytes_total) AS source_file_total,
    pg_size_pretty(p.bytes_processed) AS processed,
    CASE WHEN p.command = 'COPY FROM'
      THEN round(100 * p.bytes_processed / greatest(p.bytes_total, 1), 2)
      ELSE round(100 * p.tuples_processed / greatest(c.reltuples::bigint, p.tuples_processed), 2)
    END AS "done,%",
    p.command || ' ' || p.type AS command,
    a.query
  FROM pg_stat_progress_copy p
  LEFT JOIN pg_class c ON p.relid = c.oid
  INNER JOIN pg_stat_activity a ON p.pid = a.pid
  ORDER BY now() - a.xact_start DESC;

COPY 命令有两种工作模式：加载（COPY FROM）和导出（COPY TO）；在输出中，为了便于理解，工作模式与数据源类型合并显示。已完成工作的估算（done,% 字段）也会同时考虑这两种模式。

对于加载，可以只使用视图自身的字段进行估算：bytes_total 表示要加载的源文件大小，bytes_processed 表示 COPY 命令已经读取的字节数。因此，完成百分比就是已读取数据量与总数据量的比值。

导出的估算稍微复杂一些：数据库管理系统中的数据表示方式不同于导出格式（更不用说数据库中的数据也可能以不同方式压缩，而且导出格式不止一种），因此最终文件的大小会与原表完全不同。在示例查询中，估算值是已处理行数 tuples_processed 与从 pg_class.reltuples 字段取得的表总行数之比。不过，这种方法也有一个缺点：pg_class.reltuples 会在清理后更新，如果清理已经很久没有执行，该值就会与实际情况不符，估算也会不准确。但经验表明，这种估算仍然比根据 bytes_processed 和表大小进行的估算更准确。

应当在一个会话中使用元命令 \watch 1 查询 pg_stat_progress_copy，再在另一个会话中启动 COPY 或 pg_dump：

# pg_dump -U postgres -t pgbench_accounts pgbench >/dev/null

在查询 pg_stat_progress_copy 的会话中可以看到大致如下的输出：

-[ RECORD 1 ]------+---------------------------------------------------------------------
pid                | 223457
state              | active
wait_event         | IO.DataFileWrite
xact_age           | 00:00:18.393571
datname            | pgbench
relation           | pgbench_accounts
table_size_total   | 277 MB
table_tuples_total | 1999223
tuples_processed   | 1755368
tuples_excluded    | 0
source_file_total  | 0 bytes
processed          | 169 MB
done,%             | 87.00
command            | COPY TO PIPE
query              | COPY public.pgbench_accounts (aid, bid, abalance, filler) TO stdout;

在这个例子中，表 pgbench_accounts 使用 COPY TO 通过管道导出到标准输出（stdout）。导出已经运行了 18 秒，完成了 87%。此外，输出中还给出了进程状态的附加信息。

总结

• 数据库管理系统只提供了有限的一组视图，用于跟踪长时间运行的命令。

• 进度跟踪视图不积累统计信息，而是显示进程的当前状态。

• pg_stat_progress_vacuum 用于跟踪清理操作。

• pg_stat_progress_analyze 用于跟踪规划器所需统计信息的收集。

• pg_stat_progress_basebackup 用于跟踪备份。

• pg_stat_progress_cluster 用于跟踪 CLUSTER 和 VACUUM FULL 命令。

• pg_stat_progress_create_index 用于跟踪 CREATE INDEX 和 REINDEX 命令。

• pg_stat_progress_copy 用于跟踪 COPY 命令。

• 将这些视图与 pg_stat_activity 和 pg_locks 连接起来，可以提高信息量。