Re: Space for pg_dump

On Tue, 31 Mar 2009, Scott Marlowe wrote:

> Sadly, there is no exact maths for such things. If your database has
> tons of indexes and such, it might be 20 or 100 times bigger on disk
> than it will be during backup. If it's all compressible text with few
> indexes, it might be a 1:1 or so size.

Since running an entire pgdump can take forever on a big database, what I
usually do here is start by running the disk usage query at
http://wiki.postgresql.org/wiki/Disk_Usage

That lets you better see index vs. table usage. Then, for the bigger
tables, I do something like this:

psql -c "COPY (select * from bigtable limit 100000) to stdout" | gzip > bigtable.gz
gzip -l bigtable.gz

That lets you get sample a decent sized chunk of the table to figure out
what compression ratio you're likely to get on the data in there. Given
all the table sizes and a compression ratio estimate, from there you can
make a fairly accurate guess of what the whole dump is going to take up,
presuming your data is fairly evenly distributed such that the first
records that come back are typical of the whole thing.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD