Sample Header Ad - 728x90

Postgres 8.3 correct deletion of files in pgsql_tmp

0 votes
1 answer
1421 views
We are talking about the PostgreSQL 8.3 RDBMS here. So, no pg_terminate_backend() is possible with this version. Sometimes we have to kill running processes at Operating System level (kill -9 PID) to solve issues related to max_connections value reached. In such cases, we targeted long running SELECT queries to kill. As a result, we found out that our filesystem grows at 98% and fills up quickly, showing 1500+ files in the pgsql_tmp directory. Some orphan files are the expected result of this kind of maneuver, since Temp files should be deleted during proc_exit processing and aggressively terminating running processes are not the best option. So, to get rid of this "trash", what is our best option: 1. Conduct a postmaster restart and expect the RDBMS will run around and clean out all the temp directories by itself; or 2. stop the postmaster, manually delete the files in $PGDATA/pgsql_tmp/ and then, start postmaster again; or 3. without stopping the server, manually delete the files in $PGDATA/pgsql_tmp/ which are older than the current day. Please justify your answer(s).
Asked by dnaranjor (5 rep)
Feb 18, 2019, 09:34 PM
Last activity: Feb 19, 2019, 03:39 PM