Re: PostgreSQL 9.6 Temporary files

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_schema
UNION ALLSELECT '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_schema
ORDER BY live_rows ASC;

I obtain 80GB in total_table_size (half of my database), where are
missing data at?

2018-03-19 19:32 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>