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 1gsBu2-00076L-M9 for pgsql-admin@arkaria.postgresql.org; Fri, 08 Feb 2019 19:33:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gsBu1-0006P5-En for pgsql-admin@arkaria.postgresql.org; Fri, 08 Feb 2019 19:33:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gsBu0-0006Nu-OQ for pgsql-admin@lists.postgresql.org; Fri, 08 Feb 2019 19:33:33 +0000 Received: from mail-ot1-x343.google.com ([2607:f8b0:4864:20::343]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gsBtx-0000F1-Nj for pgsql-admin@lists.postgresql.org; Fri, 08 Feb 2019 19:33:31 +0000 Received: by mail-ot1-x343.google.com with SMTP id i20so7826571otl.0 for ; Fri, 08 Feb 2019 11:33:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=hfONJnV2Xd+xvE9KQcGfFtW36aXvawUuMtOfMdI2GEA=; b=hhrCVgpgDCB5Swc2aZ3QC5Cq+KYgU/90JJHpj2Xpf0mlLOZHKpKbkDRJnV7qtmaZNu 0e0pX1lhkSdkGphOxuR7sGvtqLQRBWNuher1HamDFBHHMbkvlDM8s44snA8XnnmINfhq 79BpjPHzPra2ws7x9TldUM/+/93CZcG0EagNfrMYH8xk4fPLSsnA9F06daDWWVPvVD4n Vi6Z2Y7TJwJNjxEssmtGWeTrOx2lsN5vHw5XvIT7khN3Owj6aRLOy47nKrcI1nIyGtxC EY8tVoi4TQnfnMQ5rP/Mp+QiW7KEmx2OdXqRhXyNQ7lzQAaurNUkog8GNEYrrM2Q4VPf FH7A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=hfONJnV2Xd+xvE9KQcGfFtW36aXvawUuMtOfMdI2GEA=; b=TXwvoys186I48QxiSGKazCqcswQiJOSwJN2+4kopw7MMoAUzX3IfoCXjkrGR40PVr6 Q+O6XIs8M2N69WADQEkpPSDdojPhT/tKoFelaXf6hacQOOk797i7Ql0YhD35f9X1cT2B i3/2FcA5VmJ+GFJ9LO863mmhM9QwMq7W7KKtoh3SOI+uWjT4VNmzZSrLICQpG/NGeFQa U4g7mPqc5P53c6TEGMe+8BiHQ2Y6/0h2AewL1zVfTBMdrGRsaCe0geuPwmWceMn6tZWv 9X3u9Gpv9D0e2ONJKT8JjgUOot0tlWrwv2x7QpchYBrveamJ+tq3Goya/6KPMa+VqapB 4fQg== X-Gm-Message-State: AHQUAuaJdpRhpiZ3VZC9Gy3O9SdyKqwZXV8sLUAHlDVpu/INew+tlO/O ttdzNaEj0Gkw8MnJi5UJrAIxHOTq X-Google-Smtp-Source: AHgI3Ia3a5vRlVcWZZhmvymxrS1AY51CBWtzzBdM68jfcKUlu2jQ8sa/ul8jyEtJxmfc+yYwnpejXw== X-Received: by 2002:a9d:6a91:: with SMTP id l17mr4598761otq.354.1549654408575; Fri, 08 Feb 2019 11:33:28 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id 127sm1270450oid.36.2019.02.08.11.33.26 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 08 Feb 2019 11:33:26 -0800 (PST) Subject: Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage To: pgsql-admin@lists.postgresql.org References: <5C5DB866.30206@sqlexec.com> From: Ron Message-ID: Date: Fri, 8 Feb 2019 13:33:25 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------9B8B7CECFB59E1911617322D" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------9B8B7CECFB59E1911617322D Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 2/8/19 12:34 PM, Jeremiah Bauer wrote: > 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 If you have enough disk space, try to VACUUM FULL the database (or do it one table at a time). > > -- > > > 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: > > Would you use pg_total_relation_size, not pg_relation_size in your > sizing query?  This will capture associated indexes and such. > > Regards, > Michael Vitale > >> Vijaykumar Jain >> Friday, February 8, 2019 12:05 PM >> Ok I am out of ideas :) >> Sorry, I’ll let the experts step in here :) >> >>  btw the 43GB and 439MB look close :) I hope there is no conversion >> error :) >> >> -- >> >> Regards, >> Vijay >> Jeremiah Bauer >> Friday, February 8, 2019 11:29 AM >> We don't have any blobs or json objects in this database.  I've >> sanitized the output of the query below, and toast doesn't show up >> until well down the list. >> >> FVDM=# SELECT nspname || '.' || relname AS "relation", >> FVDM-# pg_size_pretty(pg_relation_size(C.oid)) AS "size" >> FVDM-#   FROM pg_class C >> FVDM-#   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) >> FVDM-#   WHERE nspname NOT IN ('pg_catalog', 'information_schema') >> FVDM-#   ORDER BY pg_relation_size(C.oid) DESC >> FVDM-#   LIMIT 20; >>                        relation       |  size >> ------------------------------------------------------+--------- >>  mart.*****_data                                    | 301 MB >>  mart.detailed_*****                 | 66 MB >>  mart.major_*****              | 58 MB >>  public.temp_*****                        | 20 MB >>  public.temp_*****            | 13 MB >>  mart.customer_*****       | 11 MB >>  public.temp_*****| 11 MB >>  mart.major_*****         | 1888 kB >>  public.temp_*****  | 1880 kB >>  public.hold_*****  | 1424 kB >>  public.temp_*****                 | 1208 kB >>  mart.ranking_*****                        | 1144 kB >>  public.temp_*****  | 1080 kB >>  public.temp_*****       | 984 kB >>  mart.customer_*****             | 696 kB >>  mart.economic_*****            | 456 kB >>  public.hold_*****                 | 448 kB >>  mart.expanded_*****    | 384 kB >>  pg_toast.pg_toast_2618       | 376 kB >>  public.temp_*****       | 232 kB >> >> I also queried just for toast tables: >> >> FVDM=# SELECT nspname || '.' || relname AS "relation", >>     pg_size_pretty(pg_relation_size(C.oid)) AS "size" >>   FROM pg_class C >>   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) >>   WHERE nspname NOT IN ('pg_catalog', 'information_schema') and >> c.relname ~* 'toast' >>   ORDER BY pg_relation_size(C.oid) DESC >>   LIMIT 20; >>            relation            |    size >> -------------------------------+------------ >>  pg_toast.pg_toast_2618        | 376 kB >>  pg_toast.pg_toast_2619        | 48 kB >>  pg_toast.pg_toast_2618_index  | 16 kB >>  pg_toast.pg_toast_2619_index  | 16 kB >>  pg_toast.pg_toast_13104_index | 8192 bytes >>  pg_toast.pg_toast_3596_index  | 8192 bytes >>  pg_toast.pg_toast_2606_index  | 8192 bytes >>  pg_toast.pg_toast_2620_index  | 8192 bytes >>  pg_toast.pg_toast_2609_index  | 8192 bytes >>  pg_toast.pg_toast_2396_index  | 8192 bytes >>  pg_toast.pg_toast_3592_index  | 8192 bytes >>  pg_toast.pg_toast_1255_index  | 8192 bytes >>  pg_toast.pg_toast_13109_index | 8192 bytes >>  pg_toast.pg_toast_13114_index | 8192 bytes >>  pg_toast.pg_toast_13119_index | 8192 bytes >>  pg_toast.pg_toast_13124_index | 8192 bytes >>  pg_toast.pg_toast_13129_index | 8192 bytes >>  pg_toast.pg_toast_13134_index | 8192 bytes >>  pg_toast.pg_toast_2604_index  | 8192 bytes >>  pg_toast.pg_toast_2964_index  | 8192 bytes >> >> -- >> >> >> Jeremiah >> >> ---------------------------------------------------------------------------- >> *From:* Vijaykumar Jain >> >> *Sent:* Friday, February 8, 2019 11:21 AM >> *To:* Jeremiah Bauer >> *Cc:* pgsql-admin@lists.postgresql.org >> >> *Subject:* Re: [External] Postgres Database Disk Usage >> Do you have any blobs or large json objects as relnames? >> they generally end up as toast tables . >> Which may be the ones holding the space. >> >> >> >> On Fri, 8 Feb 2019 at 9:25 PM Jeremiah Bauer > > wrote: >> -- >> >> Regards, >> Vijay >> 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. > > 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. -- Angular momentum makes the world go 'round. --------------9B8B7CECFB59E1911617322D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 2/8/19 12:34 PM, Jeremiah Bauer wrote:
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

If you have enough disk space, try to VACUUM FULL the database (or do it one table at a time).



--


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:
Would you use pg_total_relation_size, not pg_relation_size in your sizing query?  This will capture associated indexes and such.

Regards,
Michael Vitale

Friday, February 8, 2019 12:05 PM
Ok I am out of ideas :)
Sorry, I’ll let the experts step in here :)

 btw the 43GB and 439MB look close :) I hope there is no conversion error :) 

