Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage

If you have enabled any statement logging, Postgresql logs may be huge and
need housekeeping. Can you Check the size of logs?

On Sat, 9 Feb 2019 at 3:18 AM, MichaelDBA <MichaelDBA(at)sqlexec(dot)com> wrote:

> Since the underlying filesystem shows the 43GB, try to isolate it to what
> file(s) are causing the problem. Assuming you are connected to the "FVDM"
> database and all the tables are in one schema, run this query to identify
> all the table files.
>
> select c.oid, c.relname, c.reltuples, c.relfilenode, pg_relation_filenode(c.oid), pg_relation_filepath(c.oid) from pg_class c, pg_tables t where c.relname = t.tablename and t.schemaname = 'myschema' order by 1;
>
>
> Before drilling down to where the database files are, do a du -hs at
> $PGDATA directory. Then do it at $PGDATA/base. Then do it at the table
> directory level:
> $PGDATA/base/ <database filenode> and run "du -hs" on everything there
> and see if something stands out.
>
> Jeremiah Bauer <jbauer(at)agristats(dot)com>
> Friday, February 8, 2019 2:59 PM
> Yes, it did run out of free space when some test data was being loaded.
>
> I added more space, could this be the cause of the issue? I ran a Vacuum
> Full on the entire database after adding more free space, but the size of
> the database is still significantly larger than the sum of the objects in
> it.
>
> --
>
>
> Jeremiah
>
> ------------------------------
> *From:* Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
> <mariel(dot)cherkassky(at)gmail(dot)com>
> *Sent:* Friday, February 8, 2019 2:33 PM
> *To:* Jeremiah Bauer
> *Cc:* MichaelDBA; Vijaykumar Jain; pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* [Junk released by User action] Re: [Junk released by User
> action] Re: [External] Postgres Database Disk Usage
>
> Is there a chance that you run out of disks space recently?
>
> On Fri, Feb 8, 2019, 8:34 PM Jeremiah Bauer <jbauer(at)agristats(dot)com wrote:
> 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.
> Jeremiah Bauer <jbauer(at)agristats(dot)com>
> Friday, February 8, 2019 1:34 PM
>
> Sure, I tried that before I posted to the mailing list.
>
> This is the size of the restored database with a different name:
>
> [postgres(at)localhost ~]$ pg_dump FVDM > /tmp/out.sql
> [postgres(at)localhost ~]$ less /tmp/out.sql
> [postgres(at)localhost ~]$ createdb FVDM_restored
> [postgres(at)localhost ~]$ psql FVDM_restored </tmp/out.sql
> ....completes no errors....
>
> postgres=# SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba)
> AS Owner,
> CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
> THEN
> pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
> ELSE 'No Access'
> END AS SIZE
>
> FROM pg_catalog.pg_database d
> ORDER BY
> CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
> THEN pg_catalog.pg_database_size(d.datname)
> ELSE NULL
> END DESC -- nulls first
> LIMIT 20;
> name | owner | size
> ---------------+----------+---------
> FVDM | postgres | 43 GB
> FVDM_restored | postgres | 497 MB
>
> --
>
>
> Jeremiah
> ------------------------------
> *From:* Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
> <mariel(dot)cherkassky(at)gmail(dot)com>
> *Sent:* Friday, February 8, 2019 12:42 PM
> *To:* MichaelDBA
> *Cc:* Vijaykumar Jain; Jeremiah Bauer; pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* [Junk released by User action] Re: [External] Postgres
> Database Disk Usage
>
> Can you try to dump the database and then restore it with a different name
> ? Check if the size of the restored one is the same.
>
>
>
> On Fri, Feb 8, 2019, 7:12 PM MichaelDBA <MichaelDBA(at)sqlexec(dot)com wrote:
>
> 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.
> Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
>
> Friday, February 8, 2019 12:42 PM
> Can you try to dump the database and then restore it with a different name
> ? Check if the size of the restored one is the same.
>
>
>
>
>