Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gsE0E-0004SC-UN for pgsql-admin@arkaria.postgresql.org; Fri, 08 Feb 2019 21:48:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gsE0D-0000y7-Lv for pgsql-admin@arkaria.postgresql.org; Fri, 08 Feb 2019 21:48:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gsE0D-0000v2-Bj for pgsql-admin@lists.postgresql.org; Fri, 08 Feb 2019 21:48:05 +0000 Received: from mout.perfora.net ([74.208.4.194]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gsE0A-00031d-Nc for pgsql-admin@lists.postgresql.org; Fri, 08 Feb 2019 21:48:04 +0000 Received: from [192.168.1.101] ([98.169.208.73]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0LsiHH-1hFtnS2j17-012Dwe; Fri, 08 Feb 2019 22:47:51 +0100 Message-ID: <5C5DF907.6060805@sqlexec.com> Date: Fri, 08 Feb 2019 16:47:51 -0500 From: MichaelDBA User-Agent: Postbox 5.0.25 (Windows/20180328) MIME-Version: 1.0 To: Jeremiah Bauer CC: Mariel Cherkassky , Vijaykumar Jain , "pgsql-admin@lists.postgresql.org" Subject: Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage References: <5C5DB866.30206@sqlexec.com> , In-Reply-To: Content-Type: multipart/alternative; boundary="------------030709040109020406020303" X-Provags-ID: V03:K1:QtICeBUl/sCnXQVrESN9Y0t/ulAJK3KMmGfiDKRMCVUkQF4k2FB SK0CV2qAzwSsuDBH/GpS4NQ1+t15/Bsi6vVkcP5sGPAG3yCrJVzZF4lJEGnXpS16dguqpRv 3rv+3JtlvBTxy09ZcGsaIf7uWrTZJxoBQTHI8ZQmiYOkLS7gnJFjw9h+ZPJ1NS7R1PGvb9L qWVsgDAizRVBzfX4Qp7ZQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:vT2O31IhLys=:ixBTdcr991AwULI+4djKRc eYRWfFAfIRTSxvSqQdXoIhKfWKP0EZbmwUJ9ysU/R2wcxvkxBUPxeU6jquRJhNd/4yWZboC2G CMsqNk1dyG9j+ADymGCJqpDnTPE3owj9LWnK7HZRySsy9KSLaLjcUcsBcZLFRW8B473Qm0VkF Jc3IKiq/eV9aBsRtUvnv1x0K2OlvWb9GDt5CLzhdbnQhoe7pvF/xHesSoNqK8lldy/QtMucrc PZtKPtvl0qJFZeDpr2ccziZxy/RhGmCIbZiaRUSPdZV1bI1Gl7gIHxHmGtHk5WXRrLRmBWlRX KlbiPkLdpwHjEx20pBw41SEggdIo5IXVfApFvSWwu14cBMUy4pKsCeVGBiPwVR2B2WLdp/r2+ EWrOfY2ED2g/PLblYDc1FiWZJW7foS2rid0pPHLl39tRufcxFdgP3w9m1MWbM6GXG9QwJvV5B D80FI7gn6xH0Mvew2JzZCnZFUGP4AfuiRbjMKIjsuyhWziT2c6xj/JWTd0Dfidkhv1jdQqbKC yqZRG9QyaPz6daB+aZaLrTevwauGEYe9ulC7NT4zlAvpCqq1Hm714cr8T54zQtsVPFSxKut0f wy+6KSlu8G+iUDM7NWhnGimU3viVKEnclw6MtXkDF0OU8pGXDtchFBFUqnTB08VTAi5g3Bv00 foReETEshck/dx2pUGvXDTmIkW1ZVAxngvgjtxIZDwz2Muvb0slawQyRi2eKMKZ4jk6eFDyp7 YzKevLBYUUYGVVb49KoOKIC3qfnIBdmlidvpb+Z7R2RgBXXgg4kPPyXZ0O0= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------030709040109020406020303 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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/ and run "du -hs" on everything there and see if something stands out. > Jeremiah Bauer > 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 > *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 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 > 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 ....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 > *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 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 > 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. > > > --------------030709040109020406020303 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.

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 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.
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 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.
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. 




--------------030709040109020406020303--