Ken Caruso <ken@ipl31.net> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov wrote:
>
>> Ken Caruso <ken@ipl31.net> wrote:
>>
>>
>> > SELECT SUM(pg_relation_size(pg_class.oid))
>> > FROM pg_class ;
>>
>> Perhaps you want pg_total_relation_size()?
> The number returned when using pg_total_relation size makes more
> sense: 346GB. However it looked like pg_class has toast and
> indexes listed individually, so I assumed that using
> pg_total_relation_size would count values twice, because the total
> relation size of table would include toast and indexes, and then
> they would get counted again when I ran pg_total_relation_size on
> the toast and indexes themselves. Is that an incorrect assumption
> on my part?
No that's a good point. On one of our production databases,
externally:
ccsa@SOCRATES:~> du -sh /var/pgsql/data/cc/
2.2T /var/pgsql/data/cc/
internally:
cir=# select
pg_size_pretty(sum(pg_relation_size(pg_class.oid))::bigint) from
pg_class;
pg_size_pretty
----------------
2176 GB
(1 row)
cir=# select
pg_size_pretty(sum(pg_total_relation_size(pg_class.oid))::bigint)
from pg_class;
pg_size_pretty
----------------
4223 GB
(1 row)
cir=# select
pg_size_pretty(sum(pg_total_relation_size(pg_class.oid))::bigint)
from pg_class where relkind = 'r';
pg_size_pretty
----------------
2176 GB
(1 row)
You must have a lot of something outside of relations showing as
active in pg_class taking up space. WAL files? Server logs? Temp
files left over from crashes?
-Kevin