RE: clean out ./data/base/pgsql_tmp

Since the temp files are easily identifiable as it has the PID in the fileaname.
Is it ok just manually deleting these files as the process has already being killed.

-----Original Message-----
From: Thomas Munro [mailto:thomas(dot)munro(at)enterprisedb(dot)com]
Sent: Thursday, 7 December 2017 2:56 PM
To: Dylan Luong <Dylan(dot)Luong(at)unisa(dot)edu(dot)au>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: clean out ./data/base/pgsql_tmp

On Thu, Dec 7, 2017 at 5:18 PM, Dylan Luong <Dylan(dot)Luong(at)unisa(dot)edu(dot)au> wrote:
> We have an issue where one of the developers ran a large query that
> hung was filling up the DATA drive very rapidly. From 50% usage to 95%
> in less than 2hrs.
>
> It created a very large pgsql_tmp size (300GB). To stop the drive
> filling up we had to kill the process manually using kill -9.
>
> How do we clean up the pgsql_tmp folder? Will Postgres periodically
> clean it? Ie CHECKPOINT?

Hi Dylan,

A clean shutdown/restart will clean it out. A crash restart (as happens if you kill processes like that) doesn't clear away pgsql_tmp data on the theory that it might be useful for forensics.

--
Thomas Munro
http://www.enterprisedb.com