Postgres Database Backup Size

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Postgres Database Backup Size

Shiwangini Shishulkar
Hi,

Here , we have first tried with .BAK backups. For, 43GB database-> the backup size was ~86GB.

Then, I configured backup with .sql file. Here it was ~70GB for the same database size.

Next, now our database size keeps increasing.  As well as backup size is also increasing. Now, backup size is more than 100GB for 50GB database size. Due to some business requirement , we need to restore each backup daily. 

Here, I'm taking backup via below sample statement:

pg_dump -U postgres -d welcome -f welcome_${DATE}.sql

I had also tried with compressed backups via -Fc. But, since it is compressed, in case of restoration -> first it decompresses the backup and restore it. Which again takes a lot of time.

Any way to reduce backup size of postgres backups or it's default behavior of postgres to take backups which are double in size?

Thanks,
Shiwangini 
Reply | Threaded
Open this post in threaded view
|

Re: Postgres Database Backup Size

Laurenz Albe
Shiwangini Shishulkar wrote:
> Any way to reduce backup size of postgres backups or it's default behavior of postgres to take backups which are double in size?

The smallest backup would be a compressed custom format "pg_dump":

  pg_dump -F c -Z 9 ...

I is very unusual for a custom format dump to be bigger than
the original database.  How did you measure the database size?

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com