Estimate/Print csv COPY status to postgresql table

Question

I want to get an idea of how long it will take to copy a csv to a postgresql table. Is there a way to print the rows copied in a reasonable fashion or is there another way to somehow display the progress of the copy?

Perhaps there is a verbose setting or I should use --echo or -qecho

I am using:

psql -U postgres -d nyc_data -h localhost -c "\COPY rides FROM nyc_data_rides.csv CSV"

Answer 1

In Postgres 14, it's now possible to query the status of an active COPY via the internal pg_stat_progress_copy view.

e.g. to watch progress in terms of both bytes and lines processed:

select * from pg_stat_progress_copy \watch 1

Refs:

• https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING
• https://www.depesz.com/2021/01/12/waiting-for-postgresql-14-report-progress-of-copy-commands/

Answer 2

use pv tool

pv /tmp/some_table.csv | sudo -u postgres psql -d some_db -c "copy some_table from stdin delimiter ',' null '';"

and as a result, it will show

1.42GiB 0:11:42 [2.06MiB/s] [===================================================================================================================================================================>] 100%