--

Regards,
Vijay
Friday, February 8, 2019 11:29 AM
We don't have any blobs or json objects in this database.  I've sanitized the output of the query below, and toast doesn't show up until well down the list.

FVDM=# SELECT nspname || '.' || relname AS "relation",
FVDM-#     pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FVDM-#   FROM pg_class C
FVDM-#   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
FVDM-#   WHERE nspname NOT IN ('pg_catalog', 'information_schema')
FVDM-#   ORDER BY pg_relation_size(C.oid) DESC
FVDM-#   LIMIT 20;
                       relation                       |  size  
------------------------------------------------------+---------
 mart.*****_data                                        | 301 MB
 mart.detailed_*****                 | 66 MB
 mart.major_*****              | 58 MB
 public.temp_*****                                 | 20 MB
 public.temp_*****                      | 13 MB
 mart.customer_*****       | 11 MB
 public.temp_*****| 11 MB
 mart.major_*****                  | 1888 kB
 public.temp_*****            | 1880 kB
 public.hold_*****           | 1424 kB
 public.temp_*****                          | 1208 kB
 mart.ranking_*****                                 | 1144 kB
 public.temp_*****          | 1080 kB
 public.temp_*****       | 984 kB
 mart.customer_*****                      | 696 kB
 mart.economic_*****                      | 456 kB
 public.hold_*****                          | 448 kB
 mart.expanded_*****             | 384 kB
 pg_toast.pg_toast_2618                               | 376 kB
 public.temp_*****       | 232 kB

