how to track progress of a large postgres dump

Question

Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.

Answer 1

You can see a rough progress using the TOC list.

First, get the TOC list of objects to be restored:

pg_restore -l -f list.toc db.dump

Then, you can see the TOC list line by line and compare the output of verbose or query pg_stat_activity to see where in the TOC list is pg_restore in.

It is just a rough estimate though. First because each item from the TOC list may take really different time to load (for instance, schemas are fast, but loading data of big tables and building indexes are not), and if you use -j you'll have an item being restored before a previous one has finished. Also, I'm not 100% sure if pg_restore follows TOC list precisely if you don't use -L, but I think it does.

Answer 2

Valid for Unix/Linux environments:

The Pipe Viewer (pv) utility can be used trace the backup progress. The pv animates your shell with details about the elapsed time and transferred bytes.

Below is the example of dumping using the pv and split utilities to keep the big dump files in small chunks. It might be handy to transfer it later to another location.

# dump the PREDATA in clear text into a .PREDATA.SQL text file
pg_dump -s -o --section=pre-data  -n $schemaname $DatabaseConnString | pv | split -d -b $chunksize - $backuppath/$backupfilename".PREDATA.sql"

# dump the POSTDATA in clear text into a .POSTDATA.SQL text file
pg_dump -s -o --section=post-data -n $schemaname $DatabaseConnString | pv | split -d -b $chunksize - $backuppath/$backupfilename".POSTDATA.sql"

# dump the DATA into the .DATA.dump compressed (binary) file
pg_dump -Fc   --section=data      -n $schemaname $DatabaseConnString | pv | split -d -b $chunksize - $backuppath/$backupfilename".DATA.dump"

The drawback - this approach doesn't work, if the pg_dump -Fd option (dump to folder) is used.

Answer 3

I didn't find a accurate and efficient way, yet.

A compromise way

I have to took a compromise way:

• ① query row count
• ② check dump.sql row line count
  • Statistics row only //exclude other line in .sql
  • Avoid frequently counting large files //streamly count

a crude implementation in bash:

tail -f -n+1 dump.sql | stdbuf -o0 grep '^COPY' -A999999999 | cat -n | cut -f1

got stream counts like:

  ...
  6874
  6875
  6876
  6877

then it be ease to display as progress use some progress cli.

After all, this is not the elegant way.

Answer 4

Another way is to check the size of the database with:

select pg_database_size('database_name');

Then open a linux terminal and monitor the size of the file you're dumping the database to with the following command (I don't know the equivalent on windows):

while true; do ls -l --block-size=1MB /path/to/dump.sql; sleep 10; done

Note though that the size of the dump file might be bigger than the database, as some data structures are stored in a compacted format when stored in the database.