Progress Reporting In PostgreSQL

SELECT p.datname                                                 AS database_name,       p.pid,       clock_timestamp() - a.xact_start                          AS duration_so_far,       a.application_name,       a.client_addr,       a.usename,       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting,       p.command,       trim(trailing ';' from a.query)                           AS query,       a.state,       p.index_relid::regclass                                   AS index_name,       p.relid::regclass                                         AS table_name,       pg_size_pretty(pg_relation_size(p.relid))                 AS table_size,       p.phase,       CASE p.phase           WHEN 'initializing' THEN '1 of 12'           WHEN 'waiting for writers before build' THEN '2 of 12'           WHEN 'building index: scanning table' THEN '3 of 12'           WHEN 'building index: sorting live tuples' THEN '4 of 12'           WHEN 'building index: loading tuples in tree' THEN '5 of 12'           WHEN 'waiting for writers before validation' THEN '6 of 12'           WHEN 'index validation: scanning index' THEN '7 of 12'           WHEN 'index validation: sorting tuples' THEN '8 of 12'           WHEN 'index validation: scanning table' THEN '9 of 12'           WHEN 'waiting for old snapshots' THEN '10 of 12'           WHEN 'waiting for readers before marking dead' THEN '11 of 12'           WHEN 'waiting for readers before dropping' THEN '12 of 12'       END AS phase_progress,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 2)::text || '%', 'not applicable'),           p.blocks_done::text,           p.blocks_total::text       ) AS scan_progress,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.tuples_done / nullif(p.tuples_total, 0), 2)::text || '%', 'not applicable'),           p.tuples_done::text,           p.tuples_total::text       ) AS tuples_loading_progress,       format(           '%s (%s of %s)',           coalesce((100 * p.lockers_done / nullif(p.lockers_total, 0))::text || '%', 'not applicable'),           p.lockers_done::text,           p.lockers_total::text       ) AS lockers_progress,       format(           '%s (%s of %s)',           coalesce((100 * p.partitions_done / nullif(p.partitions_total, 0))::text || '%', 'not applicable'),           p.partitions_done::text,           p.partitions_total::text       ) AS partitions_progress,       p.current_locker_pid,       trim(trailing ';' from l.query) AS current_locker_query  FROM pg_stat_progress_create_index   AS p  JOIN pg_stat_activity                AS a ON a.pid = p.pid  LEFT JOIN pg_stat_activity           AS l ON l.pid = p.current_locker_pid ORDER BY clock_timestamp() - a.xact_start DESC;

SELECT p.datname                                                 AS database_name,       p.pid,       clock_timestamp() - a.xact_start                          AS duration_so_far,       a.application_name,       a.client_addr,       a.usename,       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting,       trim(trailing ';' from a.query)                           AS query,       a.state,       p.relid::regclass                                         AS table_name,       pg_size_pretty(pg_relation_size(p.relid))                 AS table_size,       pg_size_pretty(pg_total_relation_size(p.relid))           AS total_table_size,       CASE           WHEN ltrim(a.query) ~* '^autovacuum.*to prevent wraparound' THEN 'wraparound'           WHEN ltrim(a.query) ~* '^vacuum' THEN 'user'           ELSE 'regular'       END AS mode,       p.phase,       CASE p.phase             WHEN 'initializing' THEN '1 of 7'           WHEN 'scanning heap' THEN '2 of 7'           WHEN 'vacuuming indexes' THEN '3 of 7'           WHEN 'vacuuming heap' THEN '4 of 7'           WHEN 'cleaning up indexes' THEN '5 of 7'           WHEN 'truncating heap' THEN '6 of 7'           WHEN 'performing final cleanup' THEN '7 of 7'       END AS vacuum_phase_progress,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 2)::text || '%', 'not applicable'),           p.heap_blks_scanned::text,           p.heap_blks_total::text       ) AS vacuum_scan_progress,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.heap_blks_vacuumed / nullif(p.heap_blks_total, 0), 2)::text || '%', 'not applicable'),           p.heap_blks_vacuumed::text,           p.heap_blks_total::text       ) AS vacuum_progress,       p.index_vacuum_count,       p.max_dead_tuples,       p.num_dead_tuples  FROM pg_stat_progress_vacuum  AS p  JOIN pg_stat_activity         AS a ON a.pid = p.pid ORDER BY clock_timestamp() - a.xact_start DESC;

SELECT p.datname                                                 AS database_name,       p.pid,       clock_timestamp() - a.xact_start                          AS duration_so_far,       a.application_name,       a.client_addr,       a.usename,       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting,       p.command,       trim(trailing ';' from a.query)                           AS query,       a.state,       p.relid::regclass                                         AS table_name,       p.phase,       CASE p.phase             WHEN 'initializing' THEN '1 of 8'           WHEN 'seq scanning heap' THEN '2 of 8'           WHEN 'index scanning heap' THEN '3 of 8'           WHEN 'sorting tuples' THEN '4 of 8'           WHEN 'writing new heap' THEN '5 of 8'           WHEN 'swapping relation files' THEN '6 of 8'           WHEN 'rebuilding index' THEN '7 of 8'           WHEN 'performing final cleanup' THEN '7 of 8'       END AS vacuum_phase_progress,       cluster_index_relid::regclass AS cluster_index,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 2)::text || '%', 'not applicable'),           p.heap_blks_scanned::text,           p.heap_blks_total::text       ) AS heap_scan_progress,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.heap_tuples_written / nullif(p.heap_tuples_scanned, 0), 2)::text || '%', 'not applicable'),           p.heap_tuples_written::text,           p.heap_tuples_scanned::text       ) AS heap_tuples_written_progress,       p.index_rebuild_count  FROM pg_stat_progress_cluster AS p  JOIN pg_stat_activity         AS a ON a.pid = p.pid ORDER BY clock_timestamp() - a.xact_start DESC;

