Thread: how do I get rid of huge sorttemp files?

how do I get rid of huge sorttemp files?

From
Markus Bertheau
Date:
[root@ds80-237-200-79 6221984]# pwd
/var/lib/pgsql/data/base/6221984
[root@ds80-237-200-79 6221984]# ls -lh --sort=size | head -10
total 9.9G
-rw-------    1 postgres postgres     1.0G Jan  7 12:36
pg_sorttemp10742.0
-rw-------    1 postgres postgres     1.0G Jan  7 12:36
pg_sorttemp10749.0
-rw-------    1 postgres postgres     1.0G Jan  7 12:36
pg_sorttemp10771.0
-rw-------    1 postgres postgres     1.0G Jan  7 12:36
pg_sorttemp10795.0
-rw-------    1 postgres postgres     1.0G Jan  8 17:13
pg_sorttemp3029.0
-rw-------    1 postgres postgres     779M Jan  4 11:39
pg_sorttemp10464.0
-rw-------    1 postgres postgres     599M Jan  7 16:56
pg_sorttemp12913.0
-rw-------    1 postgres postgres     515M Jan  9 17:44
pg_sorttemp2457.0
-rw-------    1 postgres postgres     349M Jan  7 12:36
pg_sorttemp10742.1

template1=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

How do I get rid of these huge files and how do I prevent them from
being created again? The dump of the whole database is ~50 Megabytes
gzipped.

Thanks

--
Markus Bertheau
Cenes Data GmbH



Re: how do I get rid of huge sorttemp files?

From
Tom Lane
Date:
Markus Bertheau <bertheau@bab24.de> writes:
>  PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96

> How do I get rid of these huge files

"rm" will do fine, assuming that you are certain the creating backends
aren't around anymore.

> and how do I prevent them from being created again?

An update to something newer than 7.1 might help.  I'm guessing that you
are running into some bug that causes backends to crash every now and
then --- a backend should remove all its temp files at normal exit, but
on crash that doesn't happen.

7.2 and later also have code to auto-remove leftover temp files during
postmaster start, but that code is not in 7.1.

            regards, tom lane