From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Jeremiah Bauer <jbauer@agristats.com>
Cc: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: Vijaykumar Jain <vjain@opentable.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
Date: Fri, 08 Feb 2019 16:47:51 -0500
Message-ID: <5C5DF907.6060805@sqlexec.com> (raw)
In-Reply-To: <DM5PR19MB141802B8FD5EFDB7E848F370C2690@DM5PR19MB1418.namprd19.prod.outlook.com>
References: <DM5PR19MB1418873B4EFF26F9CABAD90FC2690@DM5PR19MB1418.namprd19.prod.outlook.com>
<CAE7uO5iLRzzFvq80Y1vAniY0UVsLpwbObS9bn3K+t3z49=7DwQ@mail.gmail.com>
<DM5PR19MB1418E8C931F5EFE7DBED2F72C2690@DM5PR19MB1418.namprd19.prod.outlook.com>
<CAE7uO5gjtEj=nEU=_5NK4EzOkizbfZAQYWRXcNubEVRq-Af8cw@mail.gmail.com>
<DM5PR19MB1418F001A6C3FF38A90B8FC2C2690@DM5PR19MB1418.namprd19.prod.outlook.com>
<CAE7uO5izj_+sqBWvC7KeNmzDg-gDVTjCt3R9sHtvjjo3DwHF4g@mail.gmail.com>
<5C5DB866.30206@sqlexec.com>
<CA+t6e1n9JVx8_6JsaUShyZ3H8XTEq0UWj7qiByy1WLBS=gmNXg@mail.gmail.com>
<DM5PR19MB14180CDED03BF29875F1F882C2690@DM5PR19MB1418.namprd19.prod.outlook.com>
<CA+t6e1ngKNgy4L1mAm35oniLhwThykPGjA45=f7W3axTHwz8Dg@mail.gmail.com>
<DM5PR19MB141802B8FD5EFDB7E848F370C2690@DM5PR19MB1418.namprd19.prod.outlook.com>
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 <mailto:jbauer@agristats.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.cherkassky@gmail.com>
> *Sent:* Friday, February 8, 2019 2:33 PM
> *To:* Jeremiah Bauer
> *Cc:* MichaelDBA; Vijaykumar Jain; pgsql-admin@lists.postgresql.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@agristats.com
> <mailto:jbauer@agristats.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 <mailto:jbauer@agristats.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@localhost ~]$ pg_dump FVDM > /tmp/out.sql
> [postgres@localhost ~]$ less /tmp/out.sql
> [postgres@localhost ~]$ createdb FVDM_restored
> [postgres@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.cherkassky@gmail.com>
> *Sent:* Friday, February 8, 2019 12:42 PM
> *To:* MichaelDBA
> *Cc:* Vijaykumar Jain; Jeremiah Bauer; pgsql-admin@lists.postgresql.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@sqlexec.com
> <mailto:MichaelDBA@sqlexec.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 <mailto:mariel.cherkassky@gmail.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.
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, jbauer@agristats.com, mariel.cherkassky@gmail.com, vjain@opentable.com, pgsql-admin@lists.postgresql.org
Subject: Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
In-Reply-To: <5C5DF907.6060805@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox