I need to migrate a table from one PostgreSQL database to another. There was a chance I would need to fix some data, so I exported to a CSV. Then I imported the CSV in the second database with a COPY statement.

This process has been running for 5 days now. The only way I found to inspect its progress was to compare the sizes on disk. The original table was 95 GB (from psql's \dt+), and the CSV was 40 GB. So I thought I could compare the new table size with those numbers. I thought that the new table would stop at 95 GB, or even before. Instead, it's now at 103 GB and who knows when it will stop.

Of course, select count(*) does not work because the copy happens in its own transaction so the rows are shielded until it's done. But I know that the the table has about 1500 million rows. So if somehow I could get an estimate of the number of rows currently in the new table I could compare.

  • Is the table indexed? Are the same between the two versions? – jjanes Jul 8 '19 at 17:29
  • @jjanes The original table is indexed. The new ones has no indices yet. – rubik Jul 8 '19 at 17:49

There is not yet a formal facility for monitoring the progress of a COPY operation. You can use the pageinspect extension to get an estimate of the number of rows, even uncommitted ones. Assuming the existence of the table has been committed, and the table was empty other than the in-progress COPY, then you could use:

select sum(
        (select count(*) from
          heap_page_items(get_raw_page('pgbench_accounts', x))
from generate_series(0, (pg_relation_size('pgbench_accounts')/8192)::int-1) as f(x);

(This still assumes you are using the default blocksize of 8192.)

  • Thanks! Yes, the table was created before initiating the COPY operation. Inside pg_class my table has 0 under relpages. I think that messes up the whole calculation. – rubik Jul 8 '19 at 17:56
  • Yes, I didn't think about relpages being out of date. You can look at the size on disk to figure out the number of pages to plug in, I will update – jjanes Jul 8 '19 at 18:10
  • Perfect! Now I get 14 million relpages, it should work. But it will take forever I assume. – rubik Jul 8 '19 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.