Determining size of a database before dumping

Madison Kelly wrote:

Hi all,

I am (re)writing a backup program and I want to add a section for
backing up pSQL DBs. In the planning steps (making sure a given
destination has enough space) I try to calculate how much space will be
needed by a 'pg_dump' run *before* actually dumping it.

I suppose:

pg_dump $PGD_OPTIONS | wc -c

isn't efficient enough, right? Without knowing the options you plan
to use with pg_dump (compression? dump just tables?, etc.) this is
going to be hard to get a decent estimate from...

--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.

On Oct 2, 2006, at 22:17 , Madison Kelly wrote:

I am (re)writing a backup program and I want to add a section for
backing up pSQL DBs. In the planning steps (making sure a given
destination has enough space) I try to calculate how much space
will be needed by a 'pg_dump' run *before* actually dumping it.

Is there a relatively easy way to do that? Moreso, if it possible
to do this from an unpriviledged account? If not, is there a way to
add the permissions to a specific pg user to allow that user to
perform this?

You could dump the database to /dev/null, piping it through wc to
catch the size, but that would of course be wasteful.

You could count the disk space usage of the actual stored tuples,
though this will necessarily be inexact:

http://www.postgresql.org/docs/8.1/static/diskusage.html

Or you could count the size of the physical database files (/var/lib/
postgresql or wherever). While these would be estimates, you could at
least guarantee that the dump would not *exceed* the esimtate.

Keep in mind that pg_dump can compress the dump and (iirc) will do so
by default when you use the custom format (-Fc or --format=c).

Alexander.

Steve Wampler wrote:

Madison Kelly wrote:

Hi all,

I am (re)writing a backup program and I want to add a section for
backing up pSQL DBs. In the planning steps (making sure a given
destination has enough space) I try to calculate how much space will be
needed by a 'pg_dump' run *before* actually dumping it.

I suppose:

pg_dump $PGD_OPTIONS | wc -c

isn't efficient enough, right? Without knowing the options you plan
to use with pg_dump (compression? dump just tables?, etc.) this is
going to be hard to get a decent estimate from...

For now, lets assume I am doing a raw dump (no compression) and no fancy
switches. I would probably err of the side of caution and try dumping
OIDs and all schema (plus whatever else is needed to insure a full
restore to a clean DB).

I could try piping the dump into something like 'wc' but with very large
DBs I'd be worried about the (tremendous) disk I/O that would cause.
This is also why I am hoping Pg keeps this info somewhere.

Madison

Alexander Staubo wrote:

On Oct 2, 2006, at 22:17 , Madison Kelly wrote:

I am (re)writing a backup program and I want to add a section for
backing up pSQL DBs. In the planning steps (making sure a given
destination has enough space) I try to calculate how much space will
be needed by a 'pg_dump' run *before* actually dumping it.

Is there a relatively easy way to do that? Moreso, if it possible to
do this from an unpriviledged account? If not, is there a way to add
the permissions to a specific pg user to allow that user to perform this?

You could dump the database to /dev/null, piping it through wc to catch
the size, but that would of course be wasteful.

You could count the disk space usage of the actual stored tuples, though
this will necessarily be inexact:

http://www.postgresql.org/docs/8.1/static/diskusage.html

Or you could count the size of the physical database files
(/var/lib/postgresql or wherever). While these would be estimates, you
could at least guarantee that the dump would not *exceed* the esimtate.

Keep in mind that pg_dump can compress the dump and (iirc) will do so by
default when you use the custom format (-Fc or --format=c).

Alexander.

Heh, that looks like just the article I would have wanted if I had
properly RTFM. :D

Many thanks!!

Madi

Alexander Staubo <alex@purefiction.net> writes:

You could count the disk space usage of the actual stored tuples,
though this will necessarily be inexact:
http://www.postgresql.org/docs/8.1/static/diskusage.html
Or you could count the size of the physical database files (/var/lib/
postgresql or wherever). While these would be estimates, you could at
least guarantee that the dump would not *exceed* the esimtate.

You could guarantee no such thing; consider compression of TOAST values.
Even for uncompressed data, datatypes such as int and float can easily
print as more bytes than they occupy on-disk.

Given all the non-data overhead involved (eg for indexes), it's probably
unlikely that a text dump would exceed the "du" size of the database,
but it's far from "guaranteed".

