Re: PostgreSQL 9.6 Temporary files

2018-03-20 15:00 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

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

AHHH Thanks you I found missing data they are stored into pg_largeobject.

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