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:

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