From: | Nico De Ranter <nico(dot)deranter(at)esaturnus(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: pg_dump crashes |
Date: | 2020-05-22 15:11:14 |
Message-ID: | CALVv0fZ4X+12h3nTX0pZ43Xcth04ZVn8ErnoLXs_t0n3W_Hh7g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, May 22, 2020 at 5:07 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:
> On 5/22/20 7:48 AM, Nico De Ranter wrote:
> > The original server was running 9.5.14
> > The system I am currently testing on is 11.8
> >
> > 2 fields are marked as 'extended'. However if I understand correctly
> > the table isn't actually toasted:
> >
> >
> > oid | table_schema | table_name | total_bytes |
> > total | index | toast | table
> >
> -------+--------------------+-------------------------+--------------+------------+------------+------------+------------
> > 19601 | public | file | 147648061440 |
> > 138 GB | 57 GB | 8192 bytes | 80 GB
>
> What query are you using to get above?
>
SELECT oid, table_schema, table_name,
total_bytes,
pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS
table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a) a order by total_bytes desc;
Stolen from stackoverflow :-)
Nico
--
Nico De Ranter
Operations Engineer
T. +32 16 38 72 10
eSATURNUS
Philipssite 5, D, box 28
3001 Leuven – Belgium
T. +32 16 40 12 82
F. +32 16 40 84 77
www.esaturnus.com
*For Service & Support :*
Support Line Belgium: +32 2 2009897
Support Line International: +44 12 56 68 38 78
Or via email : medical(dot)services(dot)eu(at)sony(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | stan | 2020-05-22 15:11:15 | Query to get name a data type of a view |
Previous Message | Adrian Klaver | 2020-05-22 15:09:53 | Re: pg_dump crashes |