Skip to content

Instantly share code, notes, and snippets.

@kmatt
Created May 24, 2012 20:07
  • Star 27 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save kmatt/2783910 to your computer and use it in GitHub Desktop.
Monitoring dump and restore with pv
# 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
@robduncan
Copy link

The restore could be much simpler

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

@kmatt
Copy link
Author

kmatt commented May 24, 2012

Much nicer - thanks!

@kmatt
Copy link
Author

kmatt commented Jun 1, 2012

Changed the restore example to query the uncompressed file size

@alagos
Copy link

alagos commented Aug 11, 2016

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

@limsammy
Copy link

limsammy commented Nov 1, 2021

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.

@wojtulab
Copy link

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

@banderlog
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment