We are talking here about a PostgreSQL 8.3 RDBMS. So, no version of pg_terminate_backend () is possible with this version.
Sometimes we have to kill the processes running at the operating system level (kill -9 PID) to solve the problems related to the max_connections value reached. In such cases, we have targeted the long SELECT queries to kill.
As a result, we discovered that our file system is growing at 98% and filling up quickly, displaying over 1500 files in the pgsql_tmp directory.
Some orphaned files are the expected result of this type of maneuver, because temporary files need to be removed during proc_exit processing and the aggressive closing of processes being run is not the best option.
So, to get rid of this "trash", what is our best option:
a) Perform a postmaster restart and expect the RDBMS to work and clean all temporary directories by itself.
b) Stop the postmaster, manually delete the files in $ PGDATA / pgsql_tmp / and restart postmaster
c) Without stopping the server, manually delete the $ PGDATA / pgsql_tmp / files that are older than the current day
Please justify your answers