From: | raf <raf(at)raf(dot)org> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Newbie needs help with space issue |
Date: | 2020-04-22 03:51:32 |
Message-ID: | 20200422035132.7xxbyti7p7ir2y3r@raf.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Priancka Chatz | 2020-04-22 04:25:04 | Re: Newbie needs help with space issue |
Previous Message | Jerry Sievers | 2020-04-22 02:05:21 | Re: Application outage with XX000: could not receive data from client: Connection timed out |