1

postgresql version is 10

\l+ showing db size to be 7228 GB

i checked all tables sizes those comes out to be 60 GB only using

SELECT relname AS "relation", 
       pg_size_pretty(pg_relation_size(C.oid)) AS "size"  
FROM pg_class C 
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)  
WHERE nspname NOT IN ('pg_catalog', 'information_schema')  
ORDER BY pg_relation_size(C.oid) DESC 

Then i went to see filesystem in which my database base directory is:

/var/lib/pgsql/10/data/base/16384`

I see around 70k

t20_909090 files in this directory which are taking the space.

I have checked in my local postgres db instance these files are created only after i create a new temp table and got deleted after i close the session.

files in base directory from my local db:

    pwd ; ls  | grep  t
/var/lib/pgsql/10/data/base/150689
pg_internal.init
t3_150704
t3_150707
t3_150709
t3_150710
t3_150713
t3_150715
t3_150716
t3_150719
t3_150721
t3_150722
t3_150725
t3_150727

from my local postgres db the temp files and above file nodes:

    tertw=# SELECT c.oid, 
n.nspname as SchemaName
,c.relname as RelationName
,CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as RelationType
,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner               
,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n               
                ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','s') 
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC
;
  oid   | schemaname | relationname | relationtype | relationowner | relationsize 
--------+------------+--------------+--------------+---------------+--------------
 150704 | pg_temp_3  | tesst        | table        | postgres      | 0 bytes
 150710 | pg_temp_3  | tesst1       | table        | postgres      | 0 bytes
 150716 | pg_temp_3  | tesst2       | table        | postgres      | 0 bytes
 150722 | pg_temp_3  | tesst3       | table        | postgres      | 0 bytes
(4 rows)


according to above obersvation i concluded that these are temp tables but in prodcution system in pg_class these oid (e.g.: i checked 909090 as oid from t20_909090 ) are not present

how do I check what files they are or how to clean them up.

Also all these files got cleaned up automatically after i restarted the postgres db.

Extension on my DB :

tertw=# select * from pg_extension ;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
---------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql |       10 |           11 | f              | 1.0        |           | 
(1 row)

tertw=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

tertw=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

3
  • @a_horse_with_no_name i have updated the question with the extensions which are installed into my system - only plpgsql is there which dosen't looks like the one. Is there any other way i should check for the installed extension – techagrammer Nov 20 '20 at 13:15
  • You are correct, those are from temp tables. (it is temp files for sorting and hashing and such that are in pgsql_tmp). Did restarting the server clear up all of these, or was it only the ones you created for testing purposes that were cleared up? – jjanes Nov 20 '20 at 15:24
  • @jjanes yes those got cleaned up after restarting postgres and in my case these files are not in pgsql_tmp , question is updated with the ls output of db directory these files are present there – techagrammer Nov 23 '20 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.