pg_stat_progress_copy

A progress reporting view providing information on the progress of COPY execution

pg_stat_progress_copy is a progress reporting view providing information on the progress of COPY command execution.

pg_stat_progress_copy was added in PostgreSQL 14.

Definition by PostgreSQL version

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

Documentation: pg_stat_progress_copy

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

Documentation: pg_stat_progress_copy

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

Documentation: pg_stat_progress_copy

Change history

Examples

Sample output during a COPY FROM operation:

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

Note that pg_stat_progress_copy only contains progress statistics for COPY operations which are currently running, and is otherwise empty.

To demonstrate it in operation, create and populate a (large) table as follows:

postgres=# CREATE TABLE foo (id int, val text);
CREATE TABLE

postgres=# INSERT INTO foo VALUES (generate_series(1,100000000), clock_timestamp());
INSERT 0 100000000

In one session, execute e.g.:

postgres=# COPY foo TO '/tmp/foo_copy.dat';

While that command is running, from another session execute e.g.:

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)

Categories

Progress reporting view

See also

COPY