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
Is that the only thing in the logs? pg_stat_tmp problems should not bring down your database. But problems with pg_xlog running out of space certainly can--but they should also be logged.
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.
We don't know how large it is getting! If pg_stat_tmp shares the same partition as pg_xlog, base (as in the default configuration), and pg_log, then any of those things could be filling up the partition, and pg_stat_tmp could just be the canary, not the culprit.
Anyway, you don't need to use a symlink, you could just change stats_temp_directory to point someplace else.
Your best bet is run "du" or something similar to figure out where your space is actually going.
