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

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

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(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
> <mailto:MichaelDBA(at)sqlexec(dot)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
>
>> Vijaykumar Jain <mailto:vjain(at)opentable(dot)com>
>> 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 <mailto:jbauer(at)agristats(dot)com>
>> 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(at)opentable(dot)com>
>> <mailto:vjain(at)opentable(dot)com>
>> *Sent:* Friday, February 8, 2019 11:21 AM
>> *To:* Jeremiah Bauer
>> *Cc:* pgsql-admin(at)lists(dot)postgresql(dot)org
>> <mailto:pgsql-admin(at)lists(dot)postgresql(dot)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(at)agristats(dot)com
>> <mailto:jbauer(at)agristats(dot)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.