pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres Database Disk Usage
19+ messages / 9 participants
[nested] [flat]
* Postgres Database Disk Usage
@ 2019-02-08 15:47 Jeremiah Bauer <jbauer@agristats.com>
0 siblings, 3 replies; 19+ messages in thread
From: Jeremiah Bauer @ 2019-02-08 15:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hello,
We have a development database that reports a larger size than the sum of it's relations and I was hoping someone had some insight into this issue. The consumed space is reflected in the size of the data directories PostgreSQL is maintaining on disk, so it's just not a reported size issue.
This is the version of Postgres we are using:
FVDM=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
pg_database_size reports this size:
name | owner | size
------------+----------+---------
FVDM | postgres | 43 GB
Running a query to report the total relation size of each schema results in this:
schemaname | pg_size_pretty
--------------------+----------------
information_schema | 152 kB
pg_catalog | 7880 kB
public | 52 MB
mart | 439 MB
(4 rows)
The total size of the relations is less than a gigabyte, but the database is 43GB in size. I've checked each table and there aren't any added indexes, so I'm very confused.
We've run vacuum full and reindex on the database with no reduction in size and a vacuum analyze verbose reports no recoverable rows. I cloned the machine for troubleshooting and even attempted truncating every table in the database to see if that remedied the issue. The only thing that has reclaimed the space is dropping the database and restoring from backup.
What is using the space and how do we reclaim it?
Any help or insight would be greatly appreciated, I'm at a loss as to what is consuming this space.
--
Jeremiah
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [External] Postgres Database Disk Usage
@ 2019-02-08 15:51 Vijaykumar Jain <vjain@opentable.com>
parent: Jeremiah Bauer <jbauer@agristats.com>
2 siblings, 1 reply; 19+ messages in thread
From: Vijaykumar Jain @ 2019-02-08 15:51 UTC (permalink / raw)
To: Jeremiah Bauer <jbauer@agristats.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Do you have replication active?
is the size on the replica same as primary?
no stale orphan replication slots?
What is the Size of wal /pg_xlog directory ?
On Fri, 8 Feb 2019 at 9:17 PM Jeremiah Bauer <jbauer@agristats.com> wrote:
> Hello,
>
> We have a development database that reports a larger size than the sum of
> it's relations and I was hoping someone had some insight into this issue.
> The consumed space is reflected in the size of the data directories
> PostgreSQL is maintaining on disk, so it's just not a reported size issue.
>
> This is the version of Postgres we are using:
>
> FVDM=# select version();
> version
>
>
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
>
> pg_database_size reports this size:
>
> name | owner | size
> ------------+----------+---------
> FVDM | postgres | 43 GB
>
> Running a query to report the total relation size of each schema results
> in this:
>
> schemaname | pg_size_pretty
> --------------------+----------------
> information_schema | 152 kB
> pg_catalog | 7880 kB
> public | 52 MB
> mart | 439 MB
> (4 rows)
>
> The total size of the relations is less than a gigabyte, but the database
> is 43GB in size. I've checked each table and there aren't any added
> indexes, so I'm very confused.
>
> We've run vacuum full and reindex on the database with no reduction in
> size and a vacuum analyze verbose reports no recoverable rows. I cloned
> the machine for troubleshooting and even attempted truncating every table
> in the database to see if that remedied the issue. The only thing that has
> reclaimed the space is dropping the database and restoring from backup.
>
> What is using the space and how do we reclaim it?
>
> Any help or insight would be greatly appreciated, I'm at a loss as to what
> is consuming this space.
>
> --
>
>
> Jeremiah
> 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.
>
--
Regards,
Vijay
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [External] Postgres Database Disk Usage
@ 2019-02-08 15:55 Jeremiah Bauer <jbauer@agristats.com>
parent: Vijaykumar Jain <vjain@opentable.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jeremiah Bauer @ 2019-02-08 15:55 UTC (permalink / raw)
To: Vijaykumar Jain <vjain@opentable.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Thank you for the response Vijaykumar,
>Do you have replication active?
No.
>What is the Size of wal /pg_xlog directory ?
[postgres@localhost pg_xlog]$ du --si
4.1k./archive_status
403M.
Let me know if you you need any more information.
--
Sincerely,
Jeremiah
From: Vijaykumar Jain <vjain@opentable.com>
Sent: Friday, February 8, 2019 10:51 AM
To: Jeremiah Bauer
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: [External] Postgres Database Disk Usage
Do you have replication active?
is the size on the replica same as primary?
no stale orphan replication slots?
What is the Size of wal /pg_xlog directory ?
On Fri, 8 Feb 2019 at 9:17 PM Jeremiah Bauer <jbauer@agristats.com> wrote:
Hello,
We have a development database that reports a larger size than the sum of it's relations and I was hoping someone had some insight into this issue. The consumed space is reflected in the size of the data directories PostgreSQL is maintaining on disk, so it's just not a reported size issue.
This is the version of Postgres we are using:
FVDM=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
pg_database_size reports this size:
name | owner | size
------------+----------+---------
FVDM | postgres | 43 GB
Running a query to report the total relation size of each schema results in this:
schemaname | pg_size_pretty
--------------------+----------------
information_schema | 152 kB
pg_catalog | 7880 kB
public | 52 MB
mart | 439 MB
(4 rows)
The total size of the relations is less than a gigabyte, but the database is 43GB in size. I've checked each table and there aren't any added indexes, so I'm very confused.
We've run vacuum full and reindex on the database with no reduction in size and a vacuum analyze verbose reports no recoverable rows. I cloned the machine for troubleshooting and even attempted truncating every table in the database to see if that remedied the issue. The only thing that has reclaimed the space is dropping the database and restoring from backup.
What is using the space and how do we reclaim it?
Any help or insight would be greatly appreciated, I'm at a loss as to what is consuming this space.
--
Jeremiah
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.
--
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [External] Postgres Database Disk Usage
@ 2019-02-08 16:21 Vijaykumar Jain <vjain@opentable.com>
parent: Jeremiah Bauer <jbauer@agristats.com>
0 siblings, 1 reply; 19+ messages in thread
From: Vijaykumar Jain @ 2019-02-08 16:21 UTC (permalink / raw)
To: Jeremiah Bauer <jbauer@agristats.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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:
> Thank you for the response Vijaykumar,
>
> >Do you have replication active?
> No.
>
> >What is the Size of wal /pg_xlog directory ?
> [postgres@localhost pg_xlog]$ du --si
> 4.1k./archive_status
> 403M.
>
> Let me know if you you need any more information.
>
> --
> Sincerely,
>
> Jeremiah
>
>
> From: Vijaykumar Jain <vjain@opentable.com>
> Sent: Friday, February 8, 2019 10:51 AM
> To: Jeremiah Bauer
> Cc: pgsql-admin@lists.postgresql.org
> Subject: Re: [External] Postgres Database Disk Usage
>
> Do you have replication active?
> is the size on the replica same as primary?
> no stale orphan replication slots?
> What is the Size of wal /pg_xlog directory ?
>
> On Fri, 8 Feb 2019 at 9:17 PM Jeremiah Bauer <jbauer@agristats.com> wrote:
> Hello,
>
> We have a development database that reports a larger size than the sum of
> it's relations and I was hoping someone had some insight into this issue.
> The consumed space is reflected in the size of the data directories
> PostgreSQL is maintaining on disk, so it's just not a reported size issue.
>
> This is the version of Postgres we are using:
>
> FVDM=# select version();
> version
>
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
>
> pg_database_size reports this size:
>
> name | owner | size
> ------------+----------+---------
> FVDM | postgres | 43 GB
>
> Running a query to report the total relation size of each schema results
> in this:
>
> schemaname | pg_size_pretty
> --------------------+----------------
> information_schema | 152 kB
> pg_catalog | 7880 kB
> public | 52 MB
> mart | 439 MB
> (4 rows)
>
> The total size of the relations is less than a gigabyte, but the database
> is 43GB in size. I've checked each table and there aren't any added
> indexes, so I'm very confused.
>
> We've run vacuum full and reindex on the database with no reduction in
> size and a vacuum analyze verbose reports no recoverable rows. I cloned
> the machine for troubleshooting and even attempted truncating every table
> in the database to see if that remedied the issue. The only thing that has
> reclaimed the space is dropping the database and restoring from backup.
>
> What is using the space and how do we reclaim it?
>
> Any help or insight would be greatly appreciated, I'm at a loss as to what
> is consuming this space.
>
> --
>
>
> Jeremiah
>
> 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.
> --
>
> 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.
>
--
Regards,
Vijay
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [External] Postgres Database Disk Usage
@ 2019-02-08 16:29 Jeremiah Bauer <jbauer@agristats.com>
parent: Vijaykumar Jain <vjain@opentable.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jeremiah Bauer @ 2019-02-08 16:29 UTC (permalink / raw)
To: Vijaykumar Jain <vjain@opentable.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto:jbauer@agristats.com>> wrote:
Thank you for the response Vijaykumar,
>Do you have replication active?
No.
>What is the Size of wal /pg_xlog directory ?
[postgres@localhost pg_xlog]$ du --si
4.1k./archive_status
403M.
Let me know if you you need any more information.
--
Sincerely,
Jeremiah
From: Vijaykumar Jain <vjain@opentable.com<mailto:vjain@opentable.com>>
Sent: Friday, February 8, 2019 10:51 AM
To: Jeremiah Bauer
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>
Subject: Re: [External] Postgres Database Disk Usage
Do you have replication active?
is the size on the replica same as primary?
no stale orphan replication slots?
What is the Size of wal /pg_xlog directory ?
On Fri, 8 Feb 2019 at 9:17 PM Jeremiah Bauer <jbauer@agristats.com<mailto:jbauer@agristats.com>> wrote:
Hello,
We have a development database that reports a larger size than the sum of it's relations and I was hoping someone had some insight into this issue. The consumed space is reflected in the size of the data directories PostgreSQL is maintaining on disk, so it's just not a reported size issue.
This is the version of Postgres we are using:
FVDM=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
pg_database_size reports this size:
name | owner | size
------------+----------+---------
FVDM | postgres | 43 GB
Running a query to report the total relation size of each schema results in this:
schemaname | pg_size_pretty
--------------------+----------------
information_schema | 152 kB
pg_catalog | 7880 kB
public | 52 MB
mart | 439 MB
(4 rows)
The total size of the relations is less than a gigabyte, but the database is 43GB in size. I've checked each table and there aren't any added indexes, so I'm very confused.
We've run vacuum full and reindex on the database with no reduction in size and a vacuum analyze verbose reports no recoverable rows. I cloned the machine for troubleshooting and even attempted truncating every table in the database to see if that remedied the issue. The only thing that has reclaimed the space is dropping the database and restoring from backup.
What is using the space and how do we reclaim it?
Any help or insight would be greatly appreciated, I'm at a loss as to what is consuming this space.
--
Jeremiah
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.
--
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.
--
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [External] Postgres Database Disk Usage
@ 2019-02-08 17:05 Vijaykumar Jain <vjain@opentable.com>
parent: Jeremiah Bauer <jbauer@agristats.com>
0 siblings, 1 reply; 19+ messages in thread
From: Vijaykumar Jain @ 2019-02-08 17:05 UTC (permalink / raw)
To: Jeremiah Bauer <jbauer@agristats.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
:)
On Fri, 8 Feb 2019 at 9:59 PM Jeremiah Bauer <jbauer@agristats.com> wrote:
> 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:
>
> Thank you for the response Vijaykumar,
>
> >Do you have replication active?
> No.
>
> >What is the Size of wal /pg_xlog directory ?
> [postgres@localhost pg_xlog]$ du --si
> 4.1k./archive_status
> 403M.
>
> Let me know if you you need any more information.
>
> --
> Sincerely,
>
> Jeremiah
>
>
> From: Vijaykumar Jain <vjain@opentable.com>
> Sent: Friday, February 8, 2019 10:51 AM
> To: Jeremiah Bauer
> Cc: pgsql-admin@lists.postgresql.org
> Subject: Re: [External] Postgres Database Disk Usage
>
> Do you have replication active?
> is the size on the replica same as primary?
> no stale orphan replication slots?
> What is the Size of wal /pg_xlog directory ?
>
> On Fri, 8 Feb 2019 at 9:17 PM Jeremiah Bauer <jbauer@agristats.com> wrote:
> Hello,
>
> We have a development database that reports a larger size than the sum of
> it's relations and I was hoping someone had some insight into this issue.
> The consumed space is reflected in the size of the data directories
> PostgreSQL is maintaining on disk, so it's just not a reported size issue.
>
> This is the version of Postgres we are using:
>
> FVDM=# select version();
> version
>
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
> 20120313 (Red Hat 4.4.7-23), 64-bit
>
> pg_database_size reports this size:
>
> name | owner | size
> ------------+----------+---------
> FVDM | postgres | 43 GB
>
> Running a query to report the total relation size of each schema results
> in this:
>
> schemaname | pg_size_pretty
> --------------------+----------------
> information_schema | 152 kB
> pg_catalog | 7880 kB
> public | 52 MB
> mart | 439 MB
> (4 rows)
>
> The total size of the relations is less than a gigabyte, but the database
> is 43GB in size. I've checked each table and there aren't any added
> indexes, so I'm very confused.
>
> We've run vacuum full and reindex on the database with no reduction in
> size and a vacuum analyze verbose reports no recoverable rows. I cloned
> the machine for troubleshooting and even attempted truncating every table
> in the database to see if that remedied the issue. The only thing that has
> reclaimed the space is dropping the database and restoring from backup.
>
> What is using the space and how do we reclaim it?
>
> Any help or insight would be greatly appreciated, I'm at a loss as to what
> is consuming this space.
>
> --
>
>
> Jeremiah
>
> 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.
> --
>
> 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.
>
> --
>
> 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.
>
--
Regards,
Vijay
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [External] Postgres Database Disk Usage
@ 2019-02-08 17:12 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Vijaykumar Jain <vjain@opentable.com>
0 siblings, 2 replies; 19+ messages in thread
From: MichaelDBA @ 2019-02-08 17:12 UTC (permalink / raw)
To: Vijaykumar Jain <vjain@opentable.com>; +Cc: Jeremiah Bauer <jbauer@agristats.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@opentable.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@agristats.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@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
> <mailto: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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [External] Postgres Database Disk Usage
@ 2019-02-08 17:42 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 1 reply; 19+ messages in thread
From: Mariel Cherkassky @ 2019-02-08 17:42 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Vijaykumar Jain <vjain@opentable.com>; Jeremiah Bauer <jbauer@agristats.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
>
> Vijaykumar Jain <vjain@opentable.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@agristats.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@opentable.com> <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.
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [External] Postgres Database Disk Usage
@ 2019-02-08 18:28 Jeremiah Bauer <jbauer@agristats.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
1 sibling, 0 replies; 19+ messages in thread
From: Jeremiah Bauer @ 2019-02-08 18:28 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; Vijaykumar Jain <vjain@opentable.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Sure, thanks for the response Michael. I appreciate any help you can provide.
FVDM=# SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_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')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
relation | size
------------------------------------------------------+---------
mart.pms_***** | 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_*****t | 1880 kB
public.hold_***** | 1424 kB
public.temp_***** | 1208 kB
mart.ranking_***** | 1144 kB
public.temp_***** | 1080 kB
public.temp_***** | 984 kB
mart.customer_***** | 456 kB
public.hold_***** | 448 kB
mart.expanded_***** | 384 kB
pg_toast.pg_toast_2618 | 392 kB
public.temp_***** | 232 kB
(20 rows)
FVDM=# SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_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 | 392 kB
pg_toast.pg_toast_2619 | 88 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
(20 rows)
--
Jeremiah
________________________________
From: MichaelDBA <MichaelDBA@sqlexec.com>
Sent: Friday, February 8, 2019 12:12 PM
To: Vijaykumar Jain
Cc: Jeremiah Bauer; pgsql-admin@lists.postgresql.org
Subject: Re: [External] Postgres Database Disk Usage
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@opentable.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@agristats.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@opentable.com><mailto:vjain@opentable.com>
Sent: Friday, February 8, 2019 11:21 AM
To: Jeremiah Bauer
Cc: pgsql-admin@lists.postgresql.org<mailto: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<mailto: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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
@ 2019-02-08 18:34 Jeremiah Bauer <jbauer@agristats.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Jeremiah Bauer @ 2019-02-08 18:34 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Vijaykumar Jain <vjain@opentable.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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:
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@opentable.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@agristats.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@opentable.com><mailto:vjain@opentable.com>
Sent: Friday, February 8, 2019 11:21 AM
To: Jeremiah Bauer
Cc: pgsql-admin@lists.postgresql.org<mailto: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<mailto: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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
@ 2019-02-08 19:33 Ron <ronljohnsonjr@gmail.com>
parent: Jeremiah Bauer <jbauer@agristats.com>
1 sibling, 0 replies; 19+ messages in thread
From: Ron @ 2019-02-08 19:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
> <mailto: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
>
>> Vijaykumar Jain <mailto:vjain@opentable.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@agristats.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@opentable.com>
>> <mailto:vjain@opentable.com>
>> *Sent:* Friday, February 8, 2019 11:21 AM
>> *To:* Jeremiah Bauer
>> *Cc:* pgsql-admin@lists.postgresql.org
>> <mailto: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
>> <mailto: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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
@ 2019-02-08 19:33 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Jeremiah Bauer <jbauer@agristats.com>
1 sibling, 1 reply; 19+ messages in thread
From: Mariel Cherkassky @ 2019-02-08 19:33 UTC (permalink / raw)
To: Jeremiah Bauer <jbauer@agristats.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Vijaykumar Jain <vjain@opentable.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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:
> 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:
>
> 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@opentable.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@agristats.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@opentable.com> <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.
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgres Database Disk Usage
@ 2019-02-08 19:33 Carrie Berlin <berlincarrie@gmail.com>
parent: Jeremiah Bauer <jbauer@agristats.com>
2 siblings, 0 replies; 19+ messages in thread
From: Carrie Berlin @ 2019-02-08 19:33 UTC (permalink / raw)
To: Jeremiah Bauer <jbauer@agristats.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Do you character varying columns that defined with limit ? Search for pg_toast on google and find the query to determine how much pg_toast you have.
Sent from my iPhone
> On Feb 8, 2019, at 10:47, Jeremiah Bauer <jbauer@agristats.com> wrote:
>
> Hello,
>
> We have a development database that reports a larger size than the sum of it's relations and I was hoping someone had some insight into this issue. The consumed space is reflected in the size of the data directories PostgreSQL is maintaining on disk, so it's just not a reported size issue.
>
> This is the version of Postgres we are using:
>
> FVDM=# select version();
> version
> -----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
>
> pg_database_size reports this size:
>
> name | owner | size
> ------------+----------+---------
> FVDM | postgres | 43 GB
>
> Running a query to report the total relation size of each schema results in this:
>
> schemaname | pg_size_pretty
> --------------------+----------------
> information_schema | 152 kB
> pg_catalog | 7880 kB
> public | 52 MB
> mart | 439 MB
> (4 rows)
>
> The total size of the relations is less than a gigabyte, but the database is 43GB in size. I've checked each table and there aren't any added indexes, so I'm very confused.
>
> We've run vacuum full and reindex on the database with no reduction in size and a vacuum analyze verbose reports no recoverable rows. I cloned the machine for troubleshooting and even attempted truncating every table in the database to see if that remedied the issue. The only thing that has reclaimed the space is dropping the database and restoring from backup.
>
> What is using the space and how do we reclaim it?
>
> Any help or insight would be greatly appreciated, I'm at a loss as to what is consuming this space.
>
> --
>
> Jeremiah
> 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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
@ 2019-02-08 19:59 Jeremiah Bauer <jbauer@agristats.com>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Jeremiah Bauer @ 2019-02-08 19:59 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; Vijaykumar Jain <vjain@opentable.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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:
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<mailto:mariel.cherkassky@gmail.com>>
Sent: Friday, February 8, 2019 12:42 PM
To: MichaelDBA
Cc: Vijaykumar Jain; Jeremiah Bauer; pgsql-admin@lists.postgresql.org<mailto: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:
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@opentable.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@agristats.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@opentable.com><mailto:vjain@opentable.com>
Sent: Friday, February 8, 2019 11:21 AM
To: Jeremiah Bauer
Cc: pgsql-admin@lists.postgresql.org<mailto: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<mailto: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.
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
@ 2019-02-08 21:47 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Jeremiah Bauer <jbauer@agristats.com>
0 siblings, 1 reply; 19+ messages in thread
From: MichaelDBA @ 2019-02-08 21:47 UTC (permalink / raw)
To: Jeremiah Bauer <jbauer@agristats.com>; +Cc: Mariel Cherkassky <mariel.cherkassky@gmail.com>; Vijaykumar Jain <vjain@opentable.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
@ 2019-02-09 04:08 priyanka chatterjee <pc9926@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 19+ messages in thread
From: priyanka chatterjee @ 2019-02-09 04:08 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Jeremiah Bauer <jbauer@agristats.com>; Mariel Cherkassky <mariel.cherkassky@gmail.com>; Vijaykumar Jain <vjain@opentable.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
If you have enabled any statement logging, Postgresql logs may be huge and
need housekeeping. Can you Check the size of logs?
On Sat, 9 Feb 2019 at 3:18 AM, MichaelDBA <MichaelDBA@sqlexec.com> wrote:
> 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 <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>
> <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.
> Jeremiah Bauer <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>
> <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.
> Mariel Cherkassky <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.
>
>
>
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Postgres Database Disk Usage
@ 2019-02-11 07:33 CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
parent: Jeremiah Bauer <jbauer@agristats.com>
2 siblings, 0 replies; 19+ messages in thread
From: CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG @ 2019-02-11 07:33 UTC (permalink / raw)
To: Jeremiah Bauer <jbauer@agristats.com>; pgsql-admin@lists.postgresql.org
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hello,<br>
<br>
We've got postgres 9.6.11 until december 7th and sometime we have
corrupt data or older files not deleted.<br>
<br>
We have found it Two times until 9.6.11.<br>
<br>
We keep cool by restoring data in another base an delete the bigger
one.<br>
<br>
You can do this to know how cost your data :<br>
<br>
<pre><code>SELECT pg_size_pretty(sum(pg_table_size(oid))) FROM pg_class;
if your directory is bigger there's a little probleme.</code>
</pre>
<br>
<div class="moz-signature">
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 2.3 (Win32)">
<meta name="AUTHOR" content="dreal-ra">
<meta name="CREATED" content="20110608;16534339">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGED" content="20120220;8494106">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<style type="text/css">
<!--
@page { size: 21cm 29.7cm; margin: 2cm }
P { margin-bottom: 0.21cm }
-->
</style>
<pre><b>Yann Convers</b>
Gestionnaire de l'infrastructure, des référentiels et des outils<p>
<font color="#000000"><b>DREAL Auvergne-Rhône-Alpes</b></font></p>
<pre>Service Connaissance, Information, Développement Durable, Autorité Environnementale - Pole Système d Information Géographique
Tél : 04 26 28 67 89
courriel : <a href="mailto:yann.convers@developpement-durable.gouv.fr">yann.convers@developpement-durable.gouv.fr</a></pre>adresse de la boite d’unité : <a href="mailto:sig.dreal-ara@developpement-durable.gouv.fr">boite unité</a>
<p>Pour toute commande interne de prestations, merci d utilisez le formulaire suivant : <a href="http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new">http://post...;
<b>Adresse postale :</b> DREAL Auvergne-Rhône-Alpes, CIDDAE - SIG
69453 LYON CEDEX 06
<b>Adresse physique :</b> 5
place Jules Ferry (immeuble Lugdunum - métro Brotteaux) -
69006 Lyon
<b>Standard :</b> 04 26 28 60 00
<b>Liens :</b><a href="http://intra.dreal-rhone-alpes.i2/">Site
Intranet</a> <a href="http://www.auvergne-rhone-alpes.developpement-durable.gouv.fr/">Site
Internet</a>
<p style="margin-bottom: 0cm">
</p>
</pre>
</div>
<div class="moz-cite-prefix">Le 08/02/2019 à 16:47, > Jeremiah
Bauer (par Internet, dépôt
<a class="moz-txt-link-abbreviated" href="mailto:pgsql-admin-owner+m63745-121696@lists.postgresql.org">pgsql-admin-owner+m63745-121696@lists.postgresql.org</a>) a écrit :<br>
</div>
<blockquote
cite="mid:DM5PR19MB1418873B4EFF26F9CABAD90FC2690@DM5PR19MB1418.namprd19.prod.outlook.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
Hello,</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
We have a development database that reports a larger size than
the sum of it's relations and I was hoping someone had some
insight into this issue. The consumed space is reflected in the
size of the data directories PostgreSQL is maintaining on disk,
so it's just not a reported size issue.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
This is the version of Postgres we are using:</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<span style="margin: 0px; font-family: Calibri, Arial,
Helvetica, sans-serif; background-color: rgb(255, 255, 255)">FVDM=#
select version();<br>
</span>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica,
sans-serif; background-color: rgb(255, 255, 255)">
version
<br>
</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica,
sans-serif; background-color: rgb(255, 255, 255)">
-----------------------------------------------------------------------------------------------------------<br>
</div>
<div style="margin: 0px; font-family: Calibri, Arial, Helvetica,
sans-serif; background-color: rgb(255, 255, 255)">
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit<br>
</div>
<span style="margin: 0px; font-family: Calibri, Arial,
Helvetica, sans-serif; background-color: rgb(255, 255, 255)"></span><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
pg_database_size reports this size:<br>
<br>
<span> name | owner | size <br>
</span>
<div>------------+----------+---------<br>
</div>
<div> FVDM | postgres | 43 GB<br>
</div>
<span></span><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
Running a query to report the total relation size of each schema
results in this:<br>
<br>
<span> schemaname | pg_size_pretty <br>
</span>
<div>--------------------+----------------<br>
</div>
<div> information_schema | 152 kB<br>
</div>
<div> pg_catalog | 7880 kB<br>
</div>
<div> public | 52 MB<br>
</div>
<div> mart | 439 MB<br>
</div>
<div>(4 rows)<br>
</div>
<span></span><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
The total size of the relations is less than a gigabyte, but the
database is 43GB in size. I've checked each table and there
aren't any added indexes, so I'm very confused.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
We've run vacuum full and reindex on the database with no
reduction in size and a vacuum analyze verbose reports no
recoverable rows. I cloned the machine for troubleshooting and
even attempted truncating every table in the database to see if
that remedied the issue. The only thing that has reclaimed the
space is dropping the database and restoring from backup.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
What is using the space and how do we reclaim it? </div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
Any help or insight would be greatly appreciated, I'm at a loss
as to what is consuming this space.</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="signature">
<div id="divtagdefaultwrapper" dir="ltr" style="font-size:12pt;
color:#000000; font-family:Calibri,Helvetica,sans-serif">
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0;
margin-bottom:0">--</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0;
margin-bottom:0"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0;
margin-bottom:0">Jeremiah</p>
</div>
</div>
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.
</blockquote>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
@ 2019-02-11 07:44 CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
parent: priyanka chatterjee <pc9926@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG @ 2019-02-11 07:44 UTC (permalink / raw)
To: priyanka chatterjee <pc9926@gmail.com>; MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Jeremiah Bauer <jbauer@agristats.com>; Mariel Cherkassky <mariel.cherkassky@gmail.com>; Vijaykumar Jain <vjain@opentable.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi,<br>
<br>
vaccum full don't deliver free space from files not in database.
Your files are in the same directory but postgres doesn't kows them.<br>
<br>
You must restore database and rename it, if you need to keep same
name.<br>
<br>
you can delete files without link in pg_database but it's more
dangerous if you make mistake.<br>
<br>
I have doing that to time last month on database of 300 GB.<br>
<br>
I think i have a bog between proxmox/postgres 9.6.11 and ubuntu
16.04 but i haven't found it<br>
<br>
<br>
<br>
<br>
<div class="moz-signature">
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 2.3 (Win32)">
<meta name="AUTHOR" content="dreal-ra">
<meta name="CREATED" content="20110608;16534339">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGED" content="20120220;8494106">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<style type="text/css">
<!--
@page { size: 21cm 29.7cm; margin: 2cm }
P { margin-bottom: 0.21cm }
-->
</style>
<pre><b>Yann Convers</b>
Gestionnaire de l'infrastructure, des référentiels et des outils<p>
<font color="#000000"><b>DREAL Auvergne-Rhône-Alpes</b></font></p>
<pre>Service Connaissance, Information, Développement Durable, Autorité Environnementale - Pole Système d Information Géographique
Tél : 04 26 28 67 89
courriel : <a href="mailto:yann.convers@developpement-durable.gouv.fr">yann.convers@developpement-durable.gouv.fr</a></pre>adresse de la boite d’unité : <a href="mailto:sig.dreal-ara@developpement-durable.gouv.fr">boite unité</a>
<p>Pour toute commande interne de prestations, merci d utilisez le formulaire suivant : <a href="http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new">http://post...;
<b>Adresse postale :</b> DREAL Auvergne-Rhône-Alpes, CIDDAE - SIG
69453 LYON CEDEX 06
<b>Adresse physique :</b> 5
place Jules Ferry (immeuble Lugdunum - métro Brotteaux) -
69006 Lyon
<b>Standard :</b> 04 26 28 60 00
<b>Liens :</b><a href="http://intra.dreal-rhone-alpes.i2/">Site
Intranet</a> <a href="http://www.auvergne-rhone-alpes.developpement-durable.gouv.fr/">Site
Internet</a>
<p style="margin-bottom: 0cm">
</p>
</pre>
</div>
<div class="moz-cite-prefix">Le 09/02/2019 à 05:08, > priyanka
chatterjee (par Internet, dépôt
<a class="moz-txt-link-abbreviated" href="mailto:pgsql-admin-owner+m63745-121812@lists.postgresql.org">pgsql-admin-owner+m63745-121812@lists.postgresql.org</a>) a écrit :<br>
</div>
<blockquote
cite="mid:CANnOdgZ-QrvQhA-YHUCHUq33FmxPbzOLivS92_=Ao5Rxkp3hHg@mail.gmail.com"
type="cite">
<div
style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">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.</div>
<div
style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)"></div>
</blockquote>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [Junk released by User action] Re: [Junk released by User action] Re: [External] Postgres Database Disk Usage
@ 2019-02-11 11:16 Guillaume Lelarge <guillaume@lelarge.info>
parent: CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
0 siblings, 0 replies; 19+ messages in thread
From: Guillaume Lelarge @ 2019-02-11 11:16 UTC (permalink / raw)
To: CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>; +Cc: priyanka chatterjee <pc9926@gmail.com>; MichaelDBA <MichaelDBA@sqlexec.com>; Jeremiah Bauer <jbauer@agristats.com>; Mariel Cherkassky <mariel.cherkassky@gmail.com>; Vijaykumar Jain <vjain@opentable.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Le lun. 11 févr. 2019 à 08:44, CONVERS Yann - DREAL
Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
a écrit :
> Hi,
>
> vaccum full don't deliver free space from files not in database. Your
> files are in the same directory but postgres doesn't kows them.
>
> You must restore database and rename it, if you need to keep same name.
>
> you can delete files without link in pg_database but it's more dangerous
> if you make mistake.
>
> I have doing that to time last month on database of 300 GB.
>
> I think i have a bog between proxmox/postgres 9.6.11 and ubuntu 16.04 but
> i haven't found it
>
>
There's more chance that PostgreSQL crashed for whatever reason and it lost
tracks of some files because of that. Did you experience crash with
PostgreSQL? because it shouldn't forget files otherwise.
--
Guillaume.
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2019-02-11 11:16 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-08 15:47 Postgres Database Disk Usage Jeremiah Bauer <jbauer@agristats.com>
2019-02-08 15:51 ` Vijaykumar Jain <vjain@opentable.com>
2019-02-08 15:55 ` Jeremiah Bauer <jbauer@agristats.com>
2019-02-08 16:21 ` Vijaykumar Jain <vjain@opentable.com>
2019-02-08 16:29 ` Jeremiah Bauer <jbauer@agristats.com>
2019-02-08 17:05 ` Vijaykumar Jain <vjain@opentable.com>
2019-02-08 17:12 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-02-08 17:42 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-08 18:34 ` Jeremiah Bauer <jbauer@agristats.com>
2019-02-08 19:33 ` Ron <ronljohnsonjr@gmail.com>
2019-02-08 19:33 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2019-02-08 19:59 ` Jeremiah Bauer <jbauer@agristats.com>
2019-02-08 21:47 ` MichaelDBA <MichaelDBA@sqlexec.com>
2019-02-09 04:08 ` priyanka chatterjee <pc9926@gmail.com>
2019-02-11 07:44 ` CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
2019-02-11 11:16 ` Guillaume Lelarge <guillaume@lelarge.info>
2019-02-08 18:28 ` Jeremiah Bauer <jbauer@agristats.com>
2019-02-08 19:33 ` Carrie Berlin <berlincarrie@gmail.com>
2019-02-11 07:33 ` CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
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