clean out ./data/base/pgsql_tmp
Hi
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?
Thanks.
Regards
Dylan
On Thu, Dec 7, 2017 at 5:18 PM, Dylan Luong <Dylan.Luong@unisa.edu.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
On Thu, Dec 7, 2017 at 1:18 PM, Dylan Luong <Dylan.Luong@unisa.edu.au> wrote:
How do we clean up the pgsql_tmp folder? Will Postgres periodically clean
it? Ie CHECKPOINT?
A postmaster restart cleans up those files automatically.
--
Michael
On Wed, Dec 6, 2017 at 8:18 PM, Dylan Luong <Dylan.Luong@unisa.edu.au>
wrote:
Hi
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.
Did you try kill -15 first? If you did try it and it did not work, then
that is probably a bug. And if you have a reproducible test case, then we
will probably be able to fix it.
Cheers,
Jeff
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.munro@enterprisedb.com]
Sent: Thursday, 7 December 2017 2:56 PM
To: Dylan Luong <Dylan.Luong@unisa.edu.au>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: clean out ./data/base/pgsql_tmp
On Thu, Dec 7, 2017 at 5:18 PM, Dylan Luong <Dylan.Luong@unisa.edu.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
On Wed, Dec 6, 2017 at 9:29 PM, Dylan Luong <Dylan.Luong@unisa.edu.au>
wrote:
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.
I've done that before without issue.
Cheers,
Jeff