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