Monitoring dump and restore with pv

pv_dump.sh

# dump
pg_dump testdb | pv -c -s $(psql -tc "SELECT pg_database_size('testdb')") -N dump | gzip > testdb.sql.gz

# restore
pv testdb_20120501.sql.gz | zcat | psql testdb

The restore could be much simpler

pv [ -N unzip ] testdb_20120501.sql.gz | zcat | psql testdb

Much nicer - thanks!

Changed the restore example to query the uncompressed file size

Thanks, this guide me to my solution, but with pg_dumpall.

pg_dumpall | pv -s $(psql -tc "SELECT SUM(pg_database_size(datname)) FROM pg_database") | gzip > pg-dump.sql.gz

Is not so precise as I was expecting (pg_database_size returned 380 MB and the final dump without compression is about 290MB) , but is something...

Cheers for this. Some documentation on pv flags used in pg_dump:

       -c, --cursor
              Use cursor positioning escape sequences instead of just
              using carriage returns.  This is useful in conjunction
              with -N (name) if you are using multiple pv invocations in
              a single, long, pipeline.

       -s SIZE, --size SIZE
              Assume the total amount of data to be transferred is SIZE
              bytes when calculating percentages and ETAs.  The same
              suffixes of "k", "m" etc can be used as with -L.

              Has no effect if used with -d PID to watch all file
              descriptors of a process, but will work with -d PID:FD.

       -N NAME, --name NAME
              Prefix the output information with NAME.  Useful in
              conjunction with -c if you have a complicated pipeline and
              you want to be able to tell different parts of it apart.

you can round it by 10%
pg_dumpall | pv -s $(psql -tc "SELECT SUM(1.1*(pg_database_size(datname))) FROM pg_database") | gzip > pg-dump.sql.gz

Cool solution :)

Also, interactive password input will not work, so you need to pass it through.
Something like (running postgres from container):

PGPASSWORD="postgrespw" \
pg_dump -U postgres -h localhost DATABASE |\
pv -c -s $(psql -h localhost -U postgres -tc "SELECT pg_database_size('DATABASE')") -N dump |\
gzip > DATABASE.sql.gz