pg_database_size differs from df -s

Frank Lanitz <frank@frank.uvena.de> writes:

I've got an issue I'm not sure I might have a misunderstanding. When
calling

select sum(pg_database_size(datid)) as total_size from pg_stat_database

the result is much bigger than running a df -s over the postgres folder
- Its about factor 5 to 10 depending on database.

Did you mean "du -s"?

My understanding was, pg_database_size is the database size on disc. Am
I misunderstanding the docu here?

For me, pg_database_size gives numbers that match up fairly well with
what "du" says. I would not expect an exact match, since du probably
knows about filesystem overhead (such as metadata) whereas
pg_database_size does not. Something's fishy if it's off by any large
factor, though. Perhaps you have some tables in a nondefault
tablespace, where du isn't seeing them?

regards, tom lane

Am 06.06.2012 17:49, schrieb Tom Lane:

Frank Lanitz <frank@frank.uvena.de> writes:

I've got an issue I'm not sure I might have a misunderstanding. When
calling

select sum(pg_database_size(datid)) as total_size from pg_stat_database

the result is much bigger than running a df -s over the postgres folder
- Its about factor 5 to 10 depending on database.

Did you mean "du -s"?

Yepp, sure. Was to confused about the two numbers. ;)

My understanding was, pg_database_size is the database size on disc. Am
I misunderstanding the docu here?

For me, pg_database_size gives numbers that match up fairly well with
what "du" says. I would not expect an exact match, since du probably
knows about filesystem overhead (such as metadata) whereas
pg_database_size does not. Something's fishy if it's off by any large
factor, though. Perhaps you have some tables in a nondefault
tablespace, where du isn't seeing them?

Nope. Its a pretty much clean database without any fancy stuff.

Cheers,
Frank

Frank Lanitz <frank@frank.uvena.de> writes:

Am 06.06.2012 17:49, schrieb Tom Lane:

For me, pg_database_size gives numbers that match up fairly well with
what "du" says. I would not expect an exact match, since du probably
knows about filesystem overhead (such as metadata) whereas
pg_database_size does not. Something's fishy if it's off by any large
factor, though. Perhaps you have some tables in a nondefault
tablespace, where du isn't seeing them?

Nope. Its a pretty much clean database without any fancy stuff.

Peculiar. If you want to put some time into it, you could try comparing
sizes table-by-table to see if you can isolate where the discrepancy is.

The only reason I can think of for du to report a size smaller than the
nominal file length (which is which the pg_xxx_size functions look at)
is if the file contains unallocated "holes". That really shouldn't ever
happen with PG tables though.

regards, tom lane

On Wed, Jun 6, 2012 at 6:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Frank Lanitz <frank@frank.uvena.de> writes:

Am 06.06.2012 17:49, schrieb Tom Lane:

For me, pg_database_size gives numbers that match up fairly well with
what "du" says. I would not expect an exact match, since du probably
knows about filesystem overhead (such as metadata) whereas
pg_database_size does not. Something's fishy if it's off by any large
factor, though. Perhaps you have some tables in a nondefault
tablespace, where du isn't seeing them?

Nope. Its a pretty much clean database without any fancy stuff.

Peculiar. If you want to put some time into it, you could try comparing
sizes table-by-table to see if you can isolate where the discrepancy is.

Perhaps with the contrib adminpack you may easily find where it comes from
comparing size from pg_table_size and pg_stat_file ?

On 6 Jun 2012, at 16:33, Frank Lanitz wrote:

the result is much bigger than running a df -s over the postgres folder
- Its about factor 5 to 10 depending on database.

Is your du reporting sizes in Bytes or blocks or ...?

Alban Hertroys

--
The scale of a problem often equals the size of an ego.

On Wed, 2012-06-06 at 18:46 +0200, Julien Rouhaud wrote:

On Wed, Jun 6, 2012 at 6:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Frank Lanitz <frank@frank.uvena.de> writes:

Am 06.06.2012 17:49, schrieb Tom Lane:

For me, pg_database_size gives numbers that match up fairly well with
what "du" says. I would not expect an exact match, since du probably
knows about filesystem overhead (such as metadata) whereas
pg_database_size does not. Something's fishy if it's off by any large
factor, though. Perhaps you have some tables in a nondefault
tablespace, where du isn't seeing them?

Nope. Its a pretty much clean database without any fancy stuff.

Peculiar. If you want to put some time into it, you could try comparing
sizes table-by-table to see if you can isolate where the discrepancy is.

Perhaps with the contrib adminpack you may easily find where it comes from
comparing size from pg_table_size and pg_stat_file ?

You don't need the adminpack extension to use pg_stat_file. pg_stat_file
is in PostgreSQL core.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

On Wed, 6 Jun 2012 20:31:36 +0200
Alban Hertroys <haramrae@gmail.com> wrote:

On 6 Jun 2012, at 16:33, Frank Lanitz wrote:

the result is much bigger than running a df -s over the postgres
folder
- Its about factor 5 to 10 depending on database.

Is your du reporting sizes in Bytes or blocks or ...?

Should be byte as its a linux.

cheers,
Frank
--
Frank Lanitz <frank@frank.uvena.de>

Le mercredi 06 juin 2012 ￜ 21:45 +0200, Frank Lanitz a ￜcrit :

On Wed, 6 Jun 2012 20:31:36 +0200
Alban Hertroys <haramrae@gmail.com> wrote:

On 6 Jun 2012, at 16:33, Frank Lanitz wrote:

Is your du reporting sizes in Bytes or blocks or ...?

Should be byte as its a linux.

Not sure about this; from du's man page :

Display values are in units of the first available SIZE from
--block-size, and the DU_BLOCK_SIZE, BLOCK_SIZE and BLOCKSIZE
environment variables.
Otherwise, units default to 1024 bytes (or 512 if POSIXLY_CORRECT is
set).

vv=# select sum(pg_database_size(datid)) as total_size from
pg_stat_database;
total_size
------------
105086036
(1 ligne)

du -s base
103808 base

--
Vincent Veyron
http://vincentveyron.com
Logiciels de gestion pour le service juridique