regards, tom lane

On Oct 2, 2006, at 23:19 , Tom Lane wrote:

Alexander Staubo <alex@purefiction.net> writes:

You could count the disk space usage of the actual stored tuples,
though this will necessarily be inexact:
http://www.postgresql.org/docs/8.1/static/diskusage.html
Or you could count the size of the physical database files (/var/lib/
postgresql or wherever). While these would be estimates, you could at
least guarantee that the dump would not *exceed* the esimtate.

You could guarantee no such thing; consider compression of TOAST
values.
Even for uncompressed data, datatypes such as int and float can easily
print as more bytes than they occupy on-disk.

Why does pg_dump serialize data less efficiently than PostgreSQL when
using the "custom" format? (Pg_dump arguably has greater freedom in
being able to apply space-saving optimizations to the output format.
For example, one could use table statistics to selectively apply
something like Rice coding for numeric data, or vertically decompose
the tuples and emit sorted vectors using delta compression.) As for
TOAST, should not pg_dump's compression compress just as well, or
better?

Alexander.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/02/06 16:19, Tom Lane wrote:

Alexander Staubo <alex@purefiction.net> writes:

You could count the disk space usage of the actual stored tuples,
though this will necessarily be inexact:
http://www.postgresql.org/docs/8.1/static/diskusage.html
Or you could count the size of the physical database files (/var/lib/
postgresql or wherever). While these would be estimates, you could at
least guarantee that the dump would not *exceed* the esimtate.

You could guarantee no such thing; consider compression of TOAST values.
Even for uncompressed data, datatypes such as int and float can easily
print as more bytes than they occupy on-disk.

Given all the non-data overhead involved (eg for indexes), it's probably
unlikely that a text dump would exceed the "du" size of the database,
but it's far from "guaranteed".

It's my experience that when there are lots of numeric fields,
fixed-width text records are approx 2.5x larger than the original
binary records.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFIZgvS9HxQb37XmcRAp6xAKC74LV+2wR6Ao5Oq56RInkkDP8PZgCglKEv
z0fvjrXTloWJJ7qdhfOpIoI=
=jICB
-----END PGP SIGNATURE-----

On Tue, 2006-10-03 at 00:42 +0200, Alexander Staubo wrote:

Why does pg_dump serialize data less efficiently than PostgreSQL when
using the "custom" format? (Pg_dump arguably has greater freedom in
being able to apply space-saving optimizations to the output format.
For example, one could use table statistics to selectively apply
something like Rice coding for numeric data, or vertically decompose
the tuples and emit sorted vectors using delta compression.) As for
TOAST, should not pg_dump's compression compress just as well, or
better?

It would be a strange set of data that had a larger representation as a
compressed pg_dump than the data directory itself. However, one could
imagine a contrived case where that might happen.

Let's say you had a single table with 10,000 columns of type INT4, 100M
records, all with random numbers in the columns. I don't think standard
gzip compression will compress random INT4s down to 32 bits.

Another example is NULLs. What if only a few of those records had non-
NULL values? If I understand correctly, PostgreSQL will represent those
NULLs with just one bit.

What you're saying is more theoretical. If pg_dump used specialized
compression based on the data type of the columns, and everything was
optimal, you're correct. There's no situation in which the dump *must*
be bigger. However, since there is no practical demand for such
compression, and it would be a lot of work, there is no *guarantee* that
the data directory will be bigger. However, it probably is.

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

On Tue, 2006-10-03 at 00:42 +0200, Alexander Staubo wrote:

Why does pg_dump serialize data less efficiently than PostgreSQL when
using the "custom" format?

What you're saying is more theoretical. If pg_dump used specialized
compression based on the data type of the columns, and everything was
optimal, you're correct. There's no situation in which the dump *must*
be bigger. However, since there is no practical demand for such
compression, and it would be a lot of work ...

There are several reasons for not being overly tense about the pg_dump
format:

* We don't have infinite manpower

* Cross-version and cross-platform portability of the dump files is
critical

* The more complicated it is, the more chance for bugs, which you'd
possibly not notice until you *really needed* that dump.

In practice, pushing the data through gzip gets most of the potential
win, for a very small fraction of the effort it would take to have a
smart custom compression mechanism.

regards, tom lane