0

I'm trying to dump a huge database and compress the dump in order to not have to wait hours till it's done.

I dump the database the following way:

 pg_dump -Fc -U -v | gzip > db$(date +%d-%m-%y_%H-%M).tar.gz

This leaves me with a compressed tar file. I know want to unzip it in order to have a .tar file only:

tar -xvf xxx.tar.gz

This leaves me with an error message saying This does not look like a tar archive file

My goal is to then import it via psql. I do not see what I am doing wrong – according to the Postgres documentation on dumps, I can use -Fc to dump in any wanted format? Thank you

1
  • Have you try to see what is the actual result of pg_dump -Fc -U -v >file? Oct 19, 2022 at 12:06

1 Answer 1

2

This leaves me with a compressed tar file

No. You're using -Fc, which gives you a "custom" file format specific to pg_dump and pg_restore. That's not a tar, so you're not compressing a tar file with your gzip call.

Furthermore, the pg_dump documentation points out:

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

Your gzip tries to compress something that's already compressed. That's not gonna do much, aside from wasting time.

As a matter of fact, under --compress=0..9, the same documentation tells us:

Specify the compression level to use. Zero means no compression. For the custom and directory archive formats, 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.

So, it uses gzip already! Can't reduce the size of something that's already gzip'ed using gzip.

What you could do instead is using

pg_dump -Fc -Z0 -U -v | zstd -5 > db$(date +%d-%m-%y_%H-%M).custom.zst
#        ^  ^            ^    ^
#        |  |            |    \----- zstd compression level 5:
#        |  |            |           better than gzip --best, 
#        |  |            |           but much, much faster
#        |  |            \-------- use the zstd compressor
#        |  \-------------------- don't compress yourself                  
#        \--------------------- custom format

Because, honestly, gzip is very obsolete. It's slow, doesn't scale well, and the compression ratios are terrible. There's many nicer alternatives, but zstd allows for a wide range of speed/compression ratio tradeoffs, and is very actively maintained and available for all platforms.

Warning: Slight ranting below!
Note that you can, on the compressing side, use a higher compression setting than -5; but the higher you go, the slower compression gets. It really depends on your time vs space tradeoff whether you want to try -18, I often go with -11 for zstd, which for typical data is a bout two thirds as fast as gzip --best, but tends to produce files that are 10% smaller. zstd's range of compression vs speed tradeoff (-1 to -18, or up to -22 if you really have too much free CPU time and care about 0.1% better compression ratio) is much more finely grained than gzip's, and more useful on modern machines, where zlib (which underlies gzip) is limited to a 32 kB sized window. Because, who got more than 64 kB of RAM? Everyone. In 2022, even my baking oven has more than 64 kB of RAM. So, zstd doesn't try to use incredibly small dictionary-building windows. That's one of the simpler reasons why its compression can work better than that of zlib/gzip.

4
  • Thank you for the explanation! I can then simply restore it via pg_restore?
    – Djabone
    Oct 20, 2022 at 9:15
  • you would need to pipe it through zstd, e.g. zstd --stdout -d db20-10-22_11-30.custom.zst | pg_restore. Oct 20, 2022 at 9:31
  • I tried your dumping method, it was fast, but on the other hand it generated 3 files instead of one?
    – Djabone
    Oct 20, 2022 at 10:46
  • I don't understand what you mean with "it generated three files". | zstd -5 > db$(date +%d-%m-%y_%H-%M).custom.zst definitely only generates one file. Oct 20, 2022 at 11:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .