4

I've had a couple of instances recently of being forced to shut down my PostgreSQL 13.10 database uncleanly, and it taking a few hours to come back up afterwards.

In both cases, strace revealed that what the process was doing was stat-ing (maybe lstat, I forget) and then deleting (unlink) files from base/pgsql_tmp. This took hours because there were tens of millions of files in there (which itself is unusual, but probably just a symptom of the issues that forced the unclean shutdown).

My question is: is it just blindly deleting all the temporary files, or does it use some of them in the recovery process? If it's just blindly deleting them, I imagine I might be able to improve recovery time by deleting them more quickly (e.g. moving the whole directory aside, giving the server a fresh one, and then deleting it at my leisure).

Of course, messing with the internals of the data directory is always fraught, but I also have regular disk snapshots, so even in the worst case it's only a day or so of data loss. With that in mind, reducing the downtime from a few hours to a few minutes seems worth a little risk.

1 Answer 1

2

It is absolutely safe to delete all files in the pgsql_tmp of any tablespace (base being the default tablespace). The question is whether manually deleting the files will be slower than PostgreSQL doing it or not.

I recommend that you set the temp_file_limit parameter to limit how much temporary data a PostgreSQL session can write.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.