Score:2

What is the best way to compress postgres database dump file?

cn flag

I have large databases which im dumping with pg_dump and I need to make dump file smaller. What is the best way to compress this postgres database dump file?

Score:2
us flag

pg_dump comes with compression feature built-in:

  • -Z 0..9
  • --compress 0..9

Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support compression at all.

There are 9 levels of compression - higher the level, higher the compression. 0 means no compression.

pg_dump [connection-option...] --compress=9 [dbname]

for maximum compression.

p10l avatar
us flag
@masonrye if you believe my answer was helpful, you should consider [accepting it](https://serverfault.com/help/someone-answers) so the question won't stay as unanswered.
Score:1
us flag

xz compresses data better than gzip. Running xz -6 <dumpfile> will compress the database dump with maximum compression.

Arto Bendiken avatar
eg flag
"Better" is an understatement, actually. I've seen ratios of 1:100 for some dumps.
Score:0
kr flag

there's also parallel gzip https://zlib.net/pigz/ for when you really need to maximize all your cores on a big compression job.

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.