pg_stat_progress_copy 是一个 进度报告视图，提供有关 COPY 命令执行进度的信息。
pg_stat_progress_copy 在 PostgreSQL 14 中添加。
按 PostgreSQL 版本定义
pg_stat_progress_copy (PostgreSQL 19)
View "pg_catalog.pg_stat_progress_copy"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
type | text | | |
bytes_processed | bigint | | |
bytes_total | bigint | | |
tuples_processed | bigint | | |
tuples_excluded | bigint | | |
tuples_skipped | bigint | | |
pg_stat_progress_copy (PostgreSQL 18)
View "pg_catalog.pg_stat_progress_copy"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
type | text | | |
bytes_processed | bigint | | |
bytes_total | bigint | | |
tuples_processed | bigint | | |
tuples_excluded | bigint | | |
tuples_skipped | bigint | | |
pg_stat_progress_copy (PostgreSQL 17)
View "pg_catalog.pg_stat_progress_copy"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
type | text | | |
bytes_processed | bigint | | |
bytes_total | bigint | | |
tuples_processed | bigint | | |
tuples_excluded | bigint | | |
tuples_skipped | bigint | | |
pg_stat_progress_copy (PostgreSQL 16)
View "pg_catalog.pg_stat_progress_copy"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
type | text | | |
bytes_processed | bigint | | |
bytes_total | bigint | | |
tuples_processed | bigint | | |
tuples_excluded | bigint | | |
pg_stat_progress_copy (PostgreSQL 15)
View "pg_catalog.pg_stat_progress_copy"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
type | text | | |
bytes_processed | bigint | | |
bytes_total | bigint | | |
tuples_processed | bigint | | |
tuples_excluded | bigint | | |
pg_stat_progress_copy (PostgreSQL 14)
View "pg_catalog.pg_stat_progress_copy"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
type | text | | |
bytes_processed | bigint | | |
bytes_total | bigint | | |
tuples_processed | bigint | | |
tuples_excluded | bigint | | |
变更历史
- PostgreSQL 17
- 添加了列
tuples_skipped(commit 72943960)
- 添加了列
- PostgreSQL 14
- 添加 (initial commit 8a4f618e)
示例
在 COPY FROM 操作期间的示例输出
postgres=# SELECT * FROM pg_stat_progress_copy\gx -[ RECORD 1 ]----+---------- pid | 1708412 datid | 13968 datname | postgres relid | 16473 command | COPY FROM type | PIPE bytes_processed | 496156582 bytes_total | 0 tuples_processed | 522864 tuples_excluded | 0
请注意，pg_stat_progress_copy 只包含当前正在运行的 COPY 操作的进度统计信息，否则它是空的。
为了演示其运行情况，请按以下方式创建并填充一个（大型）表：
postgres=# CREATE TABLE foo (id int, val text); CREATE TABLE postgres=# INSERT INTO foo VALUES (generate_series(1,100000000), clock_timestamp()); INSERT 0 100000000
在一个会话中，执行例如：
postgres=# COPY foo TO '/tmp/foo_copy.dat';
当该命令正在运行时，从另一个会话执行例如：
postgres=# SELECT relid::regclass AS table,
command,type,
bytes_processed, bytes_total,
tuples_processed, tuples_excluded
FROM pg_stat_progress_copy;
table | command | type | bytes_processed | bytes_total | tuples_processed | tuples_excluded
-------+---------+------+-----------------+-------------+------------------+-----------------
foo | COPY TO | FILE | 149623523 | 0 | 3978098 | 0
(1 row)
参考资料
- PostgreSQL 文档: pg_stat_progress_copy