2018-03-20 15:00 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.com >> <mailto:adrian.kla...@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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
