Re: PostgreSQL 9.6 Temporary files

On 03/20/2018 03:16 AM, Jimmy Augustine wrote:
> Thanks all for your response,
> 
> $du -h $MY_DATA/base/$BASE_OID/ returns 162GB but when I execute this query:
> 
> |SELECT stats.relname AS table, 
> pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, 
> pg_size_pretty(pg_total_relation_size(statsio.relid) - 
> pg_relation_size(statsio.relid)) AS related_objects_size, 
> pg_size_pretty(pg_total_relation_size(statsio.relid)) AS 
> total_table_size, stats.n_live_tup AS live_rows FROM 
> pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS 
> stats USING (relname) WHERE stats.schemaname = current_schemaUNION ALL 
> SELECT 'TOTAL' AS table, 
> pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, 
> pg_size_pretty(sum(pg_total_relation_size(statsio.relid) - 
> pg_relation_size(statsio.relid))) AS related_objects_size, 
> pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS 
> total_table_size, sum(stats.n_live_tup) AS live_rows FROM 
> pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS 
> stats USING (relname) WHERE stats.schemaname = current_schemaORDER BY 
> live_rows ASC;
> 
> |
> 
> |I obtain 80GB in total_table_size (half of my database), where are 
> missing data at?

First of all you are using pg_statio_user_tables which does not count 
system tables.

Second pretty sure the use of current_schema is limiting the results to 
only one schema in the database.

> |
> 
> 
> 2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>:
> 
>     On 03/19/2018 10:27 AM, Jimmy Augustine wrote:
> 
>         I tried this query and my database size is equal to 162GB.
> 
> 
>     Well you can always look in $DATA directly. The database will be
>     under $DATA/base/<db oid>.
> 
>     You can find the <db oid> like this:
> 
>     select oid, datname from  pg_database where datname='<db name>';
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com