PostgreSQL 8.3 – Correct Deletion of Files in pgsql_tmp

postgresqlpostgresql-8.3

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).

Best Answer

Rather than seeking better ways to suture up your foot, you should just stop shooting yourself in the foot.

All that pg_terminate_backend does is check for permission, then deliver a SIGTERM. You can do this directly by using kill -15 PID rather than kill -9 PID. This will give that one process a chance to clean up after itself before exiting. More importantly, it will not needlessly kill every other process and trigger a crash recovery.

If that process is so wedged that it won't respond to kill -15, then pg_terminate_backend wouldn't help anyway. And in that case, it is probably a bug, and using software that is not 5 years past its EOL would likely help there. If it is wedged like that, then rather than killing it with -9, you could probably just restart the server with pg_ctl restart -mi. It would accomplish the same thing, but would clean up the files for itself.

If you are really only interested in better ways to suture up your foot, then your option 1 should work fine. Option 2 seems pointless, since 1 works.

Your option 3 should also work as long as one day is long enough to avoid any still-running processes. If you remove a file that some process still needed, the worst that happens is that that one process throws an error (which you must already be used to, as a user of kill -9). I would say it is not a "best practice", but since you are running long out-of-support software and routinely using kill -9, the best practice ship has already sailed.

Related Question