Обсуждение: cleanup of pgsql_tmp directory

This morning we found out that our filesystem was at 98% and filling
quickly.  We immediately found one query running for a long time and saw
1500+ files in the pgsql_tmp directory we could attribute to it.



I killed using pg_terminate_backend and some space was reclaimed, but the
temporary files from the process are still in the pgsql_tmp directory.



We're on postgres 8.4.9 on centos.   Which process should be deleting these
files and what is normally the delay?  I'm assuming the postmaster process
will eventually delete them, but does the server have to be bounced in order
for this to happen?   It's been 30 minutes since the process was killed.



Also, is there some method of governing the temporary space used?  Our
temp_tablespace parameter is null.



Thanks



Robert

"Robert Klaus" <rklaus@nexgenwireless.com> writes:
> This morning we found out that our filesystem was at 98% and filling
> quickly.  We immediately found one query running for a long time and saw
> 1500+ files in the pgsql_tmp directory we could attribute to it.

> I killed using pg_terminate_backend and some space was reclaimed, but the
> temporary files from the process are still in the pgsql_tmp directory.

Temp files should be deleted during proc_exit processing.  You're sure
you used pg_terminate_backend() and not something more aggressive?  Are
you sure these files actually belong to the process you killed?

> We're on postgres 8.4.9 on centos.   Which process should be deleting these
> files and what is normally the delay?  I'm assuming the postmaster process
> will eventually delete them, but does the server have to be bounced in order
> for this to happen?   It's been 30 minutes since the process was killed.

If a process failed to remove its own temp files for some reason,
there's no other mechanism for getting rid of them, except that a
postmaster restart will run around and clean out all the temp
directories.

            regards, tom lane

You are correct.  I later found two other processes excuting the same query.
Once I killed them all the temp files automatically were deleted.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, February 05, 2013 12:07 PM
To: Robert Klaus
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] cleanup of pgsql_tmp directory

"Robert Klaus" <rklaus@nexgenwireless.com> writes:
> This morning we found out that our filesystem was at 98% and filling
> quickly.  We immediately found one query running for a long time and
> saw
> 1500+ files in the pgsql_tmp directory we could attribute to it.

> I killed using pg_terminate_backend and some space was reclaimed, but
> the temporary files from the process are still in the pgsql_tmp directory.

Temp files should be deleted during proc_exit processing.  You're sure you
used pg_terminate_backend() and not something more aggressive?  Are you sure
these files actually belong to the process you killed?

> We're on postgres 8.4.9 on centos.   Which process should be deleting
these
> files and what is normally the delay?  I'm assuming the postmaster
> process will eventually delete them, but does the server have to be
bounced in order
> for this to happen?   It's been 30 minutes since the process was killed.

If a process failed to remove its own temp files for some reason, there's no
other mechanism for getting rid of them, except that a postmaster restart
will run around and clean out all the temp directories.

            regards, tom lane


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general