From: | "androxkentaki (dot)" <androxkentaki(at)gmail(dot)com> |
---|---|
To: | Priancka Chatz <pc9926(at)gmail(dot)com> |
Cc: | raf <raf(at)raf(dot)org>, pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Newbie needs help with space issue |
Date: | 2020-04-22 07:23:30 |
Message-ID: | CAL+gpC8UQhfXW+T7z2v3mFEpGVN44VKOw+PqL01xErUy4RWC2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Priancka Chatz | 2020-04-22 07:52:33 | Re: Newbie needs help with space issue |
Previous Message | Priancka Chatz | 2020-04-22 04:25:04 | Re: Newbie needs help with space issue |