8

Similar to this question about MySQL -- I need to find out the size of a dump file prior dumping but for a PostgreSQL Database. I could not find any reference to this in the documentation

2 Answers 2

4

A very rough estimate is to compute the size of all tables with:

select pg_size_pretty(sum(pg_table_size(tablename::regclass))) 
from pg_tables 
where schemaname not in ('pg_catalog','information_schema');
3

It's hard to predict the size of the dump file. large fields will be compressed using LZ77 so the result could be larger than the table files. Also , bytea, bit[], jsonb. integers, dates, and timestamps, expressed in ASCII are larger than they are on disk.

Dumps don't have slack space, or indexes, so the result could be smaller that file sizes of the database.

If you compress the dump it could compress by 90%

If you want certainty you really have to suck it and see. Eg pipe pg_dump output to a byte counter

pg_dump  datname | wc --bytes
2
  • My understanding of the final pg_dump ... | wc ... command is that it will operate entirely in memory, not using disk space at all, so is relatively safe to use even if you aren't at all sure that your dump will fit in the remaining free disk space. Does this sound correct? Commented May 7 at 17:54
  • yes. that's what it will do,
    – Jasen
    Commented May 7 at 23:27

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.