Обсуждение: base directory size getting increased

Hi,

I have a query  from which I am trying to export the data into a csv
file(around 10 lakhs record) but when I am doing so the base sub
directory size of data directory is getting increased.

can someone help me in telling 'why base sub directory size is getting
increased'?


my postgres version is 9.6.

Regards,
Atul.

On Mon, 2021-06-07 at 16:11 +0530, Atul Kumar wrote:
> I have a query  from which I am trying to export the data into a csv
> file(around 10 lakhs record) but when I am doing so the base sub
> directory size of data directory is getting increased.
> 
> can someone help me in telling 'why base sub directory size is getting
> increased'?
> 
> my postgres version is 9.6.

Based on the little information you gave us, it could be one of

- temporary files that get created by the query
- you export the data into the data directory

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

initially pgsql_tmp size was 87 GB and after execution of query it was
started increasinng beyond 87GBs so yes, pgsql_tmp directory size is
getting increased while exporting  data into csv file.

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.







Regards,
Atul








On 6/7/21, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Mon, 2021-06-07 at 16:11 +0530, Atul Kumar wrote:
>> I have a query  from which I am trying to export the data into a csv
>> file(around 10 lakhs record) but when I am doing so the base sub
>> directory size of data directory is getting increased.
>>
>> can someone help me in telling 'why base sub directory size is getting
>> increased'?
>>
>> my postgres version is 9.6.
>
> Based on the little information you gave us, it could be one of
>
> - temporary files that get created by the query
> - you export the data into the data directory
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>

>
> initially pgsql_tmp size was 87 GB and after execution of query it was
> started increasinng beyond 87GBs so yes, pgsql_tmp directory size is
> getting increased while exporting  data into csv file.
>
> 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.
>

Ideally applications which creates temp table should drop it when no longer needed.
When not dropped, they get dropped automatically when the session which created
them terminates.  Looks like in your case both are not happening.

Which is the oldest session still shown as connected?

On Mon, 2021-06-07 at 17:48 +0530, Atul Kumar wrote:
> initially pgsql_tmp size was 87 GB and after execution of query it was
> started increasinng beyond 87GBs so yes, pgsql_tmp directory size is
> getting increased while exporting  data into csv file.
> 
> 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.

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

On Mon, 2021-06-07 at 21:58 +0530, Atul Kumar wrote:
> On Monday, June 7, 2021, Laurenz Albe <laurenz.albe@cybertec.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