Re: Newbie needs help with space issue

If you have the superuser login, you can get the individual table sizes by
running
\dt+ *.*

On Wed, 22 Apr 2020 at 12:53 PM, androxkentaki . <androxkentaki(at)gmail(dot)com>
wrote:

> 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
>