Thread: 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
Hi Sir,
On Monday, June 7, 2021, Ravi Krishna <ravikrishna3@icloud.com> wrote:
I don’t think there is any old connection connected as there is no process I found from pg_stat_activity.
Is there any other way to trace out such sessions.
Regards
Atul
On Monday, June 7, 2021, Ravi Krishna <ravikrishna3@icloud.com> 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.
>
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?
Hi,
Regards
Is there any way to check which temporary file is being used by which query.
Any query/ view to check it, could you share or suggest ?
Is there any way to be safe while deleting such files.
Is there any way to be safe while deleting such files.
Regards
Atul
On Monday, June 7, 2021, Laurenz Albe <laurenz.albe@cybertec.at> 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:
> 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