Re: [External] Postgres Database Disk Usage

Do you have replication active?
is the size on the replica same as primary?
no stale orphan replication slots?
What is the Size of wal /pg_xlog directory ?

On Fri, 8 Feb 2019 at 9:17 PM Jeremiah Bauer <jbauer(at)agristats(dot)com> wrote:

> Hello,
>
> We have a development database that reports a larger size than the sum of
> it's relations and I was hoping someone had some insight into this issue.
> The consumed space is reflected in the size of the data directories
> PostgreSQL is maintaining on disk, so it's just not a reported size issue.
>
> This is the version of Postgres we are using:
>
> FVDM=# select version();
> version
>
>
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
>
> pg_database_size reports this size:
>
> name | owner | size
> ------------+----------+---------
> FVDM | postgres | 43 GB
>
> Running a query to report the total relation size of each schema results
> in this:
>
> schemaname | pg_size_pretty
> --------------------+----------------
> information_schema | 152 kB
> pg_catalog | 7880 kB
> public | 52 MB
> mart | 439 MB
> (4 rows)
>
> The total size of the relations is less than a gigabyte, but the database
> is 43GB in size. I've checked each table and there aren't any added
> indexes, so I'm very confused.
>
> We've run vacuum full and reindex on the database with no reduction in
> size and a vacuum analyze verbose reports no recoverable rows. I cloned
> the machine for troubleshooting and even attempted truncating every table
> in the database to see if that remedied the issue. The only thing that has
> reclaimed the space is dropping the database and restoring from backup.
>
> What is using the space and how do we reclaim it?
>
> Any help or insight would be greatly appreciated, I'm at a loss as to what
> is consuming this space.
>
> --
>
>
> Jeremiah
> CONFIDENTIALITY NOTICE: The information contained in this email (and any
> attachments) is privileged and confidential and protected from disclosure.
> If you are not the intended recipient of this email or the attachments, be
> aware that any disclosure, copying, distribution or use of this email or
> any attachment is strictly prohibited and you should not read the message
> or read or open any attachment. If you have received this email by mistake,
> please immediately notify the sender and delete it permanently from your
> system. Agri Stats, Inc. and its subsidiaries will not be held liable to
> any person or entity resulting from the unintended or unauthorized use of
> any information contained in this email.
>
--

Regards,
Vijay