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?
3 Answers
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.
- @masonrye if you believe my answer was helpful, you should consider accepting it so the question won't stay as unanswered.user638219– user6382192021-06-17 19:10:47 +00:00Commented Jun 17, 2021 at 19:10
xz compresses data better than gzip. Running xz -6 <dumpfile> will compress the database dump with maximum compression.
- 1"Better" is an understatement, actually. I've seen ratios of 1:100 for some dumps.Arto Bendiken– Arto Bendiken2021-10-19 16:18:03 +00:00Commented Oct 19, 2021 at 16:18
there's also parallel gzip https://zlib.net/pigz/ for when you really need to maximize all your cores on a big compression job.
