2

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.

Improve this question

1 Answer 1

Reset to default
5

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thanks for the quick response. I'm new to postgrace. sharing the sample link/command will be a great help for small/compressed backups
The documentation page has examples of usage if you need them. You probably want "custom" or "directory" format, depending on your server. Compression will be a time/space tradeoff that you'll have to decide for your situation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.