SELECT p.datname                                                 AS database_name,       p.pid,       clock_timestamp() - a.xact_start                          AS duration_so_far,       a.application_name,       a.client_addr,       a.usename,       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false') AS waiting,       trim(trailing ';' from a.query)                           AS query,       a.state,       p.relid::regclass                                         AS table_name,       pg_size_pretty(pg_relation_size(p.relid))                 AS table_size,       pg_size_pretty(pg_total_relation_size(p.relid))             AS total_table_size,       CASE           WHEN ltrim(a.query) ~* '^analyze' THEN 'user'           ELSE 'regular'       END AS mode,       p.phase,       CASE p.phase             WHEN 'initializing' THEN '1 of 6'           WHEN 'acquiring sample rows' THEN '2 of 6'           WHEN 'acquiring inherited sample rows' THEN '3 of 6'           WHEN 'computing statistics' THEN '4 of 6'           WHEN 'computing extended statistics' THEN '5 of 6'           WHEN 'finalizing analyze' THEN '6 of 6'       END AS phase_progress,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.sample_blks_scanned / nullif(p.sample_blks_total, 0), 2)::text || '%', 'not applicable'),           p.sample_blks_scanned::text,           p.sample_blks_total::text       ) AS scan_progress,       format(           '%s (%s of %s)',           coalesce((100 * p.ext_stats_computed / nullif(p.ext_stats_total, 0))::text || '%', 'not applicable'),           p.ext_stats_computed::text,           p.ext_stats_total::text       ) AS extended_statistics_progress,       format(           '%s (%s of %s)',           coalesce((100 * p.ext_stats_computed / nullif(p.child_tables_total, 0))::text || '%', 'not applicable'),           p.child_tables_done::text,           p.child_tables_total::text       ) AS child_tables_progress,       current_child_table_relid::regclass AS current_child_table  FROM pg_stat_progress_analyze AS p  JOIN pg_stat_activity         AS a ON a.pid = p.pid ORDER BY clock_timestamp() - a.xact_start DESC;

SELECT p.datname                                                      AS database_name,       p.pid,       clock_timestamp() - a.xact_start                               AS duration_so_far,       a.application_name,       a.client_addr,       a.usename,       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false')      AS waiting,       p.command,       p.type,       trim(trailing ';' from a.query)                                AS query,       a.state,       p.relid::regclass                                              AS table_name,       coalesce(pg_size_pretty(pg_relation_size(p.relid)), '-')       AS table_size,       coalesce(pg_size_pretty(pg_total_relation_size(p.relid)), '-') AS total_table_size,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.bytes_processed / nullif(p.bytes_total, 0), 2)::text || '%', 'not applicable'),           p.bytes_processed::text,           p.bytes_total::text       ) AS bytes_progress,       p.tuples_processed,       p.tuples_excluded  FROM pg_stat_progress_copy  AS p  JOIN pg_stat_activity       AS a ON a.pid = p.pid ORDER BY clock_timestamp() - a.xact_start DESC;

SELECT a.datname                                                      AS database_name,       p.pid,       clock_timestamp() - a.query_start                              AS duration_so_far,       a.application_name,       a.client_addr,       a.usename,       coalesce(a.wait_event_type ||'.'|| a.wait_event, 'false')      AS waiting,       trim(trailing ';' from a.query)                                AS query,       a.state,       p.phase,       CASE p.phase             WHEN 'initializing' THEN '1 of 6'           WHEN 'waiting for checkpoint to finish' THEN '2 of 6'           WHEN 'estimating backup size' THEN '3 of 6'           WHEN 'streaming database files' THEN '4 of 6'           WHEN 'waiting for wal archiving to finish' THEN '5 of 6'           WHEN 'transferring wal files' THEN '6 of 6'       END AS phase_progress,       format(           '%s (%s of %s)',           coalesce(round(100.0 * p.backup_streamed / nullif(p.backup_total, 0), 2)::text || '%', 'not applicable'),           p.backup_streamed::text,           coalesce(p.backup_total::text, '0')       ) AS backup_progress,       format(           '%s (%s of %s)',           coalesce((100 * p.tablespaces_streamed / nullif(p.tablespaces_total, 0))::text || '%', 'not applicable'),           p.tablespaces_streamed::text,           p.tablespaces_total::text       ) AS tablespace_progress  FROM pg_stat_progress_basebackup  AS p  JOIN pg_stat_activity             AS a ON a.pid = p.pid ORDER BY clock_timestamp() - a.query_start DESC;