Re: Newbie needs help with space issue - Mailing list pgsql-admin

From Priancka Chatz
Subject Re: Newbie needs help with space issue
Date
Msg-id CANnOdgbvTFK+St7FuM-A_kMpNpJ-xY-z57dmteHPT-1QO4MPkw@mail.gmail.com
Whole thread Raw
In response to Re: Newbie needs help with space issue  (raf <raf@raf.org>)
Responses Re: Newbie needs help with space issue  ("androxkentaki ." <androxkentaki@gmail.com>)
List pgsql-admin
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@raf.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



pgsql-admin by date:

Previous
From: raf
Date:
Subject: Re: Newbie needs help with space issue
Next
From: "androxkentaki ."
Date:
Subject: Re: Newbie needs help with space issue