The discussion revolves around the large size of backups. BCHK is surprised by the 2.25 GB size of their database backup, considering the posts are short and simple text files. Falco suggests using compression with pg_dump to reduce the file size, citing a significant reduction in size and time from their own experiment.
However, codinghorror points out that backups are already compressed, as indicated by the .tar.gz extension, and provides an example of a 742 MB backup being compressed into a 415 MB file. The large size is largely due to uploaded images, which are not very compressible. If images are excluded from the backup, it will not be a complete archive of the site’s content.
codinghorror suggests that compressing only the text data can result in significant size reduction, citing an example of a 339 MB file being compressed to 60 MB. sam agrees that images in posts are usually a large percentage of the backup size.
We have about 250,000 posts from 25,000 topics - and our database / backup is about 4.5 backups (I have it set up for 2 backups - so I think the post database is about 2.25 Gig.
That seems really large for what are essentially short, simple text files.
Is there anyway that can be made smaller? How about a simple compression algorithm included so that we can save space on our backups?
pg_dump supports compression:
-Z, --compress=0-9
You just need to balance between time spent VS file-size.
Maybe patch this file and add a small compression level (2~3) and measure the impact on your workload.
In my starting database (10 posts, 50 users) I see the following:
-rw-r--r-- 1 postgres postgres 2,2M Jul 24 16:04 back_Z_2 -rw-r--r-- 1 postgres postgres 11M Jul 24 16:04 back_no_compression
`
Compression (Z=2)
real 0m0.557s
user 0m0.072s
sys 0m0.032s
No Compression
real 0m0.837s
user 0m0.280s
sys 0m0.036s
`
But you need to use some real data to really commit to this. Using Meta would be a good example.
Backups are compressed, that’s what the .tar.gz extension means. It is basically .zip for UNIX:
That’s a 742 MB backup that is compressed into a 415 MB file:
It does include uploaded images, if you extract it:
The uploads folder is 314 MB across 6,947 files and 116,168 folders (!).
You can get smaller backups by opting not to include the uploaded images as well, but it won’t be a complete archive of the content (text and images) that makes up the topics on your site!
Remember images are not very compressible. But text is. If I compress just dump.sql alone with 7zip it goes from 339 MB to 60 MB.
Images in posts are usually a very large percentage of backup size
