Re: base directory size getting increased

On Mon, 2021-06-07 at 21:58 +0530, Atul Kumar wrote:
> On Monday, June 7, 2021, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
> > On Mon, 2021-06-07 at 17:48 +0530, Atul Kumar wrote:
> > > But once I rolled back the query, pgsql_tmp directory it was back to
> > > 87 GBs so please help me in telling how do I clean that 87GB of space
> > > of pgsql_tmp directory.
> >
> > The files in that directory will always be cleaned up when the
> > query that uses the temporary files is done.
> > The backend process ID of the query is part of the temporary file name.
> >
> > It could be that such files are left behind after a crash.
> > If you are sure that the don't belong to a running query (for example,
> > if their modification timestamp is old), you can delete them.
>
> Is there any way to check which temporary file is being used by which query.

As I said, the file name contains the process ID.

> Any query/ view to check it, could you share or suggest ?

This would be difficult with SQL.
You could use "pg_ls_dir" to list directory contents, but remember that
process IDs get reused, so you would have to check if the file modification
time is older than the session start time in addition to checking for
the process ID.

> Is there any way to be safe while deleting such files.

You can shut down PostgreSQL and remove the "pgsql_tmp" directory.
It will be created again when needed.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com