Search code examples
postgresqlbackupdatabase-backups

Postgres backup size is getting double of database size itself

I'm totally new to Postgres. I had been needed to take Postgres database backup. I have used below command to take backup. 

    pg_dump Live backup > Livebackup.bak

The backup was being taken. However, backup file size is ~double of original database size itself. (Database size was 43GB and backup size is 86GB).The backup file should have around or ~database size.

I have already did full vacuum to this database. but still the backup size is larger.

Solution

  • Per the pg_dump docs, the default format is "plain text". That means that pg_dump is generating a large SQL script which can then be imported using the psql command line.

    Because it's plain text, any numeric or time types will take up much more space than they do in the database (where they can be stored as integers, floating point numbers, or in a binary format, etc). It's not a surprise to me that your backup file is larger than your database.

    I recommend looking at the -F/--format and -Z/--compress options for pg_dump and figure out the best method for your use case.

    I routinely have backups which take about 10-15% of the database size, by using those flags.