I also queried just for toast tables:

FVDM=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema') and c.relname ~* 'toast'
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;
           relation            |    size    
-------------------------------+------------
 pg_toast.pg_toast_2618        | 376 kB
 pg_toast.pg_toast_2619        | 48 kB
 pg_toast.pg_toast_2618_index  | 16 kB
 pg_toast.pg_toast_2619_index  | 16 kB
 pg_toast.pg_toast_13104_index | 8192 bytes
 pg_toast.pg_toast_3596_index  | 8192 bytes
 pg_toast.pg_toast_2606_index  | 8192 bytes
 pg_toast.pg_toast_2620_index  | 8192 bytes
 pg_toast.pg_toast_2609_index  | 8192 bytes
 pg_toast.pg_toast_2396_index  | 8192 bytes
 pg_toast.pg_toast_3592_index  | 8192 bytes
 pg_toast.pg_toast_1255_index  | 8192 bytes
 pg_toast.pg_toast_13109_index | 8192 bytes
 pg_toast.pg_toast_13114_index | 8192 bytes
 pg_toast.pg_toast_13119_index | 8192 bytes
 pg_toast.pg_toast_13124_index | 8192 bytes
 pg_toast.pg_toast_13129_index | 8192 bytes
 pg_toast.pg_toast_13134_index | 8192 bytes
 pg_toast.pg_toast_2604_index  | 8192 bytes
 pg_toast.pg_toast_2964_index  | 8192 bytes

--


Jeremiah

From: Vijaykumar Jain <vjain@opentable.com>
Sent: Friday, February 8, 2019 11:21 AM
To: Jeremiah Bauer
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: [External] Postgres Database Disk Usage
 
Do you have any blobs or large json objects as relnames?
they generally end up as toast tables .
Which may be the ones holding the space.



On Fri, 8 Feb 2019 at 9:25 PM Jeremiah Bauer <jbauer@agristats.com> wrote:
--

Regards,
Vijay
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.

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.

--
Angular momentum makes the world go 'round.
--------------9B8B7CECFB59E1911617322D--