The past few days I’ve been encountering the following error, followed by a full db restart and recovery
2013-12-16 07:12:53 GMT LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device
This occurs at a time of moderate load, during the same set of operations each morning.
Interestingly, when I execute this manually at any other time of date, the process completes normally.
I presume that the pg_stat_tmp location is system-wide and likely is not impacted by temp_tablespaces
The root partition, where postgresql is installed does not have a lot of disk available (4GB).
My first instinct here is to symlink pg_stat_tmp against another disk with a little more room to breathe, however I’m surprised that pgstat.tmp would grow to be so large in the first place – possibly there is something else at play here.
I should note that this issue has only recently occurred, no major changes have been introduced.
Thanks for any advice on this.
You can control the location of the file with the parameter stats_temp_directory.
The size is most likely because the directory needs space for >1 copy of the file, since it's written to a separate file and then renamed()d over the existing one for atomicity. That said, even with that it shouldn't grow *that* large, unless you've go a huge number of tables. Certainly not into gigabyte range. So it could be that it's just the first indicator that fires, but that there's actually something else using up your space. But setting stats_temp_directory is a good first way to find that out.