CODEWITHSUNDEEP
postgresqlpg-dumppg-restore
Twenty_eight
Twenty_eight

Reputation: 21

Recompress PostgreSQL custom format dump file -Z 6 to -Z 9

Could anybody explain to me how to recompress pgdump file in a custom format without restoring it?

My workaround is the main database with replication, 5 nodes in total, with approx size about 300GB, I'm using one for slave replicas for creating dump file with -Z 9 it took about 4 hours, with -Z 6 it took about 2 hours. The problem in using -Z 9 is that it took too much time and replica became way too behind master node, that is why I'm using -Z 6 compression. I can't add one more node just for making the dump files.

Restore the database on dump storage and create the new dump file better compression is no option due to low resources on the dump storage to restore the database.

I'm already tried to compress the pgdump file with bz2 or 7z but I got difference only 1 GB on the total size of pgdump file about 40GB. If I use -Z 9 compression I got pgdump file size about 32 GB.

Is there any option to use pg_restore and pg_dump and recompress pgdump file with -Z 6 compression to -Z 9 compression?

Upvotes: 1

Views: 618

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246143

I don't think there is a simple way to rewrite a PostgreSQL dump file with higher compression, but what about creating the dump with -Z 0 and later compressing it with gzip or something similar?

That would make pg_dump as fast as possible, and you could still have compression.

Upvotes: 2

Related Questions