I am trying to dump a database which has a size of just over 100 GiB. I am using the following command (with pv to give a progress bar to observe where this problem occurs):

pg_dump -h <host> -U <user> <db> | pv -c -s $(psql -h <host> -U <user> <db> -tc "SELECT pg_database_size('<db>')") -N dump | gzip > <db>.sql.gz

The dump chugs along happily until it reaches 56.8GiB raw passing through the pipeline (~25GiB of the compressed file), and then just dies. I am at a loss as to why this is happening. The filesystem is ext4, so it's not a size limitation there.

I'm currently running it again, but splitting files into 10GiB chunks. Let's see how this goes.

Any ideas why, no matter what, the dump stops at 56.8GiB?

EDIT: The server I'm trying to get the dump from is a pod running on Google Kubernetes Engine. I have tried dumping both on the pod itself, and from another pod connecting through a service, and the problem persists.

  • Do you receive any error messages? Or do you just terminate/kill the pg_dump? Are there any other "dumps" running? – hot2use Aug 8 '18 at 11:37
  • 1
    An original database size of 100Gb does not necessarily mean that a pg_dump will be the same size. It's almost certainly going to be smaller due to bloat / empty space. It might have finished? – Philᵀᴹ Aug 8 '18 at 11:46
  • 1
    The problem is, it fails to import into the new database. It will get ~60% of the way through at a fair speed (10-15MiB/s), then completely drop to ~50KiB/s. I left it overnight in case it was just taking a while to process the import, but it still didn't complete by the next morning, after 18 hours of running. – G Newbury Aug 8 '18 at 11:50
  • 1
    Do you encounter the same issue if you leave the pv... part out of the equation? e.g. pg_dump -h <host> -U <user> <db> | gzip > <db>.sql.gz. – hot2use Aug 8 '18 at 11:54
  • 1
    Just curiosity, why u don't use pg_dump with --compress=9 ? – Sahap Asci Aug 8 '18 at 16:58

Your Answer

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

Browse other questions tagged or ask your own question.