0

We are facing default temporary tablespace disk space full issue due to lots of below temporary relations files created but not removed after ending session

1.1G    t756_1536213694.409
1.1G    t756_1536213694.410
1.1G    t756_1536213694.411
1.1G    t756_1536213694.412
1.1G    t756_1536213694.413
1.1G    t756_1536213694.414

Please note: - these files are not in pgsql_tmp <-- this is used for temporary files like sorting etc. these file are created in temp_tablespace location which default tablespace currenlty.

spin=# show temp_tablespaces ;
 temp_tablespaces
------------------

(1 row)

I tried to get the backend id from below query but no luck as process id is changing every time when execute the below query .

spin=# SELECT bid, pg_stat_get_backend_pid(bid) AS pid FROM pg_stat_get_backend_idset() bid where bid='756';
 bid |  pid
-----+-------
 756 | 41533
(1 row) 
 spin=# SELECT bid, pg_stat_get_backend_pid(bid) AS pid FROM pg_stat_get_backend_idset() bid where bid='756';
 bid |  pid
-----+-------
 756 | 86639
(1 row) 

Questions: 1. Can we remove directly ?
           2. are these files are removed once db restart ? 
           3. How WE CAN find which session created these temp relations which is not get removed once session ends ? 
thanks for your help.
Improve this question
5
  • If you close the client session properly, these files get removed. A restart will remove them in any case.
    – Laurenz Albe
    Commented Sep 2 at 14:36
  • Hi Laurenz , as always thanks for your comment. what if DB services crashed instead of clean shutdown? then also these files will be removed? the issue is we are facing issue like sudden of lot of session i can see starting up in PostgreSQL standalone server. and client getting timeout error. we need to kill all startup session to prevent db from stopping.
    – Adam Mulla
    Commented Sep 2 at 16:38
  • If the server crashes, the files will certainly stay behind. But PostgreSQL should delete them when you restart the server. With "standalone", do you mean single-user mode? I don't understand the last sentence at all. Please edit the question to add new information rather than writing it as a comment.
    – Laurenz Albe
    Commented Sep 2 at 19:53
  • thanks i edited with single PostgreSQL instance.
    – Adam Mulla
    Commented Sep 3 at 11:50
  • If you close the database session correctly, the file get removed. So you must have done something unusual. Anyway, as I said, a restart will fix it.
    – Laurenz Albe
    Commented Sep 3 at 12:19

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.