Re: Newbie needs help with space issue

this sql for table size ends with error that some of relation was not found.
So this is better one:

select
t.schemaname ,
t.tablename as object
,pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))
,pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))

from
pg_tables t
where 1=1
--and t.schemaname = 'public'
--and t.schemaname <> 'public'
order by pg_total_relation_size(schemaname || '.' || tablename) desc;

śr., 22 kwi 2020 o 06:25 Priancka Chatz <pc9926(at)gmail(dot)com> napisał(a):

> Hi,
>
> Also you might want to check the logging settings and log file sizes if
> logging is enabled. Sometimes due to some issues in database, the number of
> log file entries can be a lot leading to large log files.
>
> Regards,
> Priyanka
>
> On Wed, 22 Apr 2020 at 9:21 AM, raf <raf(at)raf(dot)org> wrote:
>
>> Jesse Josserand wrote:
>>
>> > I apologize in advance for bothering you, but I've not been able to get
>> > answers from the community by searches I've done, nor have I figured out
>> > how to post a question there.
>> >
>> > I'm working for a gov't entity here in Mississippi and while I have
>> > extensive Oracle and MySQL experience, as well as Linux admin experience
>> > (which is what I was hired for), I've been given PostgreSQL DBA
>> > responsibilities since the last DBA quit abruptly (probably due to poor
>> > architecture and documentation here -- another thing I'm resolving).
>> >
>> > I'm having serious space issues in a production environment and psql
>> > commands and queries are not helping me isolate it nor determine the
>> root
>> > cause... possibly due to my naivete.
>> >
>> > Would you be so kind as to give me some insight as to how to best
>> determine
>> > what is growing to fast and why? I have a 20G disk that recently was
>> > increased to 40G and am now in less than 3 months almost out of space
>> there
>> > again.
>> >
>> > Yours truly,
>> > Jesse Josserand, CTO
>> > IT Web Services, LLC <https://itwebservicesllc.com/> and
>> > Ace Consulting Solutions, Inc. <https://aceconsultingsolutionsinc.com/>
>>
>> Hi,
>>
>> Here's a query to list individual table sizes:
>>
>> select
>> t.tablename as "object",
>> pg_size_pretty(pg_relation_size(cast(t.tablename as text))) as
>> "data",
>> pg_size_pretty(pg_total_relation_size(cast(t.tablename as text)))
>> as "total"
>> from
>> pg_tables t
>> where
>> t.schemaname = 'public'
>> order by
>> pg_total_relation_size(cast(t.tablename as text)) desc
>>
>> I think the last column includes index sizes.
>>
>> Running this repeatedly over time should show what's growing.
>>
>> cheers,
>> raf
>>
>>
>>
>>

--
Z wyrazami szacunku
Andrzej Gerasimuk