From: | Priancka Chatz <pc9926(at)gmail(dot)com> |
---|---|
To: | "androxkentaki (dot)" <androxkentaki(at)gmail(dot)com> |
Cc: | pgsql-admin(at)lists(dot)postgresql(dot)org, raf <raf(at)raf(dot)org> |
Subject: | Re: Newbie needs help with space issue |
Date: | 2020-04-22 07:52:33 |
Message-ID: | CANnOdgacU6-T7ZYZYtrgeT-mW11kw_qMMPAcVrO3-5C3Sgyp+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | soumik.bhattacharjee | 2020-04-22 13:54:49 | Composite primary key duplicate records - Issue In PostgreSQL But not in Oracle |
Previous Message | androxkentaki . | 2020-04-22 07:23:30 | Re: Newbie needs help with space issue |